QuickBooks Small Business

Tips for Migrating QuickBooks Desktop

Written by Sara Laidlaw

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.

Migrating QuickBooks Desktop

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.

About the author

Sara Laidlaw

Sara Laidlaw, owner of Accounting Services Bureau Inc., specializes in nonprofits and is the developer of TBX Trial Balance Exporter. By the mid 1980s, she was an accountant by day and restoring exotic cars by night. When informed that Price Waterhouse was closing her write-up department, she bought it. When challenged by a shipping company to find a program for International Longshoremen's Association (ILA) payroll, she wrote one and started a payroll company. Somewhere in her accounting journey, Sara became known as the local nonprofit guru, leading to nonprofits becoming 90% of her client base. With remote access, her clients are all over the US. When told that QuickBooks doesn’t export a trial balance by class or period, she wrote a utility that does just that. Lesson? Just don’t tell her that she “can’t” and get used to hearing “why not?” Sara’s purpose and passion lies in helping clients improve business processes in the most efficient and painless manner. She’s valued for her no-nonsense, down to earth approach to solving problems.