CSVtoQIF.sh

#!/bin/bash
accountPrefix="Assets:Current Assets:Cash"
lastDate=$(date +"%d/%m/%y")
while getopts "a:" c
do
	case $c in
		'a')	accountPrefix=$OPTARG
				;;
		*)		echo "Usage: $0 [-a accountPrefix (default='Assets:Current Assets:Cash')] <csvfile ...>"
				;;
	esac
done
shift $(($OPTIND - 1))
declare -A outFileNames
declare -A outTmpFileNames
declare -A currencyFile
declare -A currencyBalance
currencyFile["\$"]="Aussie Dollar"
currencyFile["£"]="Sterling"
currencyBalance["\$"]=0
currencyBalance["£"]=0
while [[ $# -gt 0 ]]
do
	accountFilePath=$1
	oldIFS=$IFS
	IFS=,
	while read tDate category amount currencyRaw memo
	do
		currency=${currencyRaw//\"}
		if [[ "${outTmpFileNames[$currency]}" == "" ]]
		then
			echo "DBG: Got no filename for cur:$currency"
			outTmpFileNames[$currency]=$(mktemp)
		fi
		echo 'D'${tDate//\"} >> ${outTmpFileNames[$currency]}
		echo 'T-'${amount//\"} >> ${outTmpFileNames[$currency]}
		echo 'P'${memo//\"} >> ${outTmpFileNames[$currency]}
		echo 'NCash Spend' >> ${outTmpFileNames[$currency]}
		echo 'L'${category//\"} >> ${outTmpFileNames[$currency]}
		echo '^' >> ${outTmpFileNames[$currency]}
		currencyBalance[$currency]=$(echo ${currencyBalance[$currency]} - ${amount//\"} | bc)
	done <<-!END
		$(cat $accountFilePath)
	!END
	IFS=$oldIFS
	shift
done
#echo "${!outTmpFileNames[*]}"
for thisIndex in ${!outTmpFileNames[*]}
do
	accountName="Assets:Current Assets:Cash:${currencyFile[$thisIndex]}"
	accountOutFile="$accountName".qif
	cat > "$accountOutFile" <<-!END
		!Account
		N$accountName
		TCash
		\$${currencyBalance[$thisIndex]}
		$lastDate
		^
		!Type:Cash
	!END
	cat ${outTmpFileNames[$thisIndex]} >> "$accountOutFile"
done
printf "${outTmpFileNames[*]}\n"
rm -f "${outTmpFileNames[*]}"

GetDetails.rb

#! /usr/bin/env ruby
#= Get Details
#
#This script is designed to retrieve a specific set of files from the online
#banking web pages of the National Australia Bank. It relies on the funcitonality
#implemented in the NABUtils library.
#
#There are a number of options that can be provided on the command line to define
#the period for which the data should be retrieved;
#
#start <start date>:: start getting transactions from the nominated date
#end <end date>:: stop getting transactions from the nominated date
#this-month:: get the transactions for the whole of this month
#last-month:: get the transactions for the whole of last month
#
#In addition, the user should supply the internet banking customer ID and password as
#the last two parameters on the command line.
#
#For example normal running on a monthly basis would be;
#
#    ./GetDetails.rb <customer number> <password>
#
#To get this months data
#
#    ./GetDetails.rb --this-month <customer number> <password>
 
require 'NABUtils'
require "getoptlong"
require "rdoc/usage"

opts = GetoptLong.new(
	["--start", "-s", GetoptLong::REQUIRED_ARGUMENT],
	["--end", "-e", GetoptLong::REQUIRED_ARGUMENT],
	["--this-month", "-t", GetoptLong::NO_ARGUMENT],
	["--last-month", "-l", GetoptLong::NO_ARGUMENT],
	["--test", "-d", GetoptLong::REQUIRED_ARGUMENT],
	["--keep", "-k", GetoptLong::NO_ARGUMENT],
	["--help", "-h", GetoptLong::NO_ARGUMENT]
)

start_date = nil
end_date = nil
end_of_month = false
save_data = false
test_path = nil
opts.each do |opt, arg|
	case opt
		when '--start'
			start_date = Date.strptime(arg)
		when '--end'
			end_date = Date.strptime(arg)
		when '--this-month'
			start_date = Date.new((Date.today >> 1).year, (Date.today >> 1).mon, 1)
			end_of_month = true
		when '--last-month'
			start_date = Date.new((Date.today).year, (Date.today).mon, 1)
			end_of_month = true
		when '--keep'
			save_data = true
		when '--test'
			test_path = arg
		when '--help'
			RDoc::usage
			puts opts.error_message()
	end
end
if ARGV.size != 2
	puts "You must supply a Customber Number and Password"
	puts "#{$PROGRAM_NAME} --help for more information"
	exit 5
end

# some more code
nf = NABUtils::Fetcher.new(ARGV[0], ARGV[1], save_data, test_path)
start_date = Date.new((Date.today << 1).year, (Date.today << 1).mon, 1) unless start_date
end_date = Date.new((start_date >> 1).year, (start_date >> 1).mon, 1) - 1 unless end_date
if end_of_month then
	balances = nf.end_of_month(start_date)
else
	balances = nf.get_transactions(start_date, end_date)
end
puts "as at #{end_date}:"
balances.each { |key, b| puts "   '#{key}' balance is #{b}" }

NABUtils.rb

require 'rubygems'
require 'nokogiri'
require 'mechanize'
require 'logger'

# This library provides some classes to download account and transaction information from the 
# internet banking website of the National Australia Bank. 
#
# From time to time these sites change their structure and/or functionality and software such
# as this library, that are quite tightly coupled to the specific structure of the pages and the website
# as a whole, will break.
#
# Revision:: 0.1
# Date:: 14th October 2009
# Author:: Wesley Moore (additional work Philip Haynes)
module NABUtils

	# A class to represent a transaction in an NAB Account
	class Transaction
		attr_accessor :date, :type, :reference, :payee, :memo, :amount, :debit, :credit, :category, :balance

		# Create a new Transaction. You must provide one of;
		# * amount - a signed value for the amount of the transaction (negative for a debit)
		# * debit - a non signed value for the debit amount of the transaction
		# * credit - a non signed value for the credit amount of the transaction
		# 'amount' will override debit and credit, debit will override credit.
		#
		# Failure to provide a category will leave the Transaction as catgegory 'Unspecified'.
		#
		# Optionally you may provide 'balance' which is the balance on the account as a result of
		# this transaction.
		def initialize(date, type, reference, payee, memo, amount = nil, debit = nil, credit = nil, category = 'Unspecified', balance = nil)
			@date = date
			@type = type
			@reference = reference
			@payee = payee
			@memo = memo
			@amount = amount ? amount : (debit > 0.0001 ? -1 * debit : credit)
			@debit = amount ? amount : debit
			@credit = amount ? amount : credit
			@category = category
			@balance = balance
		end

		# Output the transaction in QIF format
		def to_qif()
			qif_string = ""
			qif_string += "D#{@date}\n"
			qif_string += "T#{@amount}\n"
			qif_string += "M#{@memo}\n"
			qif_string += "N#{@type}\n"
			qif_string += "P#{@payee}\n"
			qif_string += "L#{@category}\n"
			qif_string += "^\n"
			qif_string
		end
	end

	# A class to represent an NAB account
	class Account
		# The mappings between NAB Account Types and the types of account defined in the QIF specification.
		AcctTypeMap = Hash[
			'DDA' => 'Bank', 'SDA' => 'Bank', 'VCD' => 'CCard'
		]
		AcctTypeMap.default = 'Bank'
		attr_accessor :type, :bsb, :number, :nickName, :current_balance, :available_balance, :closing_balance, :transactions

		def initialize(type, bsb, number, nickName = nil, openingBalance = 0.0, availableBalance = nil)
			@type = type
			@bsb = bsb
			@number = number
			if nickName then
				@nickName = nickName
			else
				@nickName = acctId
			end
			@current_balance = openingBalance
			if availableBalance then
				@available_balance = availableBalance
			else
				@available_balance = openingBalance
			end
		end

		# This function will generate the id used internally by NAB to identify the account within the
		# internet banking application
		def id()
			@number.gsub(/[^0-9]/, '')
		end

		# Download the transactions matching the date criteria specified in the parameters. By default, the
		# start date is the start of the current month and the end date is today.
		def download_transactions(agent, start_date = Date.new(Date.today.year, Date.today.mon, 1), end_date = Date.today)
			balances_page = agent.get('https://ib.nab.com.au/nabib/acctInfo_acctBal.ctl')
			transaction_form = balances_page.form('submitForm')
			transaction_form.accountType = @type
			transaction_form.account = id()
			transactions_page = agent.submit(transaction_form)

			transactions_page = agent.click transactions_page.links.select { |l| l.attributes['id'] == 'showFilterLink' }.first
			transaction_form = transactions_page.form('transactionHistoryForm')
			transaction_form.radiobuttons_with(:name => 'periodMode', :value => 'D')[0].check
			transaction_form.transactionsPerPage = 200
			transaction_form.action = 'transactionHistoryValidate.ctl'
			if start_date then
				transaction_form.periodFromDate = start_date.strftime("%d/%m/%y")
			end
			if end_date then
				transaction_form.periodToDate = end_date.strftime("%d/%m/%y")
			end

			transactions_page = agent.submit(transaction_form)
			# Anything happen here?
			payeeCategoryMap = Hash.new
			payeeCategoryMap.default = nil
			["PayeeCategories-" + @nickName + ".txt", "PayeeCategories.txt"].each do |file_name|
				if File.readable?(file_name) then
					File.open(file_name) do |file|
						while content = file.gets
							payee, category = content.strip.split('|')
							payeeCategoryMap[payee] = category
						end
					end
				end
			end

			@transactions = []
			transactions_page.root.css('table#transactionHistoryTable tbody tr').each do |elem|
				next if elem.xpath('.//td[5]').text.strip == ''
				elem.search('br').each {|br| br.replace(Nokogiri::XML::Text.new("|", elem.document))}
				memo_raw, type_raw, ref_raw = elem.xpath('.//td[2]').text.strip.gsub(/  */,' ').split('|')
				memo = memo_raw.gsub(/^.* [0-9][0-9]\/[0-9][0-9] /,'') if memo_raw
				payee = memo.gsub(/^.*[0-9][0-9]:[0-9][0-9] /,'').gsub(/^INTERNET BPAY */,'').gsub(/^INTERNET TRANSFER */,'').gsub(/^FEES */,'') if memo
				transaction_date = Date.parse(elem.xpath('.//td[1]').text.strip)
				category = payeeCategoryMap[:default]
				payeeCategoryMap.select do |key, value| 
					if payee =~ Regexp.compile('^.*' + key + '.*', Regexp::IGNORECASE) then
						category = value
						break
					end
				end
				@transactions << Transaction.new(transaction_date.strftime("%d/%m/%y"), type_raw, ref_raw, payee, memo, nil,
															elem.xpath('.//td[3]').text.strip.gsub(',','').to_f, elem.xpath('.//td[4]').text.strip.gsub(',','').to_f,
															category, elem.xpath('.//td[5]').text.strip.gsub(',','').gsub(/([0-9.]*)[ 	]*DR/,'-\1').gsub('CR','').to_f)
			end
			@transactions.reverse!
		end

		# Output this account in QIF format, including all the transactions currently downloaded into
		# this instance of the class. The whole account is returned as a string.
		def to_qif()
			qif_string = ""
			qif_string += "!Account\n"
			qif_string += "N#{@bsb} #{@number}\n"
			qif_string += "T#{AcctTypeMap[@type]}\n"
			qif_string += "^\n"
			qif_string += "!Type:#{AcctTypeMap[@type]}\n"
			@closing_balance = @current_balance
			if @transactions then
				@transactions.each do |t|
					qif_string += t.to_qif
					@closing_balance = t.balance
				end
			end
			qif_string
		end

		# Generate a QIF file of all the transactions specified by the date criteria passed in as parameters. By
		# default, the date parameters start with the first of the current month and end at today. If no name is
		# specified for the output file name then the nick name of the account is used with the file type '.qif'.
		def generate_qif(agent, start_date = Date.new(Date.today.year, Date.today.mon, 1), end_date = Date.today, output_file = @nickName + '.qif')
			#output_file = start_date.strftime("%Y%m%d") + '-' + end_date.strftime("%Y%m%d") + '-' + @nickName + '.qif')
			puts "Generating QIF for '#{@nickName}' account (#{@bsb} #{@number}) in file #{output_file}"
			download_transactions(agent, start_date, end_date)
			out_file = File.new(output_file, 'w')
			out_file.puts to_qif
			out_file.close
			@closing_balance
		end
	end

	# A class to represent the connection to the NAB internet banking site. It represents the 'client' application
	# internally in the 'agent' variable and the list of accounts is a hash, keyed by the nick name of the account.
	class Fetcher
		attr_accessor :agent, :accounts

		# If you provide a user and password the new instance will attempt to login to the internet banking
		# site and download all the available accounts.
		def initialize(client_number = nil, password = nil)
			if client_number and password then
				login(client_number, password)
				download_accounts()
			end
			@agent
		end

		# Login to the internet banking website with the user and password provided as parameters.
		def login(client_number, password)
			@agent = WWW::Mechanize.new() do |a|
				a.log = Logger.new("mech.log")
				a.user_agent_alias = 'Mac FireFox'
				a.keep_alive = false # For slow site
			end
			login_page = @agent.get('https://ib.nab.com.au/nabib/index.jsp')
			login_form = login_page.form('sf_1')
			key = ''
			alphabet = ''
			sf1_password = 'document\.sf_1\.password\.value'
			login_page.search('//script[6]').each do |js|
				if js.text =~	/#{sf1_password}=check\(#{sf1_password},"([^"]+)","([^"]+)"\);/
					key = $1
					alphabet = $2
				end
			end
			login_form.userid = client_number
			login_form.password = check(password, key, alphabet)
			balances_page = @agent.submit(login_form)
			ObjectSpace.define_finalizer(self, self.method(:logout).to_proc)
			@agent
		end

		# Download the accounts associated with this user.
		def download_accounts()
			if not @agent then
				puts "Not logged in"
				return nil
			end
			@accounts = {}
			balances_page = @agent.get('https://ib.nab.com.au/nabib/acctInfo_acctBal.ctl')
			balances_page.root.css('table#accountBalances_nonprimary_subaccounts tbody tr').each do |elem|
				type = elem.xpath('.//a[@class="accountNickname"]/@href').text.strip.gsub(/[^(]*.([^,]*),([^)]*).*/,'\2').gsub(/'/,'')
				bsb, number = elem.xpath('.//span[@class="accountNumber"]').text.strip.split(' ')
				if not number then
					number = bsb
					bsb = nil
				end
				nickName = elem.xpath('.//span[@class="accountNickname"]').text.strip
				if not nickName then
					nickName = elem.xpath('.//span[@class="accountNumber"]').text.strip
				end
				current_balance = elem.xpath('.//td[2]').text.strip.gsub(',','').gsub(/([0-9.]*)[ 	]*DR/,'-\1').gsub('CR','').to_f
				available_balance = elem.xpath('.//td[3]').text.strip.gsub(',','').to_f
				@accounts[nickName] = Account.new(type, bsb, number, nickName, current_balance, available_balance)
			end
		end

		# Logout from the website.
		def logout()
			if not @agent then
				puts "Not Logged in"
				return nil
			end
			@agent.get('https://ib.nab.com.au/nabib/preLogout.ctl')
			@agent = nil
		end

		# Get a generic page using the currently instanciated agent.
		def get_page(uri, referrer = nil)
			@agent.get(uri, nil, referrer)
		end

		# Perform the end of month function. By default it uses today's date as the basis of the
		# processing. The function calculates the start and end of the preceding month and calls
		# the NABUtils::Account::generate_qif method for each account and calculates the closing balance as at the
		# end of the month in question and writes them all to a file called '<last day of month>-Closing Balances.csv'.
		def end_of_month(current_date = Date.today)
			start_date = Date.new((current_date << 1).year, (current_date << 1).mon, 1)
			end_date = (Date.new((start_date >> 1).year, (start_date >> 1).mon, 1) - 1)
			closing_balances = {}
			out_file = File.new(end_date.strftime("%Y%m%d") + '-Closing Balances.csv', 'w')
			@accounts.each do |key, a|
				closing_balances[key] = a.generate_qif(@agent, start_date, end_date) 
				out_file.puts "#{a.nickName}|#{a.bsb} #{a.number}|#{closing_balances[key]}"
			end
			out_file.close
			closing_balances
		end

	protected

		def check(p, k, a)
			# Implementation of the following javascript function
			# function check(p, k, a) {
			#	for (var i=a.length-1;i>0;i--) {
			#		if (i!=a.indexOf(a.charAt(i))) {
			#			a=a.substring(0,i)+a.substring(i+1);
			#		}
			#	}
			#	var r=new Array(p.length);
			#	for (var i=0;i<p.length;i++) {
			#		r[i]=p.charAt(i);
			#		var pi=a.indexOf(p.charAt(i));
			#		if (pi>=0 && i<k.length) {
			#			var ki=a.indexOf(k.charAt(i));
			#			if (ki>=0) {
			#				pi-=ki;
			#				if (pi<0) pi+=a.length;
			#				r[i]=a.charAt(pi);
			#			}
			#		}
			#	}
			#	return r.join("");
			# }

			# puts "check(password, #{k})"

			# 0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
			# Generate this above as an array of chars
			r = []
			last_index = p.length - 1
			# TODO: Use the passed alphabet instead
			alphabet = ('0'..'9').to_a + ('a'..'z').to_a + ('A'..'Z').to_a
			(0..last_index).each do |i|
				r[i] = p[i,1]

				pi = alphabet.index(r[i])
				unless pi.nil? or i >= k.length
					ki = alphabet.index(k[i,1])
					unless ki.nil?
						pi -= ki
						pi += alphabet.size if pi < 0
						r[i] = alphabet[pi]
					end
				end
			end
			r.join('')
		end
	end
end

nf = NABUtils::Fetcher.new(ARGV[0], ARGV[1])
start_date = ARGV[2] ? ARGV[2] : Date.new((Date.today << 1).year, (Date.today << 1).mon, 1)
end_date = ARGV[3] ? ARGV[3] : (Date.new((start_date >> 1).year, (start_date >> 1).mon, 1) - 1) unless end_date
balances = nf.end_of_month
puts "as at #{end_date}:"
balances.each { |key, b| puts "   '#{key}' balance is #{b}" }