Introduction

The Bank Import facility makes Bank reconciliation more efficient, reducing the manual effort in reconciling your system bank accounts with your bank statements and improving accuracy. The process involves:

1.  Uploading an electronic version of a bank statement. This can be from your online bank, see Generating Bank Statement Files (Online Banking) or created from a PDF statement using AutoRec, see Generating Bank Statement Files (AutoRec)‍ ‍  
2. Reconciling the electronic statement to your system bank using matching rules applied by the system. Where the system cannot match transactions, it will suggest several possible matches, or you can create and post a matching transaction. 

In future imports, the system will remember the matching rules, letting you quickly identify unreconciled items and complete reconciliations when you need to - daily, weekly, or monthly. 

Alternatively, you can set up Bank Feeds.‍ 

Bank Reconciliation Schematic Overview

See:

Bank Feeds (6B.1) - AIQ Academy

Importing bank statements manually (6B.2) - AIQ Academy

Transaction Matching and Quick Post (6B.3) - AIQ Academy

Advanced Posting and Matching (6B.4) - AIQ Academy

Bank Reconciliation (Manual) (6A.6) - AIQ Academy

Importing transactions

Step One: Create a Bank Transaction File

For more details on this process, see Generating Bank Statement Files (Online Banking)‍. Alternatively, if you have bank feeds set up, see Managing Bank Feeds.‍ 

1. Log into your On-Line Banking Facility.
2. Choose the relevant Bank Account.
3. Click Export Data. 
4. Select the relevant Date range and click Export. 
5. Click Save. Save the file to a convenient location on your local computer/file server with an appropriate name. The example shown is from Lloyds Bank U.K. Here, the output file is in CSV format which can be opened in Excel.

Step Two: Import the Bank Transaction File into AIQ

1. In AIQ, in the white banner go to Bank > Import Bank Statement.
   
   
2. Delete

   Info: Our New Look

   If you are using our new look navigation, Import Bank Transactions can be found in the Bank tab in the new sidebar:

   
   

3.  Under Select Bank, choose the appropriate Bank Account.
4. Click Import. 

4. Click Browse to find your previously saved copy of the exported bank file.
5. Click Import again.

6. Note the following:

• Green Band: This displays posted system transactions that match those on the imported bank statement file. They have all passed the matching decision algorithm (see Transaction Matching, below).
• Yellow Band: This displays multiple potential matches found in the System that match a single transaction on the Bank Import file. Here several system transactions have passed the matching algorithm. Examine these to select the correct match.
• Red Band: This displays transactions on the Bank Statement Import file that have no matches in the system. They will mainly be items such as Bank Transfers, and Direct Debits that are currently unrecorded in the System’s Bank Account.
• Amber Band: 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.
• Export:  You can also export the contents of the Unmatched category to Excel for printing or further review and analysis.
• Clear Imported Transactions: Click this button to clear all imported (but not yet reconciled) transactions from the listing. You may wish to use this if you have transactions on your bank statement that you cannot identify or match with existing transactions in the system. Note that all transactions must be cleared from this screen before you will be allowed to reconcile your bank account. This lets you reimport or use another Import file or Bank Account.
• Imported Bank Statement: The bottom half of the screen shows all the transactions that you are importing together with their color-coded status. View the contents of each category on its own by clicking any of the fields.

Importing Bank Files using the Document Manager

If you don’t have access to the online Banking System and require another BI Code or individual to conduct the Bank File extraction:

1. Have them download the file and provide you with the name and location on your local computer/file server.
2. Go to Bank > Bank List and under Docs, click on the relevant document icon.
3. Browse to the document’s location and upload it. 
4. In the Bank System, import the relevant file as described above.

Alternatively, the person with access to the online Bank Account facilities can:

1. Download the file.
2. Attach it to the Bank Document Manager.
3. Import it as described above.

Viewing Imported Bank Statement Files

When you import a Bank File, it automatically goes to the Document Manager.

1. Go to Bank > Bank List.
   
   
2. Delete

   Info: Our New Look

   If you are using our new look navigation, Bank Accounts can be found in the Bank tab in the new sidebar:

   
   

3. Find the Bank Account and click the document icon.

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:

1. For unreconciled Lodgements, the exact same amount, with Dates within 14 days of each other.
2. For both unreconciled Lodgements and Bulk Payments, the exact same Reference No. somewhere in its Description field.
3. The exact same Amount and Date.
4. The exact same Amount, with Dates within 14 days of each other, and a partial match on Description or Reference.
5. The exact same Amount, with Dates within 14 days of each other.
6. 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:

1. Go to Bank > Import Bank Statement. 
   
   
2. Delete

   Info: Our New Look

   If you are using our new look navigation, Import Bank Transactions can be found in the Bank tab in the new sidebar:
   

   
   

3. Click Adjust Matching Settings.

3. Use the up and down arrows to adjust the parameters. Use the Match Again field to carry out a re-match of any outstanding transactions. You can use this feature at any time throughout your work.
4. Click Save to apply these modified parameters to all your Bank Accounts.

Matching and Unmatching Transactions

Matching Transactions

1. Go to Bank > Import Bank Statement.
   
   
2. Delete

   Info: Our New Look

   If you are using our new look navigation, Import Bank Transactions can be found in the Bank tab in the new sidebar:
   
   

   
   

3. 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 use the list of Matched Transactions and select each individually. 

3. Click Match.

Transactions with Multiple Matches 

1. Click Multiple Matches to view the potential matches and associated information from both the Import File and the System. This is where several system transactions have passed the matching algorithm. These will need to be examined to select the correct match. For more information on the transactions, click Type on the relevant transaction.
2. Click Match.

Alternatively:

1. Go to Bank > Bank List. Find your account.
2. Under Current Balance, click View Transactions and find your transaction.
3. Now you can find a possible match.

Matching “No Match Found” Transactions

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.

You can export transactions without matches to Excel, then print and examine them against the data in the Bank Transaction or Bank Reconciliation screens.

To match them via Bank Transaction screen:

1. Go to Bank > Bank List.
2. Under Current Balance, click View Transactions and find your transaction.
3. Use the Match Again facility (see ‘Understanding and adjusting the algorithm’, above).
4. Create matches as described above.

To match them via Bank Reconciliation:

1. Go to Bank > Reconcile Banks. 
2. In the Matched column, turn on the Show All filter. Against the appropriate corresponding transaction in the system, tick Matched. 

Unmatching Transactions

If you want to un-match any of the items form the Bank reconciliation:

1. Click Reconcile Bank to go to the Bank Reconciliation. If there is an existing Bank Reconciliation open for this bank it will be loaded automatically. If there is no bank reconciliation open clicking this button will create a new one.
   
   
2. Delete

   Info: Our New Look

   If you are using our new look navigation, Bank Reconciliation can be found in the Bank tab in the new sidebar:

   

3. Make sure that the Show All parameter is on to see both Matched and Un-Matched Transactions. Deselect the match by unchecking the box. 

See:

Amend, Cancel or Delete Transactions‍ 

Posting "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:

1. In Import Bank Statement, click Create new Posting against the relevant transaction.

2. Complete the required fields. There are four types of transactions that can be posted from the quick post:

• Purchase Payment / Sales Receipt
• Bank Payment / Bank Receipt
• Bank Transfer
• Customer Refund or Supplier Refund

3. Click Post. This creates, posts, and marks the relevant transaction as matched in both the Supplier’s Account and/or the Bank Account(s).

Purchase Payment options

If you select Purchase Payment, clicking Create Advanced Posting will bring up these additional options:

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

Receipt options

If you select Bank Receipt, clicking Create New Posting displays a set of possible transactions you can create. The required data depends on transaction type.

The Create Advanced Posting facilities for Bank Receipt are:

• Create split lodgement across GL Accounts: This displays the Sundry Bank Receipts program.
• Create receipt with advanced options: This displays the Sales Receipts and Allocations program.
• 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 Import file. 

Recognising Transactions For Future Matches

When you create a transaction as in the previous section, you can tag it. If a future imported file contains a similar transaction, it will appear in the Orange Band containing transactions which have been created and remembered in a previous import. These remembered postings (with updated date, amount, and description from the imported transaction) then must be processed.

Step One: Create Recognition Rule

The example here shows Bank Transfer progress Payments made by a Supplier against a specific BI Code. 

1. The Description from the Bank Import File automatically fills the Recognise based on Reference field. Edit if needed. It is likely that future progress payments on behalf of the same Supplier will have the same or similar description. 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 Reference field. For example, if you are billed each month by British Telecom, you could recognize this transaction in the future by memorising the description as “BT*.” This means any reference starting with “BT….”  will register as a possible match.
2. Click Memorise & Post. Alternatively, by just clicking Post, the system will automatically recognise any future transactions that have the same description.

Guidelines for Re-Importing and Subsequent Imports

1. In Import Bank Statements, click Clear Imported Transactions.
2. Re-Import the same Bank File again. 

Guidelines

• 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. 
• 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 as the corresponding system transactions were previously reconciled. Therefore, the duplicates will fail the Matching criteria.
• The purpose of the Bank Import 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 Import file. Match as many as you can and leave the remaining unresolved transactions. When you have finished, click Clear Imported Transactions.