What is an MT940 file and how to open it

MT940 is the SWIFT customer statement format: a text file in which a bank reports one account's opening balance, transactions and closing balance for a period. Banks offer it mainly to business customers, and ERP, treasury and some accounting software import it; Excel does not open it as a table.

What is inside an MT940 file

An MT940 file is plain text. Each line starts with a tag between colons:

:20:STMT20260930
:25:NL91ABNA0417164300
:28C:00187/001
:60F:C260831EUR1520,75
:61:2609030903D42,50NTRFNONREF
:86:CITY WATER ONLINE PAYMENT
:61:2609050905C1250,00NTRFNONREF
:86:ACME PAYROLL SALARY SEPTEMBER
:62F:C260930EUR2728,25

• :20: statement reference, :25: account number (often an IBAN), :28C: statement and page number.
• :60F: opening balance and :62F: closing balance: C for credit or D for debit, the date as YYMMDD, the currency and the amount.
• :61: one transaction: value date (YYMMDD), optional entry date (MMDD), D or C for debit or credit, the amount with a decimal comma, a transaction type code and a reference.
• :86: the description for the transaction above it: payee, remittance information and bank codes.

The opening balance plus the transactions equals the closing balance (1,520.75 - 42.50 + 1,250.00 = 2,728.25), a built-in check that nothing is missing.

Why banks use MT940

MT940 gives every bank the same statement structure, so one company can collect statements from several banks and import them into one system without a separate layout for each bank. Its fixed tags make it easy to process automatically, which is why ERP and treasury systems use it for daily reconciliation.

Many banks also offer camt.053, the ISO 20022 XML statement that is gradually replacing MT940. Both carry the same information.

Challenges with MT940

• Not readable as a table. Excel shows MT940 as lines of tags, not as transactions.
• Bank variations. The :86: description has no common layout: some banks split it into numbered subfields (?20, ?32), others use /NAME/ style codes, others write free text. A program that reads one bank's MT940 may get another bank's payees wrong.
• Amounts and signs. Amounts use a decimal comma and no sign; the D or C mark gives the direction, and RD and RC mark reversals.
• Two-digit years and two dates. Dates are YYMMDD, and a transaction can carry both a value date and an entry date.

How to open an MT940 file in Excel

1. Download and install ProperConvert.
2. Open the MT940 file in the app. Check the transactions and that they add up from the opening to the closing balance.
3. Click Convert to save a CSV or Excel file, or another format such as QBO, OFX or QIF for an accounting app. Several MT940 files can be converted at once.

MT940 questions

What is the difference between MT940 and MT942?

MT940 is the end-of-day or end-of-period statement with opening and closing balances. MT942 is an interim report of transactions during the day.

Can I import MT940 into QuickBooks or Xero?

Not directly. Convert it to QBO for QuickBooks or to OFX or CSV for Xero.