askrobots / dbbasic-object-server
Public
Runs live, versioned Python application objects on one VM, with a suite of apps: projects, notes, tasks, files and more.
danq9
signup: the person chooses the password and name on the confirm page
83c7aa6
2 weeks, 5 days ago
History
object_ofx.py · 16.9 KB · 415 lines
| 1 | """OFX/QFX statement parsing (plan/bank-import-reconciliation-spec.md section 3, |
| 2 | "v1.5: action_import_ofx"). |
| 3 | |
| 4 | OFX is the near-universal US bank/card download format, and it comes in two |
| 5 | dialects that share a name and nothing else structurally: |
| 6 | |
| 7 | - **OFX 1.x** is SGML-ish, not XML: a `KEY:VALUE` header block, a blank |
| 8 | line, then tags that are routinely left UNCLOSED |
| 9 | (`<DTPOSTED>20260702120000[-5:EST]` with no `</DTPOSTED>` -- the closing |
| 10 | tag is implied by the next tag starting). Feeding this to an XML parser |
| 11 | fails outright, so the SGML branch below is a small regex-based reader, |
| 12 | not a workaround for a parser bug: aggregates (STMTTRN, LEDGERBAL, ...) |
| 13 | ARE always closed in practice, it is only leaf value tags that are not, |
| 14 | so a leaf's value is simply "everything up to the next '<' or newline". |
| 15 | - **OFX 2.x** is real XML (`<?xml ...?>`, closed tags), so stdlib's |
| 16 | xml.etree can read it properly -- entity-unescaping included -- but |
| 17 | "can parse a file a user uploaded" means refusing DOCTYPE/ENTITY |
| 18 | declarations first. A statement upload is exactly the untrusted-input |
| 19 | shape XXE and billion-laughs attacks target, and there is no legitimate |
| 20 | reason an OFX statement needs a custom entity, so both dialects are |
| 21 | refused outright if either declaration appears -- before either parser |
| 22 | ever sees the text. |
| 23 | |
| 24 | Both dialects land in the SAME canonical shape object_banking.py already |
| 25 | uses for CSV imports (posted_on/amount_cents/description/external_id/raw), |
| 26 | because the importer that consumes parsed lines must not care which dialect |
| 27 | or which bank produced them -- one canonical landing zone, thin parsers |
| 28 | (the doctrine plan/bank-import-reconciliation-spec.md section 3 names |
| 29 | directly: "don't build four parsers first"). |
| 30 | |
| 31 | FITID (the bank's own transaction id) becomes external_id, which is the |
| 32 | strong half of the dedup story in object_banking.py: OFX statements don't |
| 33 | need the line_hash ordinal fallback CSV imports rely on, because the bank |
| 34 | already gave every line a stable identity. |
| 35 | """ |
| 36 | |
| 37 | from __future__ import annotations |
| 38 | |
| 39 | import re |
| 40 | import xml.etree.ElementTree as ET |
| 41 | from datetime import date |
| 42 | from decimal import Decimal, InvalidOperation |
| 43 | from typing import Any |
| 44 | |
| 45 | |
| 46 | class OfxError(ValueError): |
| 47 | """Unparseable OFX/QFX input -- refuse rather than half-read a statement. |
| 48 | |
| 49 | A statement is evidence (object_banking.py's module docstring); a |
| 50 | parser that silently dropped the transactions it could not understand |
| 51 | would corrupt that evidence without telling anyone. |
| 52 | """ |
| 53 | |
| 54 | |
| 55 | # --- sniffing --------------------------------------------------------------- |
| 56 | |
| 57 | _SNIFF_RE = re.compile(r"OFXHEADER|<OFX[ >]", re.IGNORECASE) |
| 58 | |
| 59 | |
| 60 | def is_ofx(content: str) -> bool: |
| 61 | """Cheap sniff: does this look like an OFX/QFX file at all. |
| 62 | |
| 63 | Deliberately loose (a header token or an <OFX> tag anywhere in the |
| 64 | first slice) -- this gates which parser a caller tries, not whether the |
| 65 | file is well-formed. parse_ofx() does the real, strict validation and |
| 66 | raises OfxError on anything that does not actually check out. |
| 67 | """ |
| 68 | if not content: |
| 69 | return False |
| 70 | head = content[:2048] |
| 71 | return bool(_SNIFF_RE.search(head)) |
| 72 | |
| 73 | |
| 74 | # --- shared value parsing ---------------------------------------------------- |
| 75 | |
| 76 | def parse_ofx_amount(value: Any) -> int: |
| 77 | """OFX TRNAMT/BALAMT to signed integer cents. |
| 78 | |
| 79 | These are already signed in the bank's own convention (money into the |
| 80 | account positive, out negative) -- unlike object_banking.parse_cents() |
| 81 | there is no sign-flip or parenthesised-negative convention to apply, |
| 82 | only whitespace and an occasional leading "+". Decimal throughout, |
| 83 | never float (00-doctrine-and-contract). |
| 84 | """ |
| 85 | text = str(value or "").strip() |
| 86 | if not text: |
| 87 | raise OfxError("Missing amount") |
| 88 | if text.startswith("+"): |
| 89 | text = text[1:] |
| 90 | try: |
| 91 | amount = Decimal(text) |
| 92 | except InvalidOperation as exc: |
| 93 | raise OfxError(f"Unparseable OFX amount: {value!r}") from exc |
| 94 | return int((amount * 100).to_integral_value()) |
| 95 | |
| 96 | |
| 97 | _DATE_RE = re.compile(r"^\s*(\d{4})(\d{2})(\d{2})") |
| 98 | |
| 99 | |
| 100 | def parse_ofx_date(value: Any) -> str: |
| 101 | """OFX date (DTPOSTED/DTSTART/DTEND/DTASOF) to ISO calendar date. |
| 102 | |
| 103 | Format is YYYYMMDD[HHMMSS[.XXX]][[+-]TZ[:NAME]] -- e.g. |
| 104 | "20260702120000[-5:EST]" or a bare "20260702". We take the calendar |
| 105 | date the bank stated and stop there: the timezone bracket describes |
| 106 | what clock the bank used to stamp the transaction, not a correction to |
| 107 | apply. Shifting the date across a TZ boundary would move a transaction |
| 108 | to a different calendar day than the one printed on the statement, |
| 109 | which breaks reconciliation against that same statement's own totals |
| 110 | -- the posting date is the fact, not the timestamp. |
| 111 | """ |
| 112 | text = str(value or "").strip() |
| 113 | match = _DATE_RE.match(text) |
| 114 | if not match: |
| 115 | raise OfxError(f"Unparseable OFX date: {value!r}") |
| 116 | year, month, day = (int(g) for g in match.groups()) |
| 117 | try: |
| 118 | return date(year, month, day).isoformat() |
| 119 | except ValueError as exc: |
| 120 | raise OfxError(f"Unparseable OFX date: {value!r}") from exc |
| 121 | |
| 122 | |
| 123 | def _combine_name_memo(name: str, memo: str) -> str: |
| 124 | """NAME and MEMO joined per the import shape: both when present and |
| 125 | different, otherwise whichever one exists, collapsed to single-spaced |
| 126 | text. Neither present is tolerated -- an empty description, not an |
| 127 | error, since some FITID-only lines genuinely carry no text.""" |
| 128 | name = " ".join(str(name or "").split()) |
| 129 | memo = " ".join(str(memo or "").split()) |
| 130 | if name and memo: |
| 131 | return name if name == memo else f"{name} — {memo}" |
| 132 | return name or memo |
| 133 | |
| 134 | |
| 135 | # --- input hygiene ------------------------------------------------------------ |
| 136 | |
| 137 | def _strip_bom(content: str) -> str: |
| 138 | return content.lstrip("") |
| 139 | |
| 140 | |
| 141 | def _normalise_newlines(content: str) -> str: |
| 142 | return content.replace("\r\n", "\n").replace("\r", "\n") |
| 143 | |
| 144 | |
| 145 | def _reject_dangerous_declarations(content: str) -> None: |
| 146 | """Refuse DOCTYPE/ENTITY declarations before either parser sees them. |
| 147 | |
| 148 | This is the XXE/billion-laughs gate: stdlib's xml.etree does not |
| 149 | resolve *external* entities by default, but a DTD can still define |
| 150 | internal entities that expand recursively, and a DOCTYPE has no |
| 151 | legitimate role in a bank statement -- so it is rejected on sight |
| 152 | rather than trying to parse a "safe subset" of DTD syntax. Applied |
| 153 | uniformly to both dialects: the SGML branch never touches an XML |
| 154 | parser at all, but a hostile DOCTYPE has no business in a bank |
| 155 | statement of either dialect, so it is refused just as hard. |
| 156 | """ |
| 157 | if re.search(r"<!DOCTYPE", content, re.IGNORECASE) or \ |
| 158 | re.search(r"<!ENTITY", content, re.IGNORECASE): |
| 159 | raise OfxError("OFX file declares a DOCTYPE/ENTITY -- refused") |
| 160 | |
| 161 | |
| 162 | def _looks_like_xml(content: str) -> bool: |
| 163 | return content.lstrip().lower().startswith("<?xml") |
| 164 | |
| 165 | |
| 166 | # --- OFX 1.x (SGML) ----------------------------------------------------------- |
| 167 | |
| 168 | def _aggregate(text: str, tag: str) -> str | None: |
| 169 | """Inner text of the first <TAG>...</TAG> aggregate, or None. |
| 170 | |
| 171 | Aggregates (as opposed to leaf value tags) are always explicitly |
| 172 | closed in OFX 1.x -- this is what makes a regex-based reader viable |
| 173 | without a real SGML parser. |
| 174 | """ |
| 175 | m = re.search(rf"<{re.escape(tag)}>(.*?)</{re.escape(tag)}>", text, |
| 176 | re.IGNORECASE | re.DOTALL) |
| 177 | return m.group(1) if m else None |
| 178 | |
| 179 | |
| 180 | def _iter_aggregate(text: str, tag: str): |
| 181 | """Yield (full_text, inner_text) for every <TAG>...</TAG> aggregate, |
| 182 | in document order -- full_text is the verbatim slice for `raw`.""" |
| 183 | for m in re.finditer(rf"<{re.escape(tag)}>(.*?)</{re.escape(tag)}>", text, |
| 184 | re.IGNORECASE | re.DOTALL): |
| 185 | yield m.group(0), m.group(1) |
| 186 | |
| 187 | |
| 188 | def _leaf(text: str, tag: str) -> str: |
| 189 | """Value of a leaf tag that may or may not be closed: everything from |
| 190 | just after <TAG> up to the next '<' (a following tag, closing or not) |
| 191 | or end of line. This one regex is what lets the same reader handle |
| 192 | both `<DTPOSTED>20260702120000[-5:EST]` (unclosed) and |
| 193 | `<DTPOSTED>20260702</DTPOSTED>` (closed) identically -- the closing |
| 194 | tag also starts with '<', so it stops the match on its own.""" |
| 195 | m = re.search(rf"<{re.escape(tag)}>\s*([^<\r\n]*)", text, re.IGNORECASE) |
| 196 | return m.group(1).strip() if m else "" |
| 197 | |
| 198 | |
| 199 | def _detect_statement_kind(text: str) -> tuple[str, str, str]: |
| 200 | """(acct_tag, default_account_type, is_cc) worth of routing between a |
| 201 | bank statement (STMTRS/BANKACCTFROM) and a credit-card statement |
| 202 | (CCSTMTRS/CCACCTFROM) -- same transaction/balance shape, different |
| 203 | wrapper and account-identification tags.""" |
| 204 | if re.search(r"<CCSTMTRS>", text, re.IGNORECASE): |
| 205 | return "CCACCTFROM", "CREDITCARD" |
| 206 | if re.search(r"<STMTRS>", text, re.IGNORECASE): |
| 207 | return "BANKACCTFROM", "" |
| 208 | raise OfxError("No STMTRS/CCSTMTRS statement block found") |
| 209 | |
| 210 | |
| 211 | def _transaction_from_leaves(full_text: str, inner: str) -> dict: |
| 212 | dtposted = _leaf(inner, "DTPOSTED") |
| 213 | if not dtposted: |
| 214 | raise OfxError("STMTTRN missing DTPOSTED") |
| 215 | return { |
| 216 | "posted_on": parse_ofx_date(dtposted), |
| 217 | "amount_cents": parse_ofx_amount(_leaf(inner, "TRNAMT")), |
| 218 | "description": _combine_name_memo(_leaf(inner, "NAME"), _leaf(inner, "MEMO")), |
| 219 | "external_id": _leaf(inner, "FITID"), |
| 220 | "raw": full_text.strip(), |
| 221 | } |
| 222 | |
| 223 | |
| 224 | def _parse_sgml(text: str) -> dict: |
| 225 | if not re.search(r"<OFX>", text, re.IGNORECASE): |
| 226 | raise OfxError("Not a valid OFX 1.x file: no <OFX> aggregate found") |
| 227 | acct_tag, default_type = _detect_statement_kind(text) |
| 228 | |
| 229 | acct_inner = _aggregate(text, acct_tag) or "" |
| 230 | tranlist_inner = _aggregate(text, "BANKTRANLIST") or "" |
| 231 | ledger_inner = _aggregate(text, "LEDGERBAL") |
| 232 | |
| 233 | period_start = _leaf(tranlist_inner, "DTSTART") |
| 234 | period_end = _leaf(tranlist_inner, "DTEND") |
| 235 | |
| 236 | closing_balance_cents = None |
| 237 | as_of = "" |
| 238 | if ledger_inner is not None: |
| 239 | balamt = _leaf(ledger_inner, "BALAMT") |
| 240 | if balamt: |
| 241 | closing_balance_cents = parse_ofx_amount(balamt) |
| 242 | dtasof = _leaf(ledger_inner, "DTASOF") |
| 243 | if dtasof: |
| 244 | as_of = parse_ofx_date(dtasof) |
| 245 | |
| 246 | lines = [_transaction_from_leaves(full, inner) |
| 247 | for full, inner in _iter_aggregate(text, "STMTTRN")] |
| 248 | |
| 249 | return { |
| 250 | "bank_id": _leaf(acct_inner, "BANKID"), |
| 251 | "account_id": _leaf(acct_inner, "ACCTID"), |
| 252 | "account_type": _leaf(acct_inner, "ACCTTYPE") or default_type, |
| 253 | "currency": _leaf(text, "CURDEF") or "USD", |
| 254 | "period_start": parse_ofx_date(period_start) if period_start else "", |
| 255 | "period_end": parse_ofx_date(period_end) if period_end else "", |
| 256 | "closing_balance_cents": closing_balance_cents, |
| 257 | "opening_balance_cents": None, |
| 258 | "as_of": as_of, |
| 259 | "lines": lines, |
| 260 | } |
| 261 | |
| 262 | |
| 263 | # --- OFX 2.x (XML) -------------------------------------------------------- |
| 264 | |
| 265 | def _findall_ci(elem: ET.Element | None, tag: str) -> list[ET.Element]: |
| 266 | """Elements (elem itself included) matching `tag` case-insensitively -- |
| 267 | real OFX 2.x is almost always upper-cased per the standard, but some |
| 268 | exporters emit it lower-cased throughout, and XML tag matching is |
| 269 | otherwise case-sensitive.""" |
| 270 | if elem is None: |
| 271 | return [] |
| 272 | tag = tag.lower() |
| 273 | return [e for e in elem.iter() if e.tag.lower() == tag] |
| 274 | |
| 275 | |
| 276 | def _find_ci(elem: ET.Element | None, tag: str) -> ET.Element | None: |
| 277 | matches = _findall_ci(elem, tag) |
| 278 | return matches[0] if matches else None |
| 279 | |
| 280 | |
| 281 | def _text_ci(elem: ET.Element | None, tag: str) -> str: |
| 282 | found = _find_ci(elem, tag) |
| 283 | if found is None or found.text is None: |
| 284 | return "" |
| 285 | return found.text.strip() |
| 286 | |
| 287 | |
| 288 | def _transaction_from_xml(elem: ET.Element, raw: str) -> dict: |
| 289 | dtposted = _text_ci(elem, "DTPOSTED") |
| 290 | if not dtposted: |
| 291 | raise OfxError("STMTTRN missing DTPOSTED") |
| 292 | return { |
| 293 | "posted_on": parse_ofx_date(dtposted), |
| 294 | "amount_cents": parse_ofx_amount(_text_ci(elem, "TRNAMT")), |
| 295 | "description": _combine_name_memo(_text_ci(elem, "NAME"), _text_ci(elem, "MEMO")), |
| 296 | "external_id": _text_ci(elem, "FITID"), |
| 297 | "raw": raw.strip(), |
| 298 | } |
| 299 | |
| 300 | |
| 301 | def _parse_xml(text: str) -> dict: |
| 302 | try: |
| 303 | root = ET.fromstring(text) |
| 304 | except ET.ParseError as exc: |
| 305 | raise OfxError(f"Malformed OFX XML: {exc}") from exc |
| 306 | if root.tag.lower() != "ofx": |
| 307 | raise OfxError("Not a valid OFX 2.x file: root element is not <OFX>") |
| 308 | |
| 309 | ccstmtrs = _find_ci(root, "CCSTMTRS") |
| 310 | stmtrs = ccstmtrs if ccstmtrs is not None else _find_ci(root, "STMTRS") |
| 311 | if stmtrs is None: |
| 312 | raise OfxError("No STMTRS/CCSTMTRS statement block found") |
| 313 | is_cc = ccstmtrs is not None |
| 314 | acct_elem = _find_ci(stmtrs, "CCACCTFROM" if is_cc else "BANKACCTFROM") |
| 315 | |
| 316 | tranlist = _find_ci(stmtrs, "BANKTRANLIST") |
| 317 | period_start = _text_ci(tranlist, "DTSTART") if tranlist is not None else "" |
| 318 | period_end = _text_ci(tranlist, "DTEND") if tranlist is not None else "" |
| 319 | |
| 320 | ledger = _find_ci(stmtrs, "LEDGERBAL") |
| 321 | closing_balance_cents = None |
| 322 | as_of = "" |
| 323 | if ledger is not None: |
| 324 | balamt = _text_ci(ledger, "BALAMT") |
| 325 | if balamt: |
| 326 | closing_balance_cents = parse_ofx_amount(balamt) |
| 327 | dtasof = _text_ci(ledger, "DTASOF") |
| 328 | if dtasof: |
| 329 | as_of = parse_ofx_date(dtasof) |
| 330 | |
| 331 | txn_elems = _findall_ci(tranlist if tranlist is not None else stmtrs, "STMTTRN") |
| 332 | # Pull the verbatim source slice for `raw` from the original text (not |
| 333 | # ET.tostring, which re-serialises and would not be byte-identical to |
| 334 | # what the bank sent) -- the same evidence posture object_banking.py's |
| 335 | # CSV parser uses. Falls back to a re-serialisation only if the counts |
| 336 | # somehow disagree, which should not happen for well-formed input. |
| 337 | raw_texts = [m.group(0) for m in |
| 338 | re.finditer(r"<STMTTRN>.*?</STMTTRN>", text, re.IGNORECASE | re.DOTALL)] |
| 339 | if len(raw_texts) != len(txn_elems): |
| 340 | raw_texts = [ET.tostring(e, encoding="unicode") for e in txn_elems] |
| 341 | |
| 342 | lines = [_transaction_from_xml(elem, raw) for elem, raw in zip(txn_elems, raw_texts)] |
| 343 | |
| 344 | return { |
| 345 | "bank_id": _text_ci(acct_elem, "BANKID") if acct_elem is not None else "", |
| 346 | "account_id": _text_ci(acct_elem, "ACCTID") if acct_elem is not None else "", |
| 347 | "account_type": (_text_ci(acct_elem, "ACCTTYPE") if acct_elem is not None else "") |
| 348 | or ("CREDITCARD" if is_cc else ""), |
| 349 | "currency": _text_ci(stmtrs, "CURDEF") or _text_ci(root, "CURDEF") or "USD", |
| 350 | "period_start": parse_ofx_date(period_start) if period_start else "", |
| 351 | "period_end": parse_ofx_date(period_end) if period_end else "", |
| 352 | "closing_balance_cents": closing_balance_cents, |
| 353 | "opening_balance_cents": None, |
| 354 | "as_of": as_of, |
| 355 | "lines": lines, |
| 356 | } |
| 357 | |
| 358 | |
| 359 | # --- entry point ------------------------------------------------------------ |
| 360 | |
| 361 | def _build_result(fields: dict) -> dict: |
| 362 | """Common shape-assembly for both dialects, including the period |
| 363 | fallback: DTSTART/DTEND from BANKTRANLIST when the bank supplied them, |
| 364 | else the min/max of the transaction dates actually present -- a |
| 365 | statement with a transaction list but no explicit period markers still |
| 366 | gets an honest period rather than an empty one.""" |
| 367 | lines = fields["lines"] |
| 368 | period_start = fields["period_start"] |
| 369 | period_end = fields["period_end"] |
| 370 | if not period_start or not period_end: |
| 371 | dates = sorted(l["posted_on"] for l in lines if l.get("posted_on")) |
| 372 | if dates: |
| 373 | period_start = period_start or dates[0] |
| 374 | period_end = period_end or dates[-1] |
| 375 | return { |
| 376 | "lines": lines, |
| 377 | "account": { |
| 378 | "account_id": fields["account_id"], |
| 379 | "bank_id": fields["bank_id"], |
| 380 | "account_type": fields["account_type"], |
| 381 | }, |
| 382 | "balances": { |
| 383 | "opening_balance_cents": fields["opening_balance_cents"], |
| 384 | "closing_balance_cents": fields["closing_balance_cents"], |
| 385 | "as_of": fields["as_of"], |
| 386 | }, |
| 387 | "period": {"start": period_start, "end": period_end}, |
| 388 | "currency": fields["currency"], |
| 389 | } |
| 390 | |
| 391 | |
| 392 | def parse_ofx(content: str) -> dict: |
| 393 | """Parse an OFX/QFX statement (either dialect) into the canonical |
| 394 | import shape object_banking.py's importer path consumes. |
| 395 | |
| 396 | Returns {"lines": [...], "account": {...}, "balances": {...}, |
| 397 | "period": {...}, "currency": str}. Raises OfxError for anything that |
| 398 | does not check out -- a statement is evidence (object_banking.py's |
| 399 | module docstring), and evidence half-read is worse than evidence |
| 400 | refused. |
| 401 | |
| 402 | Dialect is decided by a light prefix check (`<?xml` means OFX 2.x, |
| 403 | real XML; anything else is tried as OFX 1.x SGML) -- not by a |
| 404 | declared version number, because both header styles exist in the |
| 405 | wild independent of the OFX version a file claims. |
| 406 | """ |
| 407 | if not isinstance(content, str) or not content.strip(): |
| 408 | raise OfxError("Empty OFX content") |
| 409 | text = _normalise_newlines(_strip_bom(content)) |
| 410 | _reject_dangerous_declarations(text) |
| 411 | if _looks_like_xml(text): |
| 412 | fields = _parse_xml(text) |
| 413 | else: |
| 414 | fields = _parse_sgml(text) |
| 415 | return _build_result(fields) |