How do I convert an MT940 bank statement file to CSV or Excel?

Dec 9, 2025

Open an MT940 file and you’ll see a wall of SWIFT tags, not tidy rows. Yep, it’s normal to feel stuck.

What most finance folks want is simple: clean tables you can pivot, reconcile, and push into your ERP—without babysitting every line.

This guide answers “How do I convert an MT940 bank statement file to CSV or Excel?” and walks through quick exports, DIY scripts, and an easier route with BankXLSX.

Here’s what we’ll cover:

  • What MT940 is and why CSV/XLSX makes life easier for accounting and reporting
  • How key tags (:61:, :86:) turn into usable columns
  • Common gotchas (encoding, decimal commas, C/D signs, multi-line narratives) and fixes
  • Three ways to convert and when each one fits
  • A simple walkthrough using BankXLSX
  • Field mapping ideas for ERP imports and fast reconciliation
  • Bulk processing, multi-account setups, automation, and security
  • Quick troubleshooting and FAQs

What is an MT940 bank statement and why convert it to CSV/Excel?

MT940 is a SWIFT message banks use for end‑of‑day statements. It’s compact, consistent, and great for machines.

For humans in spreadsheets? Not so much. That’s why teams ask how to convert MT940 to CSV or convert MT940 to Excel xlsx—to reconcile balances fast, analyze activity, and load data into an ERP with fewer clicks.

A typical file includes an opening balance, a bunch of transaction lines, and a closing balance. The remittance info matters a lot for matching payments and invoices. A tiny example:

:60F:C230901EUR1234,56
:61:2309010901D123,45NTRFNONREF
:86:/EREF/INV-48291/SVWZ/Consulting Sep
:62F:C230901EUR1111,11

Once this becomes rows and columns, good things happen:

  • Reconciliation: Opening + net movement = closing, per account and day.
  • Analysis: Pivot by counterparty, category, cost center—whatever you track.
  • ERP import: Clean CSVs reduce posting errors and rework.

Bonus many teams miss: audit readiness. Standardized outputs mean the same headers, formats, and checks across banks. Reviews go faster, and your month‑end trail is easier to prove.

Inside an MT940: the tags and how they map to transactions

MT940 is a series of labeled fields. Once you know the main tags, conversion gets much easier.

Common fields you’ll see:

  • :20: Statement reference
  • :25: Account ID (IBAN or local)
  • :28C: Statement number/sequence
  • :60F: or :60M: Opening balance
  • :61: Transaction line
  • :86: Narrative/remittance details
  • :62F: or :62M: Closing balance

A :61: line carries value date, sometimes booking date, a C/D sign, amount, a code, and a reference, like:

:61:2309010901C250,00NTRFNONREF

Here’s how “MT940 :61 and :86 fields explained” looks in a sheet:

  • Booking Date: 2023-09-01 (from :61:)
  • Value Date: 2023-09-01 (from :61:)
  • Amount: +250.00 (C = credit, D = debit)
  • Currency: Often from the balance lines (EUR in the snippet)
  • Description: From :86: (combine all lines)
  • Structured refs: From :86:/EREF/, /KREF/, /SVWZ/

About those subfields—MT940 EREF KREF SVWZ meaning:

  • EREF: End‑to‑end reference (usually unique)
  • KREF: Customer/bank reference
  • SVWZ: Payment purpose or narrative

Tip from experience: when you get same‑date, same‑amount duplicates, use EREF + KREF to break ties and avoid double posting.

Common pitfalls that break MT940-to-CSV/Excel conversions

Most errors come from a handful of issues. Nail these and you’ll save hours.

  • Decimals and encoding: Many banks use comma decimals and ISO‑8859‑1. If you assume UTF‑8 or dot decimals, numbers go sideways. This is the classic MT940 decimal comma and encoding issues (UTF‑8 vs ISO‑8859‑1) problem.
  • Multi‑line :86: narratives: They wrap across lines. If you don’t merge them in order, you lose remittance info and break matching.
  • Debit/Credit sign: Honor the C/D in :61:. Guessing from transaction codes flips signs and throws off balances.
  • Value vs booking date: In value date vs booking date MT940 logic, cash uses value date; accounting often posts on booking date. Keep both.
  • Multiple statements in one file: Split by each :60:…:62: block, or balances will mix across periods.
  • Overlapping exports: When you pull Sep 1–15 and Sep 10–30, duplicates happen. Deduplicate on stable fields.
  • Currency assumptions: Confirm currency from balance lines. Don’t guess.

One strict safety check: for each statement block, verify sum of signed :61: amounts equals closing minus opening. If it doesn’t match, stop and fix it before export.

Conversion paths: bank export, DIY parsing, or a dedicated converter

Three realistic routes, each with trade‑offs.

1) Bank portal exports
Some portals let you download CSV or XLSX. Good for quick, one‑off needs.

Watch out for shifting headers across banks and trimmed narratives. Always spot‑check the first few dozen rows for EREF/SVWZ and numeric formats.

2) DIY scripting
If you’ve got engineering help, you can build a custom parser for accounting teams. Detect encoding, split statements, reconstruct :86:, parse :61:, map to CSV.

Pros: full control and extensibility. Cons: constant maintenance for bank quirks, plus handover risk if the original author moves on.

3) Purpose‑built conversion with BankXLSX
Upload files, preview results, set mapping, export. No one needs to figure out how to open MT940 file in Excel or debug scripts.

You also get consistency—stable templates, audit logs, and outputs that actually match what your ERP expects.

Practical tip: version your column mapping like a schema. If anything changes, downstream imports won’t silently break.

Step-by-step: Converting MT940 to CSV/Excel with BankXLSX

Here’s a quick path to convert MT940 to CSV or convert MT940 to Excel xlsx without fuss:

  1. Gather files
    Export MT940 by account and date range. Keep track of the periods so you can catch overlaps later.
  2. Upload
    Drag and drop one or many files into BankXLSX. Multi‑statement files are fine; they’ll be grouped.
  3. Auto‑detect
    BankXLSX detects structure and encoding, handles decimal commas, and reads C/D indicators and currencies.
  4. Preview
    Check a live table: Booking Date, Value Date, Amount (signed), Currency, Description, Account, Statement No., plus EREF/SVWZ. Skim a handful for signs and full narratives.
  5. Map fields
    Match columns to your ERP. Map /EREF/ to ExternalReference, /SVWZ/ to Purpose, and consider CompositeId = Account + ValueDate + Amount + EREF to block duplicates.
  6. Output settings
    Pick CSV or XLSX, choose delimiter, and set date/decimal formats that fit your locale and ERP.
  7. Export and validate
    Download and run a quick check: Opening + Net = Closing per statement. Save a control report for your files.

Field mapping guide: from MT940 tags to spreadsheet columns

A solid MT940 to CSV mapping template keeps everything predictable for imports and reviews.

  • Account (from :25:)
  • StatementNumber (from :28C:)
  • OpeningBalance, OpeningBalanceDate (from :60F:/M:)
  • BookingDate, ValueDate (from :61:)
  • DebitCredit, AmountSigned (from :61:)
  • Currency (from balances or :61:)
  • Description (merged :86:)
  • EREF, KREF, SVWZ (from :86: subfields)
  • BankReference or TransactionCode (from :61: tail)
  • ClosingBalance, ClosingBalanceDate (from :62F:/M:)

If you import MT940 into ERP as CSV, match the exact header names your system expects. Keep a short “dictionary” of transformations so everyone uses the same logic.

Small but useful detail: keep both AmountSigned and AbsoluteAmount. Signed for reconciliation, absolute for fees, thresholds, and filtering.

For duplicate control, add IDs:

  • CompositeId = Account + BookingDate + ValueDate + AmountSigned + EREF + BankReference
  • HashId = SHA‑256 of normalized fields

Those keys make re‑runs safe and prevent double posting across months.

Locale, formatting, and encoding settings that matter

Most silent errors come from locale mismatches and text encodings. Fix them up front.

  • Decimals and delimiters: If your Excel uses decimal commas, export a semicolon‑delimited CSV. Otherwise “1,23” may split wrong. If you use commas, stick to dot decimals.
  • Dates: Use ISO 8601 (YYYY‑MM‑DD) for imports. It avoids 01/02 confusion.
  • Encoding: Many European banks send ISO‑8859‑1. Treating it as UTF‑8 will mangle names like “München.” Detect properly, normalize to UTF‑8, then export.

Quick check: open the CSV in a plain text editor and search for characters like ü or é. If they look right, Excel will too.

Still fighting MT940 decimal comma and encoding issues (UTF‑8 vs ISO‑8859‑1)? Export XLSX instead of CSV. XLSX stores numbers as numbers, which dodges a lot of locale weirdness.

Also, write down a mini “data contract”: AmountSigned is numeric with two decimals; BookingDate is ISO; Description is UTF‑8 text. Enforce it so formats don’t drift over time.

Reconciliation and quality checks to ensure accuracy

Lock in accuracy with a few simple checks. They’re quick and save headaches later.

  • Balance math: For each :60:…:62: block, OpeningBalance + Sum(AmountSigned) = ClosingBalance. If it doesn’t, stop.
  • Transaction count: Count :61: lines and match to output rows. If it’s off, something got dropped or merged.
  • Currency consistency: Transactions should match the block’s currency. Flag anything odd.
  • Duplicate scan: Use CompositeId/HashId across months. Quarantine duplicates instead of deleting silently.
  • Narrative length: Ensure long :86: narratives are fully merged, not cut off.
  • Aging view: Compare value vs booking date. Outliers might affect interest or cash reporting.

Nice addition: a one‑page “variance digest” per export with totals, counts, and exceptions. Reviewers sign off faster, and it’s perfect for your close file.

Handling multi-account, multi-statement, and bulk conversions

Most teams juggle lots of accounts and subsidiaries. Plan for scale from day one.

  • Multi‑statement files: Split on :60:…:62: and tag rows with Account (:25:) and StatementNumber (:28C:).
  • MT940 multi‑account statement handling: Export one consolidated file with an Account column for reporting, but keep per‑account filters for posting.
  • Batch convert MT940 files: Upload a month in one go and dedupe across overlaps using your IDs.
  • Safe reprocessing: Make imports idempotent so reruns don’t create duplicates or change posted entries.
  • Per‑account control totals: Emit daily totals to spot missing statements quickly.
  • Cash pooling: Keep pool identifiers in your schema so you can analyze the underlying accounts properly.

A simple model that works: Bronze (raw MT940 archived), Silver (validated/mapped CSV/XLSX), Gold (ERP‑ready). You’ll be able to trace any figure back to the source file in seconds.

Working with the output in Excel or Power Query

Now that you’ve got clean data, make Excel do the heavy lifting.

  • Dates as dates: Ensure BookingDate and ValueDate are true date types. Power Query’s Change Type helps.
  • Signed and absolute: Keep AmountSigned for math, and AbsoluteAmount = ABS(AmountSigned) for fee analysis and filters.
  • Narratives into fields: If you didn’t map EREF/KREF/SVWZ earlier, Power Query can pull them out with Text.BetweenDelimiters.
  • Pivots: Pivot by counterparty, category, GL. Add conditional formatting for big movements or weekend activity.
  • Reconcile view: A small table that recomputes Opening + Net = Closing by StatementNumber helps reviewers move fast.

Tempted to open MT940 file in Excel directly? Skip it. Load the converted CSV/XLSX instead so signs, dates, and narratives behave.

Pro move: a Power Query that auto‑loads the latest exported file from a shared folder. Your dashboards refresh when month‑end files land.

Automation and integration

When volumes grow, let the machines handle the routine.

  • Scheduling: Run nightly or intraday to match bank cutoffs and your close calendar.
  • Pipelines: Use S3/Blob/SFTP as a landing zone. BankXLSX can watch a folder, convert, and drop standardized outputs.
  • ERP imports: Keep a versioned template. Update docs and templates together when columns change.
  • APIs/webhooks: Kick off conversions via API and get a webhook when files and control reports are ready.
  • Idempotency: Rely on CompositeId/HashId and store checksums per file to avoid repeat work.
  • Exceptions: Send failures (balance mismatches, unknown encodings) to Slack/email with context and a link to the file.

Add lineage columns like SourceFile, ParseTime, ConverterVersion to every row. Debugging and audits become a lot faster.

Security, privacy, and audit considerations

Bank data is sensitive. Treat it like it is.

  • Access: Use SSO/MFA and roles (Preparer, Reviewer, Admin). Keep permissions tight.
  • Residency: Some teams need EU‑only or region‑specific storage. Confirm where data lives.
  • Encryption: TLS in transit, encrypted at rest. For CSV distribution, password‑protect ZIPs and store keys safely.
  • Retention: Auto‑delete uploads and outputs on a schedule, with exceptions for audits.
  • Audit logs: Track who converted what, when, and with which mapping. Tie logs to your close checklist.
  • Segregation of duties: Separate mapping changes from export rights. Use a second reviewer for sign/date logic edits.

Also helpful: export a control report with totals, counts, and exceptions alongside the CSV/XLSX. Auditors prefer one signed summary over digging through raw tabs.

Troubleshooting: quick fixes for common issues

  • Weird characters: If “Müller” looks broken, re‑parse with ISO‑8859‑1 and export as UTF‑8 or XLSX.
  • Wrong signs: Double‑check the :61: C/D indicator. Don’t infer signs from transaction codes.
  • Cut‑off narratives: Merge all :86: continuation lines until the next tag. Some banks add line breaks mid‑stream.
  • Missing rows: Look for multiple :60:…:62: blocks and verify the bank export date range.
  • Duplicates: Overlapping date ranges are common. Use CompositeId (Account + ValueDate + Amount + EREF + BankReference) to drop dupes safely.
  • Date oddities: If aging looks off, revisit value vs booking date MT940 mapping.
  • Locale mishaps: If “1,23” becomes “123”, export semicolon‑delimited CSV or switch to XLSX.

Write mini playbooks for fixes you hit more than once. Future you will be grateful.

FAQs

Can Excel open MT940 files directly?
It can, but you’ll see raw tags. Convert first, then open the CSV/XLSX so fields, signs, and dates are correct.

What’s the difference between MT942 and MT940?
MT942 vs MT940 difference for CSV/Excel: MT942 is intraday and doesn’t always reconcile to a final balance. MT940 is end‑of‑day with opening and closing balances.

How do I keep all the remittance details?
Merge the full :86: and map subfields—EREF (end‑to‑end), KREF (customer ref), SVWZ (purpose). Keep a wide Description plus separate reference columns.

How do I avoid duplicates?
Build a CompositeId or HashId from stable fields (Account, ValueDate, Amount, EREF, BankReference). Deduplicate before import.

Can I convert many files at once?
Yes. Batch convert MT940 files and output a single file with an Account column. Validate control totals before import.

Quickest way to reconcile?
Use a control report: for each statement, Opening + Net Movement = Closing. If it fails, fix first, export second.

Key Points

  • MT940 isn’t built for spreadsheets. Convert tags into columns—especially :61: (dates, C/D, amount) and :86: (EREF, KREF, SVWZ)—and handle decimals, encoding, and value vs booking date.
  • Three options: bank CSV (fast but inconsistent), DIY scripts (flexible but maintenance‑heavy), or a dedicated converter. Many teams pick BankXLSX for accurate parsing, batch uploads, mapping, dedupe, and locale‑aware outputs.
  • Reliable process: upload, auto‑detect, preview, map to your ERP, export, then check Opening + Net = Closing and match :61: counts to rows. Use composite IDs for overlaps.
  • At scale: consolidate accounts with an Account column, schedule jobs, integrate via API/webhooks, and keep SSO/MFA, encryption, retention, and audit logs in place.

Conclusion and next steps

MT940 has rich data, just not in a format you want to work in. Convert to CSV or Excel with correct signs, locales, encoding, and keep both :61: and :86: intact. Add balance checks and deduplication, and you’ll trust every file you load.

You can export from your bank, code a parser, or use BankXLSX. If you want speed and accuracy with less hassle, upload an MT940 to BankXLSX, preview the results, map to your ERP template, and download a clean CSV/XLSX.

Ready to get a few hours back each close? Try a sample, confirm the preview looks right, and batch‑convert the backlog when you’re happy.