General Ledger + Converter in one subscription.
See pricingGUIDE · OFX TO EXCEL
To convert an OFX file to Excel, check its first line. An XML file (OFX 2.x) opens through Data, Get Data, From File, From XML; drill down to the STMTTRN table and load it. An older SGML file (OFX 1.x) needs a free tool such as GnuCash first. If you have the PDF statement instead, the ZeraBooks bank statement converter gives you Excel proven against the printed balances.
7-day trial · card required, nothing charged for 7 days · 1,000 pages a month from $79/month billed annually
saltmarsh-2026-09.ofx
<STMTTRN><TRNTYPE>DEBIT</TRNTYPE>
<DTPOSTED>20260910120000.000</DTPOSTED>
<TRNAMT>-64.18</TRNAMT>
<FITID>202609100001</FITID>
<NAME>CHEVRON 0091</NAME></STMTTRN>
<STMTTRN><TRNTYPE>CREDIT</TRNTYPE>
<DTPOSTED>20260912120000.000</DTPOSTED>
<TRNAMT>420.00</TRNAMT>
<NAME>SQUARE DEPOSIT</NAME></STMTTRN>Excel
| Date | Description | Amount |
|---|---|---|
| 09/10/2026 | CHEVRON 0091 | -64.18 |
| 09/12/2026 | SQUARE DEPOSIT | 420.00 |
WHAT IS INSIDE
An OFX file is tagged text. Each transaction is one STMTTRN record, and a handful of tags inside it are all most people need in a spreadsheet. The full layout, with an annotated sample, is in what is an OFX file. Bank downloads hold the transactions under BANKMSGSRSV1 › STMTTRNRS › STMTRS › BANKTRANLIST; credit card downloads use CREDITCARDMSGSRSV1 › CCSTMTTRNRS › CCSTMTRS instead. A QFX file is the same structure with an extra Intuit bank code; see what is a QFX file.
DTPOSTED (20260910120000.000)Date (needs converting)The date the bank posted it, as one run of digits, sometimes with a time and time zoneTRNAMT (-64.18)AmountThe amount, signed: money out is negativeNAME and MEMO (CHEVRON 0091)DescriptionThe payee and any extra detailTRNTYPE (DEBIT)Type (optional)A label such as DEBIT, CREDIT, CHECK, FEE or XFERFITID (202609100001)ID (keep it for duplicates)The bank’s unique ID for the transactionCHECKNUM (1043)Check numberThe check number, when there is oneLEDGERBAL > BALAMT, DTASOFNot carried overThe closing balance and its date (6,145.30 on 09/30), once per file. Not a row: use it to check the totalWe set you up and catch up your past months for free. Then ZeraBooks keeps your books done.
A REAL EXPORT
A real run in ZeraBooks Convert: the fictional Monzo sample statement Monzo_Business_statement_2026-09.pdf, already proven to the cent, downloaded Standard as an Excel file. The file that came out, Standard - Monzo_Business_statement_2026-09.xlsx, has 13 rows with the columns Date, Description, Amount.
The format, picked in Convert
The file that downloaded
FIRST, THE VERSION
Sometimes. Open the file in Notepad or TextEdit and look at the first line. If it starts with <?xml, it is OFX 2.x, which is real XML, and Excel’s Power Query can read it. If it starts with OFXHEADER:100, it is OFX 1.x, written in SGML: many tags have no closing tag, so it isn’t valid XML and Excel’s XML import will reject it.
Plenty of bank downloads are still 1.x, so don’t be surprised if yours is. For a 1.x file, either turn it into CSV with a free tool first, or skip the OFX and work from the PDF statement for the same months.
STEP BY STEP
This uses Power Query in Excel for Windows, following Microsoft’s XML connector instructions. It works for OFX 2.x and QFX files that start with <?xml.
Work on a copy. If the file picker only shows .xml files, rename the copy from .ofx or .qfx to .xml.
In Excel, go to Data, then Get Data, then From File, then From XML, and pick the file.
In the Navigator, open OFX, then BANKMSGSRSV1 (or CREDITCARDMSGSRSV1 for a card), and keep opening until you reach the table named STMTTRN.
Select Transform Data. Remove the columns you don’t need and keep DTPOSTED, TRNAMT, NAME, MEMO and FITID.
Change TRNAMT to Decimal Number. Leave DTPOSTED as text for now; it gets converted in the sheet.
Select Close & Load. Add up the amount column and compare the result with the balance check in the worked example below.
ROUTES COMPARED
Four routes that cost nothing, checked against each tool’s own documentation on October 22, 2026:
| Route | Reads | How | Good for |
|---|---|---|---|
| Excel Power Query | OFX 2.x and QFX in XML only | Data › Get Data › From File › From XML | One file now and then, if it is XML |
| GnuCash (free desktop accounting) | OFX and QFX, 1.x and 2.x | File › Import › OFX/QFX into an account, then File › Export › Export Transactions to CSV | Any version, no coding |
| ofxtools (Python library) | OFX 1.x and 2.x; QFX, ignoring Intuit’s own tags | A short script on Python 3.10 or later | Many files, repeatable |
| The PDF statement instead | Whatever the bank prints | Convert the PDF to Excel or CSV | Months older than the bank’s download window |
GnuCash makes you create an account to import into and then matches each transaction against what is already there. That matching is handy for duplicates but adds a step if all you want is a spreadsheet. Online OFX converters exist too; read their privacy terms before uploading a bank file.
WORKED EXAMPLE
Saltmarsh Kayak Rentals is a fictional business. It downloaded September twice: once for September 1 to 15 (7 transactions) and once for September 10 to 30 (9 transactions). Here are the three rows from the overlap, as they arrive and after clean-up:
| FITID | DTPOSTED (raw) | Date (cleaned) | TRNAMT | NAME | In both files? |
|---|---|---|---|---|---|
| 202609100001 | 20260910120000.000 | 09/10/2026 | -64.18 | CHEVRON 0091 | Yes |
| 202609120002 | 20260912120000.000 | 09/12/2026 | 420.00 | SQUARE DEPOSIT | Yes |
| 202609150003 | 20260915120000.000 | 09/15/2026 | -118.50 | COSTCO WHSE 0412 | Yes |
| Duplicated amount | 237.32 |
Stacked together the two downloads give 7 + 9 = 16 rows, but 3 share a FITID, so there are 13 real transactions. Remove duplicates on the FITID column, not the amount, since two genuine 4.50 coffees on one day share an amount. The proof: the 13 rows bring in 4,860.00 and send out 3,914.70, a net 945.30, and the August 31 balance of 5,200.00 + 945.30 = 6,145.30, the LEDGERBAL in the second file. Leave the duplicates in and the total is 6,382.62, off by exactly the 237.32 above.
PITFALLS
DTPOSTED is year, month, day, then often a time and a time zone in brackets. Turn it into a real date with =DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2)) and format the column as a date.
OFX signs amounts from the account holder’s side: a card purchase is negative and a payment to the card is positive. If your spreadsheet or software treats card charges as positive, flip the sign for the whole column, not row by row.
Banks let you pick any date range, so downloads overlap. FITID is the bank’s unique ID for each transaction, which makes it the column to de-duplicate on.
If SUM returns 0, the amounts came in as text. Set the column type to Decimal Number in Power Query, or use =VALUE() in a helper column.
The OFX specification says the sign of TRNAMT sets the direction, and the TRNTYPE label doesn’t change it. Sort money in from money out on the amount, not the label.
Banks often limit how far back an OFX or QFX download can go. Older months usually exist only as PDF statements, which the routes above can’t read.
WHERE ZERABOOKS FITS
ZeraBooks Convert doesn’t read or make OFX or QFX files. It reads PDF statements, which is often what you have for older months.
PDF TO EXCEL
Upload the PDF and Convert reads every transaction, splits the statement by account and cardholder, and downloads Excel or CSV. The Standard format gives Date, Description and Amount with money out as a minus and US dates, so there is no date formula and no FITID clean-up. More on that route in convert a bank statement to Excel.
Download formatsTHE BALANCE CHECK, BUILT IN
The check Saltmarsh did by hand, opening balance plus transactions equals closing balance, is what Convert runs on every account before you download. Every statement is checked against its printed opening and closing balance, to the cent. One that doesn’t tie is flagged for review, with the difference shown.
How Convert proves a statement
MORE FILE FORMATS
If the file’s first line starts with <?xml, use Data, Get Data, From File, From XML in Excel, then drill down to the STMTTRN table. If it starts with OFXHEADER:100, it is SGML and Excel can’t read it directly; convert it to CSV with a free tool such as GnuCash first.
Barely. QFX is OFX with an extra Intuit bank code and a few Intuit tags. The same routes work, and the transactions sit in the same STMTTRN records. ofxtools reads QFX but ignores Intuit’s extra tags. A .qbo file is the same idea; see QBO to CSV.
Yes. GnuCash is free: import the OFX into an account, then use File, Export, Export Transactions to CSV. For OFX 2.x files, Excel can load the data and save it as CSV. Programmers can use the free ofxtools Python library. Going the other way is covered in CSV to OFX.
OFX writes dates as a run of digits, year first, often with a time and time zone after it. Excel reads that as text or a huge number. Take the first eight digits apart with LEFT and MID inside DATE.
Remove duplicates on the FITID column. FITID is the bank’s unique ID for each transaction, so a transaction that appears in two overlapping downloads has the same FITID in both. Don’t de-duplicate on amount or description; genuine repeats share those.
No. ZeraBooks Convert reads PDF statements and downloads Excel or CSV; it doesn’t read or make OFX, QFX or QBO files. If you have the PDF statements for the same months, Convert reads them and checks each account against the printed balances.
FREE SETUP + CATCH-UP
ZeraBooks Convert reads PDF bank and card statements and checks every account to the cent before you download. 7-day trial, then $79 a month billed annually; see ZeraBooks pricing. To go from statements to books instead of spreadsheets, see AI bookkeeping.