Converting from Xero to QuickBooks Online
There are many reasons a company or its bookkeeper or tax preparer/accountant may want to convert a set of accounting books from the Xero platform to the QuickBooks Online (QBO) platform.
I will make no attempt here to discuss the possible reasons for or against doing so. I have done many of these conversions, as I have also done in the opposite direction. The only purpose of this article is to discuss the basics of doing the conversion. Whether or not it is the right thing to do is a discussion for another day.
When converting a company file from Xero to QuickBooks Online, the first thing to decide is whether the historical transactions will be part of the new file and if so, how far back to go with them. It is certainly convenient to have historical transactions in the file in case a question comes up, rather than having to search an archived record, scanned documents, or worse, paper records. However, including them also increases the cost and effort required to achieve the conversion. How much additional work and cost depends on many factors like the number and variety of transactions, number of accounts with cash transactions, tracking of inventory, payroll, and so on. Only someone with experience in converting the kinds of transactions in a company file can advise about these issues.
Unless the conversion is being done at the beginning of the fiscal year, there will undoubtedly be some transactions to convert from the Xero file to QBO. Before converting, save a copy of the balance sheet and income statement reports (Xero’s and most of the accounting world’s name for the profit and loss report) for all dates, using both cash and accrual methods and also the detailed Accounts Receivable and Accounts Payable reports. These will be used to verify the conversion after completion. Also, it is a good idea to lock the Xero file for dates before the conversion date so that no one makes changes after you begin.
Step #1 – Convert the Xero Chart of Accounts
The first step in the conversion process is to get the chart of accounts (COA) set up in QBO to match the one in Xero. Some will find it easier to create accounts in QBO to match those in Xero, but there is an easier way.
Export the COA from Xero by going to Settings>Chart of Accounts and then clicking on the “Export” button which will download a CSV (comma-separated value) file which can be opened with Excel or any spread sheet program for editing. The first three columns of the file correspond to the first three columns of the required format for importing to QBO, making it very convenient.
Account numbers in Xero are three digits and no subaccounts are allowed, but one common way to group accounts under a heading account is to add digits after a hyphen, e.g. 620-01, 620-02, etc. Since subaccounts can’t currently be imported into QBO anyway, leave them that way, making it easier to make them subaccounts later in QBO. I recommend making NO changes to account numbers, to make transaction imports easier in subsequent steps. So, in the Xero COA file, delete all columns after the first three.
Only four columns of information on accounts can be imported into QBO: Account Number, Account Name, Type, and Detail Type. Those are the proper headings that should appear in the first row of the import file, so make those changes. Account names can generally be left alone and will import just fine. Account Types largely match up as well, but it is helpful to sort the list by type and check the list against the possible account types in QBO and make any changes needed to match them up.
It is also helpful to add proper account detail types from QBO in the 4th column. It isn’t required, as QBO will
let you choose them from a dropdown list during import if you don’t, but choosing them from a dropdown for dozens or even hundreds of accounts can be very tedious. Save the changes to your Xero COA file and then import it to QBO.
Any account numbers, account names, or account types/detail types that don’t match up will be shown in red so you can make changes to import them. Once the import is complete, you might be inclined to do some cleanup of the QBO COA at this point. Resist that urge! It should be done AFTER import of transactions.
Step #2 – Import Customers and Vendors into QuickBooks Online
Next, the customers and vendors have to be imported. This can be a lot of work when converting from Xero. This is because there is no distinction between customers and vendors in Xero. They are all grouped together as contacts and can be both a customer and vendor simultaneously. For this reason, I recommend exporting them all as one and then separating them into vendor and customer lists yourself. They can be on both lists for importing, but the name will have to be altered in the list imported second or import will fail.
There is a lot more information in the exported Xero file than you can import into QBO. For example, the Xero file includes both postal and shipping addresses, but only one address can be imported to QBO. Some of it will also have to be altered as to format, such as combining four address lines into one, combining first and last names into one field, and more.
Trying to cover all the potential changes needed and how to do them in Excel or another spreadsheet program is
beyond the scope of this article.
The place to start is to download the proper format for import to QBO by choosing “Import Data” from the Gear Icon>Tools and then choosing the type of import you are doing. Under the field where you can enter the file name for import, click on the link to download a sample file.
I strongly recommend against using the open balance amounts in the import files. This only complicates things, as any open invoices and bills should be imported later and will create those balances.
Step #3 – Import Products and Services into QuickBooks Online
Next is importing products and services. This should be done, whether or not inventory is being tracked, if any items are listed on the “Inventory” page in Xero. Just like in QBO, items can be and are used in various types of transactions like invoices and bills and must be in place before they are imported. Like customers and vendors, the export file from Xero will need a lot of manipulation to match the format for import into QBO and that format can be found using the same steps as outlined above.
If inventory is being tracked, then the import list could also be very long and require a lot of work to prepare. And in this case, for those items with inventory in stock, make sure that the purchase cost and quantity on hand are correct. This will establish the inventory value in QBO, though one or more journal entries will be needed to offset the resulting opening balance entries when you are done.
Also, be sure during the import process to make sure that the “Sell” and “Buy” check boxes are properly selected for each item and for inventory items that the “Track” check box is checked. All items also need to have the inventory, non-inventory, or service selected.
Step #4 – Import Invoice and Bill Transactions from Xero
Once that is complete, the transactions can be imported to QBO. Up to 500 invoices or bills can be exported at a time. If there are more than 500 of either in the file, then I suggest splitting them up by date. These transactions cannot be imported directly into QBO, except with the use of a third-party program.
There is more than one available to accomplish this, but my experience is with Transaction Pro Importer. It is a very powerful importer of all transactions to which Intuit has allowed access via the API. Details of how to use it and how to reformat the Xero transactions to utilize it are beyond the scope of this article. It should also be used to import the payments made to both invoices and bills.
Step #5 – Import Payroll Transactions
If there are payroll transactions in the file, then it depends on the provider and how the transactions are posted to decide how to handle them. For many providers, one simple way is to import them as bills due to each employee (create a vendor with the employee’s name). This allows allocation to all the appropriate expense and liability accounts. They can then be paid as the bank transactions are imported.
Step #6 – Import Bank Transactions
Finally, the bank transactions can be imported. However, this step is also the hardest. This is because, like many accounting programs, there is no easy way to export transactions with information on both the credit and debit sides listed together. Reports can be generated of all transactions in a bank account or all transactions in a target account, but neither report can be configured to include the other side of the ledger. They need to be on the same line of a csv or spreadsheet file in order to import them easily.
All general ledger transactions can be exported, but they are in a format that requires a great deal of manipulation to get them ready for importing with Transaction Pro Importer. Instead, I have found that using the power of the bank feed function in QBO is often more useful for this step, and also with Xero and some other accounting platforms as well. It allows doing classifying of hundreds of transactions at a time, and also matches up things like bill and invoice payments.
Along with the powerful sorting feature, I have found it better than the alternative of extensive work in Excel to prepare the transactions. Therefore, I recommend exporting all the transactions in each bank account and saving them as CSV files for import as if they were bank downloads. However, if you have thousands of transactions to transfer, editing and preparing them in Excel is probably better.
If there are a large number, then break them up into files of less than 1,000 transactions each. That is the limit for import of a file to the bank feeds. From there it is just a matter of properly classifying them and accepting them into their respective registers. This should be second nature to anyone who uses the bank feeds daily.
Step #7 – Verify Results
With that step complete, the file should be complete. Before verifying, go to the Chart of Accounts and edit those accounts that were grouped together by hyphens and digits after them to be subaccounts of their header account. Then I recommend two steps to verify that the QBO file matches the Xero file.
First, reconcile the bank accounts. Whether to do this in one big reconciliation or in smaller pieces is a personal choice. I usually do them one year at a time, but if problems show up then I break it down by month. This should be made much easier if the Xero file has been properly maintained with reconciliation reports saved every month for each bank account.
Once all is reconciled, then compare the balance sheet and profit and loss reports for all dates using accrual and cash methods in QBO with those you saved earlier from Xero. Also verify that the detailed AR and AP reports match. Assuming all that went well, then the conversion is complete.
There are some special situations that are not covered here, as Xero has some other features not covered like tracking, which is an additional classification level similar to class tracking in QBO. Of course, there are a lot of QBO settings that will also need to be configured as they are for any new company file. Forms will need to be customized, any third-party apps connected, and bank feed connections set up. And users will need to be set up to access the file.
Hopefully, this article will help those considering making the switch from Xero to QBO to understand the issues and the difficulties. It is certainly possible to accomplish, if one has the necessary skills and patience to do it right.