Runs live, versioned Python application objects on one VM, with a suite of apps: projects, notes, tasks, files and more.

0 downloads

dbbasic-object-server/object_ofx.py
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/dbbasic-object-server.git
HTTPS
https://gitstable.com/git/askrobots/dbbasic-object-server.git
SSH
ssh://git@gitstable.com:2222/askrobots/dbbasic-object-server.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

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
4OFX is the near-universal US bank/card download format, and it comes in two
5dialects 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
24Both dialects land in the SAME canonical shape object_banking.py already
25uses for CSV imports (posted_on/amount_cents/description/external_id/raw),
26because the importer that consumes parsed lines must not care which dialect
27or which bank produced them -- one canonical landing zone, thin parsers
28(the doctrine plan/bank-import-reconciliation-spec.md section 3 names
29directly: "don't build four parsers first").
30
31FITID (the bank's own transaction id) becomes external_id, which is the
32strong half of the dedup story in object_banking.py: OFX statements don't
33need the line_hash ordinal fallback CSV imports rely on, because the bank
34already gave every line a stable identity.
35"""
36
37from __future__ import annotations
38
39import re
40import xml.etree.ElementTree as ET
41from datetime import date
42from decimal import Decimal, InvalidOperation
43from typing import Any
44
45
46class 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
60def 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
76def 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
100def 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
123def _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
137def _strip_bom(content: str) -> str:
138 return content.lstrip("")
139
140
141def _normalise_newlines(content: str) -> str:
142 return content.replace("\r\n", "\n").replace("\r", "\n")
143
144
145def _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
162def _looks_like_xml(content: str) -> bool:
163 return content.lstrip().lower().startswith("<?xml")
164
165
166# --- OFX 1.x (SGML) -----------------------------------------------------------
167
168def _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
180def _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
188def _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
199def _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
211def _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
224def _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
265def _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
276def _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
281def _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
288def _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
301def _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
361def _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
392def 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)