Bank of Bermuda internet banking redux

Last October, the Bank of Bermuda rolled out a new internet banking system. While the new system offered some improvements over the old, transaction downloads to Microsoft Money were essentially broken.

In the .ofx file, the payee field no longer contained the payee information (it was buried in the middle of the Memo field instead). In the .qif file, all transaction dates were out by one day. At a stroke, the amount of time it took to reconcile the transactions in my accounts more than doubled.

Despite several email exchanges with the Bank’s customer support and one of their developers, the issue was never resolved. Glumly, I resigned myself to a future of frustrating, manual transaction reconciliation.

Recently, however, a former commenter on this site alerted me to a solution. It’s a free, third-party program called mt2ofx, that takes the download file provided by the bank and reformats it to put the right information in the right places before passing it on to Microsoft Money. I’ve been using it for a couple of weeks now, and it works like a charm.

It’s a little tricky to set up, but you only need to do it once:

1. Download and install the program. You can get it here.

2. Start the program and click the “Options” button to open the options window. At the bottom of this window is a button labeled “Payee Replacement”. Click that button to open the “Payee Mapping” window.

3. In the “Payee Mapping” window, click the “Add” button. In the box labeled “Payee Mapping Rule”, set the first dropdown to “Memo” the second to “contains” and the third to “\.\d\d\s(.+)” (trust me). In the box labeled “Set the payee to”, type “%1”. Then click the “Update” button, followed by “Save & Close” to go back to the options window.

4. In the Options window, click the button labeled “Post-conversion actions”. Where it says “Output file type”, select “OFX (Open Financial Exchange File)”. Select “Open it with the follow program”, and click the button with the folder icon to specify the location of the Money import handler (if you are using Microsoft Money 2007, this will be “C:\Program Files\Microsoft Money 2007\MNYCoreFiles\mnyimprt.exe”). Click "OK".

5. In the options window again, click the "Scripts" button at the bottom. In the list of scripts, make sure "Generic-OFX.vbs" is at the top and is the only one with a check mark next to it. Click "OK".

6. Close mt2ofx.
   

Now log on to internet banking and download the transaction history file you want (in the OFX format). Instead of opening it with Microsoft Money, however, tell Windows to use mt2ofx to open it. The program will convert the file and then open it in Microsoft Money, where you should find that all the payee names have now been imported (mostly) correctly.

You can fiddle around in mt2ofx’s options screen some more to fine-tune the behaviour of the application. For example, I have specified additional Payee Mapping rules to handle certain payees whose names appear in the Bank’s download file in different formats (e.g. one rule checks to see if the “Memo” field “starts with” the value “TO BELCO”, and if so, sets the payee to “Belco”).

It’s annoying that it’s necessary to go to these lengths to work around a problem that the Bank should easily be able to fix. But for now, it’s the best solution there is.