Let's say that your transaction data is in Quicken Home & Business. You want to migrate this to GnuCash. We will first walk through the steps of performing this migration. Then we will analyze the pros and cons of such a migration:
- Prerequisites: You have already exported the data from Quicken. You have a set of QIF files. While exporting, please remember that you can have only one category per file.
- Prerequisites: Create a new account hierarchy in GnuCash first. Save it with the name CAMS.
- Prerequisites: Make a backup of the CAMS file. If you want to fine tune your migration, you may have to restart from this backup file a couple of times.
- Prerequisites: Set the following preferences. In the General tab's Files pane, set Auto-save time interval to 0. This means data is not saved automatically. If there are any problems, this will allow you to exit without saving and start over. In the Online Banking tab, the QIF Import pane, check Show Documentation.
- To start the import, select from the menu File | Import | Import QIF. You will see the QIF Import information dialog. It will show that only when you click Apply at the end of the series of steps the data will be imported. Click Forward.
- The Select a QIF file to load dialog will open. Click Select and find the folder where you have stored the QIF files. Select the first file to import and click Import. Click Forward.
- You will see the QIF files you have loaded dialog showing the file you have loaded, as shown in the following screenshot:
- Click the Load another file button and load the next file. Continue until you have loaded all the files in your set. Once you have loaded the last file, click Forward.
- You will see the Accounts and stock holdings dialog telling you that many new accounts will be created if it cannot find a match. Click Forward.
- The Match QIF accounts with GnuCash accounts dialog will open, as shown in the following screenshot:
- If you find that some of the matching is not right, you can click on that line. The Change GnuCash Account button will become enabled. You can click that and select a different GnuCash account as a match. If some of the transactions end up in the wrong account, you can individually edit those transactions to the right account, after the import. Click Forward.
- You will see the Income and Expense Categories information dialog. Click Forward.
- You will see the Match QIF accounts with GnuCash accounts dialog again. Click Forward.
- You will see the Payees and memos information dialog. Click Forward.
- The Match payees/memos to GnuCash accounts dialog will open. Click Forward.
- The Enter the QIF file currency dialog will open. USD (US Dollar) will be selected by default. Click Forward.
- GnuCash will import the data into a temporary staging area. You may see the progress bar briefly. The Update your GnuCash accounts dialog will open. This dialog has the all important Apply button that will actually import the data into GnuCash. Click Apply. You should now see the Account Tree window with all the imported transactions, as well as any newly created accounts, as shown in the following screenshot:
- Once the transactions have been imported successfully, don't forget to Save.
Now that you have seen the steps for performing one such migration, let us look at the different ways of handling such a migration and their pros and cons.
Migration of accounting data from one software to another is a difficult process for the following reasons:
- The extent of support for exporting data varies not only from one software to another, but also from one version to another.
- There is not a single common data standard across different accounting software.
- In the absence of a well defined format or process, you are left to research and try out various ways until you find one that works.
- You may have to locate, evaluate, and use third-party open source, freeware, or commercial software tools for converting from one data format to another.
- After importing, you have to rearrange your account tree.
Keeping in mind these difficulties, you may be able to meet your business requirements by archiving the old data and starting a fresh implementation of GnuCash.
The best time for archiving your old data and implementing GnuCash afresh is the beginning of the year. So, plan on continuing to use your existing software till the end of the current year. Side by side, you can keep working on trying out GnuCash with sample transactions and tweaking your account tree to better suit the needs of your business based on what you have learned about what works and what needs to be improved. Then when the New Year rolls by, you can implement GnuCash and finish up the previous year's transactions in your existing software and archive it.
We list some circumstances, as examples, that may justify putting in the effort to perform a migration of the old data from your existing accounting software to GnuCash:
- You started on another application a few months ago and want to switch to GnuCash for whatever reason.
- There are compelling business reasons why you cannot wait till the end of the year and you have to switch in the middle of the year.
- You have to have reports comparing the same period in prior years and they must be run in the same software so that your comparisons are apples to apples.
- You are evaluating GnuCash. Migrating is the shortest route to get some real data in for a quick evaluation.
- Your older application will not work on the newer operating system you are upgrading to. So, archiving is not a viable option for you.
At the end of all this assessment, if you are convinced that you have a strong business case for attempting to migrate, take a deep breath, roll up your sleeves, and get ready to get your hands dirty:
- You will be required to do web research to see what other people in your situation have done, what worked for them, what didn't, and what are the gotchas to look out for.
- You will search for, find, evaluate, and select format conversion tools. Some of them may be open source or freeware and others may be commercial software.
- You will have to back up your data and run several trials to select the process that gives the best results.
If you are tech savvy, that is great. If not, take your "trusted techie" out for lunch. This may be a friend, cousin, consultant, or a fellow small business owner you have an informal mutual cooperation agreement with. At some point, if you run into trouble, you may need to call in the troops. You can also get help in the GnuCash User Forum (http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html). Search the forum first to see whether your question has been already asked and answered. If not, post a new message clearly stating what system you are using, what you are trying to do, which step failed, and what the specific error is. And, of course, don't forget to thank the volunteers who not only provide great software but are very prompt to respond to users' questions as well.
You are using a spreadsheet to maintain your accounts. These are getting more complicated over time and seem to be requiring more and more of your time. It now appears burdensome to keep track of your accounts through this manual method. You have also come to the conclusion that your business could benefit from modern accounting software features such as bank reconciliation, scheduled transactions, sales tax, income tax statements, and check printing.
You have two options to import that data into GnuCash:
- Save as CSV and then import into GnuCash.
- Convert to QIF using a freeware Excel to QIF converter, such as XL2QIF (http://xl2qif.chez-alice.fr/xl2qif_en.php) and then import it into GnuCash. For converting from Calc, you can try a converter like Calc to QIF (http://xl2qif.chez-alice.fr/calc2qif_en.php). One advantage of using QIF is that GnuCash will create your accounts automatically from the information in the QIF files. The QIF format will also import the Notes field in addition to the Description field.
Make a trial import with each option and see what works best for you.
Microsoft Office Accounting was formerly known as Office Small Business Accounting (Office SBA for short). It allows you to export the data to an Excel file. Once the data is in an Excel file, you can follow the same process as outlined in the section on migrating from Excel.
The level of difficulty of migrating from Quicken1 depends largely on whether your version of Quicken allows you to export to a QIF file.
If your version of Quicken allows you to export to a QIF file, then you have the following direct path to bringing that data into GnuCash:
- Export your transactions from Quicken to a QIF file.
- Import the resulting QIF file into GnuCash.
An additional advantage of this approach may be that you can export the entire file and its categories in one pass.
If your version of Quicken does not allow you to export to a QIF file, then you can try the alternative approach below:
- Create a report of each Category.
- Save or print to file the report to a spreadsheet format. You may have to rename the print to file report from PRN to CSV.
- Convert the data to QIF format using a freeware or commercial tool.
- Save the newly created QIF file.
- Repeat the above steps until you have saved all of the accounts you want to import into GnuCash.
- Use the GnuCash import QIF feature to import all the saved QIF files in a single import step.
After the import is completed, you can rearrange your accounts so that they are all subaccounts of the five main accounts namely, assets, liabilities, income, expenses, or equity. You can do this from Edit Accounts by changing the Parent Account. If there is no equity account you can create one.
QuickBooks doesn't support exporting to QIF format. Instead, it allows export to the IIF format. So, you have the following options to export data from QuickBooks:
- Export to IIF and then convert it to CSV.
- Another approach that has worked for some people is as follows. Create a report of the transactions. Print the report but use the print to file option. Set the file type to Excel before printing. Rename the extension of the resulting file from PRN to CSV.
Then you can either import the CSV data directly or convert the CSV to QIF using a freeware or commercial tool.
Here is a list of frequently asked questions about exporting from other accounting software and our answers:
- Q: I can't find the Export menu item under the File menu. Help!
- A: In some of the software, in order to export, you must be in single user mode and have Owner, Office Manager, or Accountant access privileges.
- Q: Even after changing my role and trying single user mode, I am not able to find a way to export. What now?
- A: You may have to create reports and save them to QIF or Excel format.
- Q: I have created a report. However, there is not a way to save it to QIF or the Excel format.
- A: In some of the software, you have to print to file. In order to do this, print the report. However, select print to file and choose the appropriate format. When you go through this route, you may have to rename the extension of the resulting file from PRN to CSV.
Here is a list of frequently asked questions about migration to GnuCash and our answers:
- Q: I am trying to import a QIF file. I get an error message saying "QIF file load failed:File does not appear to be a QIF file". What could be wrong?
- A: Make sure the file you are trying to import is a QIF file.
- Q: My QIF import appeared to go well. The transactions seem OK. However, my account tree is all messed up. Should I start over?
- A: In migration, your primary goal is to get the transactions into GnuCash. If that is successful, there is no need to start over. You can rearrange the account tree by editing each account and changing its Parent Account, as needed.
- Q: In the QIF file, I made sure to export a 'memo' field, but that is not to be found in GnuCash after import. What do I do now?
- A: That will be imported into the Notes field, which is normally not visible in the GnuCash account register. You can select the View | Double Line mode to see this.
- Q: What is the best way to handle split transactions?
- A: You can leave split transactions, such as loan repayments, which have both interest and principal, in the Imbalance account. After the import is completed, you can open the Imbalance account and edit each transaction to send it to wherever it belongs. In short, you can use the Imbalance account as a To-Do list.
- Q: I was able to export from Quicken only one category at a time. Now I am left with a number of QIF files. What is the best way to import them?
- A: If you have multiple QIF files, make sure to import them all together, in one run. Otherwise you will find duplicate transactions or mismatched transfers.
- Q: Can I use the importer to create an empty account hierarchy?
- A: No, the QIF Importer cannot import an empty account tree. It is designed to import transactions. As part of importing transactions, it will create the accounts necessary. However, you cannot use it to import an empty account hierarchy alone.
- Which of the following formats will not only import the transactions, but also create the matching accounts?
a. QIF
b. CSV
c. IIF
d. OFX