Xero Bank Accounts – How to Import Bank Statement Transactions

Author: Mark Cunningham

Published: May 25, 2018

Categories: Bank Accounts

Free-Xero-eBook-550x623

After you have set up a bank account in Xero you may need to import some transactions or an entire bank statement. Xero has an Import Bank Transactions tool that makes this easy to do. In this tutorial, we will import a few bank transactions into a new bank account in the demo company.

Set Up the New Bank Account

Before we get into importing transactions, please set up a new bank account Xero’s demo company by following the steps in our How to Add a Bank Account tutorial.

Once you have finished, you should see the account listed in the Bank Accounts section of Xero. To see this, go to Accounting → Bank accounts from the main menu. Your new bank account should have a nil opening balance and no transactions:

0067-xero-bank-accounts-import-statement-01

How to Import a Bank Statement

To start the process of importing bank transactions, click on the Import a bank statement link or click on Manage Account → Import a Statement:

0067-xero-bank-accounts-import-statement-02

In the next screen, you will see basic instructions on how to download files from your bank’s website. The instructions here are not comprehensive as each bank’s website will be different. If you have trouble downloading the correct files, contact your bank for further instructions.

Note: you cannot upload more than 1,000 (one thousand) bank statement lines at a time. If you wish to upload more than 1,000 lines, you need to create more than one file and upload them one at a time.

As you can see from Xero’s instructions, there are three different types of files that can be uploaded into your bank account in Xero:

  1. OFX: an Open Financial Exchange file is a text file containing financial data.
  2. QIF: a Quicken Interchange Format file is a text file containing financial data.
  3. CSV: a Comma Separated Value file that uses commas to separate values.

0067-xero-bank-accounts-import-statement-03

How to Upload a CSV File

In this tutorial, we are going to upload a CSV file. As the bank account we have created is fake and we cannot download a file from a bank’s website, we must create a CSV file and add some transactions to it. To do this, click on the Download our CSV template link:

0067-xero-bank-accounts-import-statement-04

Once you have downloaded the file, open it in Microsoft Excel or Google Sheets. Now we can populate a few lines of the file with some data that will become our fake bank transactions. For this exercise, populate the first three lines underneath the header line as follows:

0067-xero-bank-accounts-import-statement-05

When you save the file, you may be asked by Excel or Sheets whether you want to keep the file in the CSV format. Be sure to say yes, otherwise your file will be saved as a spreadsheet and won’t be able to be uploaded into Xero. This is what the message looks like in Excel when I go to save the file:

0067-xero-bank-accounts-import-statement-06

When you have finished preparing and saving your CSV file, return to Xero and select the file for upload via the Browse button. Click on Import once you are ready to upload the transactions:

0067-xero-bank-accounts-import-statement-07

If this is the first time you have imported a bank statement, you will see a screen that will ask you whether the fields in your file are mapped to the right places in Xero. This can be a confusing screen at first but is easy to comprehend once you know what to do.

For our upload, we can see that the fields in our CSV file are going to be assigned to fields in Xero that make sense:

  • The * Date field of our bank statement is being mapped to the Transaction Date field in Xero.
  • The * Amount field of our bank statement is being mapped to the Transaction Amount field in Xero.
  • The Payee field of our bank statement is being mapped to the Payee field in Xero.
  • The Description field of our bank statement is being mapped to the Description field in Xero.

It is also worth noting that the first row of our CSV file was made up of column headings such as * Amount rather than bank transaction data. This is why the checkbox is ticked for the Don’t import the first line because they are column headings option. If you import a file that does not have a row of headings at the top, you may need to un-tick this box:

0067-xero-bank-accounts-import-statement-08

As we are happy with our mapping, we will press Save to continue. After the file imports, a pop-up box will appear with the results:

0067-xero-bank-accounts-import-statement-09

Click OK to continue. You should now be taken to a screen that shows the transactions you uploaded into the bank account:

0067-xero-bank-accounts-import-statement-10

That’s all you need to do to upload a bank statement in Xero. From here, you can go ahead and reconcile each transaction to the general ledger.

Recent Posts

What is the Cloud?

What is the Cloud?

The cloud is the Internet; a worldwide system of computer networks. Cloud computing means delivering computer services over the Internet, including...

read more
What is GST?

What is GST?

If you were asked to explain GST, what would you say? Would it go something like this: “It’s a tax you pay on things you buy.” If so, you're not...

read more