DEV Community

Cover image for Validate OFX 1.02 SGML FITID in Python: A 5-Gate Script
Huyen Hai Nghi
Huyen Hai Nghi

Posted on

Validate OFX 1.02 SGML FITID in Python: A 5-Gate Script

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>
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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.

Dual-panel benchmark chart showing linear O(N) stage runtime and log-scale speedup from 100 to 10,000 OFX 1.02 transactions in Python 3.13
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 .qfx exports before import, enforcing <INTU.BID> numeric rules that ofxtools strips, catching missing </STMTTRN> closures, and blocking duplicate <FITID> keys or Decimal balance gaps.
  • Use ofxtools when: parsing investment responses (INVSTMTMSGSRSV1, SECLIST security trees), handling OFX 2.x XML schemas, or generating bank requests.
  • Skip xml.etree and regex tag-closers when: bank exports mix CP1252 characters under USASCII headers, omit </STMTTRN>, or prepend a UTF-8 BOM.

Pair the script with four downstream safeguards in production jobs:

  • Pass the prior reconciled <LEDGERBAL><BALAMT> as opening so 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_POSITIVE or LEDGER_BALANCE_MISMATCH before 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)