Managing your finances is critical to every business and I think accountancy should be an effective tool for managing a business. However, for all too many SMEs accountancy is all about compliance, simply ensuring that accounts are filed correctly and taxes paid.

Now I’m not an accountant, however I have always been of the opinion that anyone in business has to be able to understand the finances of the business. So about a year ago I went and looked for the accountancy tools that would help me (a non-accountant) to manage my business. Eventually I settled on Xero, for its simplicity and because it was online giving me access no matter where in the world I was.

At that time Ulster Bank Anytime Banking supported the OFX format which was readily support by Xero, allowing me to easily import my bank statements each month. However about 2 months ago Ulster Bank stopped supporting Anytime Banking and move their customers to a new system called Bankline. Despite repeated reassurances from Ulster Bank that all features of Anytime would be supported in Bankline they dropped support for OFX and only supported CSV.

This was a disaster for Ulster Bank user who used the Xero system as it made reconciliation of your bank account slow and error prone. Last week, at the end of July 09 Xero started to support the import of CSV formatted bank statements. This post looks at the tweaks that you need to make in order to get your Ulster Bank statement into Xero.

Exporting your Ulster Bank Bankline CSV statement

Exporting your Ulster Bank Bankline CSV statement

  1. Log into your Ulster Bank Bankline account as normal and navigate to the statement of the account that you want to import.
  2. At the top of the page select the date range that you want to statement to be for and press the go button to the right of the date fields.
  3. Click the ‘export statement’ link at the bottom of the page. This will save the statement onto your computer in CSV format. The fields in this file are different to the ones required by Xero, however the file is easily editable to make it compatible.
  4. Open the file in Excel or Open Office.
  5. Xero expects the following columns which are not in your CSV file – Cheque number, Transaction amount. Add these two columns into the spreadsheet.
  6. Copy the amounts from the Debit and Credit columns into the transaction amount column. The transaction amount column should now be a solid list of numbers, positive numbers for Credits and negative numbers for Debits.
  7. Save your spreadsheet ensuring you save it as a CSV file.
  8. Bankline puts the cheque number into the Narrative #1 field. Whenever the type = ‘CHQ’ copy the number from the Narrative #1 field into the respective cell in the new Cheque number field.
  9. Log into Xero as follows and from the account of the relevant bank account menu select ‘Import a statement’.
  10. Click the ‘choose file’ button and select your CSV file and click the ‘Import’ button.

    Mapping your CSV column to Xero

    Mapping your CSV column to Xero

  11. You are now presented with a page in Xero that allows you to map columns from the CSV file to Xero. Make the following mappings:
    1. ‘Date’ in the CSV to ‘Transaction Date’ in Xero
    2. ‘Narrative #1’ in the CSV to ‘Description’ in Xero
    3. ‘Narrative #2’ in the CSV to ‘Payee’ in Xero
    4. ‘Type’ in the CSV to ‘Transaction Type’ in Xero
    5. ‘Cheque Number’ in the CSV to ‘Cheque No.’ In Xero
    6. ‘Transaction Amount’ in the CSV to ‘Transaction Amount’ in Xero
    7. Press the save button and you’re done. Xero will remember these mappings so you won’t have to do them again for subsequent imports.

Update: 2nd December 2009

After the latest Xero release there are two more optional fields.  Both of these field not have to have any content. The fields are:

  1. Account Code
  2. Tax Rate

You do not need to add this to your CSV it will work fine without them

Update May 2010

Xero have told me (see comment below) that now there are actually only two mandatory fields: transaction date and transaction amounts. Everything else is now optional which will make life a little easier for people.