Skip to content Skip to main navigation Skip to footer

How Do I Import A Bank Statement?

To save time and reduce errors. Note: this is only a guide and you may have to adapt it to suit the various banks formats.


  1. Ensure opening bank balance on statement agrees with opening balance on Bank Reconciliation – if different
    identify differences and post to ledgers until difference = nil.
  2. Download bank statement from bank as csv or excel (if excel you will need to save it as a csv when you finish working with it before importing.
  3. Have aged debtors list, remittances, aged creditors and nominal code list to hand plus anything else you may need.


  1. Open the CSV/Excel file – this is a Lloyds format
How Do I Import A Bank Statement?

Columns C, D and H can be ignored – in Column I use ‘=If( F2>0, F2*-1,G2)’ and auto fill down then copy and paste special Column I into Column J. In a preformed spreadsheet

How Do I Import A Bank Statement?

where I2 contains the formula

Copy and paste the bank export into columns A to H and copy the formula in I down and copy & paste special into J. You will then need to identify all of the items – by ledger and account – and calculate the VAT where appropriate.

In the import file – first time only the columns will be Date (A) = 1
Bank (K) = 11
Description (E) = 5
Reference (B) = 2
Ledger (L) = 12
Account (M) =13
VAT Code (N) =14
Gross Amount(J) = 10
VAT Amount (0) = 15
Whilst this may seem onerous – once set up it will only take a couple of minutes to copy the exported file into the template spreadsheet and do the autofill and copying – what will take time (whatever way you do it) will be the columns L to O.