LWN: Comments on "Escape from QuickBooks (with data in hand)"

Escape from QuickBooks (with data in hand)

vivekgani — Wed, 22 Mar 2023 15:41:02 +0000

I've recently made some improvements to both the instructions and scripts based on my experiences with quickbooks 2022 & 2023 to migrate to GnuCash in https://github.com/erikmack/qb-escape .

Notably, I found the exported data in the 'find all' approach in the article difficult to work with and the script was causing lots of imbalances (split and account values were sometimes mixed, relying on just the 'amount' value didn't work in many cases). Instead switched to an approach that uses the 'Journal' instead which had more consistent data.

Thanks to Jonathan Corbet, and the comments by jason.s and erikmack on instructions and setting up a github repo.

Escape from QuickBooks (with data in hand)

Izwebusr — Sun, 08 May 2022 02:26:25 +0000

Hi:

Thank you very much for publishing these scripts.

I am using these to migrate from QB to Gnucash. I have about 111000 transactions, over 22 years.

I have exported the lists and transactions from QB. I have also installed Python and the scripts. However, the scripts produce an error because I am stuck on how to install the Python Bindings. I do not know what needs to be done to install them. This documentations page:

https://wiki.gnucash.org/wiki/Python_Bindings#How_to_use_...

is not too helpful. Or, at the least, not easy to understand for me.

Is there any documentation someone can point me to?

Also, would it be a good idea to try to find a consultant to give me a hand with this?

Thanks!

Escape from QuickBooks (with data in hand)

lragan — Mon, 07 Mar 2022 02:26:59 +0000

My book in QuickBooks is not that large, so my method may not work for all, but here goes. I use GnuCash 2.6.19 in linux.
I was hanging on to QuickBooks 2008, which is not subscription. Giving up my Windows 7 machine, I discovered that I was, as I suspected, riding a dead horse. (I have switched to Linux for all but tax prep software. I keep a Windows 10 machine for that purpose, and occasional interaction with something that someone sends me, but for the most part, like a housecleaning service, I don't do Windows!) But, I was able to write out an account list to a CSV file. After fumbling around a bit in the import function of GnuCash, I decided to follow their format precisely. It is revealed in the import process as a "custom" import option. Not understanding the symbols that make up the command, I simply took note of the field names.
Then, using a spreadsheet program, I rearranged the CSV file into the format of the import option, adding blank columns where needed, using their field names in the first row.
After importing, it was necessary to enter the balances from my CSV file (GnuCash, understandably, does not import the balances). This was done by a series of simple cut and paste operations. It was then necessary to edit the accounts into the hierarchy that I needed. I took this opportunity to delete accounts that I no longer used or needed (one can always add an account using GnuCash if necessary).
When done, the reports work to show balance sheet and income statement. I have not used the others yet, but suspect they work, too. I entered one month's data (January 2022) taking off from the year end of QuickBooks in 2021, and all reconciles nicely. When one enters the balance for each account, GnuCash puts the compensating balance into an "Unbalance" account. If all is done correctly, this will go go zero when the last data is entered.
This is, I admit, a "brute force" approach, but for me, I feel certain that is was less work than writing code to do the job. For a more complicated business than mine, that might not be the case. I can now retire my ancient Windows 7 machine and let QuickBooks rest in peace...
Color me happy with GnuCash, and grateful for all who have contributed to it.

Escape from QuickBooks (with data in hand)

pyz01 — Tue, 05 Oct 2021 14:12:51 +0000

All -- not sure if this will help -- but this was taken from the QB site. It shows how to export specific data from QB directly to CSV:

Export CSV files
You can export a list as a CSV file if you don’t have MS Excel installed on your computer or if you want to view and edit your data using a different format.

Customers and Vendors
Open the Customer/Vendor Center.
Select the Excel drop-down, then choose:
Export Customer/Vendor list if you want to export customer/vendor data such as name, balances and contact information.
Export Transactions if you want to export transactions (either by name or transaction type).
In the Export window, choose Create a comma separated values (.csv) file.
Select Export.
Assign a file name, then choose the location where you want to save the file.
Locate, open, and edit the file as needed.

Items
Go to the Lists menu, then select Item List.
Select the Excel drop-down, then choose Export all Items.
In the Export window, choose Create a comma separated values (.csv) file.
Select Export.
Assign a file name, then choose the location where you want to save the file.
Locate, open, and edit the file as needed.

Reports
Open the report.
Select the Excel drop-down at the top of the report.
Select Create New Worksheet.
On the Send Report to Excel window, select Create a comma separated values (.csv) file.
Select Export.
Assign a file name, then choose the location where you want to save the file.
Locate, open, and edit the file as needed.

Have not tried these yet -- but I am going to attempt to this weekend to see how far I can get in exporting QBooks data to GNUCash directly using CSV files. Will let you know if I am successful.

Escape from QuickBooks (with data in hand)

erikmack@gmail.com — Sat, 01 May 2021 13:44:50 +0000

Thanks for this great article and these scripts. I have succeeded in importing data to GnuCash for my nonprofit.

The customizations I required I've pushed to GitHub, in case someone else finds them useful: https://github.com/erikmack/qb-escape

The customizations include:
- Support python 3
- Open the file in the new way
- Support account parameters for Placeholder, Hidden, Description, and Code
- Match splits to accounts by account code not name

My organization uses account codes so it's useful to import them and use them during the import.

Thanks again!

Escape from QuickBooks (with data in hand)

raygor — Mon, 18 Jan 2021 20:09:13 +0000

Thank you! I (re)discovered the git://git.lwn.net/qb-escape.git link 10 minutes after I hit submit.. :-/

Additional request for clarity, please - can you share an example of how you used the remap.py map file?

I am hopeful that this will help with QB losing the hierarchy of e.g.

Liabilities -> Credit Cards -> Card1
Liabilities -> Accounts Payable-> AP

Versus the current import where the middle tier is lost and everything is flattened under e.g. Liabilities.

Liabilities->Card1
Liabilities->AP

Thank you for publishing these scripts!

Escape from QuickBooks (with data in hand)

corbet — Mon, 18 Jan 2021 16:12:45 +0000

Those modules are in the repository with the rest of the code...

Escape from QuickBooks (with data in hand)

raygor — Mon, 18 Jan 2021 15:56:44 +0000

@Jonathan Corbet & Jason.s -

I seem to have missed some package dependency following your (and OP's) instructions. Which packages are iif and remap loaded from?

import iif
import remap

python3 qb_iif_to_gc.py -c -r ~/Desktop/2021-01\ foo.iif
Traceback (most recent call last):
File "qb_iif_to_gc.py", line 16, in <module>
import iif
ModuleNotFoundError: No module named 'iif'

Escape from QuickBooks (with data in hand)

jason.s — Tue, 06 Oct 2020 15:57:42 +0000

I just spent the past few days using these wonderful scripts to move all my books over to GnuCash 4.2 from QuickBooks Pro 2017, and to return the favor to the open source community, I thought I might help with a simple set of steps, updated for use in 2020:

Escape from QuickBooks to GnuCash (with data in hand), in simple steps (moving one company's book):
***The following was done moving from QB on Windows to GnuCash on MacOS***

Export your QuickBooks data:
1. Open QuickBooks and the Chart of Accounts
2. Check "Include inactive" at the bottom to show all inactive/hidden accounts
3. These scripts require that the accounts all have different names. Edit all accounts to be this way (double check subaccounts that may have the same name as other subaccounts). You can add numbers to the front of each to make them all uniquely named.
4. Remove forward slashes / from all account names (the scripts tripped up on this).
5. Export your Chart of Accounts and lists. Go to File > Utilities > Export > Lists to IIF Files... Not sure if the scripts can support all lists, but I believe it worked when I checked Chart of Accounts, Customer List, Vendor List, and Other Names List. Click OK and save the IIF file somewhere.
6. Determine when the EARLIEST transaction entry was ever made. If you don't know which account has the first transaction of all time, come up with a date that is definitely before when you started using QuickBooks.
7. Export all your transactions for all accounts of this company/book. Go to Edit > Find... Select the Advanced tab at the top. Under Choose Filter, scroll down and select Date. Immediately to the right make sure All is selected in the dropdown menu and specify the From and To dates with From being the date you determined in the previous step (before the first transaction) and To being today's date.
8. Click the Find button at the top right. In the resulting list below, scroll down to the very bottom to make sure that the earliest transactions are listed. If you believe all transactions are shown, click on the Export... button on the right. In the Export dialog, select "Create a comma separated values (.csv) file". Click Export below and save the CSV file where you saved the IIF file.
9. Copy these two exported files to your other computer with GnuCash (not necessary if you're using GnuCash on the same computer as QB).

Import your data into GnuCash:
10. On the computer with GnuCash, either use GIT to clone the import scripts offered on this page (if you understand GIT) from git://git.lwn.net/qb-escape.git or manually download both qb_iif_to_gc (https://lwn.net/Articles/730030/) and qb_trans_to_gc (https://lwn.net/Articles/730031/) to a folder for doing this import work.
11. Install Python via MacPorts and the GnuCash Python bindings. Go to https://wiki.gnucash.org/wiki/Python_Bindings and follow the installation steps (I did this for Mac OS X).
12. Note that if you used Homebrew to install Python before, MacPorts will install another set of them (note the directory where the MacPorts Python is). (Probably in /opt/local/bin/)
13. I was only able to get the scripts working with Python 3.6. Unfortunately, this requires updating some of the script code to work with the new Python version. Open the two scripts up in your preferred code editor (or even TextEdit/Notepad). Update all print/except commands to have round brackets (). For example:

print 'Account %s already exists' % (name)
should be:
print('Account %s already exists' % (name))

except IOError, e:
should be:
except (IOError, e):

In qb_trans_to_gc, update the following:

Line 32: return gnucash.GncNumeric(ival, SCALE)
Should be:
return gnucash.GncNumeric(int(ival), SCALE)

Under line 98: split.SetValue(GCVal(entry['Amount']))
Add
split.SetAmount(GCVal(entry['Amount']))

(We need to SetValue AND SetAmount)

Finally, for all instances of: entry = reader.next()
Change them to:
entry = next(reader)

14. It didn't work for me, but on Line 154, you can try changing the currency that should be used for this book.
15. Open GnuCash and create a new file by going to File > New. Select the least amount of accounts to create (I chose "A Simple Checkbook") that you can delete later on after importing in order to create the new file. Save the new book.
16. Quit GnuCash (this is important - the scripts will not work if GnuCash is running at the same time).
17. Open a terminal and run:
export PYTHONPATH=$PYTHONPATH:/opt/local/lib/python3.6/site-packages
18. Import the Chart of Accounts and Lists by running:
/opt/local/bin/python3.6 ~/Documents/GnuCash/qb-escape/qb_iif_to_gc ~/Documents/GnuCash/qb-escape/listexports.IIF ~/Documents/GnuCash/<YOUR GNUCASH FILENAME>.gnucash
19. Import your transactions by running:
/opt/local/bin/python3.6 ~/Documents/GnuCash/qb-escape/qb_trans_to_gc ~/Documents/GnuCash/qb-escape/exportedtransactions.CSV ~/Documents/GnuCash/<YOUR GNUCASH FILENAME>.gnucash
20. Run GnuCash. You should see all your accounts and transactions if everything worked! You can now delete all the extra .gnucash and log files generated by the scripts (keep the original .gnucash file you created). Enjoy GnuCash!

Escape from QuickBooks (with data in hand)

philtrick — Wed, 06 Dec 2017 12:51:20 +0000

Thanks for this.

Have been looking for an alternative to OpenERP 6 (haven't upgraded due to removal of features from the free version), and the stock and manufacturing of other systems aren't that great.

Will have to give this a try..

Escape from QuickBooks (with data in hand)

bsdimp — Sun, 24 Sep 2017 00:34:07 +0000

There's three bugs that I found in this when doing my conversion from my 10-year-old collection of quickbooks data for my on-again, off-again consulting business....

(1) Credit Card accounts are exported as CCARD in the iif file. These weren't handled by qb_iif_to_gc since they weren't in the table. The fix is trivial.
(2) QuickBooks mac 2007 exports values larger than $999.99 with commas. Fix GCVal to remove commas.
(3) Related, fix termination case to test for the string '0.00' rather than doing the float conversion.

I have fixes for all these issues (though I had to hand-edit the csv files to remove the extra headers so qb_trans_to_gc wouldn't get confused. It would be nice if it filtered all that, but I gave up on hacking that together. It was easier to hack 10 files than to write code to cope. :/. I'll send them to the author of the article since I can't create a pull request since the git repo isn't on github...

Still, don't count this as complaining. This stuff is awesome. While I still need to verify that all the data was transferred into gnucash correctly and the P&L and Balance Sheet reports match, the initial scan of the data looks like it's there correctly.

Warner

Escape from QuickBooks (with data in hand)

njs — Sun, 20 Aug 2017 00:11:16 +0000

> the utility function I bashed together to create such values from the (text) floating-point values

This is why python has built-in support for exact decimal calculations :-)

    import decimal
    # Disable rounding on decimal arithmetic
    # (Not really an issue b/c the only operation is multiplying by 1000, which never
    # needs to round, but paranoia is cheap.)
    decimal.getcontext().traps[decimal.Inexact] = True

    SCALE = 1000
    def GCVal(value):
        scaled = decimal.Decimal(value) * SCALE
        assert int(scaled) == scaled
        return gnucash.GncNumeric(int(scaled), SCALE)

GnuCash vs other alternatives

corbet — Sun, 13 Aug 2017 13:04:41 +0000

That, and because I'm familiar with it through use for personal finance. It's just the starting point, no more.

GnuCash vs other alternatives

jnareb — Sun, 13 Aug 2017 12:46:29 +0000

I guess that GnuCash was chosen for now, instead of other tool or accounting data format (like e.g. http://plaintextaccounting.org) because it can generate reports that you can compare with QuickBooks one. Do I understand this correctly?

Escape from QuickBooks (with data in hand)

diederich — Fri, 11 Aug 2017 17:44:04 +0000

FYI: https://metacpan.org/release/Finance-IIF seems to be a thing.

Escape from QuickBooks (with data in hand)

timrichardson — Tue, 08 Aug 2017 11:19:30 +0000

MYOB ODBC is free now. It's good enough for data conversion. It's well documented.

Escape from QuickBooks (with data in hand)

smurf — Tue, 08 Aug 2017 07:00:27 +0000

You can obtain the starting balances for an account by taking the final balance and then subtracting all the transactions in that account. A bit involved but hardly rocket science.

Splits obviously cannot have a commit method. That makes no sense. You can only commit the whole transaction, and then only when the splits sum up to zero. Otherwise Gnucash will "helpfully" credit the balance to a cure annoying "Imbalance-USD" account. (Disclaimer: that's what the UI does; I suspect the Python interface does the same thing.)

Distributions do enable database integration, though the process to actually connect to one is anything but straightforward. Still, it may be a good idea to use that because one can then run SQL statements on the raw data, which tends to be faster than starting up Gnucash itself and reading the whole account history. Note though that Gnucash still is not multiuser capable just because you're using a database back-end; it has no way to refresh the in-memory set of transactions as it doesn't store the change log in the database. (At least it *has* a change log.)

Escape from QuickBooks (with data in hand)

ringerc — Tue, 08 Aug 2017 02:26:14 +0000

I had similar issues with MYOB, but found that (at least at the time) their ODBC driver and API access is only available if you're signed on to the MYOB Developer program. And they get to tell you if they don't like what you're doing with it.

It's also read-only except for specially authorised devs, so useful for export and reporting but not integration.

Sigh.

Escape from QuickBooks (with data in hand)

Cyberax — Tue, 08 Aug 2017 01:12:56 +0000

FWIW, QuickBooks has an API that allows you to connect to a running QB instance through a REST/XML API and introspect its database. It's fairly comprehensive and it can access values like the current balances that are difficult to obtain otherwise.

In one of my previous jobs, I built a data converter that used it to pull data into another accounting system.

Escape from QuickBooks (with data in hand)

gerdesj — Tue, 08 Aug 2017 00:23:59 +0000

My company escaped from Sage about a year ago. It is extremely liberating and worth persevering at. I no longer have to kick someone out to get a license and the PostgreSQL back-end we use is rather more efficient for multi user access than the shitty text files of the bottom end Sage offering. Our new system is not perfect by any means but can only improve as we add stuff to it. It already allows everyone in the firm to use it rather than the select few that licensing allows. I can point pgadmin and jdbc at it and mess around as I see fit (read only!)

It probably isn't for everyone - https://www.uzerp.com/ and https://github.com/uzerpllp/uzerp - but might be of interest to someone. It is UK focused but supports multi currency. It is really a ERP but we are are services company and it works very nicely for us. To cut a very long story short, the original sponsor is an accountant who set up a factory and decided to develop his own system and hired a programmer who has since retired. The current dev inherited a bit of a mess from his predecessor and has spent the last three years or so battering it into some sort of order. There's a lot more to do but it is extremely functional.

uzERP runs the show for a few SMEs in the UK from manufacturing to services, that we know of. The source is GPL3 and PHP with a PostgreSQL back-end.

Feel free to fork and crack on ...

Escape from QuickBooks (with data in hand)

Beolach — Mon, 07 Aug 2017 22:21:35 +0000

All LWN articles are made public after 1 week.

Escape from QuickBooks (with data in hand)

cpitrat — Mon, 07 Aug 2017 22:03:27 +0000

I guess to increase the likelihood of this article helping people who have the same need, it would make sense to not make it limited to LWN subscribers.