camt.053 bank statement XML to Excel
Banks across the SEPA area now hand out statements as ISO 20022 XML — camt.053 — instead of MT940 or national formats. Accounting software reads it, but people cannot: opened in a browser or imported into Excel it is a five-level tree, and the amounts have no sign. Here is what is in the file and how to get one clean row per booking.
Free to use. The statement is parsed in your browser and never uploaded. Windows version — 4.9 MB, no installation: details. Pro — $29 once: price.
What a camt.053 contains
Stmt— the statement of one account: its IBAN, balances and entries.Ntry— one booking on the account:Amt,CdtDbtInd(CRDTmoney in,DBITmoney out), booking date and value date, the bank's transaction code.NtryDtls/TxDtls— the details: who paid or was paid, their IBAN, the remittance text (RmtInf/Ustrd) and theEndToEndIdthe payer set.
Two things make a naive import useless. The amount is always positive — the direction lives in CdtDbtInd. And the counterparty depends on the direction: on a credit it is the debtor (Dbtr), on a debit it is the creditor (Cdtr). Excel's XML import gives you dozens of columns and a total that means nothing.
The table you get here
The file is recognised by its namespace (urn:iso:std:iso:20022:tech:xsd:camt.053…, any version, with or without a prefix) and built with a template:
| Column | Source |
|---|---|
| Account | the statement IBAN |
| Booking date, Value date | BookgDt, ValDt |
| Signed amount, Currency | Amt with the sign from CdtDbtInd: debits are negative |
| Counterparty, Counterparty IBAN | the debtor on credits, the creditor on debits |
| Remittance info, Structured reference, EndToEndId | RmtInf/Ustrd, RmtInf/Strd, Refs/EndToEndId |
| Transaction code, Bank reference, Status, Reversal | BkTxCd, AcctSvcrRef, Sts, RvslInd |
camt.052 (intraday report) and camt.054 (debit/credit notification) carry the same entries and open the same way.
One row per booking, not per transaction
A row is an Ntry because that is what reconciles with the balance: the sum of the signed amounts is the movement on the account. A payroll batch of forty salaries is one debit with forty details; in its row the names and texts of the details are joined with “ | ”, and the Transactions column tells you how many there are.
To split batches by invoice or employee, pick the table Transaction details (TxDtls) in File parsing: one row per transaction, with its own amount and EndToEndId plus the booking date and entry amount next to it. Bookings without details have no row there, which is why it is not the default.
When you need the raw XML
The template table is read-only. In File parsing, Template → Plain XML brings back the ordinary XML view: you pick the row element yourself, see every field and can edit. See XML to Excel for how nested data is flattened there.
From the template table, filter by date or counterparty and export to Excel or CSV — or compare it with your ledger directly, as in comparing two files.
FAQ
Which camt.053 versions are supported?
Recognition goes by namespace, so both the common 001.02 and newer ones (001.08 and later, where names sit in Pty/Nm and the status in Sts/Cd) work.
Are the statement balances checked?
No: the template builds the entries. Opening and closing balances (Bal) are visible in the plain XML view.
What about payment files (pain.001)?
They are recognised too: one row per credit transfer with amount, creditor, IBAN with its check digits verified, and a comparison of NbOfTxs and CtrlSum with the header. Direct-debit files (pain.008) work the same way.
Does the statement go to a server?
No. It is parsed in your browser and never leaves your computer.
Free to use. Your file is not uploaded to a server. Windows version — 4.9 MB, no installation: details. Pro — $29 once: price.