Importing statements from Jetstar Platinum Credit Card / Macquarie Bank into MS Money OFX format
With my having several accounts both in Australia and the UK for savings, credit cards, etc., I use MS Money to try and keep track of everything. I actually started using it when it supported online updates from Nationwide, and even though that is long gone (and even imports are a pain), I continue to use it.
However – now that I’ve got my shiny new Jetstar Platinum card with my Qantas FF points, I now want to import my card spend into Money. I really want to, because I still want to use that data in MS Money to categorise my spending and keep track of each category. But with Macquarie (Jetstar card’s provider) providing two completely useless formats – PDF statement, or just the ‘recent transactions’ web page – and no numerical format (OFX, or even CSV), how could I do this.
Well… what I got to work is this:
First, I view my statement on the web page, and copy and paste it into Excel. This actually works pretty well, in terms of reading text and values into different cells.
Note where I select it – the entire table, including headers.
This is what it looks like in Excel:
Now – I can’t share all my bank details, obviously, but you can hopefully make out that it maintains most of the structure… but each transaction is two rows, the bottom one being a transaction ID or something. Also, the value is split out into 2 fields: the first number is whole dollars, and the second one is cents.
So – I feed this into a sheet that uses formulae to remove the spaces, and get the dollar and cents in one cell. Again, heavily obfuscated:
From there, I need to convert from CSV to OFX. It turns out there’s a workbook for that, with a macro to do the conversion. I actually moved my sheets into this macro workbook, so that I can reference across from start to end in one workbook, without having to copy/paste data between different workbooks.
Again, obfuscated, but nice and neat. I have manually set the credits (ie. bill payments) as negative in the previous sheet, and added a formula to read those into the ‘credit’ column in this sheet.
I press the macro button, and that spits out an OFX file.
Now I can simply double-click this to load into MS Money.
The first time, it asked me to select the account to import into, but it then seemed to remember this for future imports.
I also tweaked and assumed a few things, including:
- Setting my own card, account, etc. number.
- Assuming the statement start/end date to be the first/last transaction date in that set (it uses a max/min function to get these values)
- Concatenating name fields to 32 characters in the formulae (a limit in OFX)
There are also problems; I have to manually tweak the credits, since the formula to do this automatically is beyond my mind’s capabilities at the moment.
Anyway, I’ve created a few copy of that sheet, that I can post if anyone might find it useful.