Exporting to an accounting package

A bank statement can be downloaded as OFX, which is the file your accounting package expects when it asks you to import a statement by hand. One file, and every package below takes it.

curl "https://extractbankstatements.com/api/v1/documents/$ID/export?format=ofx" \
  -H "Authorization: Bearer $KEY" \
  -o statement.ofx

In the app, a statement in the list has an OFX button beside CSV and Excel.

FreeAgent does not need the file. It is the one package with a published endpoint for statement lines, so a statement can be sent straight into a FreeAgent bank account instead of downloaded and imported. It is the same OFX either way; the difference is who carries it.

What takes it#

The reason to prefer it over a CSV is not that it is newer. It is that a CSV import makes the person doing it answer questions: which column is the date, which is the amount, and is 04/05 the fourth of May or the fifth of April. Xero and Odoo import an OFX with no column mapping and no date question, because the file already says.

What is in the file#

One statement, one account, one currency, and every transaction on it.

Amounts are the exact figures the statement printed. They are decimal strings from end to end and nothing rounds or reformats them, which is the same guarantee the API and the spreadsheet exports make.

Dates carry an explicit time and time zone, midday GMT. A date on its own is legal in OFX and some importers fill the missing time in with the clock at the moment you import, which can put a transaction on the day before or the day after depending on when you clicked. Stating the time removes that.

Two things it cannot know#

Your account number is not in the file. OFX requires a bank identifier and an account identifier, and we do not read either off the statement, so the file carries a placeholder bank id of nine zeroes and an identifier derived from the document. Your importer will ask you, once, which of your accounts this belongs to. It is stable, so importing the same statement again reaches the same place, but a second statement from the same account is a different identifier and will ask again.

The account type is recorded as a current account. OFX offers five types and a statement does not say which it is.

One limit that is not ours: QuickBooks Online's manual upload takes 1,000 rows and 350 KB per file. A long business statement can exceed that, and the answer is to split it rather than to change anything here.

Where the verification verdict goes#

The CSV and the workbook carry a Verification column with one of three words on every row. OFX has no column for it, so it travels in the two places the format does have:

• The document's verdict is the status message on the statement, which is the same sentence the API returns as verification_summary. Some importers show it and some do not.
• A contradicted row is marked in its memo, with what we found: [contradicted] The running balance is off by 12.40 at this row.

A verified row and a not verifiable row are not marked. Nine hundred memos reading "verified" is noise, and "not verifiable" on every row of a statement reads as a fault when nothing found one.

So the OFX carries less than the CSV does. If you want the three states per row, filterable, take the CSV or the workbook. That is what they are for.

A statement that holds three accounts#

A business account PDF can contain a euro account, a sterling account and a dollar account, printed one after another. An OFX statement has one currency and one closing balance, so there is no honest way to write that as one file.

There is an honest way to write it as three, and that is what you get. Add the currency to the download:

curl "https://extractbankstatements.com/api/v1/documents/$ID/export?format=ofx&currency=EUR" \
  -H "Authorization: Bearer $KEY" -o statement-EUR.ofx

Each file describes one account: its own CURDEF, its own LEDGERBAL, and only that account's transactions. Every row of the statement is in exactly one of them. In the app the same three files are offered as three buttons on the statement, one per account.

Leave the currency off and ofx is refused, with the codes it holds named in the message, so a script that meets a multi-currency statement for the first time is told what to ask for next. Leave it off on csv or xlsx and you get what you have always got: every row, with its own currency in column F. That is already the right answer for a spreadsheet, so nothing about it changed.

The parameter works on format=csv and format=xlsx too, and on GET /v1/documents/{id}/transactions, for when the question is about the euro rows alone. A currency the statement does not hold comes back as a 422 naming the ones it does.

What it refuses, and why#

Three cases come back as an error rather than a file, with the reason:

An invoice or a receipt. OFX describes bank transactions, which have a direction: money in or money out. An invoice does not. The same invoice is money out to whoever received it and money in to whoever sent it, and the page never says which, so we do not put a sign on it anywhere in this product. Writing one into an OFX would be our claim rather than the document's. Invoices export as CSV, Excel and JSON with every figure on them.

A statement holding more than one currency, with no currency asked for. See above: ask for one account at a time and you get a file per account.

A statement whose currency we never established. CURDEF is required and has to be a real ISO 4217 code.

Each refusal is a 422 with code: "format_not_applicable" and a sentence saying which of the three it was. We would rather refuse here, where you can read it, than hand you a file that fails inside your ledger.

One more, and it is rarer: if some rows on a document carry no currency at all, the per-account route is refused whole, with code: "rows_without_currency". Those rows belong to no account, so a file per account would leave them out of all of them, and an export that is quietly short is worse than one that refuses. Take the CSV or the workbook, which carry every row.

The formats we do not offer#

Worth saying plainly, because other converters list them.

QBO and QFX. These are OFX carrying an Intuit branding id, and Intuit's own partner documentation says that id comes from a contract: "Web Connect and Direct Connect are directly associated with a contracted Intuit BID (Branding ID)." The contract is sold to insured financial institutions and priced in tens of thousands of dollars a year. We do not have one.

Other converters ship QBO anyway, by putting Wells Fargo's id in the file. We will not, and the reason is not caution: a file carrying that id tells QuickBooks, and tells you on the import screen, that Wells Fargo produced this document. We produced it. Quicken says of this directly: "Quicken does not support QFX files created by third-party conversion software."

QuickBooks Online accepts a plain .ofx on its manual upload, which is the same rows without the borrowed identity.

QIF. It cannot state its own date order, its own decimal separator, its own currency or its own character set, and it has no transaction identifier at all. This product exists because 04/05 and 05/04 are the same statement read in two countries: the same QIF file, parsed twice by the same library with one setting changed, gives two different dates for the same transaction. The decimal separator is worse, because importers ask about an ambiguous date and guess at an ambiguous amount, so 1.000 can quietly become 1 instead of 1000. And with no transaction id, importing January and then January-to-February duplicates the overlap with nothing able to notice. Every package that takes QIF also takes OFX, and several of them tell you to prefer it.

IIF. QuickBooks Desktop only, and a transaction in it has to name an account in your chart of accounts. That lives in your company file and not on your bank statement.

A CSV shaped for one package. There is no shared bank statement CSV. Sage requires a header row and FreeAgent forbids one; Sage's columns are date, description, amount and FreeAgent's are date, amount, description; QuickBooks' four column format puts money out under Credit. They are separate formats wearing the same file extension, and OFX is the one file that reaches all of them.