Automatic Bank Reconcilliation
Automatic Bank Reconciliation
The Automatic Bank Reconciliation process is used to automatically (as far as possible) reconcile transactions recorded in general ledger (bank) account(s) within Aqilla against statement files you download from your bank(s) and upload to Aqilla.
If you are using Automatic Bank Reconciliation you should not also use Manual Bank Reconciliation. When you use Automatic Bank Reconciliation you upload your bank statement to Aqilla and match bank statement lines to transactions in Aqilla. If you have marked transactions in Aqilla as reconciled (using Manual Bank Reconciliation) they will no longer be available to match against bank statement lines - which you will therefore be unable to reconcile.
During initial system set-up (and if you are intending to use Automatic Bank Reconciliation) you may need to use Manual Bank Reconciliation temporarily to reconcile bank transactions or opening balance(s) that have been manually entered into Aqilla (prior to statements that have been / will be uploaded using the Automatic Bank Reconciliation process). If you are fully using Automatic Bank Reconciliation it is good practice to hide the Manual Bank Reconciliation option from the Processes menu - ask your consultant or Aqilla Support to do this.
1. Bank Account Details
Before you use the Automatic Bank Reconciliation process you must create bank account(s) at Reference >General Ledger Accounts (make sure you select an account currency unless your bank account is truly multi-currency) and at Reference > Bank Account Details. Also make sure you link from Bank Account Details to the associated general ledger account as shown below:
2. Upload Bank Statement to Aqilla
You must start by downloading your statement from your bank. Whilst this is outside the scope of Aqilla Support we recommend that you establish a file naming and storage convention to manage downloaded statement files.
In most cases, files are transferred in comma separated value (.csv) format - although other formats exist.
Never open then save a .csv bank file using MS Excel!
For example MS Excel sometimes interprets a concatenated sort code and account number - e.g. 20141700534532 - as a very large number and converts it to "scientific" format - e.g. 7.65276E+13. The safest way to open a .csv bank file is to use MS Windows Notepad (or similar product).
To upload a file to Aqilla click on the Upload file for import icon shown below:
You will be asked to navigate to the file you wish to upload:
When you click Upload the system may give you the following message:
Cannot proceed - unknown format
This means that Aqilla has not recognised your bank statement file format; there are two common reasons for this:
The bank format is not (yet) supported be Aqilla - see Articles > Bank Interfaces for details of supported formats
The file has been corrupted by MS Excel - see above
If Aqilla recognises the bank file format (i.e. it is a supported format) the statement will be uploaded into the system and you will see a message something like:
Lloyds Bank: Import finished successfully, 47 transactions were imported, 0 duplicates ignored
The imported statement will be displayed on the right-hand side of a split-screen (the left-hand side shows unreconciled bank transactions for the bank in question) thus:
Note that you can delete downloaded transactions at this stage by clicking on the icon shown below and entering a date range.
3. Reconciliation
The following filters (and options) can be applied:
Field | Description |
|---|---|
Account Code | Only general ledger accounts (connected as shown above) will be available for selection. |
Cut Off Date | Unreconciled transactions up to and including this date will be displayed. |
Cut Off Balance | The balance of the account at the cut-off date is displayed. |
Matching Mode | Match Value: Will match two transactions based on their value only. Match Descriptions and Values: Will match two transactions based on the Description field and the value. Match References and Values: will match two transactions based on the Transaction Reference and the value |
Currency | This filters by currency if the account is a true multi-currency account. |
Automatic Match | If set to Yes the system will suggest matching transactions based on value. If set to No (sometimes better if there are many transactions of the same value on an account) no matches will be suggested. |
Rows per Page | If set to Yes the system will suggest matching transactions based on value. If set to No (sometimes better if there are many transactions of the same value on an account) no matches will be suggested. |
The Cut Off Balance is a calculated field. There are two calculations involved:
Opening Balance is the sum of all reconciled transactions (i.e. transactions with a Matching ID). Note that the transaction date of the Opening Balance is the transaction date of the most recently reconciled transaction.
Cut Off Balance is the sum of all transactions with a Transaction Date up to and including the Cut Off Date.
In the following (intentionally simple) example, Automatic Match has been set to Yes. The system shows all the currently un-matched transactions on the account and has suggested three possible matches.
4. Correcting Reconciliation Errors
5. Error Messages and Warnings
You may experience the following error messages and warnings whilst using the Automatic Bank Reconciliation process:
Cannot proceed - unknown format
Aqilla does not recognise the file format.