Online Bank File Formats

Michaela and I have been keeping a budget for years. Each month we plan our major costs, and allow some money for spare things. Most months we keep the book up to date. Some times we don’t.

Been the geek I am I want to-do two things. Make it easier by using my computer and tracking trends, also using the computer. The first making the second easier.

I’m in the middle on reading “The inmates are running the asylum”, so I’m fully aware the notebook and Excel would do me better 99% of the time, just from usability, and corner cases handling point of view.

Anyway, that aside tonight I started noting what functionality I want from my banking (cashbook) application. Then I reviewed the options for input files.

Westpac don’t have a direct API to call (that I could find) so I looked into the downloadable formats. Ignoring date ordering these consist of:

  • CSV - This seems the best, with the data been quote encapsulated when data is present.

  • MYOB (Version 3.0.246 or later) - This was CSV as well, but not quote encapsulated, which meant it would require special handling for the case with extra commas.

  • QIF for Quicken - This is a multi-line version of the CSV but missed the transaction type, so the extra values that are transaction specific would be harder to parse.

  • QIF for MS Money - This is the same as above accept the year is 4 digit instead of 2 digit.

  • DeskBank Statement Transactions - This is fixed position output with repeated date fields, and numeric for transaction details, thus would need quite some effort to gain the same level of details as the CSV, but might offer more information also.

  • DeskBank Balances & Transactions - This did not work on a yearly or 3 month data request so I flagged it.

So I think the picture Westpac are try to give is CVS is best, and I tend to agree.

I’m going to use a Chain Of Responsibility Pattern that I read about the other day, to manage the different transaction types, and parse the extra data into an xml blob. Not that COR is new as this was how ANVL implemented its testing stack back before ‘98 when I first started using it. But it’s nice to have a fancy name to place on it. Makes you feel all Pattern elite.

Anyway while I had the last years transactions from our checking account (daily cash account), I used some uber Excel =SUMIF(C:C,$Cx,B:B) and =COUNTIF(C:C,$Cx) then sorted by biggest sum. After ignoring rent and moving money between accounts, the weekly shop and Pak’n’Save was #3 @ ~$5,000 for a year, with a close follower been the local New World @ ~$1,000. Wow, I understand why they like repeat customers. The later will mostly be top-up/junk purchases, and that’s some cream.

Looking at the math the totals are less than the original budgeted amounts, but I’ve just never stopped to think about the yearly totals. It’s interesting looking at the big picture some times. Depressing, but interesting.