About Bank Statement Files

Before performing a bank reconciliation, you need to have a bank transaction statement. This can be in any of the following forms:

• Bank Statement File (described here): This is exported from your online banking platform and then imported into the system for Automated Bank Reconciliation with transaction data. It is essential that this file has both the correct format and contents.
• Bank Feeds:‍ These are the automated version of Bank Statement Files. A connection is created between the system and the bank, letting statement data flow electronically without the need to import an external file. The statement lines are then used in Automated Bank Reconciliation.
• Physical Bank Statement: Alternatively, use a printed Bank Statement for manual matching as described in Manual Bank Reconciliation.‍ 

Reconciliation Frequency

For best results, use the Bank Reconciliation facility as frequently as possible. When the system gets frequent inputs of data, it can make more matches and resolve any discrepancies sooner.

See: 

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

Bank Feeds (6B.1) - AIQ Academy

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

Automated Bank Reconciliation‍ ‍

Bank Feeds‍ 

What Formats are Supported?

Most popular online banking file formats used by major international banks are compatible. Using these standard supported formats will allow you to import your bank file, without amendment, directly into the Bank Reconciliation system. If you would like a specific format supported, please contact our support team.

AccountsIQ supports:

• QIF (Quicken Interchange Format)
• OFX (Open Financial Exchange)

AccountsIQ supports the specific file formats for:

• AIB Ireland
• Bank of Ireland
• Barclays
• CODA
• Lloyds Bank
• OCREX
• Ulster Bank
• MT940
• OFX
• QIF
• CSV Formats: If your format is not supported, see if your online banking system lets you create a CSV (comma-separated values) file. With minor adjustments, the system will accept it (more ‘CSV File Formats’ below).

What File Format does my Bank use?

To check which generic Bank Statement file formats your bank uses:

• Go to the area of the online banking system for downloading statement transactions. A list of available download file formats should be available.
• Check your bank’s website or contact them directly. 

File format will vary depending on the online banking access. To find out which file format will work, extract all possible file formats, and import them one by one, following the instructions in “Import Bank Statement File”.

How you export a bank statement file will depend on whether you have direct access to the online banking platform or rely on a senior colleague to access it for you.

Users with Direct Access to Online Banking (Example: Lloyds Bank)

The example shown is from Lloyds Bank U.K. Here, the output file is in CSV format which can be opened in Excel.

If you have direct access to online banking:

1. Log into your Online Banking Facility.
2. Choose the relevant Bank Account.
3. The exact label will vary by bank; look for an export or download option
4. Select the relevant Date range and export the data.
5. Save the file to a convenient location on your local computer/file server with an appropriate name.

Users with Indirect Access to Online Banking

If you don’t have access to the online banking or require a specific code or individual to conduct the Bank File extraction, proceed as follows:

1. Have the individual with access extract the file and download it to a secure location that is accessible to you.
2. Go to Banks > Bank List and under Docs, click on the relevant document icon and upload the file. See Document Manager for further details.

The file is now available to import as described in the next step.

AIB has two Internet Banking facilities:

• Internet Banking: This targets the small business sector.
• iBusiness Banking: This is multi-user and targets the medium to large business sector.

AIB Internet Banking

1. Log into your online banking facility and choose your account from the list.
2. In the Transactions tab, click Historical.
   
3. Click the search symbol and enter the Start Date and End Date of the transactions you wish to export. Click Apply Filter.
4. Click Export.
   
5. Save the exported file in CSV format somewhere memorable on your Desktop.

AIB iBusiness Banking (iBB) (.OUT)

Log into your online banking facility and choose the appropriate account from the list. 

To produce the required export file (.OUT format), follow the detailed instructions in the image below.

1. To access the Export Transactions screen, select Export Transactions on the side menu. The Export Transactions screen will be displayed.
2. Select the account from the ACCOUNT dropdown.
3. Enter in a date range for your transactions in the From Date and To Date fields.

4. The File Format will always be Comma Separated Variables.
5. The File Name will default to the account number of the selected account. You can change the file name to one of your choice, ensuring it is not a file name that already exists.
6. Select the source of your export. You have two options:
   • Single A/C: Default selection. This will export transactions from the account selected in the ACCOUNT dropdown.
   • Group A/C: This will export transactions from the accounts which are part of the Account Group selected in the GROUP dropdown.
7. When you have entered all the data, select Export Now.
8. A File Download pop-up will be displayed. Select Save.
9. The Save As screen will be displayed. Select the location to save the file in the Save In dropdown.
10. Enter the name of the file in the File name field and then select Save.
11. Once the transactions have been exported to the file, the File Download complete screen will be displayed. Select Close to return to the Export Transactions screen. Your transactions have now been downloaded. They can be opened in Word Pad or a spreadsheet application such as Excel. See 'how to open an '.out' file'

Many online banking systems use CSV (Comma Separated Values) for the export of statements or transaction lists. This format can create credit card statements, and transactions export files. You can then set up and maintain these files as pseudo bank accounts in the system. 

The content of CSV files varies between banks. Therefore, you need to edit them before importing them into the Bank Reconciliation system.

The following elements are unnecessary:

• Bank Account No. columns
• Currency columns
• Balance columns
• Column headings 

Editing your CSV file

There are two acceptable CSV file layouts. 

Version 1

If the CSV file has separate fields for Debits and Credits, then you should edit it, so it follows a four-column format.

Version 2

If the CSV file has only one field for the Amount (signed), you should edit it, so it follows a three-column format.

To produce an acceptable CSV file:

1. Open the CSV file (Excel).
2. Delete the Column Headings in the first Row.
3. Delete unwanted Columns.
4. You should now have three or four Columns as described above.
5. Format the Date field as DD/MM/YYYY using the Locale = English (United Kingdom), or MM/DD/YYYY using the Locale = English (United States).
6. If the Description field spans multiple columns, merge or concatenate those cells into a single column and delete the now-empty columns.
7. Format the Value Fields as Numbers instead of Currency, with no delimiting commas and with two decimal places.
8. Save the result as a CSV file in a memorable location for subsequent import to the Bank Reconciliation system.

1. Go to Banks > Import Bank Statement.
2. Under Select Bank, choose the appropriate Bank Account.
3. Click on Import.
4. Select your previously saved copy of the exported bank file, select the correct date format, and click Import. Imported Bank Statement Files are automatically stored in the Document Manager.

Imported Bank Statement

The imported transactions will appear on screen. The bottom half of the screen shows all the transactions that you are importing together with their colour-coded status. View the contents of each category on its own by clicking any of the fields. Proceed to match the imported bank statement file using Automated Bank Reconciliation.

Clear Imported Transaction

If you need to remove any unwanted but not yet reconciled import data, click Clear Imported Transactions.   This can occur if you have transactions on your bank statement that you cannot identify or match with existing system transactions. These need to be cleared before you can finalise the bank reconciliation. This allows you to reimport or import a new bank file.

The system doesn’t recognise my file

If the system doesn’t recognise your file and won’t import it, you will receive an error message. If you receive an error message:

• Check if your bank provides additional supported formats (such as QIF or OFX). 
• Contact your local support representative for assistance. AccountsIQ is adding support for additional bank files and can discuss this.

The system recognises my file but finds errors

If the system recognises the file but finds import errors, these errors will display on the Bank Import screen. Where it can, the system will provide a line number and description of the error that occurred:

• Line N (where ‘N’ is the line number): {Original line from bank statement} – {Message indicating what the error refers to}

Use this information to repair the file for immediate import. Open the file in a file editing program such as Windows Notepad or MS Excel to find the error line and diagnose the issue. Dates must fall between 1 January 1990 and 31 December 2079. Check your dates are valid. The system checks for out-of-range dates. 

If this is a recurring issue, contact the support team with details of the error for potential future updates.