Does the thought of starting a new QuickBooks Desktop data file or moving to QuickBooks Online give you nightmares? Is it time to graduate to a mid-market ERP program such as Sage-Intacct, Microsoft Dynamics 365, or NetSuite? If so, maybe I can help you sleep better by providing some tips and hints for migrating QuickBooks history.
Why Is Migrating QuickBooks So Painful?
In a perfect world, every accounting program would follow a standard data structure. But, no such luck. Different industries require different data structure and levels of complexity. Companies outgrow features and details offered in their current software, so chances are, the movement is not lateral when it comes to design and structure. Finally, software developers have zero incentive to provide an easy way to export data when someone is leaving their product! Can you blame them?
Successful Accounting Migration Involves Planning and Being Aware of Pitfalls
One must decide between trying to export/import historical transactions or monthly trial balances:
- Transactions. I rarely recommend attempting to bring over complete transactions such as A/R and A/P. It adds a level of complexity involving matching vendor and customer names, sales codes, and linked transactions such as Invoice/Payments and Bills/Bill Payments. Again, the data structure would need to be almost identical.
- Monthly Trial Balances. The cleanest and least stressful way to migrate history is exporting monthly trial balances and importing those as monthly journal entries in the target software.
Is the Data Clean? Are the Monthly Totals Valuable for Comparison Reporting?
This probably goes without saying, but make sure that the client understands that garbage in equals garbage out. Are the months closed and reconciled? Will they even use comparison reports? Have adjusting entries received from their accountant been entered and the year-end trial balances reconciled to the tax returns?
Excel Is Your Friend
The go-to intermediate holder of data is Excel, which allows you to edit data before importing.
Tip! It is painful to attempt an import and then find that there is an account unavailable to receive the journal entries. So, plan, plan, plan!
Bring Forth the Almighty Chart of Accounts!
Consistency of the chart of accounts (COA) between the old and new systems is imperative. Each figure in the trial balance journal entry needs to land in the right place! Carefully plan your new chart of accounts. Even if you adore your current chart, it can probably use some clean up. Document new accounts and/or changes to the accounts with a conversion table in Excel. Trust me, your tax accountant and auditor will thank you next year.
Your accounts may have simple one to one relationships or many accounts to one account.
A more complex one to many accounts relationship requires spending time analyzing transactions in each account to determine how they should be split up.
Edit the COA in QuickBooks First
The beauty (or scary part) of QuickBooks lists is that every name has a hidden underlying reference number. This is what enables us to edit account numbers, names, and descriptions. These “cosmetic” changes are retroactive for all activity. Accounts and other list names can be merged by naming them the same. Are there Other Expense type accounts that should be up with the regular Expense accounts? Current assets listed as long term? Has the “Insurrance” account been misspelled for years? Now’s your chance to correct such issues!
Tip! Use a copy of the master data file when migrating and make all changes there.
The Best Time to Tackle Migrating QuickBooks
Year end is the most obvious time to start a new accounting program. But January may not be the best time if staff are busy with the year-end closing, auditors, and W2s/1099s, as reduced productivity could result while learning a new system. Find a slower time of the year and implement towards year end. Design, setup, and history migration should be done well before the go live date. Once you make it to January, import the last month’s trial balance and add open A/P and A/R items. Adjusting entries can be added as the old set of books are closed.
Special GL Account Issues
Remember that Accounts Payable transactions require vendor names and Accounts Receivable requires customer names. This may require you to import history into regular current asset and liability accounts. Likewise, for Sales Tax Payable, as each transaction requires a sales tax vendor name.
Where to Find the Figures for the Monthly Journal Entries
A monthly period change trial balance can be compiled in Excel manually by recording the change column on a month to month Balance Sheet along with the monthly P&L or P&L by class, if needed. If this proves too time consuming, find a utility such as TBX Trial Balance Exporter to export the monthly activity.
What Else Do You Need for the Journal Entries?
Determine the required fields for importing journal entries into the target general ledger. Are there any field width requirements? Each monthly journal entry will need a unique Reference Number and Transaction Date. Other fields include a Memo/Description and amounts (Debits as positive/Credits as negative figures).
Tip! The QuickBooks Desktop (QBDT) journal entry Ref# cannot exceed 11 characters
What Tools Can Be Used for Importing?
When migrating to a larger system, an implementation team is usually assigned to handle this for you. Your job is to prepare and submit the data. Some companies even provide templates in Excel.
If starting a new data file in desktop or moving to QuickBooks Online (QBO), there are many programs on the market that can be used to avoid manually entering journal entries. These include:
These tools do have a cost. Free options include configuring the excel file as an “. iif” file. There are YouTube videos available and instructions from Intuit to assist you.
Caution! An iif (Intuit Interchange Format) file has zero data checking when importing into QuickBooks Desktop. I avoid them.
Tip! Microsoft Dynamics can import iif files for the COA, Vendors, and Customers.