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

Creating the Bank Export files from various Banks, dealing with their differing File Formats and any Errors.

Written By Gerry Mckeown (Liquid error: internal)

Updated at January 15th, 2019

1.     Introduction:

The Bank Reconciliation facilities of the system, as well as enabling the manual input of transactions from your printed Bank Statements, also allow the import of electronic files from your Bank containing the requisite details of such transactions. This latter facility will greatly enhance your productivity in this area.  Most On-Line Banking services will now allow you to export Statement data in the form of electronic Transaction Lists between specified date ranges in various file formats.  This Article, as well as exampling how to export the transaction files from your Bank, also details the relevant information regarding the content and format of such exported files and what to do if you encounter subsequent import errors.  An accompanying Article entitled “How do I Use the Bank Import Facility to assist with my Bank Reconciliation?” describes the mechanism and use of such imported files in the system.  It is recommended that you invoke these facilities as frequently as possible in order to match as many transactions (from the Bank’s view of your Account with that of your System’s transaction view) in order to clear up any discrepancies between the two as close as possible to the occurrence of such miss-matches.

2.     Currently Available Formats:

The system is compatible with many of the most widely used bank file formats provided by the major international banks in their online banking systems. You need to ascertain whether your own bank supports one of these general formats.  Locate the area of the online banking system that facilitates the download of statement transactions: A list of available download file formats should be available for you to choose from.  Alternatively, query them directly or on Google.  We will be adding additional standard formats in due course. If you would like a specific format supported please email our support team with the details.

The system currently supports the following generic Bank Statement File formats:

>    QIF (Quicken Interchange Format)

>    OFX (Open Financial Exchange)

In addition to this, we currently support the specific file formats for:

AIB Ireland

Bank of Ireland

Ulster Bank

Lloyds Bank

Barclays

CODA

OCREX

Use of these standard supported formats will allow you to import the exported file, without amendment, directly into the Bank Reconciliation system.  Alternatively, and as a backup, most On-Line Banking systems will produce an Export file in a. CSV format which, after a few minor modifications, is also acceptable as an import to the system – see 4. below.

3.     Example Exports (AIB):

AIB have two Internet Banking facilities called “Internet Banking” and “iBusiness Banking”.  The former is aimed at the Small Business sector whereas the latter, multi-user, is targeted at the Medium to Large end.

3.1         AIB iBusiness Banking (iBB):  Log into your On-Line Banking facility and choose the appropriate Account from the available list.  To produce the required export file, (.OUT format) follow the detailed instructions contained in Appendix 1.

3.2         AIB Internet Banking:   Log into your On-Line Banking facility and choose the appropriate Account from the available list.  Click on “Transactions” which will open up the following window;

Next, Click on “Historical” followed by the “Search” symbol;

You should now enter the date range of the transactions you wish to export using the Calendar symbols next to both the Start Date and the End Date followed by “Apply Filter”.  Then click on “Export”.

Save the exported file as a .CSV type somewhere memorable on your Desktop or Laptop.

4.     Working with .CSV File Formats:

These file formats, which, as well as being used by many On-Line Bank Systems for the export of Statements or Transaction lists, can also be used to create export files of Credit Card statements and transactions (which, of course, can also be set up and maintained as “pseudo” Bank Accounts in the system).  However, the content of these “Comma Separate Variables” files can differ somewhat from institution to institution and must therefore be edited beforehand to produce a format acceptable to the import facilities of the Bank Reconciliation system.  For example, some files will contain the Bank Account No. (not required), the Currency (not required) and a Balance Column (not required).  Also, not required are the Column Headings.  To carry out the necessary amendments, open the .CSV file which will be presented in amendable Excel Spreadsheet format.

There are two acceptable import .CSV file layouts.  The first, if your exported .CSV file has separate fields for Debits and Credits, then you should amend it to adhere to the following four (and only four) column format;

The second format, to be used in the event that your output .CSV file has only one field for the Amount (signed), then you should amend it to adhere to the following three (and only three) column format;

Follow these instructions to produce an acceptable .CSV file;

(i)    Open the “Saved” .CSV file which will be presented in Excel.

(ii)    Delete the Column Headings in the first Row.

(iii)   Delete unwanted Columns.

(iv)   You should now have four Columns or three as described above.

(v)    Format the Date field as YYYY-MM-DD using the “Locale” = English (United Kingdom).

(vi)  The Description field will undoubtedly span several Excel Cells.  Stretch the first cell of the Description such that all descriptions fit within this one cell – i.e. Cell = “B”.

(vii)  Most probably, this will result in more unwanted cells, which should be deleted.

Viii)  Format the Value Fields as “Numbers” instead of “Currency” with no delimiting commas and with two decimal places.

(ix)   Save the result as a .CSV file in a memorable location for subsequent import to the Bank Reconciliation process.

5.     File Import Problems:

If you import your bank file and it does not import because it is not recognised, the system will provide a message indicating that is the case. The best thing to do, first, is to check if your bank provides additional formats that are supported (such as QIF or OFX). Failing that, please contact your local support representative and we can assess the issue of a potential solution for you. As mentioned, we are adding continuous support for additional bank files and are happy to discuss this with your Company and Bank.

However, if the system recognises the file and tries to import it but discovers errors, then these errors will be detailed on the bank import screen. For a more detailed explanation of the level of error reporting refer to the Table below.

Where it can, the system will provide a line number and description of the error that occurred. Often, you can use this information to repair the file for immediate import. If this is a recurring issue however, we would advise you to contact the support team with details of the error as it may be that we need to update our importing routines to handle a specific case for a particular bank.

When you import a bank file into the system the routine performs a series of validation checks on the data to find anomalies and likely problems. This is to ensure that invalid data is not imported. In certain bank files (and not others) it is possible to derive the line number associated with a possible invalid piece of data. For such lines the error will be displayed with the following format:

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

This is useful information to troubleshoot any errors coming from the file itself. If such errors are encountered, we recommend that you open the file in a file editing program such as Windows Notepad or MS Excel to find the error line and diagnose the issue.

Specific Error Messages by File Type

Below we provide a list of the error messages for each Bank file type indicating whether the Failure line number is displayed or not for that input type. We also provide some examples of the errors that might be found and where possible the line number corresponding to the error [in brackets].

In addition, after the file is imported using any of the types above, the records are validated looking for out of range dates. Currently a valid date is: 1990 < Date < 2079.

Appendix