Convert MT940 files to Excel

What is an MT940 file?

MT940 is the SWIFT Customer Statement Message: an end-of-day account statement in a plain-text format of numbered tags. It was designed for banks to send account statements to their customers' systems, and it became the standard way to feed statements into treasury and accounting software.

Today banks offer MT940 mainly to business customers — as a download in business online banking, through EBICS or host-to-host connections, or over the Swift network. It is especially common in Germany, the Netherlands and Poland, where many accounting programs import it. A file usually has the extension .sta, .mt940 or .txt and often contains one statement per day, sometimes for several accounts.

The MT940 format: the tags

Every statement in the file is a block of tags, each starting a line with a colon-wrapped field number:

A balance reads: credit or debit mark (C/D), date as YYMMDD, currency, amount with a comma as the decimal separator. C260913EUR1250,00 means a credit balance of EUR 1,250.00 on 13 September 2026. When one statement is split over several messages, the intermediate balances are tagged :60M: and :62M:.

Reading the :61: statement line

The :61: line packs a whole transaction into one string with no separators. The example 2609140914D89,90NDDTNONREF breaks down like this:

Watch out for three things: amounts carry no sign, so the direction depends entirely on the D/C mark; the year has two digits and the entry date none, so at the turn of the year it has to be inferred; and an optional bank reference can follow after //, with supplementary details on a second line.

The :86: field: who paid whom, and why

:86: holds up to six lines of 65 characters describing the transaction above it. Its layout is not fixed by SWIFT, so every bank fills it a little differently — often the name of the other party, the payment purpose and a few references run together.

German banks use a structured layout: a three-digit transaction code followed by numbered subfields. ?00 holds the booking text (for example LASTSCHRIFT or GUTSCHRIFT), ?20–?29 and ?60–?63 the purpose, ?30 and ?31 the other party's bank code or BIC and account or IBAN, and ?32–?33 their name. Inside the purpose, SEPA keywords such as EREF+ (end-to-end reference), MREF+ (mandate reference) and SVWZ+ (purpose text) mark the parts.

When we read :86:, the name and the purpose are pulled out, the keyword prefixes are dropped and lines that were cut mid-word at 27 characters are rejoined. The AI then gives each transaction a clean payee name and a category.

How to open an MT940 file in Excel

MT940 is plain text, so Notepad or any text editor will open it — but Excel has no MT940 import. Opened directly, the file lands as one tag per row, with transactions spread over several lines, amounts as text with a comma and the sign hidden in a letter. Parsing that with text formulas breaks on the first :86: that wraps to a new line.

Converting gives you a normal table: one row per transaction with a real date, a signed amount (money out negative), the payee, the full description and a category. The Excel file also has a Summary sheet with totals by category and month — see bank statement to Excel for the layout — and the CSV export uses comma or semicolon format with the date format you choose.

MT940 vs MT942 vs CAMT.053

Three formats are easy to mix up when you pick a download option:

For bookkeeping, download the end-of-day statement — MT940 or, if your bank offers it, CAMT.053. An interim report can still change before the day closes, and without booked balances it can't be checked.

Is MT940 being discontinued?

Not overnight. Swift moved cross-border payment instructions from MT to ISO 20022 when the coexistence period ended in November 2025. Statement and reporting messages such as MT940, MT942 and MT950 were not switched off then: they remain on the Swift network until Swift sets an end date, while banks and their customers move to the ISO 20022 equivalents. Their ISO 20022 equivalents are camt.053 for statements, camt.052 for intraday reports and camt.054 for debit and credit notifications. Details are on Swift's ISO 20022 standards page.

An MT940 file you download from online banking doesn't travel over the Swift network at all, so whether you can keep downloading it is your bank's decision. Many banks offer MT940 and CAMT side by side; if yours does, CAMT.053 is the future-proof choice. Either way, both convert to the same Excel file.

Several accounts and a year of daily statements

An MT940 export often contains one statement per day and sometimes several accounts. We group the statements by account (:25:) and currency. The days of one account join into a single statement that runs from the first opening balance (:60F:) to the last closing balance (:62F:), and every transaction in between must add up: opening balance plus money in minus money out equals the closing balance. If it doesn't — a missing day, for example — you see that before exporting.

Upload a year of files at once and export them as one workbook, or straight into accounting software. Accountants and bookkeepers use this to catch up a client's books, and it is the starting point for a bank reconciliation in Excel.

Questions