The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,096 other followers

Some notes on the MT940 format used by various banks to export transactions

Posted by jpluimers on 2018/07/25

I needed the below links to get a feel on how much effort it would be to convert ABNAMRO transactions from MT940 into CSV or XLS extracting as many useful fields as possible.

Maybe it useful for other people as well.

I know there is CAMT.053 as well, but that puts every transaction into a separate XML file but still spreads the description over a multi-line structured field (like :86: below).

Two observations on field :86: (Information to Account Owner) are:

  1. Often it is less than 6 lines; I think it should be specified like “as most 6 lines of maximum 65 characters”
  2. At least ABN AMRO splits up the 65 characters into two columns:
    • part 1: 33 characters (almost always having a space in position 33)
    • part 2: 32 characters
  3. At least ABN AMRO uses this format for these lines:
    1. optional: description not having a semicolon (:) in it
    2. optional repetition of:
      1. mandatory keyword followed by a semicolon (:) and not having spaces in it
      2. mandatory description not having a semicolon (:) in it
      3. space

Searchable main source file of the library and easy browsing of the rest of the files: [WayBack] Mt940Parser.cs in sharpmt940lib located at /Raptorious.SharpMT940Lib | source code search engine

Background: [Archive.isJaco Adriaansen on Twitter: “@jpluimers Op dit moment parse ik 86 niet dieper. De inhoud van 86 is gewijzigd na dat ik het gemaakt heb. Zie: https://t.co/CrMc1OgaB0”

My modified source code is at [Wayback/Archive.is] GitHub – jpluimers/MT940-to-CSV: Convert MT940 files to CSV using C# library SharpMt940Lib with this README.md:

MT940-to-CSV

Convert MT940 files to CSV using C# library SharpMt940Lib

For now the implementation hard coded for ABN AMRO MT940 format expecting nl-NL formatting of numbers.

Example ABN AMRO MT940 file used for testing from wiki.yuki.nl.

Syntax:

MT940toCSV.exe [MT940-FilePath...]

You supply zero or more MT940-FilePath entries.

For each entry, it exports CSV by changing the extension of the source file.

Usually the FileName portion of MT940-FilePath looks like MT940170417161106.STA when downloaded from https://www.abnamro.nl/nl/paymentsreporting/downloadmutations.html

Note it is still work in progress.

The next step will be parsing the TransactionDescription field that is formatted contrary to what is described in “2.3 Verschillen TAG 86 Mutatie Informatie” from 2.3 Verschillen TAG 86 Mutatie Informatie.

The slash (/) separation described there is not used; in stead some (but not all) fields are in this form:

NAME: VALUE

Here:

  • NAME cannot have spaces
  • VALUE starts and ends with a non-space, but can contain spaces in other positions.

Observed NAME values for now:

  • OMSCHRIJVING
  • IBAN
  • KENMERK
  • CHECK
  • FACT.NR
  • RELATIENR
  • INCASSANT
  • BETALINGSKENM.
  • FACTUUR
  • BIC
  • NAAM
  • CREDITRENTEOVERZICHT
  • TERMIJN
  • MACHTIGING
  • RELATIENR
  • VLGNR
  • RENTE

The non-delimited first parts observed until now are:

  • SEPA ACCEPTGIROBETALING
  • SEPA IDEAL
  • SEPA INCASSO ALGEMEEN DOORLOPEND
  • SEPA OVERBOEKING
  • SEPA PERIODIEKE OVERB.

–jeroen

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: