GnuCash and QIF files

by mark | 27 Sep 2022, 8:11 p.m.

I have decided to start keeping track of financial stuff in GnuCash. Basically I want to make a personal PnL and balance sheet. GnuCash is an open source double entry accoutning software that lets you build accounts through accumulating transactions. 

I have accounts with a couple of UK financial institutions, HSBC and Monzo. They let you download QIF files which GnuCash can consume. QIF is the format used by the doyenne of 90s software for the stingy, Quicken. Of course there is no such thing as a standard QIF format as everything is slightly different from every financial institution and processing software. 

Gnucash misfeatures 

Much like its ancient inspiration Quicken, Gnucash is built around the concept of accounts and categories. Accounts are things like bank accounts, investment assets, liabilities like mortgages, and so on. Categories are special account types that are used to classify income and expense. Gnucash is highly opinionated about what it wants in a QIF file and your life will be a lot easier if you adapt the QIFs your banks give you into Gnucash's form. Then you can just import them with a few mouse clicks instead of having to use an interactive mapping thing which never seems to be reliable. 

Gnucash supports nesting categories. Colons are used to seperate category levels. 

QIF file layout

The QIFs the banks let you download are a list of transactions with a header that announces what kind of account it is. A transaction is really quite simple: it is a date, an amount, and optional info like payee, transaction category and so on. In QIF files these are (usually!) dealt with one line at a time, with the first character telling you what the rest of the line is. So a line that starts with P is the Payee of the transaction. Once all the transaction detail rows are in the file, a caret ^ signifies the end of the record and the next one begins. 

An interesting one is the category row. This starts with L. If the category is an income or expense category, you just need it in plain text. For example, LExpenses:Car means 'this transaction is from the account in the QIF file to the Car subcategory of expenses. 

HSBC QIFs

For some bizarre reason HSBC marks its QIFs as being !Type:Oth L - Other Liabilities. You want to change this to !Type:Bank so GnuCash treats it as a bank account.  (A retail bank account is just a liability account to HSBC).

Additionally, the transactions are not classified. You get a date, a payee, an amount, GnuCash will prompt you to map payees to categories but boy this sucks. You are much better off setting up your own mapping thing that takes payees and adds a category based on this.

Also it is worth adding a full header rather than just the account type. So you want to amend the QIF to start:

!Account
NHSBC
DHSBC bank account
^
!Type:Bank

This names the account HSBC and provides a description. 

Monzo QIFs

Monzo provides categories for most transactions (some very early ones are not available). You can if you want map the combination of payee and category into custom subcategories. 

Like the HSBC ones adding full headers helps. Monzo does at least make its QIFs for you, not for itself, so you don't need to change the type. 

Processing QIFs

I use Python for this. There is a package Quiffen which can load and manipulate QIFs. This is a bit easier than writing awk or whatever to process the QIFs directly. I'll share code once it is more fully developed. 

QIFs can be combined. It is easier to concatenate all QIFs into one super QIF and import that. 

Another gotcha. If a transaction category is really another QIF file, then you have to set the category to the account name and enclose it in square brackets, like L[HSBC]. Again this is easier with Quiffen. 

Having done this once, you now have an easy month end process. Download updated QIFs, filter out transactions based on date which are already in there, process as before, load into GnuCash, you are now done. You can start looking at your financial statements and use the budgeting features. This is neat. Basically tells me I spend too much on take aways.