To obtain a bank statement file, log into your bank account online and navigate to the statements page. Select the date range that you wish to download the statement for and export the statement to your computer as a CSV. This process may differ from bank to bank.
This guide demonstrates how to import this statement file.
Other types of file formats supported by the bank import tool are OFX, QBO, QIF, SWI and 940 files. The format of these file types are fixed and do not need to be mapped as shown in Step 4 below.
Alternatively, to the bank statement import tool, you can use the auto bank feeds feature which will import statements automatically.
Importing the Statement file
Navigate to the Money > Bank statements > Import statement menu.
Under the Import a bank statement from a file heading, select a bank account from the Account for import drop-down box and hit Select account.
Click on the Choose file button and select your statement file that you downloaded from your bank.
Select a payment method in the drop-down box provided if you would like every transaction on the statement to use this payment method. The payment method can be changed as each transaction is explained.
Click on the Upload button.
If you are importing a CSV statement file, you will now need to map the columns of your statement to data fields in Clear Books. If you have imported a CSV statement file before, your mappings should be remembered so you won't be required to do this step again.
The table below should help explain which columns you should map each field to.
at least 1 of these fields must be mapped to the file
Map this field against any column you don’t want to import data from.
Map this field against the description of the transaction. This can be assigned to multiple columns if required.
Map this field to a column where payments and receipts appear together (receipts as positive and payments as negative).
Map this field to a column with receipts (credits) only.
Map this field to a column with payments (debits) only.
Map this field to the transaction date column. Format DD/MM/YYYY or DD/MM/YY
Map this field against a column showing the text 'credit' or 'debit' on each row. This must be used in combination with Amount, which should be mapped to a column where all the payments and receipts appear in one column as positive amounts.
Map this field to a column where payments and receipts appear together (receipts as negative and payments as positive).
Statement Layouts and how to map them
For additional help on the mapping process, below are some of the common types of statement layouts and how mapping can be processed in each case:
i. Money in/out
If you have one column showing money in and one column showing money out, you will need to map money in (a credit on a bank statement) to the Money in field and money out (a debit on a bank statement) to the Money out field. Ignore any unnecessary columns e.g. the balance.
Use the amount field if the debits and credits are combined (money out as a negative) and ignore any unnecessary columns e.g. the balance.
iii. Credit/debit toggle
This statement format a bit rarer than the two above, however, if you have a statement which has a column describing each transaction as a debit or credit, you will need to map the credit/debit toggle to this. You will also have to map the Amount field to the column which shows the balance of each transaction. Ignore any unnecessary columns e.g. the balance.
What happens next
Once you have completed the mapping stage, this mapping is saved on the the Money > Import rules > Mapping menu. This means that the next time you import a statement, the system will remember the mapping and apply this automatically.
If the statement template changes in the future, or you make a mistake, the imported statement may appear blank or the values incorrect. To resolve this, the current mapping must be deleted from the Money > Bank Import rules, use the reset mapping guide below for more help. This will mean that the next time you import you will need to create a new mapping.
Your imported statements will appear on the Money > Bank accounts menu under the table headed Imported statements that need explaining.
To begin explaining transactions, please start with the Bank Explain screen overview guide.
You can view all fully explained imported statements on the Money > Bank accounts > History menu.