Blog postings

Been bashing QIFs again. 

There's a fault with the Quiffen parser. It uses the caret singletons to chunk the QIF into individual records. Unfortunately this means the first record gets dropped as the header row gets used and anything between the header and the first sentinel caret get dropped. Urgh. Wrote some python to make sure the second row of a QIF is a singleton caret, inserting a row if necessary. I'll make a pull at some point. 

Next thing is TSB QIFs. These have a couple of interesting features. The date format is ISO yyyy-mm-dd, which is fine, but Quiffen doesn't like it. So I had to write some sed to rewrite these:

sed -E 's/^D([0-9]{4})-([0-9]{2})-([0-9]{2})/D\3-\2-\1/g' tsb.qif 

Basically it finds a line DYYYY-MM-DD and rewrites it DDD-MM-YYYY. 

Another is that the transaction payee and transaction memo are on one line. If a transaction has a memo then it has a 40 char field for the payee and the remainder the memo. Easy enough to deal with but annoying nonetheless. 

I've now got a build process for this one QIF. Wrote a makefile to orchestrate everything. It shouldn't be this hard. But it is. None of the free apps actually do what I want so I had to resort to python, regular expressions, make and gnucash to watch my finances. Mad. 

 

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. 

 

You can't really see them because iPhone has a very wide angle camera. But they're there. 

This was down Minnis Bay earlier today. Nice cross-onshore wind. I'm learning how to do this abs I'm at the point where I'm almost getting it. Board starts are very hard. But I'm getting them. You have to dig in but not too much and angle the board just right. One thing at a time. 

 

This place is quite close to me and it's down a toll road so fewer people go there compared to elsewhere. It's a nice place to enjoy a quite wee morning if you want a little bit of peace and quiet. Although we had a lot of horse and cyclists at one point. 

 

This blog is hosted on Heroku as it is a django (i.e., python) app with a Postgres DB that hosts all the good stuff. Heroku wanted to charge me for the DB and I didn't want to pay (I'm OK tossing them a few bucks for hosting a web app, but $10 pcm for a tiny DB with this limited content? Come on). 

There is a thing called supabase which is just cloud databases. It was incredibly easy to set up. 

• Go to supabase and spin up a DB (this was three mouseclicks to log on with github and I had to type a database name and password in)
• Follow these instructions to move your DB data from heroku to supabase
• Unattach all databases from your heroku app (bravery required - when you unattach your DB is gone forever - make sure you test your new image on supabase before unattaching the heroku DBs)
• Point your DATABASE_URL heroku setting to the URI supabase tells you to use, buried slightly deeply in the DB settings on supabase
• You have now migrated to supabase

Took about fifteen minutes.