Working with electronic bank files

To set up the advanced bank statement import process successfully, a basic understanding of the structure and content of electronic bank statement files is required.

Although MT940 is a common bank standard, it allows for variants regarding structural elements and the way certain transaction information is formatted. In addition, banks sometimes send non-standard information in their MT940 files.

Other bank file formats could include CAMT.053/.054 for IS0 20022 compliance, BAI2, CFONB, and CSV files for AR Lockbox in North America. Review Understanding electronic bank statements for more information.

It is also advised to have a format description issued from the bank at hand as a point of reference.

Check your licensing details and obtain a license for this function if necessary.

If bank statement files are imported from a server directory and uploaded from a client, dedicated server volumes and/or subdirectories for import file storage should be created.

Although a basic setup is delivered as part of the preconfiguration, there is no guarantee that this will work for all variants of files without adaptation.

Understanding electronic bank statement formats

The advanced bank statement import process supports different electronic bank statement formats. It is important to have an understanding of the formats relevant to your situation.

Always confirm file format details with the bank to avoid problems when importing and matching. Standard settings and format might not apply to all variants and/or banks.

MT940

An MT940 bank statement contains several segments separated by a colon (:). Depending on the segment type, it can appear once per statement or it can be repeated.

The following is an example of a single statement with two transactions.

:20:20373B65

:25:KARSDE66XXX/12345678

:28C:00147/00001

:60:C160810EUR10000,00

:61:1608100810C3000,00N016NONREF

:86:091?00UEBERWEISUNG?10944?20RG16S49000009 RG16S4X000003

:61:1608100810C2500,17N016NONREF

:86:091?00UEBERWEISUNG?10944?20RG16S49000023?21VOM 15.07.16

:62F:C160705EUR15500,17

Information contained in a file can be structured as follows:

Red numbers indicate the segment (i.e., :20:, :25: header information of the statement; :60:, :62: balance information; :61: transaction date/amount; :86: payment reference).

Blue/yellow indicate different fields identified by field length and position within a segment (1608100810).

Green is a field identifier that indicates the start of a subfield (?00UEBERWEISUNG). This type of field is used in the payment reference. Note that unstructured variants can also exist where information is separated by keywords, not by subfields.

AR Lockbox

AR Lockbox is used in North America (NA) for importing bank files. It is a comma-separated file format with a flat file structure containing the data. You need to use the CSV file type when importing AR Lockbox bank statements.

Refer to Appendix I: Importing AR Lockbox files for details on how to define the AR Lock box file structure.

CAMT format overview

The ISO 20022 standard defines multiple XML-based message types to exchange financial transactions like credit transfers, direct debits, transaction notifications, etc. See Appendix II: CAMT default settings for more information.

One part of these messages refers to cash management (CAMT). CAMT messages are used to report between bank and customer and are the counterpart to the ISO 20022 defined payment messages.

There are three main CAMT message types:

CAMT.052: used for intraday account movements

CAMT.053:  full bank account statement typically created end-of-day; includes the statement start and end balance and the list of transactions, with or without detailed notifications

CAMT.054: partial bank account statement; used either as debit or credit notification for a single transaction or for transaction detail notifications in addition to a CAMT.053 statement

CAMT messages are built from XML elements and have a basic structure:

Level A: Message level. This block appears only once and contains elements valid for all transactions.

Level B: Account level. This block appears only once and contains elements for account and transaction information.

Level C: Transaction level. This block contains elements describing the transaction. Further transaction details can be described here or in the D level. This block can appear several times.

Level D: Transaction level. This block can appear several times and holds the transaction details for the associated C level.

The following sources provide a detailed description of the CAMT XML structure:

• CAMT format description supplied by your bank
• Description of the ISO 20022 standard:  www.iso20022.org
• Description of country-specific implementations:
  • Germany: www.ebics.de
  • Austria: www.stuzza.at
  • Switzerland: www.iso-20022.ch

Levels C and D contain the most relevant information for matching during the advanced bank statement import process.

Handling C and D levels depends on the message type and country or bank standards such as:

• Single transactions
  • C level with transaction details: All transaction details are listed on the C level. There is no associated D level entry.
  • C level with associated D level entry: Basic transaction information like amount and transaction type are given on the C level. For each C level entry, there is a single associated D level entry with further details.
• Bulk or batch transactions: Transactions can be grouped by certain criteria. In this case, the C level entry includes only summary information like transaction totals. The details for the grouped transactions can be provided as follows:
  • C level with internal detail resolution: A D level entry with transaction details exists for each transaction contained in the batch transaction.
  • C level with external detail resolution: Transaction details are listed in a supplemental, external CAMT.054 file.

Supported CAMT message types

The advanced bank statement import process supports CAMT.053 and CAMT.054 message types.

To import bank statements, use the CAMT.053 with full internal detail resolution transaction types. The advanced bank statement import process does not support CAMT.053 messages with detail resolution through linked CAMT.054 files.

Importing CAMT.054 files should only be used in specific cases when you do not want to import full bank statements.

BAI2 format overview

The BAI2 file is a standard bank statement format developed by the Bank Administration Institute (BAI) and used for electronic cash management balance reporting. It is mostly used in the United Kingdom and the United States.

A BAI2 file only contains multiple record types in a specific structure. Each file contains one File header (type 01) and one File trailer (type 99) but can contain multiple groups. Each group has a Group header (type 02) and a Group trailer (type 98). Within each group pair, there can be multiple Accounts (types 03 and 49) that contain the Transaction detail (type 16) followed by single or multiple Continuation lines (type 88).

See Appendix III: BAI2 default settings for the complete list of record types and their descriptions.

CFONB format

The CFONB bank statement file is a standard format defined by the “Comité Français d’Organisation et de Normalisation Bancaires” (CFONB). It displays financial transactions that occur within a period on a bank account held by a company.

CFONB is flat file with a fixed length and four record types:

Record type 01 – This contains the previous account balance and related information.

Record type 04 – This contains transaction information.

Record type 05 – This contains transaction details (optional). There could be multiple 05 Record types.

Record type 07 – This contains the new account balance and related information.

A CFONB file can include several bank account statements, meaning multiple pairs of Record types 01 and 07. Within each statement pair, there can be multiple transactions, Record type 04, each with multiple transaction detail lines, Record type 05. If there are no transactions within the period, only Record types 01 and 07 are present for that account.

See Appendix IV for a sample file.

Bank ID

The CFONB file also includes information from the Bank ID sent from the bank, which is used to match open items as part of the advanced bank statement import process. If the ID from the bank and the value in the Bank ID number field in the Bank file profile (GESBAN) are different, the import fails.

Therefore, when entering Bank import settings (BSIIMPPAR), the bank field is disabled for the CFONB file format. The ID from the bank is used to match to accounts and open items.

CFONB file limitations

• The reconciliation flow is not managed for CFONB bank statement files. Only the standard flow is managed (matching and payment generation).
• Splitting a CFONB bank statement file is managed for each Record type 01 (account level) if there is at least one transaction, Record type 04. Otherwise this record is skipped because this is the only record type used for matching and payment generation.
• The bank account is strictly determined by the CFONB file, so you cannot manually select a bank account.
• Bank statements with multi currencies are not managed for CFONB file formats.
• Only one Record type 05 is mapped to a transaction, Record type 04. If there are several related to the same Record type 04, the others are ignored.
• Interbank code in payments, generated through the advanced bank statement import process, are not considered.

CSV text file formats

Some banks or payment processors provide statements as CSV-formatted text files. These file types contain elements (fields) separated by a specific character, typically a comma.

The advanced bank statement import process supports CSV file formats with the following characteristics:

• Flat CSV files consisting of data lines only
• CSV files with header lines – detail lines – (footer lines) structure
• Lines ending with a Carriage Return sign

Data fields separated by a same, single ASCII character. The separator character must not occur within the data itself.

See Appendix V for specifics on using CSV text file formats.

Understanding Bank import formats and segments

As outlined in the previous sections, bank statements can come in various formats including bank- or country-specific variants inside a certain format. Most of the bank statement formats have header and line structures with several levels. You need to define the overall structure.

For flexibility in handling different import files, the advanced bank statement import process employs Bank import formats and Bank import segments:

• A Bank import format describes the overall structure of a bank file format and the sequence or occurrence of header and line information (segments). You can set up several bank import formats in parallel.
• A Bank import segment describes the detailed structure of a header or a line information on field level. This controls how the data is extracted from the bank statement file during the import. Segments are grouped into bank import formats, and you can have multiple segments defined as needed.

Currencies

• The following information is important when working with multiple currencies:
• Bank statements with multi currencies are not managed for CFONB file formats.
• The statement currency and the currency setting for bank account in your system must be the same.
• A bank statement import with a currency different from the bank account in your system is not possible and is rejected.
• In addition to the bank statement currency, the open item currency (invoice currency) must also be considered because this currency could differ from the statement currency. The advanced bank import process supports the following cases for automatic and manual matching:
  • Bank statement in company currency, open item in company currency.
    Example: EUR statement and EUR open item
  • Bank statement in foreign currency, open item in same foreign currency.
    Example: USD statement and USD open item
  • Bank statement in company currency, open item in foreign currency.
    Example: EUR statement and GBP open item
  • Other cases (like matching a GBP statement to USD open items) are not supported. For matching, within one transaction of a bank statement, only one currency can be used. This means that if you match one transaction to two open items, both open items must have the same currency.