Convert CAMT.053 to Excel and CSV
Beta This tool is in beta. It is tested on sample files, and details may still change. Review the result before relying on it.
Convert a CAMT.053 bank statement to Excel or CSV for free. Klarbon reads the XML, checks that the balances add up and shows a preview first.
In short
Turn a bank statement file or a PDF statement into Excel, CSV, CAMT.053, MT940 or OFX. Klarbon reads a PDF statement into a draft; check the result before you use it. CSV and Excel free from bank files, otherwise 1 credit (€1.99).
Klarbon converts a CAMT.053 bank statement file into Excel (XLSX) or CSV, free on the site. It reads the ISO 20022 XML element by element, so every amount and date comes from a field rather than from text recognition, and it checks the booked opening balance plus the entries against the booked closing balance. Reading is in beta. A PDF statement dropped here is read too.
Compare the draft with the original before you download it.
Where do I find a CAMT.053 file?
Online banking lists it among the statement downloads as CAMT.053, camt, XML or ISO 20022. Business accounts nearly always have it; for private accounts it depends on the bank. Some banks send a separate file for each day, others one for the chosen period, and some pack the XML into a ZIP archive, which you unpack first. Step-by-step guides for three banks: ABN AMRO statement to Excel, Rabobank business statement to Excel and Wise statement to Excel.
How do I convert CAMT.053 to Excel?
- Save the XML file from your online banking; unpack it first if it came as a ZIP.
- Drop it on the box above. The preview lists each
Stmtin the file with its IBAN, period and balance check. - Look through the rows and the notes on batches, if any.
- Download Excel or CSV. Each statement in the file becomes its own file.
How is a CAMT.053 file built?
The root element BkToCstmrStmt holds one Stmt per account and period. Each statement carries:
Acct: the IBAN, the currency and usually the bank's BIC;Bal: balances with a type code,OPBDorPRCDfor the opening andCLBDfor the closing balance;Ntry: one entry per booking, withAmt,CdtDbtInd(CRDT or DBIT),Sts,BookgDtandValDt;NtryDtls/TxDtls: the payment behind the entry, with the parties (RltdPties), the remittance information (RmtInf) and references such as theEndToEndId;TxsSummry: optional counts and sums of credits and debits.
Which element becomes which column?
| In the CAMT.053 file | In the sheet |
|---|---|
BookgDt, else ValDt |
Booking date |
ValDt |
Value date |
Amt with CdtDbtInd |
Amount, negative for DBIT |
Cdtr or Dbtr, the ultimate party first |
Counterparty |
CdtrAcct or DbtrAcct IBAN |
Counterparty IBAN |
each Ustrd line, the structured reference, AddtlTxInf |
Description, one line each |
EndToEndId, else the creditor reference, else AcctSvcrRef |
Reference |
A reference that says NOTPROVIDED is skipped, so the column holds a real reference or stays empty.
Batch bookings and other special cases
- Batches. One entry can hold many payments, as with a direct debit run. When every payment states its own amount in the account's currency and they add up exactly to the entry, each becomes a row. Otherwise the entry stays one row with the total and all the payments' texts, and the preview says that this row lacks each payment's counterparty and reference. No amount is guessed.
- Control totals. When
TxsSummrycounts or sums do not match the entries, the file is refused. - Statements split over files. A statement marked as one page of several (
StmtPgntnwithLastPgIndfalse, or a page number other than 1) is refused, because on its own it is incomplete. Ask the bank for the whole period in one file.
What does it cost?
Nothing, for Excel and CSV: the XML is read within your daily allowance (10 files without an account, 30 with a free one) and both downloads are free. Writing MT940 or OFX from the same file is paid, one credit per started 500 transactions; see CAMT.053 to MT940. A download from a PDF statement costs one credit per started 10 pages, Excel and CSV included.
Related
Frequently asked questions
Which CAMT versions does Klarbon read?
Every published version of camt.053 from .001.02 to .001.14, recognised from the namespace in the file's first lines. Banks mostly issue .001.02, .001.04 or .001.08. Reading is in beta, so compare the first export with your online banking.
My bank sent a ZIP file. What now?
Unpack it and drop the XML file inside. Klarbon does not open ZIP archives. If the archive holds a separate file for each day, each is a statement of its own.
What is the difference between CAMT.052, CAMT.053 and CAMT.054?
CAMT.053 is the end-of-day statement with booked opening and closing balances. CAMT.052 is an intraday account report and CAMT.054 a notification of single debits or credits. Klarbon reads all three, but only CAMT.053 gives a complete statement for bookkeeping.
Why is a pending payment missing from my sheet?
Only entries with the status BOOK are bookings. Entries marked PDNG (pending), INFO or FUTR are left out of the rows, because they are not part of the booked balance.
Sources
- ISO 20022, message definitions (camt.053) iso20022.org
- Ecma International, ECMA-376 Office Open XML ecma-international.org
Related
- Open any e-invoice. Check it. Convert it.
- Klarbon help centre
- What is CAMT.053?
- Bank statement tools
- Supported bank statement files
- Why do my statement balances not match?
- Convert MT940 to Excel and CSV
- Convert CAMT.053 to MT940, or MT940 to CAMT.053
- Convert a CODA file to Excel and CSV
- Convert OFX to CSV and Excel
- Simple pricing for invoices and bank statements
- What happens to my files?
- E-invoice tools
- Understanding Bank CSV export
- Understanding Excel workbook (XLSX)
- Convert a CAMT.053, MT940 or OFX bank statement to Excel
- Convert a CAMT.053, MT940 or OFX bank statement to CSV
- Convert a bank statement PDF to CAMT.053
Also as an API: Bank statement API