BooksReady

Convert an MT940 file to Excel

In short: An MT940 file is the SWIFT bank statement many European banks offer for download. It is plain text in tagged fields, with decimal commas, D and C marks for money out and in, and a free-text description field whose layout differs by bank. A spreadsheet needs one row per transaction with plain columns: dates, a signed amount, the currency, who paid or was paid, the description and the running balance. Drop the file below. It is converted on your device and never uploaded.

Convert your file

MT940 to Excel (CSV)

A .sta, .940, .mt940 or .txt file from your bank's MT940 (SWIFT) statement download

Your file stays on this device

It is read and converted by this page, inside your browser. Nothing from the file is uploaded, and the converter keeps working offline once the page has loaded.

One email when it ships. The address is used for nothing else. Details.

1. Export the file from your bank

  1. In your online banking open the account and look for Statements, Export or Download.
  2. Choose the MT940 format (some banks call it SWIFT, MT940 structured or .sta).
  3. Pick the period and download.
  4. Upload the file here exactly as downloaded.

Why a raw MT940 export fails in Excel, Google Sheets or LibreOffice

Opened in Excel, an MT940 file is a single column of tagged lines such as :61:2512011201D12,34NTRF. Amounts, dates and descriptions are packed into codes, so there is nothing to sort or sum.

Excel and Google Sheets open a CSV directly, but only if it is a simple table. A bank statement file has to be turned into one first.

What is unusual about the MT940 file

  • One file can hold many statements and several accounts. Each converted file holds one account in one currency; you choose which.
  • Amounts use a decimal comma (1234,56) and a D or C mark for money out or in. RD and RC mark reversals.
  • Each entry has a value date and usually a booking date; we use the booking date and keep the value date in the spreadsheet CSV.
  • We check every statement: the opening balance plus the entries must equal the closing balance. If it does not, you get a clear warning.
  • The description field (:86:) comes in several layouts. We read the German ?20 to ?63 subfields and the /NAME/ /REMI/ /EREF/ style used by Dutch banks, and keep anything else as plain text.

What Excel, Google Sheets or LibreOffice requires

  • One row per transaction: Date, Value date, Amount, Currency, Debit/Credit, Counterparty, Counterparty account, Description, Reference, Balance.
  • Comma style: commas between columns and 1234.56 amounts. Semicolon style: semicolons and 1234,56 amounts.
  • Dates are written as YYYY-MM-DD unless you choose another format.
  • Text that starts with = + - or @ gets an apostrophe in front so a spreadsheet does not run it as a formula.

2. 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

After: mt940-2015-02-09-to-2015-04-20-spreadsheet.csv
DateValue dateAmountCurrencyDebit/CreditCounterpartyCounterparty accountDescriptionReferenceBalance
2015-02-092015-02-09300.00EURCreditHr J DOENL75INGB0001234567bijdrage februari 20145742.89
2015-02-162015-02-16300.00EURCreditHr J DOENL75INGB0001234567Maandelijkse bijdrage februari6042.89
2015-02-192015-02-19600.00EURCreditM. DOE EONL60RABO0987654321Inleg maart 20155027902426642.89
2015-03-162015-03-16300.00EURCreditHr J DOENL75INGB0001234567Maandelijkse bijdrage februari6942.89
2015-03-222015-03-22600.00EURCreditM. DOE EONL60RABO0987654321Inleg april 20155301594787542.89
2015-03-262015-03-26-4114.00EURDebitBouwbedrijf M NAAIER B.V.NL25DEUT050881234515185Factuurnr.: 2015014 project: 201502 15344563953428.89

Synthetic sample data that follows the real export layout, not a real account. The "after" output was produced from it by the same converter this page runs.

3. Import the converted file into Excel, Google Sheets or LibreOffice

  1. Open the downloaded file in Excel, Google Sheets or LibreOffice.
  2. 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.

Other ways to convert MT940

Other exports to Excel (CSV)