Convert OFX to CSV and Excel
Beta This tool is in beta. It is tested on sample files, and details may still change. Review the result before relying on it.
Convert an OFX bank statement file to CSV or Excel for free. Klarbon reads the OFX 1.x or 2.x fields and shows the account and transactions first.
In short
Turn a bank statement file or a PDF statement into Excel, CSV, CAMT.053, MT940 or OFX. Klarbon reads a PDF statement into a draft; check the result before you use it. CSV and Excel free from bank files, otherwise 1 credit (€1.99).
Klarbon converts an OFX bank statement file into CSV or Excel (XLSX), free on the site. It reads each STMTTRN record, applies the corrections the file itself carries, and works out the opening balance from the ledger balance, the only balance OFX states. It reads OFX 1.x (SGML) and OFX 2.x (XML); reading is in beta. A PDF statement dropped here is read too.
Compare what Klarbon reads from a PDF with the original before you download.
How do I convert OFX to CSV?
- Download OFX in your online banking. Banks also label it Microsoft Money or Quicken (QFX, which works too).
- Drop the .ofx or .qfx file above.
- Compare the derived opening balance in the preview with the closing balance of your previous statement.
- Choose CSV or Excel and download it, free.
What is in an OFX statement?
After a short header (OFXHEADER:100 for 1.x, an XML declaration for 2.x), the statement sits in STMTRS, or CCSTMTRS for a card:
CURDEF, the currency, andBANKACCTFROMwithBANKIDandACCTID, the account;BANKTRANLISTwithDTSTARTandDTEND, the period the download covers;- one
STMTTRNper transaction:TRNTYPE,DTPOSTED,TRNAMT,FITID,NAMEorPAYEE,MEMO; LEDGERBALwithBALAMTandDTASOF, the balance at the end.
How do the fields become CSV columns?
| OFX field | Column |
|---|---|
DTPOSTED |
Booking date |
DTUSER |
Value date, when present |
TRNAMT |
Amount, with its sign; a decimal comma is read too |
NAME, or NAME inside PAYEE |
Counterparty |
MEMO, else NAME |
Description |
REFNUM, else CHECKNUM |
Reference |
When ACCTID is an IBAN, it becomes the account's IBAN; otherwise the bank ID, branch and account number make up the account. TRNTYPE and FITID are not columns, but FITID matters for corrections.
Corrections inside the file
A transaction with CORRECTACTION REPLACE takes the place of the one whose FITID it names in CORRECTFITID; DELETE removes it. Klarbon applies both when the corrected transaction is in the same file. When it came in an earlier download, the correction stays a row of its own and the preview says so, because one file alone cannot tell what it changes. A correction that points at itself or loops is refused as malformed.
When is a file refused?
When the bank's status code is not 0, when there is no LEDGERBAL, or when a transaction is posted after the ledger balance's date, since that balance cannot include it. Each case gets one plain sentence in the preview.
What does it cost?
Opening the file is free within the daily limit, 10 a day without an account and 30 with a free one, and CSV and Excel from it are free. Writing CAMT.053 or MT940 from it costs one credit per started 500 transactions. A PDF statement dropped here costs one credit per started 10 pages for any download, CSV and Excel included.
Related
Frequently asked questions
Which OFX versions does Klarbon read?
OFX 1.x, the SGML style where a value needs no closing tag, and OFX 2.x in XML. Quicken's QFX files and credit card statements read the same way. Reading is in beta.
Why does the balance check say less for OFX?
OFX states only the ledger balance at the end. Klarbon works the opening balance out from it and marks it as derived, so the check cannot catch a missing transaction. Compare the opening balance with your previous statement.
My file lists the newest transactions first. Is that a problem?
No. OFX has no running balance, so Klarbon sorts the rows by posting date, oldest first, keeping the bank's order within a day.
Klarbon says the file reports an error. What does that mean?
The bank's server put an error status in the file instead of a statement. Download the file again, and ask your bank if it happens twice.
Sources
- Financial Data Exchange, OFX Banking Specification 2.3 (PDF) financialdataexchange.org
- IETF, RFC 4180: Common Format and MIME Type for CSV Files rfc-editor.org
Related
- Open any e-invoice. Check it. Convert it.
- Klarbon help centre
- What is OFX?
- Bank statement tools
- Supported bank statement files
- Why do my statement balances not match?
- Convert CAMT.053 to Excel and CSV
- Convert MT940 to Excel and CSV
- Convert CAMT.053 to MT940, or MT940 to CAMT.053
- Convert a CODA file to Excel and CSV
- Simple pricing for invoices and bank statements
- What happens to my files?
- E-invoice tools
- Understanding Bank CSV export
- Understanding Excel workbook (XLSX)
- Convert a CAMT.053, MT940 or OFX bank statement to Excel
- Convert a CAMT.053, MT940 or OFX bank statement to CSV
- Convert a bank statement PDF to OFX
Also as an API: Bank statement API