Parsing OFX bank files by hand: SGML without end tags, GMT dates and backwards card signs

#python #parsing #fintech #xml

An OFX file is a bank or card download in Open Financial Exchange, the format Microsoft, Intuit and CheckFree announced in 1997. Version 1.x is SGML, where a leaf value like <TRNAMT>-64.18 has no end tag. Version 2.x is XML with every element closed. To parse either one correctly you need three things: a tolerant tokenizer, careful date handling and a sign rule that ignores TRNTYPE.

If you only need the data, use ofxtools, the open-source Python library that reads both styles and QFX too. This post is for when you want to know what the library is doing, or you are stuck on a file it rejects.

Step 1: tell the two versions apart

Read the first non-empty line.

OFXHEADER:100        -> 1.x, SGML, header is key:value lines
<?xml version=...    -> 2.x, XML, followed by <?OFX OFXHEADER="200" ...?>

A 2.x file goes straight into any XML parser. A 1.x file will not, because the header is not XML and most leaves are never closed. Branch on this line before anything else.

Step 2: tokenize 1.x SGML without end tags

In the 1.x style, aggregates (the blocks, like <STMTTRN>) open and close. Elements (single values) only open: the value runs until the next <. So the rule is simple. A tag followed by text is a leaf. A tag followed directly by another tag opens a block.

import re

TAG = re.compile(r"<(/?)([A-Z0-9.]+)>([^<]*)")

def parse_sgml(text):
    body = text[text.index("<OFX>"):]          # skip the key:value header
    root = {"tag": "ROOT", "children": []}
    stack = [root]
    for closing, name, value in TAG.findall(body):
        value = value.strip()
        if closing:
            # Close a block only if it is open. A stray end tag on a
            # leaf is ignored instead of collapsing the whole tree.
            if any(node["tag"] == name for node in stack):
                while stack[-1]["tag"] != name:
                    stack.pop()
                stack.pop()
            continue
        if value:
            stack[-1]["children"].append({"tag": name, "value": value})
        else:
            node = {"tag": name, "children": []}
            stack[-1]["children"].append(node)
            stack.append(node)
    return root

Two notes. Tag names include dots because Intuit's extensions look like INTU.BID. And the guard on closing tags matters: write it as "pop until the name matches" without checking first, and one unexpected end tag empties your stack.

Once you have the tree, the path to transactions is OFX > BANKMSGSRSV1 > STMTTRNRS > STMTRS > BANKTRANLIST > STMTTRN for bank accounts, and CREDITCARDMSGSRSV1 > CCSTMTTRNRS > CCSTMTRS > BANKTRANLIST > STMTTRN for cards. Check both.

Step 3: dates, time zones and the day that moves

The full DTPOSTED form is YYYYMMDDHHMMSS.XXX[offset:TZ], for example 20260914093000.000[-5:CDT]. The offset is hours from GMT and the name after the colon is optional. Banks may truncate from the right, so 20260914 alone is legal, and the spec says to read a truncated value as GMT.

That last rule causes a classic bug. Parse 20260914 as midnight GMT, convert to America/Chicago, and the transaction lands on September 13. Your output disagrees with the PDF statement by a day, for every transaction.

from datetime import datetime, timedelta, timezone

DT = re.compile(r"(\d{8})(\d{6})?(?:\.\d+)?(?:\[([+-]?[\d.]+)(?::\w+)?\])?")

def parse_ofx_date(raw):
    day, clock, offset = DT.match(raw).groups()
    if clock is None:
        # Date only. Keep it a calendar date; don't push it through a zone.
        return datetime.strptime(day, "%Y%m%d").date()
    tz = timezone(timedelta(hours=float(offset or 0)))
    return datetime.strptime(day + clock, "%Y%m%d%H%M%S").replace(tzinfo=tz)

This handles the full form and the date-only form. If your bank sends other truncations, such as hours and minutes without seconds, add those cases explicitly rather than loosening the regex until anything matches.

Step 4: card signs and why TRNTYPE is a trap

TRNAMT is signed by the transaction's effect on the customer. On checking, deposits are positive and purchases negative. On a credit card, a purchase is negative because it raises what you owe, and a payment to the card is positive. LEDGERBAL on a card is negative when there is a balance owed.

TRNTYPE (DEBIT, CREDIT, INT and so on) is a label. The spec is clear that the sign decides the effect, so INT can be interest earned on savings (positive) or interest charged on a card (negative). Code that does if trntype == "DEBIT": amount = -abs(amount) will get real files wrong.

If you are loading a card into a double-entry ledger as a liability, flip the sign once, at the account level, based on whether the statement came from CCSTMTRS. Never per transaction.

Step 5: dedupe on FITID

Every STMTTRN carries a FITID, the bank's unique ID for that transaction. Store it. Overlapping downloads are common, and FITID is the only reliable key; date plus amount plus NAME collides on two identical coffees.

The thing the file doesn't contain

A statement download has LEDGERBAL at the end but no opening balance. You can't prove completeness from the file alone. If you need that proof, compare the derived opening (LEDGERBAL minus the sum of TRNAMT) with the previous period's closing balance.

Disclosure: I'm Damin, I build ZeraBooks. We took the other route: ZeraBooks reads the PDF statement instead, because it prints both balances, and writes CSV from the bank statement after checking the totals tie. It doesn't read or write OFX. The full annotated 2.x sample and the sign table are in our page on the OFX file format.

DEV Community