Skip to content
logo Knowledgebase
Page Language

Import a bank statement

Created on  | Last modified on 

Summary

How to import a bank statement.

Resolution

If you have historical transactions, a large number of transactions, or if your bank feed isn’t working, you can upload them from a bank statement. We give you the flexibility to use whichever format works best for you, including XLS, CSV, QIF and OFX.

If your bank statement is only available in PDF format, consider using AutoEntry to export it directly into Sage Accounting. Import your statement using AutoEntry.

Once your statement has been uploaded you can also:

  • apply bank rules to transactions to automate the process, saving you time

  • bulk delete any duplicate transactions before importing them

Download your bank statement

Log in to your online banking service and download your bank statement.

You need to make sure you download the file as one of the following types:

  • OFX

  • QIF

  • CSV

  • XLS

OFX file

OFX files are an industry-standard format. This means you can upload your OFX file without needing to edit or view it, and we’ll be able to process it. If your bank gives you the option to download your statement in this format we recommend using this over other formats.

The maximum file size you can upload is 250 MB.

We can only import transactions from one bank account at a time. We’ll import transactions from the first bank account within a file, and ignore the rest. Make sure that you are not importing transactions for several accounts using a single OFX file.

QIF file

We need to know how the dates are formatted in the file so we can process it correctly,. We will prompt you to confirm this prior to uploading.

To find out how your dates are formatted:

  • Open your QIF file in a text editor. If you’re a Windows user, you can use Notepad, or use TextEdit if you use a Mac.

  • Find a date linked to a transaction and take note of their order, for example DD/MM/YYYY. We need to know the order of day, month and year.

  • Select the correct date order when you upload your file.

The maximum file size you can upload is 250 MB

CSV files
 TIP: Your file must contain the right headers so we can upload it. The headers you need to use will depend on the file type and how many columns are in your file.The file must contain information in the correct date, amount and description formats listed in the steps below.
 
CSV file with 3 columns

Download a 3 column CSV template

  1. The file must contain columns labeled Date, Description and Amount and be in that order.

  2. Dates in the Date column must be separated by a slash, such as 04/01/2021.

  3. The Description column must contain text and not left blank.

  4. The text in the Description column must all be on 1 line.

  5. The Amount column must only contain numbers.

  6. Payments in and payments out must both be in the Amount column.

  7. Payments out must have a negative value.

CSV file with 4 columns

Download a 4 column CSV template

  1. The file must contain columns labeled Date, Description, Money in, Money out and be in that order.

  2. Dates in the Date column must be separated by a slash, such as 04/01/2021.

  3. The Description column must contain text and not left blank.

  4. The text in the Description column must all be on 1 line.

  5. The Money in and Money Out columns must only contain numbers.

  6. Payments in must be in the Money in column.

  7. Payments out must be in the Money out column.

Add more details with columns

If you want to upload more detailed transaction information, you can add additional columns in your file. You can include information like:

  • transaction reference

  • payee name

  • address

Any new column headings must match one of the column headings below:

Header options Information in your column
Check number For example, 650240
Check
Check ref
Document number
   
Reference For example, 20-inch lemon drizzle cake
Reference number
Transaction reference
Transaction ref
   
Payee name For example, Jane Doe
Payee
Payee description
Customer reference
Customer
Cust ref
   
Address Line1 For example, Newcastle upon Tyne
Address
Street
House/street
Address Line2
Address Line3
State
Region
Province
   
Zipcode For example, NE13 9AA
ZIP
Zip-code
   
Phone number For example, 07700 900 982
Phone
Phone #
   
Payee bank ID For example, HSBC
Payee BankID
BankID
Bank ID
   
Payee account For example, 12345678
Payee account number
Payee account ref
XLS file

If you’re uploading an XLS or XLSX file, we can only process the first tab in your file.

If you want to import data from other tabs in your file, you can:

  • Copy all the data into the first tab.

  • Upload the data from each tab as a separate file.

  • Upload each tab individually, using the same file. You'll need to move each tab to the first tab position before starting a new import.

Import your statement in 3 steps

Step 1 of 3

Upload your file

1. Go to Banking. Select the account you want to import transactions to.

2. From Actions select the drop-down arrow and Import statement.

3. Select your file type.

4. To add the file you can either:

Choose Select file and find the bank statement file on your computer. Select Open to add the bank statement.

or

Drag and drop the file.

5. Select the date format used in your file so we can process it correctly. Choose either:

Day, month, year

Month, day, year

Year, month, day

6. Add a comment to help you identify the import later.

7. Select upload file.

Step 2 of 3

Verify transactions

After the file upload, you need to tell us which transactions show:

  • money coming into the account

  • money coming out of the account

To help with this, one of your transactions will be displayed on screen. You need to confirm your transaction as either money in or money out of the account. We will apply this rule to the rest of your transactions.

Step 3 of 3

Review transactions

All your transactions will be listed and shown as either money in or money out of the account. If there are errors with the way these are categorized, you can go back to the Verify screen.

Once you have reviewed the transactions select Import transactions. You will be taken back to the Banking screen. Once the transactions have been imported into your account, they will show on your banking tile.

Review bank transactions

The number of imported transactions from your statement are shown on the Banking page. Select the number to view all transactions on the incoming bank transactions page.

The Imported from statement message shows each transaction that has been imported from your bank statement.

Match, create or transfer transactions

For each transaction you need to choose whether to:

Help with importing a statement

Fixing import issues

If your file isn't imported, review the list of errors to see which entries failed and why. Correct the issues in your file and then try the import again.

Unable to upload bank statement

Find out about the different file types we support.

Apply bank rules to transactions

Find out how to apply bank rules to transactions imported from your bank statement.

Delete transactions you don’t want to import

Find out how to delete duplicate transactions, including how to save time with bulk delete.

Delete transactions you don’t want to import