Parsing a Bank Statement in XML and Reconciling the Balance

A bank sends you a statement as XML, and the job is to get the entries into a ledger or a spreadsheet. The format (the ISO 20022 camt family is the usual one) is verbose, namespaced and, above all, sign-agnostic: amounts are positive and a separate field says whether money came in or went out. Parse it naively and you add salary to groceries. This post parses a small statement, applies the sign, and then does what makes a financial import trustworthy: it proves the entries explain the change between the opening and closing balance.

The shape of a statement

The relevant parts, with the prefix-free default namespace the format uses:

<Document xmlns="urn:example:camt.053">
 <BkToCstmrStmt><Stmt>
  <Id>S-2026-10</Id>
  <Bal><Tp>OPBD</Tp><Amt Ccy="EUR">1000.00</Amt><CdtDbtInd>CRDT</CdtDbtInd></Bal>
  <Ntry><Amt Ccy="EUR">250.00</Amt><CdtDbtInd>CRDT</CdtDbtInd><BookgDt><Dt>2026-10-02</Dt></BookgDt><AddtlNtryInf>Salary</AddtlNtryInf></Ntry>
  <Ntry><Amt Ccy="EUR">80.40</Amt><CdtDbtInd>DBIT</CdtDbtInd><BookgDt><Dt>2026-10-03</Dt></BookgDt><AddtlNtryInf>Groceries</AddtlNtryInf></Ntry>
  <Ntry><Amt Ccy="EUR">19.99</Amt><CdtDbtInd>DBIT</CdtDbtInd><BookgDt><Dt>2026-10-05</Dt></BookgDt><AddtlNtryInf>Phone</AddtlNtryInf></Ntry>
  <Ntry><Amt Ccy="EUR">5.00</Amt><CdtDbtInd>CRDT</CdtDbtInd><BookgDt><Dt>2026-10-09</Dt></BookgDt><AddtlNtryInf>Refund</AddtlNtryInf></Ntry>
  <Bal><Tp>CLBD</Tp><Amt Ccy="EUR">1154.61</Amt><CdtDbtInd>CRDT</CdtDbtInd></Bal>
 </Stmt></BkToCstmrStmt>
</Document>

Three conventions to notice. All elements are in one namespace with no prefix, which makes the default-namespace trap easy to fall into: findtext("Amt") returns nothing, and the right query uses a mapped prefix. Balances are typed by a code (OPBD opening booked, CLBD closing booked), so you find them by type and not by position. And every amount is positive. The direction is in CdtDbtInd: CRDT is a credit to the account (money in) and DBIT is a debit (money out). A balance can also carry a debit indicator, meaning an overdrawn account.

Applying the sign

Write one function that turns an element with an Amt and an indicator into a signed Decimal, and use it everywhere:

from decimal import Decimal
from lxml import etree

ns = {"c": "urn:example:camt.053"}
r = etree.fromstring(xml)

def signed(el):
    a = Decimal(el.findtext("c:Amt", namespaces=ns))
    return a if el.findtext("c:CdtDbtInd", namespaces=ns) == "CRDT" else -a

bal = {b.findtext("c:Tp", namespaces=ns): signed(b) for b in r.iterfind(".//c:Bal", ns)}
entries = [(e.findtext("c:BookgDt/c:Dt", namespaces=ns), signed(e), e.findtext("c:AddtlNtryInf", namespaces=ns))
           for e in r.iterfind(".//c:Ntry", ns)]

Printing the entries:

2026-10-02    250.00 Salary
2026-10-03    -80.40 Groceries
2026-10-05    -19.99 Phone
2026-10-09      5.00 Refund

The groceries and the phone bill are negative, the salary and the refund positive. The function treats anything other than CRDT as a debit. A stricter version would accept only CRDT and DBIT and raise on anything else, because a typo in a bank file should stop an import, not silently flip a sign.

The reconciliation

A statement carries its own proof. The opening balance plus the sum of the entries must equal the closing balance:

tot = sum(e[1] for e in entries)
print("opening", bal["OPBD"], "entries", tot, "computed closing", bal["OPBD"] + tot, "stated closing", bal["CLBD"])
print("reconciles:", bal["OPBD"] + tot == bal["CLBD"])

opening 1000.00 entries 154.61 computed closing 1154.61 stated closing 1154.61
reconciles: True

1,000.00 plus 250.00 minus 80.40 minus 19.99 plus 5.00 equals 1,154.61, the stated closing balance. This is the check to run on every statement before it goes into a ledger. If it fails, the cause is one of a short list: a sign applied wrongly, a missing entry (the file is truncated or the statement is paginated and you have one page), a duplicate entry, entries from outside the statement period or an entry whose amount is in another currency. The size of the difference points to which. A difference equal to twice an entry means that entry has the wrong sign; equal to one entry means it is missing or duplicated.

Look at the alternative to see why the sign matters. Summing the amounts without the indicator:

abs-sum (ignoring sign): 355.39

355.39 is 250.00 + 80.40 + 19.99 + 5.00, a figure that is neither the net movement (154.61) nor anything a customer would recognize. A parser that read Amt and forgot CdtDbtInd would produce this and no error.

Why Decimal and not float

Money in binary floating point cannot represent most cent values exactly. Adding a long list of amounts like 19.99 and 80.40 accumulates tiny errors, and a reconciliation that compares floats with == fails on a correct statement, so the usual patch becomes a tolerance, which also hides real discrepancies of a cent. Use Decimal from the string, as above, and the comparison is exact. In a database, use NUMERIC(18,2) or the currency's minor unit as an integer.

Details that matter on real statements

Currency. Each Amt has a Ccy attribute. A statement for a multi-currency account can mix them. Group by currency before reconciling, and never add across currencies.

Dates. An entry has a booking date and a value date, which differ by a day or more. Use the one your ledger needs, and keep both.

Reversals. Some entries carry a reversal indicator, meaning the entry cancels an earlier one. Its effect on the sign depends on the vocabulary version. Read the specification for your bank's version, and test with a statement that has one.

Batches. One entry can contain many transaction details (a salary run, a bulk payment). The entry amount is the total, and the detail lines are the parts. Import at the level you need, and check that the parts sum to the whole.

Pagination. Large statements arrive in several messages. Each page has its own balances only if the bank sends them; check the sequence numbers in the group header.

Duplicates across files. The same entry appears in the end-of-day and the intraday report. Deduplicate by the bank's entry reference, not by date and amount.

Importing into a ledger

Once the statement reconciles, the import is a mapping problem. Each entry becomes a transaction row with the booking date, the signed amount, the currency, the bank's own entry reference and the free-text information. Store the reference and make it unique together with the account: re-importing the same statement then fails on the constraint instead of doubling every transaction, which is the most common import accident.

Keep three timestamps apart. The booking date says when the bank recorded the entry, the value date says when the money counts for interest, and the import time says when you loaded it. Reports by month use the booking date, cash forecasts use the value date, and audits use the import time. Collapsing them loses the ability to answer why a month's total changed after the fact.

Finally, treat categorization as a separate step. The free-text field is where "Groceries" lives, and in real statements it holds merchant names and codes, not clean categories. Rules or a lookup table assign categories to rows after the import, and they can be rerun when the rules change. Mixing them into the parser makes a rule change a reparse, and it makes the raw statement harder to trace.

Related reading

This post belongs to XML problems worked from real data. For the namespace details, see selecting elements with a namespace in XPath, and for the general financial XML case reading financial and government XML datasets. The message format is published by ISO 20022, and Python's decimal module is the right tool for the arithmetic. Format a statement for reading in the XML formatter.

When parsing the statement is not enough for reconciliation

The ledger and the statement use different cut-offs. Entries booked after midnight appear on the next day's statement. Reconcile over a period that both sides cover completely.

Names and references are free text. Matching an entry to an invoice from a payment reference works when customers write it correctly and often does not. Plan for a manual queue.

There are several accounts. Reconcile per account and currency, then roll up.

You must keep the original. For audit, store the statement file unchanged beside the parsed rows (storing raw XML in a lake and parsing it later).