What the ING MT940 file looks like
- The account line (:25:) is the IBAN followed by the currency, for example NL20INGB0001234567EUR.
- Each booking line (:61:) can have a second line with an ING transaction code, for example /TRCD/00100/, and /OCMT/ with the original amount when it differs from the booked amount.
- The description field (:86:) is built from code words in the form /code word/value/: /EREF/ for the end-to-end reference, /CNTP/ for the other party as account/BIC/name/city, and /REMI/ for the payment description, either /REMI/USTD// with free text or /REMI/STRD/CUR/ with a structured reference. Other code words include /IREF/, /PREF/, /MARF/, /CSID/ and /RTRN/.
- Lines are at most 65 characters and a value can continue on the next line in the middle of a word (for example "ING T" then "estrekening").
- After the closing balance, a last :86: line names the account owner and gives totals: /NAME/.../BIC/.../SUM/ with the number and total of debit and credit bookings.
What the converter does with it
- Reads the name and account from /CNTP/, the description from /REMI/, and the /EREF/ value as the reference. Values split across lines are joined back together.
- Uses the account from :25: without the currency as the account number, and the currency from the balance lines.
- Skips the /TRCD/ code line and the closing /NAME/ /SUM/ line: they are not transactions.
- Checks every statement: the opening balance plus the bookings must equal the closing balance, and you get a warning if it does not.
Before and after
Before: the MT940 statement as downloaded
{1:F01INGBNL2ABXXX0000000000}{2:I940INGBNL2AXXXN}{3:{108:B12345678S000001
}}{4:
:20:P140104000009999
:25:NL20INGB0001234567EUR
:28C:00003
:60F:C250204EUR1000,00
:61:2502040103C350,00NTRF5200150700013379//00000000000001
/TRCD/00100/
:86:/EREF/E2E2014030100899040//CNTP/NL08INGB0000001234/INGBNL2A/ING After: what Excel, Google Sheets or LibreOffice receives
| Date | Value date | Amount | Currency | Debit/Credit | Counterparty | Counterparty account | Description | Reference | Balance |
|---|---|---|---|---|---|---|---|---|---|
| 2025-01-03 | 2025-02-04 | 350.00 | EUR | Credit | ING Testrekening | NL08INGB0000001234 | 5200150700013379 | E2E2014030100899040 | 1350.00 |
| 2025-01-03 | 2025-02-04 | 125.00 | EUR | Credit | ING Testrekening | NL08INGB0000001234 | UstrdRemInf5878992110 | E2E2014030100655465 | 1475.00 |
Sample: ING's own published MT940 sample file (first statement), copied from the PDF. 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
- ING: Sample Files NL MT940 (PDF)
- ING: MT940 and MT942 format description for InsideBusiness Payments (PDF)
BooksReady is not affiliated with, endorsed by or sponsored by ING. The layout notes above come from the sources listed.