Type Slowly

Making Nationwide’s online banking play nicely with Freeagent

I don’t like Barclays Bank. I don’t like their President, I don’t like their attitude to tax payments, and I definitely don’t like the extent to which they invest in the sale and manufacture of weapons. As a result of this, I’ve moved my bank account to Nationwide, who so far have been most impressive, apart from one small issue. I use the wonderful FreeAgent software to handle all my accounting, tax, and the like, and one of the awesome things it can do is import your bank statements in OFX format, which saves me doing a hell of a lot of very boring data entry, and leaves me with only a small amount of slightly less boring book-balancing to do.

However, Nationwide don’t support export of your banking data as an OFX file, opting instead for a rather arbitrary CSV-based format, delivered in an arcane character encoding that appears to be mostly ASCII, apart from £-signs, which are encoded as Latin-9.

All’s not lost though, since Freeagent accept CSV files (in their own, rather simpler format), it’s possibile to convert between the two with this handy Ruby script. Simply download it, set the executable bit, and run it with:

convertBankStatement your_nationwide_statement.csv file_to_write_to.csv

You’ll need Ruby 1.9.2. This is how it works:

#!/usr/bin/env ruby
# encoding: UTF-8
require 'csv'
require 'iconv'

@converter = Iconv.new("ISO8859-15", "utf-8")

date = /\d\d [A-Z][a-z][a-z] \d\d\d\d/
string = /.*/
nothing = /^$/
amount = /£\d+\.\d\d/
money_in = [date, string, nothing, amount, amount]
money_out = [date, string, amount, nothing, amount]

def normalise(t)
  t.map { |s| s.to_s.gsub("\xA3".force_encoding(Encoding.aliases["ISO8859-15"]), "£").force_encoding(Encoding.default_external) }
end

def transaction_type(array, pattern)
  !array.empty? && array.zip(pattern).inject(true) {|m, (string, pattern)| m && string =~ pattern}
end

def date(transaction)
  Date.parse(transaction[0]).strftime("%d/%m/%Y")
end

def description(transaction)
  transaction[1].strip[0..31]
end

def outgoing(transaction)
  transaction[2].gsub("£", "-")
end

def incoming(transaction)
  transaction[3].gsub("£", "")
end

CSV.open(ARGV[1], "wb") do |o|
  CSV.foreach(ARGV[0], :encoding => Encoding.aliases["ISO8859-15"]) do |t|
    t = normalise(t)
    if transaction_type(t, money_in)
      o << [date(t), incoming(t), description(t)]
    elsif transaction_type(t, money_out)
      o << [date(t), outgoing(t), description(t)]
    end    
  end
end