How to convert an OFX file to a spreadsheet, four ways

 You can turn an OFX file into a spreadsheet in four ways: download a CSV from the bank instead, load a version 2.x file into Excel as XML, run a short script for a version 1.x file, or skip the OFX and convert the PDF statement. Which one works depends mostly on the first line of your file.

An OFX file is a bank download in Open Financial Exchange format. Each transaction sits in a record called STMTTRN with a posting date (DTPOSTED), a signed amount (TRNAMT), a unique ID (FITID) and a payee (NAME). Those four fields become your spreadsheet columns. Most files also carry a MEMO with extra description, which is worth keeping because NAME is limited to 32 characters and long payee names get cut.

Before you start: which version is it?

Open the file in Notepad or TextEdit and look at the top.

  • OFXHEADER:100 on the first line means a 1.x file, in SGML. It isn't valid XML.
  • A line starting with <?xml means a 2.x file. It is valid XML.

Option 1: ask the bank for a CSV

Go back to the download screen. The page that offers OFX very often offers CSV for the same date range. A CSV opens straight in Excel or Google Sheets. The trade-off is that bank CSVs rarely include balances, so you can't check the totals against anything.

Option 2: open a 2.x file in Excel

  1. In Excel, go to Data, then Get Data, then From File, then From XML.
  2. Choose the .ofx file. If the file picker hides it, switch the file type filter to all files.
  3. In the Power Query window, expand the nested blocks until you reach the STMTTRN rows. For a bank account the path runs through BANKMSGSRSV1 and STMTRS; for a credit card, through CREDITCARDMSGSRSV1 and CCSTMTRS. Both end in BANKTRANLIST.
  4. Keep DTPOSTED, TRNAMT, NAME and MEMO. Remove the rest.
  5. Load the result to a sheet.

Then fix the dates. Excel reads 20260914 as a number, so sorting works but date filters and pivot tables by month don't. In a new column, use =DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2)) with A2 pointing at the date cell. If some dates carry a time, like 20260914093000.000[-5:CDT], the formula still works because it only reads the first eight characters.

Option 3: script a 1.x file

A 1.x file won't load as XML because values like <TRNAMT>-64.18 have no closing tag. The open-source Python library ofxtools reads both versions, plus QFX files, and gives you each transaction as an object you can write out as CSV. If you aren't comfortable with Python, this is the point to try option 1 or option 4.

Watch the signs on credit cards

In OFX, a card purchase is negative and a payment to the card is positive. If you are building a spending sheet, you may want to flip the column so purchases read as positive spending. Do it to the whole column, not row by row, and don't use the TRNTYPE field to decide. It's only a label, and the same INT type appears on interest earned and interest charged.

Check for duplicates

If you downloaded overlapping date ranges, the same transaction can appear twice. Sort by FITID and remove repeats. Don't remove rows just because the date and amount match, since two identical purchases on one day are common.

Option 4: start from the PDF statement

If you have the statement PDF, ZeraBooks can convert it to Excel or CSV directly and check each statement against its printed opening and closing balance first. That check isn't possible with an OFX file alone, because the file has a closing balance but no opening one. ZeraBooks doesn't read or create OFX files, so this route skips the OFX completely. The steps are here: https://www.zerabooks.com/convert-bank-statement-to-csv

For what each OFX tag means and an annotated sample file, see the ZeraBooks OFX guide: https://www.zerabooks.com/what-is-an-ofx-file

Comments

Post a Comment

Popular posts from this blog

How to clean up messy books without redoing the same month twice

Image
Clean up books in this order: collect every statement , set opening balances from the last period your accountant signed off, then reconcile each account one month at a time, oldest first . Only once a month ties do you pair transfers, remove duplicates, place uncategorized lines and move owner spending out. A different order means categorizing lines you later delete. A month that doesn't tie, with the gap shown. During a cleanup you stay on that month until the gap is zero. The worked figures below belong to a fictional design studio whose books stopped being kept in February. They are illustrative only. What has to happen before any categorizing? Step 1: Build a statement grid Accounts down the side, months across the top. Tick a cell when you hold that month's statement as a PDF from the bank. Our studio has checking, savings and one card across eight months, so the grid has 24 cells. Any blank cell is a month you can't prove yet, so request it today. Activity...
Read more

How to convert a credit card statement PDF to Excel, step by step

 You need last quarter's card charges in a spreadsheet by Friday, and all you have is three PDFs from the issuer's website. To convert a credit card statement to Excel, download the PDF statement (not the activity export), run it through a converter or copy the tables out by hand, then fix three things: the signs on payments, the foreign currency detail lines, and the summary blocks. Finish by checking that your rows rebuild the printed new balance. Here's each step. Step 1: Download the statement PDF Go to the card's statements page on the issuer's site and download the monthly statement. Many issuers also offer a "download activity" CSV. Skip it for this job. It usually has no previous or new balance printed anywhere, and those two numbers are how you'll prove the spreadsheet is complete at the end. Watch the dates. A card statement ends on a closing date, not the end of the month. If you need the full quarter, you may need the statement after it too...
Read more

How to scan a bank statement so it converts to Excel cleanly

Image
To scan a bank statement so it converts to Excel cleanly, use 300 dpi and grayscale , keep pages straight, save each account's pages into a single PDF , and include the last page even if it looks empty. Then convert it and check that the opening balance plus the rows equals the printed closing balance. Most conversion problems with scanned statements are decided before the conversion starts, at the scanner. Here is how to get it right, step by step. Where a scanned PDF goes in ZeraBooks Convert: the Upload button, or drop the file on the page. Do you need to scan the statement at all? Step 1: Check online banking first Log in and look at the statements section. If the month is there, download the PDF. A downloaded statement carries the bank's own text and will always beat a scan. What scanner settings work best for bank statements? Step 2: Set the scanner Setting Use Why Resolution 300 dpi Lower loses faint digits; higher only makes bigger files Colo...
Read more