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 (Liquid error: internal)

Updated at March 26th, 2018

1.     Introduction:

This feature of the system 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 (as opposed to its Statement Printing facilities) and enables you to match these imported transactions with the System’s Bank Accounts’ transactions.  Refer to the companion Article How do I Export Bank Statement Files and Formats and Subsequent Import Errors?“  As well as matching paired transactions, it also facilitates the creation and posting of new transactions into your System when you encounter entries on the Bank Statement File which are not present in the Accounts System.  These created postings can also be “remembered” and re-used in future imports.

The result is greatly improved productivity in the area of Bank Reconciliation by vastly reducing the tedium of manually reconciling transactions on a transaction by transaction basis from printed Bank statements.  It is also of great importance to those Customers who depend solely on the Bank Transaction File Import as the original source of their revenue transactions (e.g. internet based Retailers employing Credit/Debit Card sales, BPO providers and so on).  Obviously it requires you to have an arrangement with your Bank which allows you to gain regular access to such an electronic file, be it daily, weekly, or monthly.   This is normally achieved by accessing your On-Line Banking facility and extracting the file you require from the relevant Bank Account between the dates you request.  The system accommodates quite a number of Bank File formats with others being added constantly.  If your particular Bank file proves to be unsupported then contact your System Provider who will add support for importing your specific file format.

The functionality in this module of the system also features a “learning” process whereby Transaction Matching can be instructed to create new system transaction postings and “remember” them in order to deal with subsequent repetitive occurrences.  The result is that, as time passes by, many earlier miss-matched transactions are automatically matched, and do not subsequently require continual intervention by the User to create a match and a posting to the accounts system. 

Clearly, the Bank Import and Matching facility is only a productivity aid to the complete process of Bank reconciliation, which, in any event, should be carried out periodically to ensure that the system’s view of the Bank Accounts is in accord with the real Bank Account’s status.  You should therefore have a full understanding of the complete bank reconciliation process detail of which is contained in the Article entitled “How do I Carry out a Bank Reconciliation?   In addition, it’s not necessary to match all of the imported transactions that you import on a daily basis.  Some of the more difficult or unresolved ones can be left un-matched until the full Bank Reconciliation is carried out, perhaps by persons with a higher level of competence in such matters.  The purpose of this facility is to match as many transactions as possible on a daily or regular basis prior to the full Bank Reconciliation.

2.     Importing Transactions:

The first step in the process is 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” as in this example.  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 Orange 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;

3.     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:   Clearly these Bank File transactions require further investigation.  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. above and, upon investigation, 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:

4.     Creating Invoices and other Postings from Miss-Matches:

The exact facilities provided by this program under this option are dependent on whether the transaction is a Payment or a Receipt according to the imported bank statement file.  These facilities are very similar to the “Create Transaction” capability in the Bank Reconciliation program.

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;

You can close this Window by clicking on the “Hide” field.  There are three types of transaction you can create and post here;  A Supplier (Vendor) Payment (for example a manual Cheque was given to the Supplier (Vendor) but was subsequently forgotten to post to the Supplier’s (Vendor's) Account),  a Bank Payment (for example a Debit Card was used to pay for some Office Supplies) or a Bank Transfer (for example a verbal instruction was given to the Bank to place some funds on deposit).  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 (Vendor's) Account and/or the Bank Account(s).

Supplier (Vendor) Payments (and Customer Receipts) 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 (Vendor's) 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;  

5.     Remembering Miss-Matches for Future Automatic Matching:

When you have created your transaction using the facilities described in Section 4. (whether Payment Side or Receipt Side) you can also tag it to be recognised in the event of a similar transaction being encountered in a future imported file.   If so, in that instance the system will then create a new transaction based on the one that you’ve just created for the first time, but differing in regard to the transaction date and which may also differ in relation to the transaction reference, description and amount. 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.  The mechanism for recognising such future Receipts is by way of the Imported Bank File Description on the transaction - which might be a reference to the BI Code or the Suppliers Name or ID.  Whatever it is, it is very likely that future progress payments on behalf of the same Supplier (Vendor) 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 somewhat on future imported transactions, the system will still recognise it based on its Partial Match capability described in 3. above.  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.

Further examples of these types of transactions might include regular Interest Receipts or Payments on Deposit Accounts and Loan Accounts, Direct Debit Deductions for variable amounts in respect of Electricity, Gas, Water, Telephone, etc. and many other such transactions.

Obviously you can create and refine rules over time as different transactions are imported to the system from your Bank.  It is important to continually create new, or refine existing, recognise rules in order to increase productivity and reduce the need for transaction entry in many of these automatic and repetitive areas.

6.     Processing Suggested Auto Post Transactions:

The foregoing 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.

 

7.     Re-Importing Transactions and/or Subsequent Imports:

You can clear the Import File using the “Clear Imported Transactions” button and then, if you wish, 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.

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