How Do I Import A Bank Statement?

Purpose:
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.

Preparation:

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.

Processing:

1. Open the CSV/Excel file – this is a Lloyds format

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

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.