Skip to content

Instantly share code, notes, and snippets.

@awol

awol/CSVtoQIF.sh

Created October 14, 2009 10:03
Download ZIP

Revisions

  1. Philip Haynes revised this gist Apr 15, 2012. 2 changed files with 148 additions and 0 deletions.
    65 changes: 65 additions & 0 deletions CSVtoQIF.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,65 @@
    #!/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[*]}"
    83 changes: 83 additions & 0 deletions GetDetails.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,83 @@
    #! /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}" }
  2. @invalid-email-address Anonymous created this gist Oct 14, 2009.
    336 changes: 336 additions & 0 deletions NABUtils.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,336 @@
    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}" }