I use the excellent open source accounting software GnuCash to manage my personal finances. I discovered it earlier this year and found it to be just as good as Quicken and without the evil. I’m old-fashioned when it comes to managing finances. I don’t need to have access to my financial records on all my devices; I am content to have a single app running on my laptop (backed up using Dropbox, of course) and to enter my receipts into GnuCash when I get home each day. I prefer apps I can easily get my data into and out of. Finally, I don’t believe handing my bank account login information to a third party is a good idea, preferring to download financial transactions from my accounts, in CSV format, manually.
GnuCash has the option to import transactions from QIF format, which allows for “split” (also known as double entry) transactions. For example, this is what a QIF file would look like for my monthly electric payment:
!Account NAssets:Checking Account ^ !Type:Bank D2017-11-28 T-50.00 PFlorida Power and Light SExpenses:Electric $50.00 ^
This transaction has two entries: the deduction of $50.00 from the bank account to the power company, and the increase of $50.00 in the GnuCash account “Expenses:Electric.” The same transaction as downloaded in a CSV file from my bank would be something like this:
11/28/17,Florida Power and Light,-50.00
Obviously, this is a radically different file format than QIF. Also note that the bank only has one “side” of the transaction, the deduction from the bank account. Getting transactions like these into GnuCash, then, requires the additional step of adding the correct GnuCash expense account to each transaction — not just converting from CSV to QIF.
Fortunately, I’ve discovered Python is very good at converting between very different text file formats.
In my transition to GnuCash, the biggest challenge was converting four years of financial transactions from Microsoft Access 2010 to QIF. I started by exporting from Access to CSV. The resulting CSV file had over 20,000 lines. I was able to throw together some messy Python code to convert the 20,000 lines of CSV to QIF, and then get all the data into Gnu Cash. There were so many lines that I worked with them in batches so I could check the account balances as I went.
I still use a slightly cleaner version of this code on a monthly basis to import transactions from PayPal, my Amazon seller account and eBay into QIF format, for import into GnuCash. This makes it easy to keep detailed sales and expense records. Of all the Python code I’ve written, this QIF creator has been by far the most useful to me. Now I hope to make it useful to others.
The more I learn about Python, the more I see how much my code can be improved. My existing code for CSV to QIF conversion is neither user-friendly nor GitHub worthy, so I plan to clean up, refactor and redesign it. From this point forward, I’ll call this project “Quiffer.”
To illustrate the challenges faced by this project, here’s a sample PayPal CSV file, which is the most complicated file type I deal with regularly:
Date, Time, Time Zone, Name, Type, Status, Currency, Gross, Fee, Net, From Email Address, To Email Address, Counterparty Status, Address Status, Item Title, Item ID, Shipping and Handling Amount, Insurance Amount, Sales Tax, Option 1 Name, Option 1 Value, Option 2 Name, Option 2 Value, Auction Site, Buyer ID, Item URL, Closing Date, Reference Txn ID, Invoice Number, Custom Number, Receipt ID, Balance, 11/28/2017,21:26:38,EST,"Almech Devices, LLC",Express Checkout Payment Sent,Completed,USD,-2.75,0,-2.75,email@example.com,firstname.lastname@example.org,Verified,Confirmed,Tempered Glass Protective Screen Protector Film for Samsung Galaxy S5 Active,131511092340,0,0,0,,,,,Ebay,rxx04,http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=131511092340,,,,,,58.28, 11/28/2017,21:26:36,EST,rosie castle,Express Checkout Payment Sent,Completed,USD,-7.98,0,-7.98,email@example.com,firstname.lastname@example.org,Verified,Confirmed,"The Good Dinosaur (DVD, 2016) NEW MOVIE! NOW SHIPPING ! ",222735118701,0,0,0,,,,,Ebay,rxx4,http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=222735118701,,,,,,61.03, 11/28/2017,21:26:34,EST,广州满翼易有限公司,Express Checkout Payment Sent,Completed,USD,-5.5,0,-5.5,email@example.com,firstname.lastname@example.org,Non-U.S. - Verified,Confirmed,Cotton Linen Harry Potter Pillow Case Sofa Waist Throw Cushion Cover Home Decor [S1],282606610348,0,0,0,,,,,Ebay,rxx04,http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=282606610348&var=581788364837,,,,,,69.01, 11/28/2017,21:14:09,EST,eBay Inc Shipping,Preapproved Payment Sent,Completed,USD,-2.77,0,-2.77,email@example.com,firstname.lastname@example.org,Verified,,"ShipSvc:USPS First Class Package, Trk#:239685693898520305, Postage:$2.77, Ins:$0.00",,0,,0,,,,,,,,,,3559525440,,,74.51,
Note there are a few difficulties here:
- There are numerous columns I don’t need.
- The date format will need to be changed to YYYY-MM-DD for the QIF file.
- There are multiple columns (Name, Type, Status, Item Title) containing data that I want to appear in GnuCash’s “Description” field. These columns will therefore need to be combined into a single column.
- Transactions that are payments for products I’ve sold include an amount in separate column, “Fee”, which is the fee charged by PayPal to receive funds. Such transactions will actually need FOUR entries in GnuCash: two each for the payment and fee.
- My code needs to include a means of determining, automatically, the correct GnuCash accounts where the income and expense will be posted.
The first step for Quiffer will be to import the data from the CSV file into a Python data structure. That will be the topic of my next post. Stay tuned.