Archive

Archive for May, 2012

Importing statements from Jetstar Platinum Credit Card / Macquarie Bank into MS Money OFX format

May 23, 2012 Leave a comment

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.

Image

Note where I select it – the entire table, including headers.

This is what it looks like in Excel:

 

Image

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:

 

Image

 

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.Image

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.

Image

 

Now I can simply double-click this to load into MS Money.

Image

 

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.

 

Advertisements
Categories: Uncategorized

XBMC Constellation Remote Review for iOS

May 12, 2012 Leave a comment

20120513-010626.jpg

I’ve just come into the XBMC world after 2 years with Boxee, and my eyes have been opened to the world of rich remote apps like this one. This is the first one I’ve tried.

After around 10 mins playing, it crashed. It then repeated crashing out immediately, until I had power-cycled my iPad AND reinstalled the app.

When working, the app feels like a pretty poor skin choice for XBMC. Feels like a mix between Tron and the Starcraft font. You’d better like sci-fi. Sadly, unlike XMBC, it’s a skin you cant change.

And otherwise, it’s incredibly powerful, and perfect.

Like INCREDIBLY! After auto detecting my instance, it synced all the lists, artwork, etc, and let me play/watch/control whatever I wanted, seamlessly. Collapsible episode lists, drill-down into each movie metadata. It even lets you tweet your current media, and search YouTube for related clips. Amazing!

You know, one other gripe. Confusing. Each screen is solid with sidebars, topbars, icons, buttons, dials. And no help.

And one other? Just slightly too slow and juddery all over, on my iPad 2.

Still…. It’s the one to beat. And at least it’s free to try – which is why I won’t even bother paying the $5 each to compare other leading apps to it. It’s a great start, and they just need to make it more “Apple-style”

Categories: iphone