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.0through1.6(VERSION:102SGML headers), not XML-based OFX2.xpayloads - Single-byte bank encodings (
CHARSET:1252ascp1252,ISO-8859-1aslatin_1, orUTF-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.OFXHeaderV1decodes headers andofxtools.Parser.TreeBuildercloses leaf tags during tree construction, yetAggregate.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. -
ofxparseruns a regex preprocessor (OfxPreprocessedFile) that collects every</TAG>name intoclosing_tagsfirst, then closes a preceding opening tag before the next tag only when that tag name is absent fromclosing_tags. BecauseSTMTTRNappears inclosing_tagswhenever 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:
-
Decode headers and reject leading BOM bytes: Split bytes at
<OFX>, flag any UTF-8 BOM (\xef\xbb\xbf) ahead ofOFXHEADER:100, verifyDATA:OFXSGMLandVERSION:102, and decode strictly viaCHARSET(cp1252,latin_1, orutf_8). Many US banks emitENCODING:USASCIIwithCHARSET:1252, so strictcp1252decoding catches broken bytes without dropping punctuation. -
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. -
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 plusdatetime.date()so20260230fails immediately. -
Enforce
<FITID>uniqueness andTRNTYPEpolarity: Require a non-whitespace<FITID>within255characters on every<STMTTRN>, detect collisions locally or against a historicalset(), and flag sign reversals (DEBITwith positive<TRNAMT>, orCREDITwith negative amounts). -
Reconcile balances in
decimal.Decimal: Sum deposits and withdrawals usingdecimal.Decimalinstead of binaryfloat, then testopening_balance + deposits - withdrawals == reported_closingagainst<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.

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(0issues): Parses10transactions, acceptsINTU.BID=3000, confirms10uniqueFITIDkeys, and reconciles$4,250.00 + $3,840.00 - $1,624.85 = $6,465.15(diff=$0.00). -
02_duplicate_fitid.ofx(1Gate 4 error): Transaction #8 reuses20261009004from 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(1Gate 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(3errors in Gates 1 and 3): Flags a UTF-8 BOM ahead ofOFXHEADER:100at line 1, rejects non-numeric<INTU.BID>BANK-09at line 24, and catches February 30 (20260230093000) at line 67 where pattern-only regexes pass. -
05_sign_flip_reversal.ofx(11errors in Gates 4 and 5): Inverted CSV-to-OFX signs trigger10polarity mismatches plus a$4,430.30ledger gap. -
06_ledger_mismatch.ofx(1Gate 5 error): All SGML tags, dates, bank codes, andFITIDvalues validate cleanly, yet<BALAMT>6322.65falls-$142.50short of the calculated$6,465.15close.

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.

Median 15-run stage latency across 100 to 10,000 transactions (left) and log-scale speedup after fixing the O(N^2) line-count loop (right).
Initially I computed line numbers by calling body_text.count("\n", 0, m.start()) on each match. At 100 rows (6.10 ms) the bug hid; at 10,000 rows rescanning from byte zero stalled for 33,699.44 ms (297 tx/s). Replacing that with cur_line += gap.count("\n") keeps every byte scanned once and cuts 10,000 transactions (15-run median) to 119.17 ms, or 83,914 tx/s (76.25 ms in Gates 1-2, 42.92 ms in Gates 3-5). Across 100 to 10,000 rows, throughput holds steady between 74,074 and 84,674 tx/s.
When should you run this validator vs. ofxtools or ofxparse?
Run this stdlib script as a preflight gate ahead of imports, rather than replacing ofxtools on brokerage trees or signed Direct Connect HTTP calls:
-
Use this 5-gate stdlib validator when: checking bank or credit-card OFX
1.02,.qbo, or.qfxexports before import, enforcing<INTU.BID>numeric rules thatofxtoolsstrips, catching missing</STMTTRN>closures, and blocking duplicate<FITID>keys orDecimalbalance gaps. -
Use
ofxtoolswhen: parsing investment responses (INVSTMTMSGSRSV1,SECLISTsecurity trees), handling OFX2.xXML schemas, or generating bank requests. -
Skip
xml.etreeand regex tag-closers when: bank exports mixCP1252characters underUSASCIIheaders, omit</STMTTRN>, or prepend aUTF-8BOM.
Pair the script with four downstream safeguards in production jobs:
- Pass the prior reconciled
<LEDGERBAL><BALAMT>asopeningso Gate 5 checks month-to-month continuity. - Enforce a
UNIQUE(account_id, fitid)index (ON CONFLICT DO NOTHING) in SQLite or PostgreSQL across overlapping date windows. - Decode raw bytes strictly via Gate 1 headers instead of calling
errors="ignore". - Quarantine batches raising
SIGN_FLIP_OUTFLOW_POSITIVEorLEDGER_BALANCE_MISMATCHbefore posting to QuickBooks or GnuCash.
Editorial note: Portions of the outline and prose review for this tutorial were assisted by an LLM editor, while the Python 3.13 validator, synthetic OFX 1.02 test fixtures, scaling benchmarks, and matplotlib charts were written and executed locally by the author.
Top comments (0)