Convert CAMT.053 files to Excel

What is a CAMT.053 file?

CAMT.053 is the ISO 20022 Bank-to-Customer Statement: the end-of-day account statement as an XML file. It carries the same information as a printed statement — account, period, opening and closing balance, every booked entry — but in tagged fields a computer can read without guessing.

Banks offer it mainly to business customers, through business online banking, EBICS or host-to-host connections, and it is common wherever SEPA payments are used. It is the ISO 20022 successor of the SWIFT MT940 statement. The exact version is in the file's namespace, for example camt.053.001.02 (widely used for SEPA) or a later version such as camt.053.001.08. The message definitions are published in the ISO 20022 catalogue.

The camt.053 file format: the parts that matter

A camt.053 file is long and deeply nested, but for bookkeeping only a handful of elements matter. All of them sit inside Document → BkToCstmrStmt:

Amounts in camt.053 are always positive. Whether money went in or out is in CdtDbtInd, and a reversal indicator (RvslInd) can flip it. Summing the Amt column without that logic gives a meaningless total.

A camt.053 example, element by element

Here is one direct debit as it appears in a camt.053 file, and where each value ends up in the Excel file:

How to open a camt.053 file in Excel

A browser or text editor shows a camt.053 file as raw XML. Excel for Windows can load it through Data → Get Data → From File → From XML, but Power Query returns nested tables: amounts without signs, CRDT and DBIT in a separate column, names and purpose texts in child tables you have to expand and join. That works for a one-off look; it is slow to repeat every month and easy to get wrong.

Converting the file gives you one flat table instead. When we read a camt.053 file:

• Debits become negative and credits positive, with reversals taken into account
• The booking date is used as the date (the value date when there is no booking date)
• The description combines the other party — the creditor for money out, the debtor for money in — with the purpose text
• Entries that are not booked yet (pending or information-only) are left out, so the statement matches its booked balances
• Each entry gets a clean payee name and a category, the same as a PDF converted to Excel
• The opening balance plus all entries must equal the closing balance; if it doesn't, you see it before you export

camt.052 vs camt.053 vs camt.054

The three cash-management messages share the same building blocks (Acct, Bal, Ntry) but serve different jobs. All three can be uploaded:

For accounting and reconciliation, download camt.053. A camt.054 file usually has no opening or closing balance, so there is nothing to check its entries against.

CAMT.053 vs MT940: which should I download?

Both describe the same statement. CAMT.053 is richer: names, IBANs, end-to-end references and the purpose text each have their own field, while MT940 squeezes the same details into at most six lines of 65 characters in its :86: field, laid out differently from bank to bank. Swift's equivalence tables map MT940 to camt.053, and banks are moving customers to ISO 20022.

If your bank offers both, take camt.053. Use MT940 only when the software you feed it to reads nothing else. For converting to Excel it makes no difference: we read both, see MT940 to Excel.

Several accounts, daily files and whole years

One camt.053 file often holds several statements: one per account, or one per day. We group them by account and currency. Daily statements of the same account join into one statement, with the opening balance of the first day and the closing balance of the last, and a second account or currency becomes a separate statement.

Some banks deliver one XML file per day, often zipped. Unzip the archive and upload all the files at once, then export them together into one Excel workbook or CSV file, optionally limited to a date range — the usual request when accountants and bookkeepers close a month or a year.

Questions