BooksReady

Convert an ING camt.053 file to Excel

In short: ING's camt.053 sample uses version camt.053.001.02, with the account by IBAN, several balance types, and ING's own transaction codes next to the ISO ones. The converter turns each booking into a spreadsheet row. Drop the file below; it is converted on your device and never uploaded.

Before you start: ING's published camt.053 sample holds only 1 of the 9 bookings its own summary counts, so it does not add up and the converter shows its balance warning on the sample below. A complete statement should add up.

Convert ING camt.053

camt.053 to Excel (CSV)

An .xml file from your bank's camt.053 (ISO 20022) 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.

What the ING camt.053 file looks like

  • ING's sample declares camt.053.001.02 and names the account by IBAN with its currency (Acct/Ccy EUR).
  • Each statement lists four balances: PRCD (previous closing), OPBD (opening), CLBD (closing) and CLAV (closing available).
  • A booking carries the ISO transaction code (for example PMNT, IDDT, ESDD) and an ING code with issuer "INGGroup" (for example 01010).
  • A batch booking can come with only the number of transactions (NbOfTxs 2) and no details of each payment.

What the converter does with it

  • Uses OPBD as the opening balance and CLBD as the closing balance, and checks that the bookings add up.
  • Writes a batch booking without payment details as one row, "Batch of 2 transactions".
  • Reads the other party's name and IBAN from RltdPties and the description from RmtInf when ING includes them.

Before and after

Before: the camt.053 statement as downloaded

<?xml version="1.0" encoding="utf-8"?>
<Document
xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<BkToCstmrStmt>
<GrpHdr>
<MsgId>201401030009999_20140104015504378</MsgId>
<CreDtTm>2014-01-04T01:55:04.378+01:00</CreDtTm>
<MsgRcpt>

After: what Excel, Google Sheets or LibreOffice receives

After: camt053-2014-01-03-to-2014-01-03-spreadsheet.csv
DateValue dateAmountCurrencyDebit/CreditCounterpartyCounterparty accountDescriptionReferenceBalance
2014-01-032014-01-03270.00EURCreditBatch of 2 transactions31027666869991270.00

Sample: the statement part of ING's own published camt.053 sample file, copied from the PDF. The "after" output was produced from it by the same converter this page runs.

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 camt.053 statement carries an opening and a closing booked balance. The converter adds up the booked entries 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.
What about pending entries and batch bookings?
Only booked entries are converted; pending ones are left out with a note, because they are not part of the booked balance yet. A batch booking, such as one line for several direct debits, is split into its payments when they add up exactly to the booked amount, and kept as one line otherwise.
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.

Sources

BooksReady is not affiliated with, endorsed by or sponsored by ING. The layout notes above come from the sources listed.

More