Does the thought of starting a new QuickBooks Desktop data file or moving to QuickBooks Online (QBO) 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 leaving their product! Do 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 cleanup. 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 change them!
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, W2s/1099s, and reduced productivity 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 QBO, there are many programs on the market that can be used to avoid manually entering journal entries. Including:
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. I avoid them.
Tip! Microsoft Dynamics can import iif files for the COA, Vendors, and Customers.
Migrating QuickBooks QBDT to QBDT
My favorite trick when starting a new desktop data file is to try and salvage as much of the old file as possible and use it for the new file. It doesn’t always work but is worth the effort.
Strip out all transactions and start with a shell. There are many hours to be saved since you don't have to set up user profiles and dashboards, employee records, payroll item codes, memorized transactions and memorized reports.
I put a copy of the data on my fastest machine. Never work on data over a network. Remove all transactions by following the steps under “Condense Data Utility” within QuickBooks. Once the transactions are removed, it is possible to delete unnecessary accounts and names. I had one file take over 15 hours. I thought it was locked up, but I left it running overnight and was amazed when it finished!
Tip! Re-sort all lists when done.
Warning! All payroll transactions will not purge unless they are in a prior year. Either wait until 1/1 to purge the file copy or try changing the operating system date.
Migrating QuickBooks QBDT to QBO
Tip! Editing the COA, Customer and Vendor lists in QBO can be dreadful. One trick, after cleaning the lists in QBDT, is to use the export list feature to export as iif files. Use Excel to further review, edit, or delete names. Import the lists into a fresh QBDT file. Use this file to migrate to QBO.
Migrating QuickBooks to programs that require separate debit and credit columns
If your exported amounts are in a single column but your target software requires separate debit and credit columns, this is an easy fix in Excel. First, create two new columns titled Debit and Credit. Let’s say that your Trial Balance figures are in column G and you need them broken out into columns E for Debits and F for Credits.
In the Debit column E, add this formula and paste it down. =IF(G2>0,G2,"")
In the Credit column F, add this formula and paste it down. =IF(G2<0,G2*-1,"")
Tip! You will need to do this when migrating to NetSuite or Microsoft Dynamics.
Migrating QuickBooks Import Plan Re-cap
- Verify that the exporting COA and the new COA are compatible.
- Replace system created A/R and A/P accounts with current asset and liability accounts.
- Import the ending Trial Balance as of the oldest year desired into the target software.
- Export and prepare each subsequent year’s monthly journal entries and import as required.
- Once you Import the final month’s trial balance, close out the temporary A/R and A/P accounts to a clearing account as of the last day.
- Following your new software’s instructions, enter open A/P bills and credits and A/R invoices and unapplied credits, posting to the clearing account as of the last day.
- For bank reconciliation purposes, also using the clearing account, adjust the bank accounts to the “cleared” bank balances as of the last day and enter outstanding checks and deposits in transit.
- Finally, compare financials from both systems to test, test, test!
Tip! Test, Test, Test, Test the results! Did I say test?
Need Expert Help?
Sara Laidlaw-Woodruff:
Expert with QuickBooks Desktop.
Owner of Accounting Services Bureau Inc Specializing in nonprofits and audit prep.
The developer of TBX Trial Balance Exporter for QuickBooks Desktop.
Ernest Cook:
Expert with QuickBooks SDK, QQube, and Power BI.
Owner of http://www.BetterIdeaGroup.com
Hector Garcia, CPA:
Expert with Intuit QuickBooks.
Owner of hectorgarcia.com and QuickBooks-Training.net
Mario Nowogrodzki, CPA/CITP:
Expert with Intuit QuickBooks, and Microsoft Dynamics 365 for Financials.
Owner of http://www.mendelsonconsulting.com/
