Introduction

Bank statement files can be used in Automated Bank Reconciliation.‍ Most online banking services allow you to export bank statement data in a range of file formats. 

Alternatively, you can reconcile banks using other methods:

• Manually input transactions from your printed Bank Statement as described in Manual Bank Reconciliation.‍ 
• Automatically import bank transactions using Bank Feeds‍ files for enhanced productivity. You must export the transaction files from your bank with the correct contents and format and then import them without errors. See Automated Bank Reconciliation.‍  

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

Managing Bank Feeds‍ 

Delete

Currently Available Bank Statement File Formats

Find your Bank Statement file format

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. 

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.
  
  

Your file format will vary depending on the access you have to your online banking. To find out which file format will work for you, extract all possible file formats, and import them one by one under Import Transactions in the Import Bank Statement option.

The supported formats

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

Unsupported formats

If your format is not in the list above, see if your online banking system lets you create a CSV (comma-separated values) file. Most banks have this as a backup facility. With minor adjustments, the system will accept it (more below).

If you would like a specific format supported, please contact our support team.

Delete

Example Exports: AIB

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)

1. Log into your online banking facility and choose the appropriate account from the list. 
2. To produce the required export file (.OUT format), follow the detailed instructions in the image below.

Delete

Working with CSV File Formats

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 YYYY-MM-DD using the Locale = English (United Kingdom).
6. The Description field will probably span several cells. Widen so all content is visible. If this results in unwanted cells, delete them.
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.

Delete

Troubleshooting file import issues

The system doesn’t recognize my file

If the system doesn’t recognize 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 additional bank files and can discuss this.

The system recognizes my file but finds errors

If the system recognizes the file and tries to import it but finds errors, these errors will display on the Bank Import screen (see the table below). 

 

Check your dates are valid. The system checks for out-of-range dates. Currently, a valid date is:1990 < Date < 2079.

 

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}

Often, you can 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.

 

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

 

Delete