To validate OFX 1.02 SGML FITID in Python without crashing on legacy bank exports, treat the file as a plain-text header plus an SGML token stream instead of XML. Track container tags on a stack, allow unclosed leaf tags, and run FITID deduplication alongside fixed-point Decimal balance checks after parsing.

Most Python tutorials stop right after pip install ofxparse or ofxtools and print tx.fitid. Both libraries deserialize bank rows fine, yet neither rejects a file whose Opening + Deposits - Withdrawals total misses <LEDGERBAL><BALAMT> by $142.50, nor stops two transactions inside the same statement from reusing an identical <FITID>. Worse, an invalid or stripped <INTU.BID> tag passes silently until accounting software rejects the Web Connect upload.

Any preflight check for these exports operates under five constraints:

  • Python 3.10+ stdlib modules only (re, decimal, datetime, dataclasses, pathlib)
  • OFX 1.0 through 1.6 (VERSION:102 SGML headers), not XML-based OFX 2.x payloads
  • Single-byte bank encodings (CHARSET:1252 as cp1252, ISO-8859-1 as latin_1, or UTF-8)
  • Unclosed leaf tags (<TRNTYPE>, <DTPOSTED>, <TRNAMT>, <FITID>, <INTU.BID>)
  • Explicit closing tags required on containers (<OFX>, <STMTRS>, <BANKTRANLIST>, <STMTTRN>)

Why standard XML parsers fail when you validate OFX 1.02 SGML FITID in Python

Standard XML parsers fail as soon as you try to validate OFX 1.02 SGML FITID in Python because OFX versions 1.0 through 1.6 use SGML, not XML. Every valid file begins with an ASCII key-value block (OFXHEADER:100, DATA:OFXSGML, VERSION:102) above <OFX>, and data elements inside transactions omit closing tags.

Pass that raw file to Python's xml.etree.ElementTree, and it aborts on line 7 with ParseError: mismatched tag: line 7, column 2:

OFXHEADER:100 DATA:OFXSGML VERSION:102 SECURITY:NONE ENCODING:USASCII CHARSET:1252 COMPRESSION:NONE OLDFILEUID:NONE NEWFILEUID:NONE <OFX> <SIGNONMSGSRSV1> <SONRS> <STATUS> <CODE>0 <SEVERITY>INFO </STATUS> <INTU.BID>3000 </SONRS> </SIGNONMSGSRSV1> <BANKMSGSRSV1> <STMTTRNRS> <STMTRS> <CURDEF>USD <BANKTRANLIST> <DTSTART>20261001000000 <DTEND>20261031235959 <STMTTRN> <TRNTYPE>DEBIT <DTPOSTED>20261004141500 <TRNAMT>-184.20 <FITID>20261004002 <NAME>PACIFIC ELECTRIC </STMTTRN> </BANKTRANLIST> <LEDGERBAL> <BALAMT>6465.15 <DTASOF>20261031235959 </LEDGERBAL> </STMTRS> </STMTTRNRS> </BANKMSGSRSV1> </OFX> 

Open-source OFX parsers handle the SGML header and unclosed leaves, though reading their source code turns up two surprising validation gaps:

  • ofxtools.header.OFXHeaderV1 decodes headers and ofxtools.Parser.TreeBuilder closes leaf tags during tree construction, yet Aggregate.groom() drops every tag whose name contains a dot (if "." in child.tag: elem.remove(child)). Both the ofxtools README and the ofxtools parser guide confirm that Intuit proprietary extensions such as <INTU.BID> get stripped with a warning rather than checked for valid bank IDs.
  • ofxparse runs a regex preprocessor (OfxPreprocessedFile) that collects every </TAG> name into closing_tags first, then closes a preceding opening tag before the next tag only when that tag name is absent from closing_tags. Because STMTTRN appears in closing_tags whenever other transactions close properly, omitting a single </STMTTRN> leaves that element open in the preprocessed stream rather than flagging a broken block on its own line.

Neither package enforces statement-wide <FITID> uniqueness or verifies ledger totals against <LEDGERBAL><BALAMT>. That leaves you writing a thin validation pass before loading rows.

Building the five-gate OFX 1.02 validator step by step

A preflight validator passes raw bytes through five ordered gates so structural breaks abort early while valid files reach balance math:

  1. Decode headers and reject leading BOM bytes: Split bytes at <OFX>, flag any UTF-8 BOM (\xef\xbb\xbf) ahead of OFXHEADER:100, verify DATA:OFXSGML and VERSION:102, and decode strictly via CHARSET (cp1252, latin_1, or utf_8). Many US banks emit ENCODING:USASCII with CHARSET:1252, so strict cp1252 decoding catches broken bytes without dropping punctuation.
  2. Walk SGML tokens with a container stack: Match <(/?)([A-Za-z0-9_.]+)>\s*([^<]*) sequentially, pushing container tags (OFX, STMTRS, BANKTRANLIST, STMTTRN, LEDGERBAL) onto a stack while allowing unclosed leaves (FITID, TRNAMT, INTU.BID). A second <STMTTRN> opening before </STMTTRN> pops logs the open and failure line numbers cleanly.
  3. Validate <INTU.BID> and calendar dates: Check that <INTU.BID> (used by Intuit's QFX/QBO Web Connect extensions) is a positive integer, and validate <DTPOSTED>, <DTSTART>, and <DTEND> against OFX Section 3.2.8.2 regex rules plus datetime.date() so 20260230 fails immediately.
  4. Enforce <FITID> uniqueness and TRNTYPE polarity: Require a non-whitespace <FITID> within 255 characters on every <STMTTRN>, detect collisions locally or against a historical set(), and flag sign reversals (DEBIT with positive <TRNAMT>, or CREDIT with negative amounts).
  5. Reconcile balances in decimal.Decimal: Sum deposits and withdrawals using decimal.Decimal instead of binary float, then test opening_balance + deposits - withdrawals == reported_closing against <LEDGERBAL><BALAMT>.

Save the runnable implementation below as validate_ofx.py and pass any .ofx or .qbo file on the command line:

from decimal import Decimal import datetime as dt from pathlib import Path import re import sys CONTAINER_TAGS = frozenset({ "OFX", "SIGNONMSGSRSV1", "SONRS", "STATUS", "FI", "BANKMSGSRSV1", "STMTTRNRS", "STMTRS", "BANKACCTFROM", "CREDITCARDMSGSRSV1", "CCSTMTTRNRS", "CCSTMTRS", "CCACCTFROM", "BANKTRANLIST", "STMTTRN", "PAYEE", "LEDGERBAL", "AVAILBAL", "BALLIST", "BAL", "CURRENCY", "ORIGCURRENCY", }) OUTFLOW_TYPES = frozenset({"DEBIT", "FEE", "SRVCHG", "ATM", "POS", "CHECK", "PAYMENT", "CASH", "DIRECTDEBIT", "REPEATPMT"}) INFLOW_TYPES = frozenset({"CREDIT", "DEP", "INT", "DIV", "DIRECTDEP"}) CHARSET_MAP = {"1252": "cp1252", "ISO-8859-1": "latin_1", "8859-1": "latin_1", "NONE": "utf_8"} TAG_RE = re.compile(r"<(/?)([A-Za-z0-9_.]+)>\s*([^<]*)") DT_RE = re.compile( r"^(?P<year>\d{4})(?P<month>0[1-9]|1[0-2])(?P<day>0[1-9]|[12]\d|3[01])" r"(?:(?P<hour>[01]\d|2[0-3])(?P<minute>[0-5]\d)" r"(?:(?P<second>[0-5]\d|60)(?:\.(?P<ms>\d{3}))?)?" r"(?:\[(?P<offset>[+-]?\d+(?:\.\d{2})?)(?::[^\]]+)?\])?)?$" ) def parse_ofx_datetime(raw: str) -> dt.date | None: m = DT_RE.match(raw.strip()) if not m: return None try: return dt.date(int(m["year"]), int(m["month"]), int(m["day"])) except ValueError: return None def decode_header(raw: bytes) -> tuple[str, int, list[str]]: errs: list[str] = [] if raw.startswith(b"\xef\xbb\xbf"): errs.append("G1 line 1: UTF-8 BOM precedes OFXHEADER:100") raw = raw[3:] idx = raw.lower().find(b"<ofx") if idx == -1: return raw.decode("utf_8", errors="replace"), 1, errs + ["G1 line 1: missing <OFX> root"] hdr_txt = raw[:idx].decode("ascii", errors="replace") hdr = {k.upper(): v.strip() for k, v in re.findall(r"([A-Z]+)\s*:\s*([^\s\r\n]+)", hdr_txt)} if hdr.get("OFXHEADER") != "100" or hdr.get("DATA") != "OFXSGML" or hdr.get("VERSION") != "102": errs.append(f"G1 line 1: invalid OFX 1.02 header {hdr}") enc = hdr.get("ENCODING", "USASCII").upper() codec = "utf_8" if enc in ("UTF-8", "UNICODE") else CHARSET_MAP.get(hdr.get("CHARSET", "NONE").upper(), "cp1252") return raw[idx:].decode(codec, errors="strict"), hdr_txt.count("\n") + 1, errs def validate_ofx_file(path: Path, opening: Decimal, known_fitids: set[str] | None = None) -> list[str]: body_text, cur_line, errors = decode_header(path.read_bytes()) stack: list[tuple[str, int]] = [] last_leaf, last_end = None, 0 txs, current_tx, meta = [], None, {} for m in TAG_RE.finditer(body_text): gap = body_text[last_end:m.start()] if gap.strip(): errors.append(f"G2 line {cur_line}: stray text {gap.strip()[:30]!r}") cur_line += gap.count("\n") line = cur_line cur_line += m.group(0).count("\n") last_end = m.end() is_close, tag, val = bool(m.group(1)), m.group(2).upper(), m.group(3).strip() if not is_close: if tag in CONTAINER_TAGS: last_leaf = None if tag == "STMTTRN" and stack and stack[-1][0] == "STMTTRN": _, prev_line = stack.pop() errors.append(f"G2 line {line}: missing </STMTTRN> opened at line {prev_line}") if current_tx is not None: txs.append(current_tx) stack.append((tag, line)) if tag == "STMTTRN": current_tx = {"line": line} else: last_leaf = tag if current_tx is not None and stack and stack[-1][0] == "STMTTRN": current_tx[tag], current_tx[f"{tag}_line"] = val, line elif stack and stack[-1][0] == "LEDGERBAL": meta[f"LEDGERBAL_{tag}"] = val else: meta[tag], meta[f"{tag}_line"] = val, str(line) elif tag in CONTAINER_TAGS: last_leaf = None if not stack or stack[-1][0] != tag: errors.append(f"G2 line {line}: unexpected </{tag}>") while stack and stack[-1][0] != tag: unclosed, _ = stack.pop() if unclosed == "STMTTRN" and current_tx is not None: txs.append(current_tx) current_tx = None if stack and stack[-1][0] == tag: if stack.pop()[0] == "STMTTRN" and current_tx is not None: txs.append(current_tx) current_tx = None elif last_leaf == tag: last_leaf = None intu_bid = meta.get("INTU.BID", "") bid_line = meta.get("INTU.BID_line", "1") if not re.fullmatch(r"\d{1,10}", intu_bid) or int(intu_bid) <= 0: errors.append(f"G3 line {bid_line}: invalid INTU.BID={intu_bid!r}") seen: dict[str, int] = {} deposits, withdrawals = Decimal("0.00"), Decimal("0.00") for tx in txs: line = int(tx["line"]) dt_line = int(tx.get("DTPOSTED_line", line)) if parse_ofx_datetime(str(tx.get("DTPOSTED", ""))) is None: errors.append(f"G3 line {dt_line}: invalid DTPOSTED={tx.get('DTPOSTED')!r}") fitid = str(tx.get("FITID", "")) fid_line = int(tx.get("FITID_line", line)) if not fitid or len(fitid) > 255 or any(c.isspace() for c in fitid): errors.append(f"G4 line {fid_line}: malformed FITID={fitid!r}") elif fitid in seen or (known_fitids and fitid in known_fitids): errors.append(f"G4 line {fid_line}: duplicate FITID={fitid!r} (first seen line {seen.get(fitid)})") seen.setdefault(fitid, fid_line) amt = Decimal(str(tx["TRNAMT"])) amt_line = int(tx.get("TRNAMT_line", line)) ttype = str(tx.get("TRNTYPE", "")).upper() if (ttype in OUTFLOW_TYPES and amt > 0) or (ttype in INFLOW_TYPES and amt < 0): errors.append(f"G4 line {amt_line}: sign flip {ttype} with amount {amt}") deposits += amt if amt >= 0 else Decimal("0.00") withdrawals += -amt if amt < 0 else Decimal("0.00") diff = Decimal(meta["LEDGERBAL_BALAMT"]) - (opening + deposits - withdrawals) if diff != Decimal("0.00"): errors.append(f"G5: balance mismatch diff={diff}") return errors if __name__ == "__main__": issues = validate_ofx_file(Path(sys.argv[1]), Decimal(sys.argv[2])) print("PASS" if not issues else "\n".join(issues)) sys.exit(1 if issues else 0) 

Notice how cur_line advances incrementally via gap.count("\n") and m.group(0).count("\n"). That single detail turns out to matter a lot at scale.

What six edge-case fixtures reveal across the five gates

Testing the validator against xml.etree.ElementTree.fromstring() across six synthetic OFX 1.02 checking statements (10 transactions each, $4,250.00 opening balance) shows why XML parsing gives a useless signal here.

Two-panel chart comparing xml.etree.ElementTree crashes against the five-gate script used to validate OFX 1.02 SGML FITID in Python
Defects pinned per validation gate across six OFX 1.02 SGML fixtures (left) versus ElementTree parse crashes on every sample (right).

ElementTree crashes at line 7 (mismatched tag: line 7, column 2) on all six files because <CODE>0 lacks </CODE>, so it can't tell a valid bank download apart from a corrupted one. Our five-gate script clears the baseline statement and pins each defect to its exact rule:

  • 01_valid_checking.ofx (0 issues): Parses 10 transactions, accepts INTU.BID=3000, confirms 10 unique FITID keys, and reconciles $4,250.00 + $3,840.00 - $1,624.85 = $6,465.15 (diff=$0.00).
  • 02_duplicate_fitid.ofx (1 Gate 4 error): Transaction #8 reuses 20261009004 from transaction #4 on line 69, catching the collision at line 97 before duplicate transactions enter matchers like GnuCash's 42-day transaction importer.
  • 03_missing_close_stmttrn.ofx (1 Gate 2 error): Dropping </STMTTRN> after transaction #3 triggers a container stack error at line 64 (<STMTTRN> opened at line 58 was never closed) while keeping transaction #4 intact for downstream diagnostics.
  • 04_bad_header_bid_date.ofx (3 errors in Gates 1 and 3): Flags a UTF-8 BOM ahead of OFXHEADER:100 at line 1, rejects non-numeric <INTU.BID>BANK-09 at line 24, and catches February 30 (20260230093000) at line 67 where pattern-only regexes pass.
  • 05_sign_flip_reversal.ofx (11 errors in Gates 4 and 5): Inverted CSV-to-OFX signs trigger 10 polarity mismatches plus a $4,430.30 ledger gap.
  • 06_ledger_mismatch.ofx (1 Gate 5 error): All SGML tags, dates, bank codes, and FITID values validate cleanly, yet <BALAMT>6322.65 falls -$142.50 short of the calculated $6,465.15 close.

Line chart plotting cumulative Decimal running balances and LEDGERBAL mismatches across ten OFX 1.02 statement transactions
Cumulative Decimal balance path for the clean checking statement ($6,465.15, $0.00 diff) against sign-flip reversal and a -$142.50 ledger mismatch.

Look closely at 06_ledger_mismatch, because every line passes OFX 1.02 SGML grammar. Without computing Opening + Deposits - Withdrawals in Decimal, an importer posts that file and leaves a $142.50 discrepancy at close. Credit card statements (CCSTMTRS) follow the same equation: purchases arrive as negative <TRNAMT> values toward a negative <LEDGERBAL><BALAMT> liability, so keep the opening balance signed.

Throughput scaling across 100 to 10,000 transactions

Benchmarking synthetic files from 100 rows (11.8 KB) up to 10,000 rows (1,115.9 KB) on Windows Python 3.13.3 exposed an O(N^2) bug on my first pass.

Source:https://dev.to/huyentoolnotes/validate-ofx-102-sgml-fitid-in-python-a-5-gate-script-3b0