Introduction

The Bank Import facility makes Bank reconciliation more efficient. It allows you to:  

• Import electronic Bank Statement Files on a regular using your Bank’s On-Line Transaction Export facility. You can then match these imported transactions with those in the system’s Bank Accounts. 
• Create and post new transactions for entries on the Bank Statement File. The system remembers these and re-uses them in future imports.

To ensure the systems view of the Bank Account are in accord with the real Bank Account’s status, carry out bank import and matching periodically.

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

Create a Bank Transaction File

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.

Import the Bank Transaction File into AIQ

1. In AIQ, in the white banner go to Bank > Import Bank Statement.
2.  Under Select Bank, choose the appropriate Bank Account.
3. 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).
• 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.
• 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.
• 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: This reverses the import and lets you re-import 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 (highlighted in 
  purple). 

Viewing the Bank File

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

 To view the bank file:

1. Go to Bank > Bank List.
2. Find the Bank Account and click the document icon.

Understanding and adjusting the 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. 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 processing Transactions

Matching transactions

1. Go to Bank > Import Bank Statement.
2. Click Match All against the Green band of Matched Transactions. The related transactions in the System’s Bank Account display with ticks under Matched. Alternatively, you can use the list of Matched Transactions and select each individually.

3. Click Match.

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. 
2. Make sure that the Show All parameter is on to see both Matched and Un-Matched Transactions. Deselect the match by unchecking the box. 

Processing Multiple Matched Transactions

1. Click Multiple Matches to view the potential matches and associated information from both the Import File and the System. For more information on any of 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.

Processing “No Match Found” Transactions

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

To match them via Bank Transaction Browser:

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 (see, start of this section)

To match them via Bank Reconciliation:

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

See:

How Do I Amend, Cancel or Delete Transactions? 

Creating New Postings

If the system does not recognise the 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 or Sales Receipt
• Bank Payment or 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. 

Remembering Miss-Matches for Future Automatic Matching

When you have created your transaction, you can tag it. This makes it recognisable if a future imported file contains a similar transaction. The transaction displays in the Orange Band, for processing.
 

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

It is likely that future progress payments on behalf of the same Supplier will have the same or similar description. The Description from the Bank Import File automatically fills the Recognise based on Description field. If the Description changes on future imported transactions, the system will still recognise it based on its Partial Match capability.
 

To specify how the system should recognise this transaction in the future:

1. In posting Details, edit Recognise based on Reference.
2. Click Memorise & Post. Alternatively, by just clicking Post, the system will automatically recognise any future transactions that have the same description.

Partial Matches

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

Processing Suggested Auto Post Transactions

When the system encounters what it thinks is a remembered transaction it will create an entry in Import Bank Statement in the Suggested Auto Post band. 

You need to review, then approve or reject the entries:

1. Click Edit Post to review the transaction and, if relevant, allocate it. You can also further edit the Recognise based on Description field.
2. Click Memorise & Post.

If you want to remove a Suggested Auto Post rule:

1. Edit the Recognise For Future Match description.
2. Click Forget. The associated matching logic will clear.

Re-Importing Transactions and/or Subsequent Imports

1. In Imported 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.

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

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

See:

How do I Carry out a Bank Reconciliation? 

How do I Export my real Bank Statements and Transactions Files for Subsequent Import to the System's Bank Account Facilities?