09 November 2008

Crunching Numbers

During the last couple of day, I spent my free time trying to sort out the family finances; big (good) changes are on the horizon, we need to plan a bit better from now on, and it's amazing how "bad" expenses stick out straight away when you aggregate them, instead of relying just on rough day-by-day cash-flow estimates ("how much money is still in my account?").

I tried to use KMyMoney for this sort of thing several times in the past, but I was always inevitably thwarted by the effort required to copy records one-by-one from online bank statements, because the sort of bank accounts I use don't allow any desktop clients to automatically pull data. So this time I thought I'd fix it once and for all, and set down to write a few scripts to do that, albeit in a somehow indirect way.

KMyMoney can natively import transactions in the legacy (and wildly non-standard) QIF format, while OFX requires a plugin (why? No idea). Unfortunately, on my Debian Etch, the bloody plugin somehow never gets installed correctly, but hey, maybe one day it will, and OFX is the way of the future anyway (it's XML-based and much more exactly specified than the old plaintext-based, informal QIF). So I wrote a Python script to convert the HTML or CSV produced by my online accounts to OFX, then passed the output to ofx2qif, a handy script included in the libofx-dev package (at least in the 0.8.2 version I'm using). The result is ready to be imported in KMyMoney. Slightly cumbersome, but it does the trick. I need to add a bit more intelligence to the scripts, to speed up the categorisation effort that follows (which is the whole point of the exercise), e.g. "LINK xxxxxx" payees should all be set to "cash machine" etc, but it's already working fairly well.

The effect was startling; finally, all my expenses are tracked and I can properly budget and forecast. (...How the hell I'm spending so much on mobile-phone calls??)

I'm actually slightly pissed off that cash transactions are now so opaque; I've no idea why £50 were withdrawn from an ATM on that January day (even though I'm sure it made sense when I first checked the statement 6+ months ago), but I know for a fact that those £37.68 from last December were for a delicious Japanese dinner, as I paid for it with my debit-card.
In a way, this goes against the "classic" principle that "by using plastic, you never really know how much money you don't have" (so you tend to spend more). I still believe in that principle, and I'm slightly baffled by the evidence.

I'd strongly recommend this sort of exercise to everyone, anyway. You don't need to use a dedicated program like KMyMoney (even though it helps), Excel might be enough, as long as you can easily add transactions from your online account (via CSV or cut&paste).

2 comments:

  1. Anonymous9/11/08 22:25

    Wow, nice post.

    big (good) changes are on the horizon

    Is this referring to what I think it is referring?

    How the hell I'm spending so much on mobile-phone calls?

    Bad contract? Having more information like this, you may adjust and perhaps change for the better, even if, at least in principle, there is only so much you can optimize when your usage patterns vary in unreliable (for some meaning of "unrealiable") ways.

    In a way, this goes against the "classic" principle that "by using plastic, you never really know how much money you don't have" (and so you tend to spend more). I still believe in that principle, so I'm slightly baffled by the evidence.

    Isn't such a statement just too... capitalistic? :-P

    I'd strongly recommend this sort of exercise to everyone, anyway. You don't need to use a dedicated program like KMyMoney (even though it helps), Excel might be enough, as long as you can easily add transactions from your online account (via CSV or cut&paste).

    Family finances are still on paper and pencil here. (Yes, I know: yuck!) But Carlo would be very proud of you as soon as he'll be reading this (or the topic will enter one of our conversations).

    ReplyDelete
  2. Is this referring to what I think it is referring?
    I'll tell you next month ;)

    Bad contract?
    Yeah, I think Vodafone is fleecing me. I think I'll leave them as soon as I can (next month), if I can keep my handset (or get a better one).

    Isn't such a statement just too... capitalistic?
    I don't see how...? Capitalism being built on debt, I'd say it's actually quite anti-capitalistic ;)

    ReplyDelete