This feature allows you to import electronic Bank Statement Files from your Bank on a regular basis (daily, weekly, etc.) using your Bank’s On-Line Transaction Export facility and enables you to match these imported transactions with the system’s Bank Accounts transactions. The feature also facilitates the creation and posting of new transactions into the system when you encounter entries on the Bank Statement File which do not exist in the system. These created postings can also be “remembered” and re-used in future imports.
The Bank Import and Matching facility is a productivity aid to the complete process of Bank reconciliation. It should be carried out periodically to ensure the systems view of Bank Accounts are in accord with the real Bank Account’s status.
First, you will need to create a Bank Transaction File. To do this, log into your On-Line Banking Facility and, having chosen the relevant Bank Account, choose “Export Data”. Select the relevant Date range and click on “Export”. Then “Save” the file to a convenient location on your local computer/file server with an appropriate name. The example shown below is from Lloyds Bank U.K. and may not exactly correspond to the facilities provided by your Bank - but they will be very similar in terms of the functionality provided. In this particular example, the output file is a .CSV file which you can also examine in Excel if you wish.
Next, you need to import this Bank Transaction File into the system. To do this go to your Main Menu, Click on “Bank” followed by “Import Bank Statement” and select the appropriate Bank Account from the Drop Down list;
Now click on the Import Button beside the chosen Bank Account which will cause the following Window to be invoked;
And then “Browse” to the location on your local computer/file server where you previously saved the exported file from your Bank, followed by “Import”. The result will look somewhat like this depending on what the system finds;
The Green Band represents and contains system 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 (described below).
The Red Band represents those transaction 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.
The Yellow Band comprises multiple possible matches found in the System which match a single transaction on the Bank Import file – i.e. where several system transactions have passed the matching algorithm. These will need to be examined in order to select the correct match.
The Amber Band is made up of those transactions on the import file that look like transactions which have been created and “remembered” in a previous import. These “remembered” postings into the accounts system (with updated date, amount and description from the imported transaction) are presented here for scrutiny and approval.
The bottom half of the screen shows all the transactions that you are importing together with their colour coded status as per the summary in the top half of the screen. Note that you can view the contents of each category on its own by clicking on any of the highlighted fields (purple box) on the left hand side of this screen. You can also export the contents of the Unmatched category to Excel for Printing or further review and analysis (Right hand side Button above);
The “Clear Imported Transactions” Button reverses the import and allows you to re-import or use another Import file or Bank Account.
When you import a Bank File as described above, it is also automatically appended to the Document Manager and can be viewed, as usual, by clicking on this Icon;
The matching algorithm is reasonably complex, but, in summary, it functions like this and in the following order;
(i) When it encounters an unreconciled Lodgement in the system and it finds a corresponding transaction on the Bank Import file with an exactly matching Amount and with Dates within 14 days of each other (+ or - 14 days) it will create a match.
(ii) Exactly the same for an unreconciled Batch Payment. Lodgements and Batch Payments are recognised by virtue of their Reference Nos. The matching transaction from the Bank Import file must therefore contain this No. somewhere in its Description field.
(iii) If an unreconciled system transaction has a corresponding import file transaction with the exact same amount and the exact same date, it will create a match.
(iv) If an unreconciled system transaction has a corresponding import file transaction with the exact same amount and the dates within 14 days of each other, and there is a partial match on Description or Reference, it will create a match.
(v) If an unreconciled system transaction has a corresponding import file transaction with the exact same amount and the dates within 14 days of each other, it will create a match.
(vi If it finds an unreconciled transaction with a Reference No. (e.g. Cheque) exact match and an exact Amount match it will create a match.
These are the standard default Parameters and can be adjusted if you wish. For example the “exactly matching Amount” as used above can become an “approximately matching Amount”. To modify these Parameters click on the “Adjust Matching Settings” field at the bottom of this screen;
Which will cause the following Pop-Up Window to appear;
You can use the “Up” and “Down” arrows to change the Parameters. Once amended, these modified Parameters will apply to all your Bank Accounts and can be saved for subsequent use.
You can also use the “Match Again” field to carry out a re-match of outstanding transactions (i.e. transactions which have already been matched, see below, will not re-appear again). Note that you can use this match again feature at any time throughout your work.
3.1 Processing Matched Transactions: You can Press the “Match All” Button against the Green band set of Matched Transactions;
which will then cause the related transactions in the System’s Bank Account to be ticked as “Matched”;
Alternatively you can use the list of Matched Transactions and click off each one in turn;
If you come across a Match that you think shouldn’t be one, then use the “Reconcile Bank” Button to go into the Bank Account and “Un-Match” the Transaction by simply “Un-Ticking” the Box shown against that transaction on the previous screen. Make sure that the “Show All” Parameter is displayed on that screen to ensure that you can see the Matched Transactions as well as the Un-Matched ones.
3.2 Processing Multiple Matched Transactions: Click on the “Multiple Matches” as highlighted below to view the possible matches and associated information from both the Import File and the System.
If you require more information on any of the transactions, you can click on the “Type” field against the relevant transaction;
Alternatively, you can click on the “Bank List” Tab to access the Bank Transaction Browser;
When you are satisfied that you have found the right transaction, you can “Match” one of the possible matches or neither by leaving them as they are.
3.3 Processing “No Match Found” Transactions: You can Export these transactions to Excel for Printing and detailed examination against the System’s view of the Bank Account, which you can access either through the “Bank Reconciliation” button or the Bank Transaction Browser as described in the previous paragraph.
If some of these Transactions have failed the Matching criteria described in 3. and, you have found matches for them, then all you need to do is match then manually by clicking on the “Bank Reconciliation” button and create the match by ticking the Match Tick Box against the appropriate corresponding transaction in the system.
Alternatively, you can amend the system transaction details within the Transaction Browser (see the Article entitled “How do I Amend, Correct, Reverse or Delete a Transaction?”) so that your system transaction now correctly matches your Bank Statement. This, of course, requires you to use the “Match Again” facility described earlier in order to effect and clear the match.
However, should a number of these “No Match Found” Transactions from the Bank Import File represent transactions which are unknown to the system, (and you don’t want to leave them until a full Bank Reconciliation takes place), then you can process them as follows:Delete
Creating Invoices and other Postings from Miss-Matches:
4.1 Payment Side Transactions: To deal with Payments first, in the list of displayed transactions in the bottom half of the screen, Click on the “Create new Posting” field against the relevant Payment transaction.
This will cause the following Drop Down Window to be invoked;
There are three types of transaction you can create and post here; A Supplier Payment, a Bank Payment or a Bank Transfer. Fill in the required fields and Post the transaction which will then create, post and mark as matched the relevant transaction both in the Supplier’s Account and/or the Bank Account(s).
Supplier Payments also facilitate the allocation of the Payment or Receipt Amount, with the available candidates being displayed in the Drop Down located in this field.
Alternatively, you can click on the “Create Advanced Posting” field which will bring up some additional options that you can use;
Clicking on the “Create payment with advanced options” field will invoke a further Pop-Up window comprising the full Purchase Payments and Allocations facilities of the system which you can then employ to create the payment. If you have supplied the Supplier Code above then the complete Suppliers details will be filled into the Payments and Allocations Pop-Up Window, otherwise not.
Similarly, if you click on the “Create split payment across GL Accounts” this will invoke the Sundry Bank Payments Pop-Up Window which will allow you to allocate your Bank Payment (e.g. Debit Card Purchase) across a number of General Ledger Expense Postings.
4.2 Receipt Side Transactions: When you click on the “Create New Posting” against an unrecognised Receipt you are presented with a different set of possible transactions which you can create by entering the required data depending on the type of transaction you want to create;
And the “Create Advanced Posting” facilities in this instance are;
The “Create split lodgement across GL Accounts” invokes the Sundry Bank Receipts Program in a Pop-Up Window. The “Create receipt with advanced options” invokes the Sales Receipts and Allocations Program and the “Create split lodgement across Customer accounts” invokes the Customer Receipts Batch Entry program.
N.B. If you employ the latter Customer Receipts Quick (Batch) Entry program this also includes facilities for automatically creating an Invoice for each entry using the “Auto Invoice” button;
This is of especial relevance to those Clients whose primary source of Sales transactions is from the Bank Import file. On invoking the Auto Invoice feature, the following Window is presented;Delete
Remembering Miss-Matches for Future Automatic Matching:
When you have created your transaction you can also tag it to be recognised in the event of a similar transaction being encountered in a future imported file. This will then be displayed in the Orange Band awaiting processing (see 6. below).Delete