What the Rabobank MT940 file looks like
- In the sample, each day is its own statement: the reference (:20:) is 940S followed by the date, and days without bookings still have a statement with equal opening and closing balances.
- The account line (:25:) is the IBAN, a space and the currency, for example NL68RABO0123456789 EUR.
- The other party's IBAN is on the second line of the booking (:61:), not in the description.
- The description field (:86:) uses code words such as /ORDP//NAME/ with /ADDR/ for money in, /BENM//NAME/ for money out, /REMI/ for the payment description, /EREF/ for the end-to-end reference and /ISDT/ for a date. Values can continue on the next line in the middle of a word or date.
What the converter does with it
- Reads the other party's name from /NAME/, the description from /REMI/, the /EREF/ value as the reference, and the IBAN from the second :61: line.
- Reads every daily statement in the file and checks that each one opens with the closing balance of the day before, and that the bookings add up to each closing balance.
- Uses the IBAN without the currency as the account number.
Before and after
Before: the MT940 statement as downloaded
:20:940S150209 :25:NL68RABO0123456789 EUR :28C:0 :60F:C150206EUR000000005442,89 :61:150209C000000000300,00N541NONREF NL75INGB0001234567 :86:/ORDP//NAME/Hr J DOE/ADDR/Ieplaan 100 2500AA S GRAVENHAGE NL/REMI/bijdrage februari 2014/ISDT/2015-02-09 :62F:C150209EUR000000005742,89
After: what Excel, Google Sheets or LibreOffice receives
| Date | Value date | Amount | Currency | Debit/Credit | Counterparty | Counterparty account | Description | Reference | Balance |
|---|---|---|---|---|---|---|---|---|---|
| 2015-02-09 | 2015-02-09 | 300.00 | EUR | Credit | Hr J DOE | NL75INGB0001234567 | bijdrage februari 2014 | 5742.89 | |
| 2015-02-16 | 2015-02-16 | 300.00 | EUR | Credit | Hr J DOE | NL75INGB0001234567 | Maandelijkse bijdrage februari | 6042.89 | |
| 2015-02-19 | 2015-02-19 | 600.00 | EUR | Credit | M. DOE EO | NL60RABO0987654321 | Inleg maart 2015 | 502790242 | 6642.89 |
| 2015-03-16 | 2015-03-16 | 300.00 | EUR | Credit | Hr J DOE | NL75INGB0001234567 | Maandelijkse bijdrage februari | 6942.89 | |
| 2015-03-22 | 2015-03-22 | 600.00 | EUR | Credit | M. DOE EO | NL60RABO0987654321 | Inleg april 2015 | 530159478 | 7542.89 |
| 2015-03-26 | 2015-03-26 | -4114.00 | EUR | Debit | Bouwbedrijf M NAAIER B.V. | NL25DEUT050881234515185 | Factuurnr.: 2015014 project: 201502 1 | 534456395 | 3428.89 |
Sample: "RABO MT940 Structured", a Rabobank-style MT940 file shared publicly on GitHub (not published by Rabobank). It adds up: 5,442.89 at the start, 4,003.49 at the end, over 8 bookings. The "after" output was produced from it by the same converter this page runs.
Import the converted file into Excel, Google Sheets or LibreOffice
- Open the downloaded file in Excel, Google Sheets or LibreOffice.
- If every value lands in one column, use the semicolon style (for European Excel) or import it with Data > From Text/CSV.
Questions
- How do I know nothing went missing?
- Every MT940 statement carries an opening and a closing balance. The converter adds up the bookings of each statement and compares the result with the closing balance. If they do not match, you see a warning that names the statement and the difference.
- Why does everything land in one column in Excel?
- Excel uses the list separator of your computer's region. In much of Europe that is a semicolon and the decimal mark is a comma. Choose the semicolon style and download again, or open the file through Data > From Text/CSV and pick the separator there.
- My file has several accounts. What happens?
- One file can hold statements for several accounts or currencies. Each converted file holds one account in one currency, and a menu lets you pick which one. Convert each account into its own account in your books.
- Are accents and umlauts kept?
- Yes. The file is UTF-8 and starts with the marker Excel looks for, so names such as Müller or Société open correctly.
Sources
BooksReady is not affiliated with, endorsed by or sponsored by Rabobank. The layout notes above come from the sources listed.