Tim Richardson

Netbank, the Commonwealth Bank of Australia's online banking service for consumers, allows export of OFX formats. I noticed this only recently; until now I've been exporting in the less-sophisticated format QIF.

OFX is a huge improvement with my personal finance software, gnuCash, because Bayesian filtering is invoked to match transactions to expense accounts. Bayesian filtering is the logic behind spam filters. It's also good at guessing how to read narrative data on a bank statement. I'd say 90% of my bank entries are classified automatically, which is a huge time saver.

However, Netbank currently causes some big problems with OFX export. It often leaves the vital "ID" field blank. This field is supposed to contain a unique identifier for each transaction. Blank entries are not trusted by gnuCash: they are ignored. I're reported this to CBA. Below is a simple Python script to fix the problem.

I'm learning Python, this is my third useful program. Actually, I put a GUI front end on it but this fragment is easier to use. The "BeautifulSoup" module can be installed from Debian/Ubuntu repositories if you're using them.

This works for me: I export transactions in one month chunks. The unique ID combines transaction date and an incrementing number. If you export the same transaction in different date ranges so that it appears in two exported OFX files, this simple script will give the same transaction two different IDs. I avoid this by not overlapping date ranges. A better way is to use the hashlib standard library on the amount, date and memo, instead of adding datestrings and an incrementing sequence number. I was going to do that, but got lazy.

#!usr/bin/python
 from BeautifulSoup import BeautifulStoneSoup
 
 ofx = BeautifulStoneSoup(file('201005_mainAcct_test.ofx'))
 seq = 0
 for txn_ofx in ofx.findAll('stmttrn'):
     seq+=1
     id_tag = txn_ofx.find('fitid')
     if hasattr(id_tag, "contents"):
         
         if id_tag.contents[0] =='\n':   #blank FITID
             memo_tag = txn_ofx.find('memo')
             if hasattr(memo_tag, "contents"):
                     memo = memo_tag.contents[0]
 
             amt_tag = txn_ofx.find('trnamt')
             if hasattr(amt_tag, "contents"):
                 amount = amt_tag.contents[0].strip()
 
             date_tag = txn_ofx.find('dtposted')
             if hasattr(date_tag, "contents"):
                 datePosted = date_tag.contents[0].strip()
         
             newID = datePosted+ str(seq) + '\n'
             
             id_tag.contents[0] .replaceWith(newID)
          
 outFile = open('201005_mainAcct_test_v2.ofx', 'w')
 outFile.write(repr(ofx))
 outFile.close()