The OFX format, explained for accountants

What OFX is for

Open Financial Exchange is a specification for exchanging financial data between institutions and software, first published in the late nineties and stable since. Xero, Sage, MYOB, Wave, Zoho Books and QuickBooks all read some form of it.

Its advantage over CSV is that it is self-describing. A CSV date is ambiguous and its amount column may or may not be signed; an OFX date states its own format and its amounts are signed by definition. There is no mapping step because there is nothing to map.

Reading one

A file has a header block, then a nested body. The parts worth knowing are few:

<BANKID>     the routing or sort code
<ACCTID>     the account number the file claims
<ACCTTYPE>   CHECKING, SAVINGS, CREDITLINE
<DTSTART>    period start, YYYYMMDD
<DTEND>      period end
<STMTTRN>    one transaction
  <TRNTYPE>  DEBIT, CREDIT, CHECK, FEE, INT...
  <DTPOSTED> the date, YYYYMMDD
  <TRNAMT>   signed: negative is money out
  <FITID>    the transaction identifier
  <NAME>     description

Most import failures are visible here. A missing ACCTID, a period in the future, an ACCTTYPE that disagrees with the account you are importing into — all findable in under a minute with a text editor.

The two versions

Version 1.x is SGML: the header block above, and tags that need not be closed. Version 2.x is XML and begins with an XML declaration. Both are legitimate OFX, and a program that reads one may reject the other — QuickBooks in particular has historically expected 1.x.

Why it needs no bank ID

OFX carries a BANKID — a routing or sort code — which identifies the bank in the ordinary sense. What it does not carry is Intuit’s INTU.BID, which exists to confirm the institution participates in Web Connect. That tag is a commercial check, not a technical one, which is why plain OFX imports into non-Intuit software without one.

In practice this makes OFX the best default for anything that is not QuickBooks or Quicken: same data, no number to hunt for.

The transaction types, and what they change

TRNTYPE classifies each transaction, and importers use it to suggest a posting or to filter. The values you will actually meet are few.

• DEBIT and CREDIT — the generic pair, and what most converters write when the statement does not say more.
• CHECK — a cheque, usually with a CHECKNUM alongside it.
• FEE, SRVCHG and INT — bank charges and interest, which some importers route to their own accounts automatically.
• XFER — a transfer between accounts, worth distinguishing because posting one as income overstates revenue.
• ATM, POS, DIRECTDEP, DIRECTDEBIT — self-explanatory, and useful for filtering rather than for posting.

The sign in TRNAMT is authoritative regardless. A DEBIT with a positive amount is a contradiction some software resolves one way and some the other, so the type should never be relied on to carry direction.

The status block, and what a rejection looks like

Near the top of every file sits a sign-on response with a status code inside it. Zero means success. Anything else is the bank or the file telling you why it will not proceed, and it is worth reading before assuming the transactions are the problem.

<STATUS>
  <CODE>0
  <SEVERITY>INFO
</STATUS>

Statement files produced by converters generally set this to zero as a formality. Files downloaded from a bank sometimes carry a real error here, which is the actual cause of an import that appeared to fail for no reason.

Balances, and why they matter for checking

A statement block carries LEDGERBAL, the closing balance, and often AVAILBAL, the available balance. These are the closest thing OFX has to a self-check: opening plus the sum of the transactions should reach the ledger balance.

Available balance is not the same figure and should never be used for that arithmetic — it reflects pending items and any overdraft facility, so it will not agree and is not supposed to.