Table of contents
1. Obtaining a statement file from your bank
2. Importing the Statement file
2.3.1 Statement Layouts and how to map them
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 section 2.3.1 of this guide ('Statement Layouts and how to map them'), 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.
Automatic bank feed guide2. Importing the Statement file
This section explains the process of uploading the file and mapping the statement.
To begin, 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 to continue.
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
| Field Name | Required | Notes |
Ignore | Map this field against any column you don’t want to import data from. | |
Description | Yes | Map this field against the description of the transaction. This can be assigned to multiple columns if required. |
Amount* | Map this field to a column where payments and receipts appear together (receipts as positive and payments as negative). | |
Money in* | Map this field to a column with receipts (credits) only. | |
Money out* | Map this field to a column with payments (debits) only. | |
Date | Map this field to the transaction date column. Format DD/MM/YYYY or DD/MM/YY | |
Credit/debit toggle | 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. | |
Inverse amount* | Map this field to a column where payments and receipts appear together (receipts as negative and payments as positive). |
2.3.1 Statement Layouts and how to map them
.
Bank statements 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.
You do not need to map the balance from the statement as this will build up automatically in Clear Books.You can map as many columns as you like to the 'Description' field in Clear Books2.3.1.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 of data from the statement.
2.3.1.2 Amount
Use the amount field if the debits and credits are combined and the money out is shown as a negative value. Ignore any unnecessary columns e.g. the balance.
2.3.1.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.
3. Post Import
Once you have completed the mapping stage and imported the statement, this mapping is saved on the the Tools > Import > Overview > Manage import Mappings 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.
4. Deleting a Mapping
If the statement layout changes in the future, or you make a mistake in the mapping process, the imported statement may appear blank or the values incorrect. To resolve this, please try deleting the mapping and re-importing.
Click on the delete icon to the right of the appropriate bank account. Once this is done you can begin the import process again (Section 2 of this guide).
5. 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.
The Bank Explain screen overview
You can view all fully explained imported statements on the Money > Bank Statements > History menu.