spb/air Public MIT
AIR — The Language of Accounting.
Python 100%
1# =============================================================================2# Projet : AIR — Accounting Intermediate Representation3# Auteur : Simon-Pierre Boucher4# Contact : contact@spboucher.ai5# Fichier : bank_formats.py6# Description : Bank statement parsers — camt.053, MT940, CSV -> BankTransaction.7# =============================================================================8"""Bank statement parsers (specs: docs/research/bank-statement-formats.md,9verified 2026-08-05 — ISO 20022 camt.053 is the strategic standard, MT940 is10deprecated by SWIFT but still the most deployed corporate format; AIR parses11both into one model).1213Field semantics implemented from the research:14- camt.053: Ntry/Amt carries NO sign and a Ccy attribute; direction comes15 exclusively from CdtDbtInd (CRDT = money in, DBIT = money out); date from16 BookgDt/Dt (ValDt/Dt fallback); reference from NtryDtls/TxDtls/Refs/17 EndToEndId, else NtryRef; description from AddtlNtryInf, else RmtInf/Ustrd.18- MT940 :61: line — value date YYMMDD, optional entry date MMDD, D/C mark19 (D, C, RD = reversal of debit -> money in, RC = reversal of credit -> money20 out), optional funds code, UNSIGNED amount with COMMA decimal separator,21 1!a3!c transaction type, customer reference (16x, often NONREF), optional22 //bank reference. Currency comes from :60F:/:62F:, never from :61:.23 Integrity: opening balance +/- sum of lines must equal closing balance.2425Amounts are Decimal from string, never floats.26"""27from __future__ import annotations2829import csv30import re31import xml.etree.ElementTree as ET32from datetime import date, datetime33from decimal import Decimal, InvalidOperation34from pathlib import Path3536from kernel.reconcile import BankTransaction373839class BankFormatError(ValueError):40 pass414243# --- camt.053 (ISO 20022 BankToCustomerStatement) --------------------------------44def _local(tag: str) -> str:45 """Strip the XML namespace: '{urn:...}Ntry' -> 'Ntry'."""46 return tag.rsplit("}", 1)[-1]474849def _find(element: ET.Element, *path: str) -> ET.Element | None:50 """Namespace-agnostic descent (camt versions differ only in namespace)."""51 current: ET.Element | None = element52 for name in path:53 if current is None:54 return None55 current = next((c for c in current if _local(c.tag) == name), None)56 return current575859def _text(element: ET.Element | None) -> str:60 return (element.text or "").strip() if element is not None else ""616263def parse_camt053(path: Path) -> list[BankTransaction]:64 root = ET.parse(path).getroot()65 statement = _find(root, "BkToCstmrStmt", "Stmt")66 if statement is None:67 raise BankFormatError(f"{path}: no BkToCstmrStmt/Stmt — not a camt.053 file")6869 transactions: list[BankTransaction] = []70 for entry in statement:71 if _local(entry.tag) != "Ntry":72 continue73 amt = _find(entry, "Amt")74 if amt is None:75 raise BankFormatError(f"{path}: Ntry without Amt")76 amount = Decimal(_text(amt)) # unsigned by spec77 currency = amt.get("Ccy", "")78 direction = _text(_find(entry, "CdtDbtInd"))79 if direction == "DBIT":80 amount = -amount81 elif direction != "CRDT":82 raise BankFormatError(f"{path}: CdtDbtInd must be CRDT|DBIT, got {direction!r}")83 when = _text(_find(entry, "BookgDt", "Dt")) or _text(_find(entry, "ValDt", "Dt"))84 if not when:85 raise BankFormatError(f"{path}: Ntry without BookgDt/ValDt date")86 reference = (87 _text(_find(entry, "NtryDtls", "TxDtls", "Refs", "EndToEndId"))88 or _text(_find(entry, "NtryRef"))89 )90 description = (91 _text(_find(entry, "AddtlNtryInf"))92 or _text(_find(entry, "NtryDtls", "TxDtls", "RmtInf", "Ustrd"))93 )94 transactions.append(BankTransaction(95 date=date.fromisoformat(when[:10]),96 amount=amount, currency=currency,97 description=description, reference=reference,98 ))99 return transactions100101102# --- MT940 (SWIFT customer statement) -----------------------------------------------103# :61: value-date(6) [entry-date(4)] D/C-mark [funds-code] amount type(4) refs104_LINE_61 = re.compile(105 r"^:61:(?P<valdate>\d{6})(?P<entrydate>\d{4})?(?P<mark>RC|RD|C|D)"106 r"(?P<funds>[A-Z])?(?P<amount>\d{1,15},\d*)"107 r"(?P<type>[A-Z][A-Z0-9]{3})(?P<custref>[^/\n]{0,16})(?://(?P<bankref>.{0,16}))?"108)109_BALANCE = re.compile(110 r"^:6[02][FM]:(?P<mark>[CD])(?P<date>\d{6})(?P<ccy>[A-Z]{3})(?P<amount>\d{1,15},\d*)"111)112113114def _yy_to_date(yymmdd: str) -> date:115 year = int(yymmdd[:2])116 year += 1900 if year >= 70 else 2000 # SWIFT 2-digit pivot117 return date(year, int(yymmdd[2:4]), int(yymmdd[4:6]))118119120def _comma_decimal(text: str) -> Decimal:121 try:122 return Decimal(text.replace(",", "."))123 except InvalidOperation as exc:124 raise BankFormatError(f"bad MT940 amount {text!r}") from exc125126127def parse_mt940(path: Path) -> list[BankTransaction]:128 lines = path.read_text(encoding="utf-8").splitlines()129 currency = ""130 opening: Decimal | None = None131 closing: Decimal | None = None132 transactions: list[BankTransaction] = []133134 i = 0135 while i < len(lines):136 line = lines[i].strip()137 if line.startswith((":60F:", ":60M:", ":62F:", ":62M:")):138 match = _BALANCE.match(line)139 if not match:140 raise BankFormatError(f"{path}: malformed balance line {line!r}")141 currency = match["ccy"]142 balance = _comma_decimal(match["amount"])143 if match["mark"] == "D":144 balance = -balance145 if line.startswith(":60"):146 opening = balance147 else:148 closing = balance149 elif line.startswith(":61:"):150 match = _LINE_61.match(line)151 if not match:152 raise BankFormatError(f"{path}: malformed :61: line {line!r}")153 amount = _comma_decimal(match["amount"])154 # D = money out; C = money in; RD reverses a debit (in); RC (out)155 if match["mark"] in ("D", "RC"):156 amount = -amount157 reference = match["custref"].strip()158 if reference.upper() == "NONREF":159 reference = match["bankref"] or ""160 description = ""161 if i + 1 < len(lines) and lines[i + 1].startswith(":86:"):162 description = lines[i + 1][4:].strip()163 i += 1164 transactions.append(BankTransaction(165 date=_yy_to_date(match["valdate"]),166 amount=amount, currency=currency,167 description=description, reference=reference,168 ))169 i += 1170171 # research §2.2: :60F: +/- sum(:61:) must equal :62F:172 if opening is not None and closing is not None:173 total = opening + sum(t.amount for t in transactions)174 if total != closing:175 raise BankFormatError(176 f"{path}: statement does not balance — opening {opening} + "177 f"movements = {total}, but closing balance is {closing}"178 )179 if not currency:180 raise BankFormatError(f"{path}: no :60F:/:62F: balance line — currency unknown")181 return transactions182183184# --- generic CSV ----------------------------------------------------------------------185def parse_bank_csv(path: Path) -> list[BankTransaction]:186 """Columns: date, amount (signed, money in positive), currency,187 description, reference."""188 transactions: list[BankTransaction] = []189 with path.open(encoding="utf-8") as f:190 for row in csv.DictReader(f):191 try:192 transactions.append(BankTransaction(193 date=datetime.strptime(row["date"].strip(), "%Y-%m-%d").date(),194 amount=Decimal(row["amount"].strip()),195 currency=row["currency"].strip(),196 description=(row.get("description") or "").strip(),197 reference=(row.get("reference") or "").strip(),198 ))199 except (KeyError, ValueError, InvalidOperation) as exc:200 raise BankFormatError(f"{path}: bad CSV row {row!r}: {exc}") from exc201 return transactions202203204# --- dispatcher --------------------------------------------------------------------------205def parse_statement(path: Path) -> list[BankTransaction]:206 """Detect the format by suffix, then by content sniffing."""207 suffix = path.suffix.lower()208 if suffix == ".xml":209 return parse_camt053(path)210 if suffix in (".mt940", ".sta", ".940"):211 return parse_mt940(path)212 if suffix == ".csv":213 return parse_bank_csv(path)214 head = path.read_text(encoding="utf-8", errors="replace")[:2000]215 if "<Document" in head or head.lstrip().startswith("<?xml"):216 return parse_camt053(path)217 if ":61:" in head:218 return parse_mt940(path)219 raise BankFormatError(220 f"{path}: unrecognized statement format (expected camt.053 XML, "221 "MT940, or CSV with date,amount,currency,description,reference)"222 )223