Editing OFX files

With the growth of third-party online financial management sites (like Mint and Buxfer, to name two), the ability to get detailed financial data out of your bank’s portal and into somewhere else is more important than ever. If you’re doing this, you should be using OFX (sometimes labeled Microsoft Money) format files. They’re very simple, humanly-editable, and financial websites love to read from them.

Some banks, however, aren’t as flexible as one would like in controlling what they export – for example, my bank (RBC) won’t let me select a specific date range to export. To simulate this, I decided to edit the OFX files they did offer by hand.

The motivating factor for this was that I decided that it was high time to start using one of these sites – I chose Wesabe, owing to a lack of Mint in Canada – to get a detailed view of what I was spending where. This coincided with a drastic change in my income stream and living conditions, and accordingly I wasn’t that interested (for this purpose) in what they were like before that change happened.

Ideally, my bank would allow me to select a date range to export, or Wesabe would have an easy facility to delete records past a certain date; neither of those are the case, however, so I was left doing it by hand. Handily, OFX is really XML with some extra stuff at the top, so it’s easy to get an idea of what’s going on without even reading the spec.

To eliminate the old records I mentioned earlier, I manually read through the file and eliminated all the <STMTTRN> elements with a <DTPOSTED> that was outside the range I was interested in. The trick is to not change the values for <DTSTART> or <DTEND> at the start of the <BANKTRANLIST> element.

I’m not sure of the reason (and in this limited case, it didn’t concern me), but if those values were adjusted the file would not be read properly, but if they weren’t adjusted (and simply left to cover a much longer time than there was data for), the file was read properly and imported successfully.