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
- Writes the /NAME/ value as Payee and the /REMI/ text as Description, and reads the other party's IBAN from the second :61: line.
- Xero's Reference column gets the references from the :61: booking line. In the Rabobank-style sample that line holds no reference (NONREF, or the word EREF that only says an /EREF/ follows), so the column is empty. The /EREF/ value is in the Reference column of the spreadsheet CSV.
- 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 Xero receives
| *Date | *Amount | Payee | Description | Reference | Check Number |
|---|---|---|---|---|---|
| 02/09/2015 | 300.00 | Hr J DOE | bijdrage februari 2014 | ||
| 02/16/2015 | 300.00 | Hr J DOE | Maandelijkse bijdrage februari | ||
| 02/19/2015 | 600.00 | M. DOE EO | Inleg maart 2015 | ||
| 03/16/2015 | 300.00 | Hr J DOE | Maandelijkse bijdrage februari | ||
| 03/22/2015 | 600.00 | M. DOE EO | Inleg april 2015 | ||
| 03/26/2015 | -4114.00 | Bouwbedrijf M NAAIER B.V. | Factuurnr.: 2015014 project: 201502 1 |
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 Xero
- In Xero open Accounting > Bank accounts and choose the account.
- Click Manage account > Import a statement.
- Choose this CSV and click Import.
- Check the column mapping (Date, Amount, Payee, Description, Reference) and confirm the date format if asked.
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.
- Which date format does Xero need?
- Xero lists both month-first and day-first dates as accepted and asks you to confirm the format when it is unclear. Choose United States for MM/DD/YYYY or Elsewhere for DD/MM/YYYY, and confirm the same format in Xero if it asks.
- 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.
- Can I import more than one month at once?
- Yes. Xero imports the whole statement file you give it. Keep each file to one bank account in Xero, and avoid overlapping a period you already imported so you do not have to delete duplicate statement lines.
Sources
BooksReady is not affiliated with, endorsed by or sponsored by Rabobank. The layout notes above come from the sources listed.