Related topics: ABR Overview, Select Statement, Bank File Types, Grouping, Build List, Transaction Search, Options, Auto Reconcile, Manual Reconcile

The Automated Bank Reconciliation module enables you to import transactions from an online bank statement and to match them to the unreconciled payments and receipts that are held in the relevant bank accounts) in Access Accounts. This module is an enhancement to existing reconciliation facilities that are available in Cash Book. You can still use all the reconciliation features in Cash Book should you so wish.

Some of the advantages of using Automated Bank Reconciliation are:

For an example of how to use the ABR Window, please see the ABR Overview topic, and for example of using matching templates see ABR Examples.

For information on giving access to this feature, please see the Security Profiles topic.

From the Financials tab, in the Cash Book group, click Auto Bank Reconciliation to open the Automated Bank Reconciliation window.

Transactions can be manually or automatically matched. Matching templates can be configured for auto matching. Also, additional templates for the creation of ledger transactions can be configured for further ease of use.

Note that the imported statement transactions are displayed according to the import format used. Also, when viewing the imported transactions, debits and credits are seen from the banks’ perspective.

Please also see ABR Examples for further details.

The following procedure documents a simple automated bank reconciliation. There are four stages (further detail can be found below):

You are also able to Correct Statements

When you have chosen your Bank Account and you have a new bank statement to load, carry out the following procedure:

The Imported Transaction panel displays transactions for the current bank statement you have loaded. The column headers reference your bank statement. Note that the Rec (Reconciliation) column is blank until you use automatic or manual reconciliation when a 'C' against a transaction indicates a complete reconciliation and a 'P' held against a transaction indicates a partial reconciliation.

Below the Imported Transactions panel there are three fields that contain details about your loaded statement:

You are now ready to build the list of unreconciled transactions for your chosen account. These actions are taken in the Unreconciled Transactions panel.

To build the list of unreconciled transactions for your selected bank account, select either the:

If required, select the Grouping button that enables you to display transactions according to Reference, Batch Reference or Date. The default of No Grouping displays each transaction and its value in the Unreconciled Transactions panel.

Select the Set Options button to open the Options window to check that your required matching options are specified and change the matching criteria as necessary. Note that you must enter a Date Tolerance of at least '1' for the automated reconciliation to be successful.

You are able to auto reconcile or manually reconcile your transactions using the Unreconciled Transactions panel. In this example, we are using the automatic reconciliation functionality. For details of making a manual reconciliation, see Manual Reconciliation.

Note: if matching templates have been defined then you are prompted to select a template after selecting the Auto Reconciliation button. You must select a template in order to continue.

The following choices are available:

Note: the balance displayed in the Reconciled Balance field in the header is reduced or increased by the amount you have unreconciled.

These are used to guide the automatic bank reconciliation when attempting to find matches between the accounts transactions and the bank statement transactions. While there are matching templates defined the user will always be prompted to select one when matching - they will not be able to match without one.

There are 3 formulas that can be configured per matching template:

Clicking on the calculator button next to any of the formula fields opens a Formula Editor window with a pre-defined list of fields and a pre-defined list of functions. Double-clicking on a line in the field list will add a field token to the formula. When ABR evaluates the formula it will replace the tokens with the appropriate field name. For syntax purposes the formula is essentially a SQL 'Where' clause - therefore anything that is valid in a SQL 'Where' clause will be valid here. If the formula does not make sense ABR will report an error when it tries to use the template. Please note that semi-colons cannot be used in the SQL statement as a precaution against SQL injection attacks.

Matching always looks for transactions in the date range set by the tolerance option and where the values match. The match by reference option automatically creates the following matching formula at the point of performing the match if a template has not been used:

'<STATEMENT TRANSACTION REFERENCE>=<DIMENSIONS TRANSACTION REFERENCE>'

Both <DIMENSIONS TRANSACTION VALUE> and <STATEMENT TRANSACTION VALUE> are rounded to 2dp for matching purposes. The matching formula is useful when there is a part of a field from one source that needs to match another field of a different source.

<DIMENSIONS TRANSACTION REFERENCE> = DET_HEADER_REF

<DIMENSIONS TRANSACTION VALUE> = DET_GROSS

<DIMENSIONS TRANSACTION DATE> = DET_DATE

<DIMENSIONS BATCH REFERENCE> = DET_BATCH_REF

<STATEMENT TRANSACTION REFERENCE> = ABRST_REFERENCE

<STATEMENT TRANSACTION REFERENCE 2> = ABRST_REFERENCE2

<STATEMENT TRANSACTION REFERENCE 3> = ABRST_REFERENCE3

<STATEMENT TRANSACTION REFERENCE 4> = ABRST_REFERENCE4

<STATEMENT TRANSACTION REFERENCE 5> = ABRST_REFERENCE5

<STATEMENT TRANSACTION TYPE> = ABRST_TYPE

<STATEMENT TRANSACTION VALUE> = ABRST_VALUE

<STATEMENT TRANSACTION DATE> = ABRST_DATE

Dimensions stores it's values as they were entered but the type of the transaction affects whether the transaction increases or decreases the balance of an account, i.e. 100.00 PL payment is stored as a positive figure but would decrease the bank account because it is a PL payment.

Bank statements can state values in different ways:

Caution! It is unlikely that a simple matching template formula will work 100% of the time: <Statement Transaction Value>=<Dimensions Transaction Value>

Import formats are files designed to help ABR translate a downloaded bank statement. The various banks distribute their data in differing formats - some like the Alliance and Leicester allow the user to customise the format in which the statement is downloaded. The differences mean it's necessary to perform some translation in order for ABR to understand the information.

Import formats are created on the create import window. The import name field is self-explanatory and is shown to the user in a list when they choose to import a statement file. There are two main types of files that ABR can read, these are comma separated (.CSV) or fixed width (.TXT). The import type is set by choosing one of these two options from the Import Type pop-up menu.

Most statement files will include some column headings which will need to be ignored by ABR. The number of lines to be ignored should be entered in the Skip First X Lines field. To check how many line need to be skipped it will be necessary to open up the bank statement file in notepad or something similar. It's not advisable to open the file in Excel because it interprets the data and will not give a true reflection of the contents of the file.

This is a list of fields that ABR can recognise on the left of the window. In order to configure the import format you have to select a line from the list and tell ABR where in the text file the appropriate data is stored.

There are separate fields for comma separated files and fixed width files - care should be taken to use the correct fields.

In the case of a comma separated file the column number is the count of commas on a line before the data + 1. So if the data appears before the first comma on a line it is classed as column 1.

For fixed width files a column exists for every single character on a line. It is therefore necessary to specify a start column and a finish column - both values are inclusive. So specifying Column 1 to 10 for transaction type would mean the first 10 characters of every line identify the transaction type in the bank statement file.

There may be occasions where advanced configuration is necessary, for example, to control the format of a date field. In this case it will be necessary to open up the saved import format file in notepad. Once in notepad it's possible to embed Omnis functions to control the conversion of the data in finer detail.

An ABR import template can contain lines starting with the following commands:

NAME:[Name of import template] '[Name of import template] is just free text no quotes required.

SET:[Variable]=[Value]

ABRST_REFERENCE - Char(100) - Free text reference field

ABRST_REFERENCE2 - Char(100) - Free text reference field

ABRST_REFERENCE3 - Char(100) - Free text reference field

ABRST_REFERENCE4 - Char(100) - Free text reference field

ABRST_REFERENCE5 - Char(100) - Free text reference field

ABRST_DATE - Date - The date of the transaction

ABRST_VALUE - Number 2dp - Value of the transaction from the bank statement (from the banks point of view)

ABRST_TYPE - Char(5) - Bank's description of the transaction type

This can be a mix of a valid Omnis calculation, which evaluates to a value of the correct type, and the ABR functions #GETCOLUMN or #GETFIXED.

IF:LINE>[Value] '[Value] can be an integer value. Only lines counted from the beginning of the file past [Value] will be processed.

IF:[Conditional expression] '[Conditional expression] can be a mix of valid Omnis functions and the ABR functions #GETCOLUMN or #GETFIXED, which evaluates to true or false.

To reference data in the import file there are two ABR functions that can be used:

Examples:

Here is an example for converting a date using the Omnis dat function:

SET:ABRST_DATE=dat(#GETCOLUMN(2,','),'y-M-D')

Here is an example of checking a debit/credit text column to determine where to get the value from. If it's the debit column the value is inverted:

SET:ABRST_VALUE=pick(upp(#GETCOLUMN(10,','))='DETAIL DEBITS',#GETCOLUMN(13,','),#GETCOLUMN(13,',')*-1)

Here is an example of checking a debit/credit text column to determine whether the value from the value column should be inverted (e.g. debit):

SET:ABRST_VALUE=#GETCOLUMN(2,',')*pick(#GETCOLUMN(5,',')='D',1,-1)

Here is an example of a condition which will skip lines that do not match the criteria:

IF:(#GETFIXED(1,3)<>'VOL')&(#GETFIXED(1,3)<>'HDR')&(#GETFIXED(1,3)<>'UHL')&(#GETFIXED(1,3)<>'EOF')&(#GETFIXED(1,3)<>'UTL')

Here is an example of concatenating two columns to make one long reference field:

SET:ABRST_REFERENCE=con(#GETCOLUMN(11,','),#GETCOLUMN(12,','))