Import bank statement

How to import bank transactions from a file downloaded from your bank. Use this when your bank doesn't have a bank feed, your banking concern feed isn't working or y'all accept historical transactions not included in your bank feed. Import using XLS, CSV, QIF and OFX files.

If your bank statement is merely available in PDF format, consider using AutoEntry to import directly into Accounting. Come across Import your statement using AutoEntry.

What you need to know

Once your argument has been uploaded you tin can also:

  • Apply bank rules to transactions to automate the process, saving you lot fourth dimension.

  • Bulk delete whatsoever duplicate transactions before importing them.

Download your bank statement

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

Yous 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 tin can upload your OFX file without needing to edit or view it, and nosotros'll be able to process it. If your banking company gives you the option to download your statement in this format we recommend using this over other formats.

The maximum file size you lot can upload is 250 MB.

We can just import transactions from 1 banking company business relationship at a time. We'll import transactions from the starting time bank account within a file, and ignore the balance. Brand sure that y'all 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 and then nosotros can process information technology correctly,. Nosotros volition prompt you lot to ostend 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 utilize Notepad, or use TextEdit if y'all 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 yr.

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

The maximum file size you tin can upload is 250 MB

CSV files

Your file must incorporate the right headers and then we can upload information technology. The headers you demand to use will depend on the file type and how many columns are in your file.

The file must contain information in the right date, amount and description formats listed in the steps below.

Tips to create your CSV file

There two ways to create the CSV file to import

  • Suit 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 depository financial institution into our template.

CSV file with 3 columns

Download a 3 column CSV template

  1. The file must contain columns labelled Date, Description and Amount and exist in that order.

  2. Dates in the Appointment cavalcade must be separated by a slash, such every bit 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 ane line.

  5. The Amount column must but comprise numbers.

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

  7. Payments out must take a negative value.

CSV file with 4 columns

Download a 4 cavalcade CSV template

  1. The file must contain columns labelled Date, Description, Money in, Money out and exist 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 Clarification column must all exist on ane line.

  5. The Coin in and Money out columns must merely incorporate numbers.

  6. Payments in must exist in the Money in cavalcade.

  7. Payments out must exist in the Coin out column.

Add together more details with columns

If you want to upload more than detailed transaction information, you can add boosted columns in your file. You tin include data similar:

  • Transaction reference

  • Payee name

  • Accost

Addtional column headings

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

Header options Information in your column
Bank check number For example, 650240
Bank check
Bank check ref
Document number
Reference For case, 20-inch lemon drizzle block
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 lawmaking
Mail service-code
Phone number For case, 07700 900 982
Phone
Phone #
Payee bank ID For example, HSBC
Payee BankID
BankID
Banking concern ID
Payee business relationship For case, 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 desire to import data from other tabs in your file, you can:

  • Re-create all the information into the first tab.

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

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

Import your statement in 3 steps

Pace 1 of 3

Upload your file

  1. From Banking, select the bank account y'all desire to import transactions to.

  2. From Deportment select the drib-downward arrow and Import statement.

  3. Select your file type.

  4. To add the file you tin either:

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

    or

    Drag and drop the file.

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

    • Twenty-four hours, month, year

    • Month, day, year

    • Yr, month, day

  6. Add a comment to aid you place the import later.

  7. Select upload file.

Stride 2 of 3

Verify transactions

After the file upload, y'all need to tell us which transactions show:

  • Money coming into the account

  • Coin coming out of the account

To aid with this, i of your transactions will be displayed on screen. You demand to confirm your transaction as either money in or coin out of the business relationship. We will apply this rule to the residue of your transactions.

Step 3 of 3

Review transactions

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

Once you accept reviewed the transactions select Import transactions. You will exist taken back to the Banking screen. In one case the transactions take been imported into your account, they will show on your cyberbanking tile.

Review banking concern transactions

The number of imported transactions from your statement are shown on the Cyberbanking page. Select the number to view all transactions on the incoming banking concern transactions page.

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

Match, create or transfer transactions

For each transaction you lot 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 business relationship

  • Delete transactions you don't desire to import

Help with importing a statement

Fixing import bug

If your file isn't imported, review the list of errors to see which entries failed and why. Right the bug in your file and and so effort the import again.

Tips for creating a CSV file

If y'all are consistently getting errors when importing, try copy and pasting into a template, then re-create the formatting from an example row across all the copied rows:

  1. Download CSV template that matches the information from your depository financial institution's CSV file. The CSV file from your depository financial institution volition usually have either a single amount cavalcade (3 cavalcade CSV file) or a column for money in and a column for money out (4 column CSV file).

  2. In the CSV template file, exit the heading and the top instance row, then delete the information from all other rows.

  3. In your banking company's CSv file,

    1. Remove any columns that are not in the template file.

    2. Re-create all the remaining rows and columns.

  4. In the CSV template,

    1. Right click under the elevation case row (row iii),

    2. In the paste options, choose Paste as values.

  5. Copy the formatting from the tiptop example to all the pasted rows below.

  6. Make certain corporeality columns with no values are left bare and do not contain zeros, 0.00.

  7. Remove any special characters.

Unable to upload bank argument

Find out about the different file types nosotros support.

Employ bank rules to transactions

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

Delete transactions you don't desire to import

Notice out how to delete duplicate transactions, including how to relieve time with bulk delete.

Delete transactions you don't want to import