1. Obtaining a statement file from your bank
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.
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 'Statement Layouts and how to map them' below, the system will automatically do the mapping for you (these file types tend to have a permanently fixed format which we have mapped in the back end).
Alternatively, to the bank statement import tool, you can use the auto bank feeds feature which will import statements automatically.
2. Importing the Statement file
This section explains the process of uploading the file and mapping the statement.
Navigate to the Money > Bank statements > Import statement menu.
2.1 Select account
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.
2.2 Upload file
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.
2.3 Map file to fields
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).
3. Statement Layouts and how to map them
Bank statements from banks come in a variety of different layouts. 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.
3.1 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.
3.3 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.
4. Post Import
Once you have completed the mapping stage and imported the statement, 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.
Your imported statements will appear on the Money > Bank accounts menu under the table headed Imported statements that need explaining.
5. Deleting a Mapping
If the statement layout 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.
Also, in general, if you experience any problems preventing you from importing, or you experience problems with the statement format once it has been imported, please try deleting the mapping and re-importing.
Click on the delete icon to the right of the appropriate bank account.
6. Explaining Transactions
Once you have imported your statement you can begin explaining transactions so that they update the reports and bank account balances of your Clear Books account.
You can view all fully explained imported statements on the Money > Bank accounts > History menu.