Parsing OFX/SGML Statements in Python

Plenty of feeds still show up as OFX 1.x, and OFX 1.x is not XML — it is Open Financial Exchange laid over SGML, which means leaf tags like <TRNAMT> are frequently left unclosed and only container tags like <STMTTRN> get an explicit close. Feed an OFX 1.x body straight into xml.etree.ElementTree and it raises a parse error on the first unclosed tag. This page implements the narrow fix: split the plain-text header from the SGML body, normalise the body into well-formed markup, extract every STMTTRN record, and project the result onto the same canonical transaction contract used across OFX & MT940 Parser Design — the same Decimal amounts, timezone-aware UTC timestamps, and audited idempotency keys that How to Parse MT940 Files in Python produces from a completely different wire format. Where MT940’s equivalent free-text field is :86:, covered in Normalising MT940 Field 86 Remittance Info, OFX carries the same kind of remittance narrative split across NAME and MEMO.

Prerequisites

Step 1 — Split the plain-text header from the SGML body

An OFX 1.x file opens with a flat KEY:VALUE header block (OFXHEADER, DATA, VERSION, SECURITY, ENCODING, CHARSET) followed by a blank line and then the <OFX> root. The header is not SGML and must never be run through a tag parser; the two blocks are parsed independently and the header’s ENCODING/CHARSET pair decides how the bytes are decoded before anything else happens.

python
import re
from decimal import Decimal, InvalidOperation
from datetime import datetime, timedelta, timezone
import hashlib
import logging

log = logging.getLogger("ofx.parser")

HEADER_LINE = re.compile(r"^([A-Z0-9]+):(.*)$")


def split_ofx_header_body(raw: bytes, encoding: str = "cp1252") -> tuple[dict[str, str], str]:
    """Decode raw bytes and separate the flat header block from the SGML body."""
    text = raw.decode(encoding)
    body_start = text.find("<OFX>")
    if body_start == -1:
        raise ValueError("OFX_ROOT_MISSING: no <OFX> root tag found in payload")
    header_block, body = text[:body_start], text[body_start:]
    header: dict[str, str] = {}
    for line in header_block.splitlines():
        match = HEADER_LINE.match(line.strip())
        if match:
            header[match.group(1)] = match.group(2)
    return header, body

split_ofx_header_body reads the declared encoding from the caller rather than guessing, because a mismatched CHARSET silently corrupts payee names before a single STMTTRN is even reached.

Step 2 — Normalise unclosed SGML tags into well-formed markup

This is the load-bearing step. Walk the body line by line, treat any tag with an inline value as a leaf that gets an immediate synthetic close, and treat any bare tag as a container pushed onto a stack. A real closing tag pops the stack down to its match. If a closing tag appears with no corresponding entry on the stack, the structure is genuinely broken and the file is rejected rather than silently reshuffled.

python
SGML_TAG = re.compile(r"<([A-Z0-9.]+)>([^<\r\n]*)")
SGML_CLOSE = re.compile(r"^</([A-Z0-9.]+)>$")


def _escape(value: str) -> str:
    return value.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")


def sgml_to_xml(body: str) -> str:
    """Rewrite OFX 1.x SGML into well-formed XML that ElementTree can parse."""
    stack: list[str] = []
    out: list[str] = []
    for raw_line in body.splitlines():
        line = raw_line.strip()
        if not line:
            continue
        close = SGML_CLOSE.match(line)
        if close:
            tag = close.group(1)
            if tag not in stack:
                raise ValueError(f"SGML_UNCLOSED_TAG: closing </{tag}> matches no open tag")
            while stack[-1] != tag:
                out.append(f"</{stack.pop()}>")
            stack.pop()
            out.append(line)
            continue
        opened = SGML_TAG.match(line)
        if not opened:
            out.append(line)
            continue
        tag, value = opened.groups()
        if value:
            out.append(f"<{tag}>{_escape(value)}</{tag}>")
        else:
            stack.append(tag)
            out.append(f"<{tag}>")
    while stack:
        out.append(f"</{stack.pop()}>")
    return "\n".join(out)

The trailing while stack: drain handles files that omit the very last container closes (some core-banking exports do this on </OFX> itself); it only fires after every line has been consumed, so it never masks a genuine mid-file structural break.

Step 3 — Extract STMTTRN records

Once the body is well-formed, xml.etree.ElementTree.iter("STMTTRN") walks every transaction regardless of nesting depth under BANKTRANLIST or CCSTMTRS. Pull the raw string fields here and defer type coercion to the next step, so a malformed value fails with a field-specific error rather than an opaque XML exception.

python
import xml.etree.ElementTree as ET


def extract_stmttrns(xml_body: str) -> list[dict[str, str]]:
    root = ET.fromstring(xml_body)
    records: list[dict[str, str]] = []
    for trn in root.iter("STMTTRN"):
        records.append({
            "trntype": (trn.findtext("TRNTYPE") or "").strip(),
            "dtposted": (trn.findtext("DTPOSTED") or "").strip(),
            "trnamt": (trn.findtext("TRNAMT") or "").strip(),
            "fitid": (trn.findtext("FITID") or "").strip(),
            "name": (trn.findtext("NAME") or trn.findtext("PAYEE") or "").strip(),
            "memo": (trn.findtext("MEMO") or "").strip(),
        })
    return records

Step 4 — Convert amounts to Decimal and DTPOSTED to timezone-aware UTC

TRNAMT is a plain decimal string with an explicit sign (-42.17), so the conversion risk is a stray currency symbol or thousands separator slipping through, not a locale mismatch. DTPOSTED is the harder field: OFX allows YYYYMMDD, YYYYMMDDHHMMSS, an optional .XXX fractional-second suffix, and an optional bracketed GMT offset like [-5:EST]. A date with no bracketed offset is not UTC — it is ambiguous — so it falls back to a caller-supplied default_tz rather than being treated as naive.

python
DTPOSTED_RE = re.compile(
    r"^(\d{8})(\d{6})?(?:\.\d{3})?(?:\[(-?\d+(?:\.\d+)?):\w+\])?$"
)


def parse_trnamt(raw: str) -> Decimal:
    try:
        return Decimal(raw.strip())
    except InvalidOperation as exc:
        raise ValueError(f"AMOUNT_NOT_DECIMAL: {raw!r} is not a valid decimal") from exc


def parse_dtposted(raw: str, default_tz_hours: float = 0.0) -> datetime:
    match = DTPOSTED_RE.match(raw.strip())
    if not match:
        raise ValueError(f"NAIVE_DTPOSTED: unparseable DTPOSTED {raw!r}")
    date_part, time_part, offset = match.groups()
    naive = datetime.strptime(date_part + (time_part or "000000"), "%Y%m%d%H%M%S")
    tz_hours = float(offset) if offset is not None else default_tz_hours
    aware = naive.replace(tzinfo=timezone(timedelta(hours=tz_hours)))
    return aware.astimezone(timezone.utc)

parse_dtposted always returns a value with tzinfo set — either from the bracketed offset in the record or from default_tz_hours — so nothing downstream can receive a naive datetime for DTPOSTED.

Step 5 — Map FITID to the idempotency key and validate with pydantic

FITID is the bank’s own duplicate-suppression identifier for the statement line; treat it as the seed for the idempotency key rather than inventing a new one, so re-ingesting the same OFX file twice — a common retry pattern for SFTP pulls — produces identical keys both times. Combine it with source_hash (the SHA-256 of the raw bytes) so the same FITID from two different banks, or two different accounts at the same bank, never collides.

python
from pydantic import BaseModel, ConfigDict, Field, field_validator


class OfxTransaction(BaseModel):
    model_config = ConfigDict(frozen=True)

    fitid: str = Field(min_length=1)
    trntype: str
    trnamt: Decimal
    dtposted: datetime
    name: str
    memo: str = ""
    idempotency_key: str
    source_hash: str

    @field_validator("dtposted")
    @classmethod
    def must_be_aware(cls, v: datetime) -> datetime:
        if v.tzinfo is None:
            raise ValueError("NAIVE_DTPOSTED: dtposted lost timezone before validation")
        return v.astimezone(timezone.utc)


def build_transaction(record: dict[str, str], source_hash: str,
                       default_tz_hours: float, strict_mode: bool) -> OfxTransaction:
    fitid = record["fitid"]
    if not fitid:
        if strict_mode:
            raise ValueError("MISSING_FITID: STMTTRN has no FITID")
        fitid = hashlib.sha256(
            f"{record['dtposted']}:{record['trnamt']}:{record['name']}".encode()
        ).hexdigest()[:20]
    idempotency_key = hashlib.sha256(f"{source_hash}:{fitid}".encode()).hexdigest()
    return OfxTransaction(
        fitid=fitid,
        trntype=record["trntype"],
        trnamt=parse_trnamt(record["trnamt"]),
        dtposted=parse_dtposted(record["dtposted"], default_tz_hours),
        name=record["name"],
        memo=record["memo"],
        idempotency_key=idempotency_key,
        source_hash=source_hash,
    )

idempotency_key is what the reconciliation worker checks against Redis before writing a transaction; storing it with a TTL keyed to the statement’s retention window is covered separately in Idempotency Key Storage with Redis TTL and should not be re-derived on every retry.

Step 6 — Run the pipeline with a structured audit log

Tie the steps together and emit one audit line per file, carrying trace_id, source_hash, and a match_decision of either PARSE_OK or QUARANTINE. A file that fails normalisation or validation never partially commits — it raises, and the caller routes the raw bytes to a dead-letter queue with the same trace_id attached.

python
def audit_log(trace_id: str, source_hash: str, match_decision: str, **fields) -> None:
    log.info("ofx.parsed", extra={
        "trace_id": trace_id,
        "source_hash": source_hash,
        "match_decision": match_decision,
        **fields,
    })


def parse_ofx_sgml(raw: bytes, trace_id: str, encoding: str = "cp1252",
                    default_tz_hours: float = 0.0,
                    strict_mode: bool = True) -> list[OfxTransaction]:
    source_hash = hashlib.sha256(raw).hexdigest()
    try:
        header, body = split_ofx_header_body(raw, encoding)
        xml_body = sgml_to_xml(body)
        records = extract_stmttrns(xml_body)
        transactions = [
            build_transaction(r, source_hash, default_tz_hours, strict_mode)
            for r in records
        ]
    except ValueError as exc:
        audit_log(trace_id, source_hash, "QUARANTINE", error=str(exc))
        raise
    audit_log(trace_id, source_hash, "PARSE_OK",
              ofx_version=header.get("VERSION", "unknown"),
              transaction_count=len(transactions))
    return transactions

Configuration boundary table

Parameter Default Valid range Notes
ofx_version 102 100–103 OFX 1.x SGML; 200+ is native XML and skips sgml_to_xml entirely
encoding cp1252 usascii / cp1252 / utf-8 Read from the header’s ENCODING/CHARSET pair, never guessed
default_tz 0.0 (UTC) -12.0–14.0 Applied only when DTPOSTED omits the bracketed [gmt offset]
date_format %Y%m%d%H%M%S fixed DTPOSTED without the optional .XXX fractional-second suffix
strict_mode True True / False False tolerates a missing FITID via a derived placeholder; sandbox backfills only

Verification and testing

A minimal single-transaction fixture with a deliberately unclosed leaf tag and an explicit GMT offset proves the three risk points at once: SGML normalisation, Decimal amount conversion, and UTC conversion from a non-UTC offset.

python
FIXTURE = b"""OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252

<OFX>
<BANKMSGSRSV1>
<STMTTRNRS>
<STMTRS>
<BANKTRANLIST>
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20260410120000[-5:EST]
<TRNAMT>-42.17
<FITID>2026041000019
<NAME>ACME SUPPLY CO
<MEMO>Invoice 40213
</STMTTRN>
</BANKTRANLIST>
</STMTRS>
</STMTTRNRS>
</BANKMSGSRSV1>
</OFX>
"""


def test_parses_single_transaction() -> None:
    txns = parse_ofx_sgml(FIXTURE, trace_id="test-ofx-001")
    assert len(txns) == 1
    txn = txns[0]
    assert txn.trnamt == Decimal("-42.17")
    assert txn.dtposted.tzinfo is not None
    assert txn.dtposted == datetime(2026, 4, 10, 17, 0, tzinfo=timezone.utc)
    assert txn.idempotency_key and len(txn.idempotency_key) == 64

A green run confirms the unclosed <TRNTYPE>, <DTPOSTED>, <TRNAMT>, <FITID>, <NAME>, and <MEMO> tags were all normalised correctly, the -5:EST offset shifted 12:00 local to 17:00 UTC, and the amount stayed exact to the cent as a Decimal rather than drifting through a float.

Troubleshooting

  • SGML_UNCLOSED_TAG — sgml_to_xml raises on a closing tag with no match on the stack. Root cause: a container was truncated mid-file (a partial SFTP transfer) or a bank emits an out-of-order close, such as </BANKTRANLIST> before its own <STMTTRN> closes. Fix: quarantine the file and re-fetch; do not attempt to auto-repair a genuinely broken tag order, since guessing the intended nesting can silently attach a transaction to the wrong account block.
  • AMOUNT_NOT_DECIMAL — parse_trnamt raises on TRNAMT. Root cause: a bank emits a thousands separator (1,042.17) or a stray currency symbol inside the numeric field. Fix: strip known separator characters in a pre-pass keyed off the bank’s feed ID before calling Decimal(), rather than loosening the regex globally.
  • NAIVE_DTPOSTED — parse_dtposted raises, or the pydantic validator rejects a value with no tzinfo. Root cause: the record’s DTPOSTED has no bracketed offset and default_tz_hours was left at its assumed value for a feed that is not actually UTC. Fix: set default_tz explicitly per bank connection rather than relying on the 0.0 default.
  • MISSING_FITID — build_transaction raises in strict_mode. Root cause: a small number of core-banking exports omit FITID on pending or reversed transactions. Fix: confirm the feed is genuinely non-compliant, then run backfills only with strict_mode=False so a derived placeholder is used, and flag those rows for manual review rather than trusting the derived key long-term.
  • OFX_ROOT_MISSING — split_ofx_header_body raises before any tag parsing starts. Root cause: the payload is OFX 2.x XML (or an HTML error page returned by the bank’s endpoint) misrouted into the 1.x SGML pipeline. Fix: branch on the header’s VERSION value before choosing sgml_to_xml versus a native XML parser.

Part of OFX & MT940 Parser Design within Core Architecture & Bank Feed Ingestion.