Introduction
The Automated Bank Reconciliation Process
The Automated Bank Reconciliation lets you reduce manual processes when reconciling system bank transactions with actual transactions in your bank accounts. It can be used in conjunction with Manual Bank Reconciliation.
The process is as follows:
- Import an electronic bank statement. This can be from online banking, or alternatively, you can set up Bank Feeds.
- Perform a bank reconciliation using automatic matching rules. If the system cannot find a match, it will suggest possible options. If none of these are correct, you can create and post a matching transaction and set rules for future matching. In future imports, the system will remember the matching rules.
Bank Reconciliation Schematic Overview
Here is an overview of the Bank Reconciliation process:
See:
Bank Reconciliation Troubleshooting
Bank Feeds (6B.1) - AIQ Academy
Importing bank statements manually (6B.2) - AIQ Academy
Transaction Matching and Quick Post (6B.3) - AIQ Academy
Matching Transactions
After importing, the screen displays transactions grouped into four colour-coded bands.
Green: Matched
This displays posted system transactions that match those on the imported bank statement file. They have all passed the matching decision algorithm.
Click Match All against the green band of Matched Transactions. These are transactions which are already posted in the accounts system that match up against those on the imported bank statement file. They have all passed the matching decision algorithm.
Alternatively, you can click on Matched to view the list of Matched Transactions. Then select each individually by clicking the Match button.
Yellow: Multiple Matches
This displays multiple potential matches found in the System that match a single transaction on the Bank Statement File. Here several system transactions have passed the matching algorithm. Examine these to select the correct match.
.png)
Click on Multiple Matches to view the list of potential matches. To get more information on transaction details, click on the Type link or go to the Bank List and click on View Transactions for your account and find a match. When you find the correct transaction, click the Match button for that transaction.
Red: No Match Found
The red band represents those transactions on the Bank Statement Import file for which no matches can be found in the system. They will mainly comprise Bank Transfers, Direct Debits, etc. that have yet to be recorded in the system's Bank Account.
Note, if required, you can also export the contents of the No Match Found category to Excel for printing or further review and analysis.
Option One: Match Transactions in the Bank Reconciliation Screen
- Click on Export to create an Excel record of unmatched transactions.
- Examine the unmatched transactions against the data in Bank List > View Transactions for your account.
- Once you have identified the matching system transaction, go to Bank > Reconcile Banks. In the Matched column, turn on the Show All filter. Against the appropriate corresponding transaction in the system, check Matched.
Option Two: Create New Postings for "No Match Found" Transactions
If the system does not recognise items on the Bank Statement and presents them in the No Match Found bucket, you can post them directly from the Import Bank Statement screen as follows:
- In the Imported Bank Statement section, click Create new Posting against the relevant transaction.
- Select the type of transactions you need to create:
- Purchase Payment / Sales Receipt
- Bank Payment / Bank Receipt
- Bank Transfer
- Customer Refund / Supplier Refund
- Then either:
- Complete the required posting details and click Post. This creates, posts, and marks the relevant transaction as matched in both the Supplier’s Account and/or the Bank Account(s).
- Click on Create Advanced Posting.
Advanced Payment
If you select a Purchase Payment, clicking Create Advanced Posting will bring up these additional options:
- Create split payment across GL Accounts: Click to display Sundry Bank Payments to allocate your Bank Payment (Debit Card Purchase) across several General Ledger Expense Postings. See Sundry Payments for more details.
- Create payment with advanced options: Click to display full Purchase Payments and Allocations facilities and create a payment. If you have entered the Supplier Code above, then the complete Suppliers details will autofill in Payments and Allocations. See Purchase Payments & Allocations for more details.
Advanced Receipt
If you select a Bank Receipt, clicking Create New Posting displays a set of possible transactions you can create. The required data depends on transaction type.
Create Advanced Posting for Bank Receipt includes:
- Create split lodgement across GL Accounts: This displays the Sundry Bank Receipts program. See Sundry Receipts for more details.
- Create receipt with advanced options: This displays the Sales Receipts and Allocations program. See Sales Receipts & Allocations for more details.
- Create split lodgement across Customer accounts: This displays the Customer Receipts Bulk Entry program. Click Auto Invoice to automatically create an Invoice for each entry. This is of special relevance if your primary source of Sales Transactions is from the Bank Statement File. See Sales Receipts - Quick Entry.
Allocating Multiple Supplier Payments
If you want to allocate multiple suppliers' payments to one amount on your bank statement use our bulk payment feature and the relevant bank file. See Bulk Payments along with Generic Bank Payment File Formats or Compatible Bank Statement File Formats.
Amber: Suggested Auto Post
This displays transactions in the import file that look like previously created and remembered examples. These remembered postings (with updated date, amount, and description from the imported transaction) appear here for you to examine.
Recognising Transactions for Suggested Auto Post
When you create a transaction, you can tag it. If a future imported file contains a similar transaction, it will appear in the amber band with updated date, amount, and description from the imported transaction. These remembered postings then must be processed.
Step One: Create Recognition Rule
The example here shows Purchase Payment made to a Supplier based on a specific Description. The Description from the Bank Statement File automatically fills the Recognise based on Description field. Edit if needed. If the Description changes on future imported transactions, the system will still recognise it based on its Partial Match capability. 
You can also tell the system to recognise a transaction based on a partial match by using a wildcard character in the Recognise based on Description field. For example, if you are billed each month by British Telecom, you could recognise this transaction in the future by memorising the description as “BT*.” This means any reference starting with “BT….” will register as a possible match.
Click Memorise & Post. Alternatively, by just clicking Post, the system will automatically recognise any future transactions that have the same description.
Step Two: Process Suggested Auto Post Transactions
When the system encounters a remembered transaction, it will create an entry in the Suggested Auto Post (amber).
If needed, click Edit Post to review any memorised transactions:
- You can further edit the Recognise based on Description field. When finished, click Memorise & Post.
- If you want to remove a Suggested Auto Post rule, click Forget. The associated matching logic will clear.
Finish by clicking Post to reconcile these transactions individually or Post All.
Unmatching Transactions
Transactions can be unmatched in the Import Bank Statement, Bank Reconciliation, or Transaction Browser screens. See Bank Reconciliation Troubleshooting if you encounter issues with bank reconciliation.
From the Import Bank Statement Screen
If a suggested match is incorrect, click Clear Imported Transactions, followed by Synchronise Transactions. Note that this only applies for unmatched transactions. Once matched, transactions are marked as reconciled and disappear from the Import Bank Statement screen and can only be unmatched in the Bank Reconciliation screen (see next section).
From the Bank Reconciliation screen
If a transaction was matched in error in the Import Bank Statement screen, unmatch it in the Bank Reconciliation screen and perform a manual bank reconciliation. Note that unmatching it in the manual Bank Reconciliation screen, will not make it reappear in the Import Bank Statement screen. See Manual Bank Reconciliation for full details on this screen.
- Go to Bank List > Reconcile Now > Current Reconciliation. If there is an existing Bank Reconciliation open for this bank it will be loaded automatically. If there is no bank reconciliation open, a new one will be created.
- Make sure that the Show All parameter is on to see both Matched and Un-Matched Transactions. Deselect the match by unchecking the box.
From the Transaction Browser
If the transaction does not appear on the Bank Reconciliation screen, you can unmatch it in the Transaction Browser.
- Go to General Ledger > Actions > Transaction Browser and find the relevant Purchase Payment.
- Remove the bank reconciliation match by editing the transaction amount to zero. See Amending Transactions (last section) for more details.
Adjusting the Transaction Matching Algorithm
How the algorithm works
The matching algorithm is complex and works in a specific order. It will create a match if an unreconciled system transaction has a corresponding import file transaction with the following:
- For unreconciled Lodgements, the exact same amount, with Dates within 14 days of each other. For more on Lodgements, see Sales Receipts & Allocations.
- For both unreconciled Lodgements and Bulk Payments, the exact same Reference No. somewhere in its Description field.
- The exact same Amount and Date.
- The exact same Amount, with Dates within 14 days of each other, and a partial match on Description or Reference.
- The exact same Amount, with Dates within 14 days of each other.
- A matching Reference No. (for example, Cheque) and Amount.
Adjusting the algorithm
You can adjust these algorithm defaults. For example, the exactly matching amount as used above can become an approximately matching amount.
To modify the algorithm’s parameters:
- Go to Banks > Import Bank Statement.
- Click on the Settings icon.
- Adjust the parameters and click Save to apply these modified parameters to all your Bank Accounts.
- Click on the Refresh icon to carry out a re-match of any outstanding transactions. You can use this feature at any time throughout your work.
Matching Transactions from Re-Imported Bank Files
Re-importing a Bank Transaction File
- In the Import Bank Statement screen, click Clear Imported Transactions.
- Re-Import the same Bank File again. See Import Bank Statement.
What happens if the first import file had 100% matches?
If your first import of the Bank File resulted in automatically matched and processed transactions, then these will no longer appear on the import bars.
What happens if the subsequent file overlaps in date with a prior file?
If you create new Bank Statement Files with overlapping dates with a previous import, then these duplicate imported transactions will show up as No Match Found as the corresponding system transactions were previously reconciled. Therefore, the duplicates will fail the Matching criteria.
The purpose of the Import Bank Statement facility is to assist in the Bank Reconciliation process, not to replace it. Therefore, it is not necessary to find a match for all transactions on a Bank Statement file. Match as many as you can and leave the remaining unresolved transactions. When you have finished, click Clear Imported Transactions.