Jason Trost’s Blog

I’ve been messing around with xero.com online accounting service. They only accept bank imports of QIF and OFX, and my bank NatWest only exports CSV…sigh. I spent the better part of a day looking for a way to convert CSV to QIF, and couldn’t find any good solutions so I hacked one together. This is based on Jelmer Vernooij’s script that he kindly posted to his blog.

I’ve been an active user of open source software since RedHat 7.3, but this is the first time I’ve open sourced any of my code. Better late than never. Drink it in!

#!/usr/bin/python
# Very simple converter from NatWest Bankline .csv files to
# QIF files for use by (among others) gnucash.
#
# Written by Jelmer Vernooij <jelmer@samba.org>, 2007
# Modified by Jason Trost <trostmail@gmail.com>, 2009
#
# Usage python bankline_csv2qif.py < input.csv > output.qif

import csv, sys

lookup_dict = {
    'AMD' : 'Amendments History',
    'BAC' : 'Automated Credit',
    'BOE' : 'Bill of Exchange',
    'EBP' : 'Bill Payment',
    'MGT' : 'Bonds & Guarantees',
    'BAE' : 'Branch Acc Entry',
    'LST' : 'Bulk Entry',
    'CRD' : 'Card Pymnt or Cash',
    'TLR' : 'Card Pymnt or Cash',
    'POS' : 'Point of Sale (Card Transaction)',
    'ATM' : 'Cash Withdrawal',
    'CHP' : 'CHAPS Transfer',
    'CHG' : 'Charges',
    'CHQ' : 'Cheque',
    'CCD' : 'Cheque at Despatch',
    'CAE' : 'Cheque Collection',
    'CCB' : 'Cheque Collection',
    'CND' : 'Cheque Negotiation',
    'BCA' : 'Clean Acceptance',
    'CNA' : 'Clean Cheque Neg',
    'BCR' : 'Clean Reimburse',
    'C/R' : 'Credit',
    'D/R' : 'Debit',
    'D/D' : 'Direct Debit',
    'DIV' : 'Dividend',
    'DCR' : 'Documentary Credit',
    'ECA' : 'eurocheque',
    'ECD' : 'eurocheque',
    'EBP' : 'Electronic Payment',
    'MEC' : 'Export Credits',
    'DFT' : 'Foreign Draft',
    'BGT' : 'Guarantees',
    'MIC' : 'Import Credits',
    'ITM' : 'Incoming CHAPS',
    'IP1' : 'Inland Payments',
    'IP2' : 'Inland Payments',
    'IP3' : 'Inland Payments',
    'IP4' : 'Inland Payments',
    'IP5' : 'Inland Payments',
    'IPB' : 'Inland Payments',
    'INT' : 'Interest',
    'ITL' : 'International Transfer',
    'MIB' : 'Inward Bills',
    'ICP' : 'Inward Ccy Pymt',
    'ISP' : 'Inward Stg Pymt',
    'LST' : 'Supplementary List',
    'LVP' : 'Low Value Payment',
    'MKD' : 'Market Deal',
    'MFD' : 'Maturing Fwd Deal',
    'LON' : 'New Loan',
    'NDC' : 'No Dividend C/foil',
    'BCO' : 'Non Mkt Close Out',
    'NVD' : 'Novated Deal',
    'OTR' : 'OnLine Transaction',
    'MOB' : 'Outward Bills',
    'RTF' : 'Relay Transfer',
    'ADV' : 'Separate Advice',
    'STL' : 'Settlement',
    'S/O' : 'Standing Order',
    'TFP' : 'Trade Finance Product',
    'TFR' : 'Transfer',
    'U/D' : 'Unpaid Direct Debit',
    'SDE' : 'Urgent Euro Tfr',
    '100' : 'MT100',
    '101' : 'MT101',
    '103' : 'MT103',
    '200' : 'MT200',
    '201' : 'MT201',
    '202' : 'MT202',
    '203' : 'MT203'
}

rows = csv.reader(sys.stdin)

header = rows.next()

assert header == ['Sort Code', 'Account Number', 'Account Alias',
                  'Account Short Name', 'Currency', 'Account Type',
                  'BIC', 'Bank Name','Branch Name','Date',
                  'Narrative #1', 'Narrative #2', 'Narrative #3',
                  'Narrative #4', 'Narrative #5', 'Type', 'Debit',
                  'Credit']

print '!Type:Bank\n'

for l in rows:
    print "D%s" % l[9] # date
    if l[16]:
        print 'T%s' % l[16] # amount
    else:
        print 'T%s' % l[17] # amount
    print 'P%s -- %s' % (l[10], l[11]) # Payee field
    print 'M%s ; %s ; %s ; %s ; %s ; %s ; %s' % \
        (l[10], l[11], l[12], l[13], l[14], l[15], \
             lookup_dict.get(l[15], 'No lookup')) # payee / description
    print 'L' #%s' % l[7]
    print '^\n' # end transaction