Import bank statement

Summary

How to import bank transactions from a file downloaded from your bank into Sage Accounting Start.

Description

You can use a bank statement import:

When your bank doesn't have a bank feed
If your bank feed isn't working
If you have historical transactions not included in your bank feed

Resolution

What you need to know

The accounts start date should not be after your first transactions you wish to import

Download your bank statement

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

OFX/ASO file >

OFX or ASO 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 OFX.

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 what format the dates are in so we can process the file correctly. We will prompt you to confirm this before uploading.

To find what format the dates are in:

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 file >

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.

Tips to create your CSV file

There are two ways to create the CSV file to import:

Adjust the CSV file from your bank to match one of our CSV file templates
Download a template file, then copy and paste information from your bank into our template

CSV file with 3 columns

Download a 3 column CSV template

The file must contain columns labelled Date, Description and Amount and be in that order.
Separate the dates in the Date column must by a slash, such as 04/01/2021.
The Description column must contain text and not left blank.
The text in the Description column must all be on 1 line.
The Amount column must only contain numbers.
Payments in and payments out must both be in the Amount column.
Payments out must have a negative value.

CSV file with 4 columns

Download a 4 column CSV template

The file must contain columns labelled Date, Description, Money in, Money out and be in that order.
Separate the dates in the Date column must by a slash, such as 04/01/2021.
You musn't leave the Description blank.
The text in the Description column must all be on 1 line.
The Money in and Money out columns must only contain numbers.
Payments in must be in the Money in column.
Payments out must be in the Money out column.

Add more details with columns

For more detailed transaction information, you can add additional columns in your file. You can include information like:

Transaction reference
Payee name
Address

Additional Column Headings

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

Header options	Information in your column
Cheque number	For example, 650240
Cheque
Cheque 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
City
Town/city
Town

Postcode	For example, NE13 9AA
Post code
Post-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

Step 1 of 3 - Upload your file >

From Banking, select the bank account you want to import transactions to.
From Actions select the drop-down arrow and Import statement.
Select your file type.
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.
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
Add a comment to help you identify the import later.
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, we'll display one of your transactions on the 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 >

We'll list all your transactions, and show them as either money in or money out of the account. If we've categorised these incorrectly, you can go back to the Verify screen.

Once you have reviewed the transactions select Import transactions. You will then go back to the Banking screen. Once you've imported the transactions into your account, they'll show on your banking tile.

Review bank transactions

The number of imported transactions from your statement appear 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 you've imported from your bank statement.

Match, create or transfer transactions

For each transaction, you need to choose whether to:

Match transactions to bank rules
Match transactions to outstanding invoices, credit notes or receipts
Create a new transaction
Transfer from or to a different account
Delete transactions you don’t want to import

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. You'll need to correct the issues in your file and then try the import again. Read more on how to deal with bank statement import errors.

Tips for creating a CSV file >

To resolve import errors, copy and paste into a template. Then apply example row formatting to all copied rows:

Download CSV template that matches the information from your bank's CSV file. Bank CSV files contain either one amount column (3 columns) or columns for money in and money out (4 columns).
In the CSV template file, leave the heading and the top example row. Then delete the information from all other rows.
In your bank's CSV file:
Remove any columns that are not in the template file.
Copy all the remaining rows and columns.
In the CSV template:
Right click under the top example row (row 3),
In the paste options, choose Paste as values.
Copy the formatting from the top example to all the pasted rows below.
Make sure you leave the amount columns with no values blank and do not contain zeros, 0.00.
Remove any special characters.

Once you've uploaded your statement

Once you've uploade your statement you can also:

Apply bank rules to transactions to automate the process, saving you time
Bulk delete any duplicate transactions before importing them

Solution Properties

Solution ID: 222001000100087
Last Modified Date: Mon Dec 11 16:03:52 UTC 2023
Views: 0