account_banking_nl_ing_mt940 parse failure

[Kees van den Broek]

I tried out account_banking_nl_ing_mt940.
Got the following error when it parsed a .940 file that I downloaded from my ing.nl portal:

     File "/var/lib/openerp/ENV/openerp/local/lib/python2.7/site-packages/openerp-7.0_20140419_231145-py2.7.egg/openerp/addons/account_banking_nl_ing_mt940/account_banking_nl_ing_mt940.py", line 54, in handle_tag_61
    self.current_statement.id)
AttributeError: 'NoneType' object has no attribute 'groupdict'

I think the regex needs a modification, like:

    tag_61_regex = re.compile(
        '^(?P<date>\d{6})(?P<sign>[CD])(?P<amount>\d+,\d{2})N(?P<type>\w{2})'
        '(?P<reference>\w{1,16})')

These are typical :61: lines:

:61:131210D90,75NDV MARF
:61:140328C302,50NVZ 0000000000040626

[Stefan Rijnhart (Opener) (stefan-opener)]

Thanks for the analysis! Your examples only match with an additional (optional?) space in the expression before the reference. We'll compare with our own data.

'^(?P<date>\d{6})(?P<sign>[CD])(?P<amount>\d+,\d{2})N(?P<type>\w{2}) (?P<reference>\w{1,16})'

[Kees van den Broek (kvdb-kvdb)]

The extra space was not optional if I look at my 940 data. On all :61: lines, it's present.

[Holger Brunn (Therp) (hbrunn)]

Well, in the MT940 files i got from ING, it's not. Also http://www.ing.nl/Images/ING_IBP_MT940_942_format_description_The_Netherlands_tcm7-154872.pdf (p9) is quite clear on that.

The codes you quote look like some legacy stuff. (I remember 'DV' and 'VZ' codes from the csv parser)

What kind of account do you have at ING?

[Kees van den Broek (kvdb-kvdb)]

The Portal is called 'Mijn ING Zakelijk'.
I download files from this URL:
https://bankieren.mijnzakelijk.ing.nl/zakelijk/overzichten/af-en-bij-betaalrekeningen/index

As I read it, there may be multiple versions of MT940 indeed within ING.
On the right side of this page:
http://www.ing.nl/zakelijk/internetbankieren/verbetering-rekeningrapportage-mt940-mt942/
There's an interesting read called: "Vragen en antwoorden omzetting (PDF)"

It mentions people are able to start testing the new format from may 1st. Not sure if that applies to my subscription at ING too, but I'll ask them anyway.

[Stefan Rijnhart (Opener) (stefan-opener)]

@Holger, The specs you referred to indicate that the transaction code has a fixed length of 4. Kees' sample tags adhere to this specification: the space seems to be a filler here. The regexp seems to implement a length of 3 though.

[Holger Brunn (Therp) (hbrunn)]

The four includes the 'N' which is placed outside the group.

Reading the specs more closely, it says nowhere that the type field is numeric, so the better regex would be

tag_61_regex = re.compile(
        '^(?P<date>\d{6})(?P<sign>[CD])(?P<amount>\d+,\d{2})N(?P<type>.{3})'
        '(?P<reference>\w{1,16})')

I prepared an MP, could you test that please?

[Stefan Rijnhart (Opener) (stefan-opener)]

@Holger, sorry I misread the bug report and thought that the regexp mentioned in there was the one from the actual code...

[Kees van den Broek (kvdb-kvdb)]

The updated regex in that branch fixed _that_ problem. Thanks.

Now, the parser got stuck a bit further ahead:

  File "/var/lib/openerp/ENV/openerp/local/lib/python2.7/site-packages/openerp-7.0_20140419_231145-py2.7.egg/openerp/addons/account_banking_nl_ing_mt940/account_banking_nl_ing_mt940.py", line 79, in handle_tag_86
    subfields[current_codeword].append(word)
KeyError: None

Here are some more lines from my .940 file.
Perhaps it contains a clue.
(only changed some irrelevant bits to protect privacy of customers)

:61:140101D0,01NDV NONREF
:86: RC AFREK. REK. 15.31.365 EUR
COR.PERIODE 01.10 T/M 31.12.2013 CREDITRENTE

:61:140327C90,75NOV EREF
:86:NL39RABO01234567891 RABONL2U ABC DEF 1403260010001001 200100 8
9482

:61:140310D181,50NDV MARF
:86:AC01 NL37ABNA0123456780 ABNANL2A tableaux E3048B1111A04E2 E2E-ID-
c88c4841111111a181111a050 ABC DEF

[Holger Brunn (Therp) (hbrunn)]

Well, this is what ING calls MT940 unstructured, and the parser can't make anything out of that.

If you replace the offending line with

if current_codeword:
  subfields[current_codeword].append(word)

it shouldn't error out anymore, but you won't be happy with the result. Without the structured description, you'll get the other party's account data in the comment and no matching will be done.

[Stefan Rijnhart (Opener) (stefan-opener)]

Kees, you should check if you can download the structured variant of the MT940 format.

[Kees van den Broek (kvdb-kvdb)]

@Holger:
If I grep the :86: lines in the .940 file, I can categorize the 'unstructured' data as follows:

* Starting with a space, ING is the other party:

:86: 21-03-14 18:24 BETAALAUTOMAAT
:86: RC AFREK. REK. 15.31.365 EUR

* Starting with an IBAN:

:86:NL05ABNA0411111111 ABNANL2A ABC DEF

* Starting with a prefix, then IBAN:

:86:MD06 NL05ABNA0411111111 ABNANL2A ABC DEF
:86:AC01 NL05ABNA0411111111 ABNANL2A ABC DEF

* Mentioning of incasso, then IBAN:

:86:Europese Incasso, doorlopend NL05ABNA0411111111 ABNANL2A ABC DEF
:86:Zakelijke Europese Incasso, doorlopend NL05ABNA0411111111 ABNANL2

* One last case I can't figure out. It only appears when the preceeding :61: line contains a NONREF. The number shown is the non-IBAN account number of the other party.
:61:140214C90,75NOV NONREF
:86:0110444444 ABC DEF

The corresponding line from the CSV file looks like:
"20140214","OTHER PARTY","153xxxx","0110444444","OV","Bij","90,75","Overschrijving","ABC DEF"
https://bugs.launchpad.net/banking-addons/+bug/1317021/+addcomment
Not sure if this is structure enough to make something out of it. I can imagine the answer is no. That means I'll head back to csv...

[Kees van den Broek (kvdb-kvdb)]

@Stefan
Attached, a screenshot from the portal. A lot to choose from. But only one 940 format.