How do I Use the Bank Import Facility to assist with my Bank Reconciliation?

Importing Electronic Bank Statements and Matching with the System's Transactions prior to a full Bank Reconciliation.

Written By Gerry Mckeown ()

Updated at June 20th, 2022


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. 

Importing Transactions:

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;


Transaction Matching:

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:


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;  


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).

This example, shown above, relates to inward bound Bank Transfer progress Payments made by a Supplier (Vendor) against a specific BI Code. It is very likely that future progress payments on behalf of the same Supplier will bear the same or a similar description.  The Description from the Bank Import File is automatically placed in the “Recognise based on Description” Box.  Should the Description change on future imported transactions, the system will still recognise it based on its Partial Match capability described in point 3. To specify how the system should recognise this transaction in the future, edit the “Recognise based on Description” entry and click on the “Memorise & Post” button.  Alternatively, by just clicking on “Post” the system will automatically recognise any future transactions that have the same description as the one just posted.

Note that 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*” and any reference starting with “BT….” (e.g. “BT” followed by an invoice number) will be picked up in the matching routine.


Processing Suggested Auto Post Transactions:

This section brings us finally to the processing of the Orange Band of transactions and the topic as to how these “Remembered” suggested auto post transactions are treated in subsequent imports of electronic Bank Statement files. 

When the system encounters what it thinks is a “Remembered” transaction it will create an entry in the “Suggested Auto Post” band.  The entries here need to be reviewed and approved or rejected.  Click on the “Edit Post” button to review the transaction and perhaps to allocate it as well.  This should be followed by “Post”.  You can also further edit the “Recognise based on Description” field followed by “Memorise and Post”.

If you wish to remove a Suggested Auto Post rule, just edit out the “Recognise For Future Match” description and click on the “Forget” button.  The associated matching logic will then be cleared.



Re-Importing Transactions and/or Subsequent Imports:

You can clear the Import File using the “Clear Imported Transactions” button and then,  re-Import the same Bank File again.  However, you need to be aware that, if your first import of the Bank File resulted in automatically matched transactions which were processed, then these will no longer appear on the import bars. 

Also if you create new Bank Import Files with overlapping dates with a previous import, then these duplicate imported transactions will show up as “No Match Found” since the corresponding system transactions were previously reconciled. Therefore the duplicates will fail the Matching criteria described in 3. above.

N.B.  It should be stressed that the purpose of the Bank Import facility is to assist in the Bank Reconciliation process, not to supplant it.  Therefore, it is not necessary to eliminate all the transactions on a Bank Import file by finding a match for each and every one.  Like the duplicates above, there will be other occasions where, for one reason or another, matches cannot be established.  For example, some of them may already be matched.  Just leave these remaining unresolved transactions when you have matched as many as you can and use the “Clear Imported Transactions” button when you have finished and before you exit.


Importing Bank Files using the Document Manager:

If you don’t have access to the On-Line Banking System and require another BI Code or individual to carry out the Bank File extraction, then once they have downloaded the file and provided you with the name and location of the file on your local computer/file server, you can proceed as follows;

Go to the Bank List Tab and Click on the Document Manager Tab:

Browse to the location provided by your colleague, and upload the file.  Now, in the Bank System, import the relevant file as shown below;

Alternatively, the person with access to the on-line Bank Account facilities can simply download the file, attach it to the Bank Document Manager where it will then be available for importing as above.


Other articles you might useful related to this are :
 How do I Carry out a Bank Reconciliation?
 How do I Export Bank Statement Files and Formats and Subsequent Import Errors?