How to Open an OFX File in Excel

TL;DR: Excel cannot read an OFX file properly on its own. OFX 1.x is SGML, not XML, so Excel's XML import usually fails on it outright, and opening the file directly shows a wall of tags instead of transactions. The reliable route is to convert the OFX to CSV first, then open that CSV in Excel. You get one row per transaction with clean date, description, amount, and balance columns, ready to sort, filter, and categorize.

Why won't my OFX file open in Excel?

Because OFX was never designed to be read by a spreadsheet. It is a data interchange format built so finance programs could load transactions into an account register automatically. When you point Excel at one, Excel has no idea that a DTPOSTED tag means a date or that TRNAMT means an amount. It shows you the raw markup, or it throws an error, or it produces dozens of near empty columns you then have to clean up by hand.

There is a second wrinkle that catches people out. There are two generations of the format, and they are structurally different:

This is the part most guides skip. They tell you to use Data then Get Data then From XML, and that advice only has a chance of working on OFX 2.x. Most files handed out by US banks are still OFX 1.x, where tags like <TRNAMT>-42.10 are simply never closed. An XML parser rejects that. So if you tried the XML route and got an error, your file was not corrupt. It was just not XML in the first place.

How do I open an OFX file in Excel?

Convert the OFX to CSV, then open the CSV in Excel. That is the short answer, and it is the only method that works the same way on both OFX 1.x and 2.x without manual cleanup. Upload the OFX to an OFX to CSV converter, download the CSV, then in Excel go to the Data tab, choose From Text/CSV, pick the file, and import. You end up with a normal table you can actually work with.

Below are the three approaches people try, and what each one actually gets you.

Method 1: Convert to CSV first (recommended)

This is the approach that produces a clean, reconcilable sheet every time. A converter reads the OFX tags and maps them to spreadsheet columns: the DTPOSTED stamp becomes a readable date, NAME and MEMO collapse into a description, TRNAMT becomes a signed amount, and any ledger balance carries through as a running balance column. Import that CSV through the Data tab rather than double clicking it, so Excel lets you set the column types instead of guessing.

One detail worth setting deliberately during import: force the description column to Text. If you let Excel guess, it will occasionally reinterpret a reference number in a payee string as a date or a number in scientific notation, and you will not notice until the figures stop tying out.

Method 2: Power Query as XML (OFX 2.x only)

If you know your file is OFX 2.x, Data then Get Data then From File then From XML will parse it. Expect work afterwards. Power Query surfaces the sign-on and account blocks as nested tables you have to expand, and you will be deleting a lot of columns that carry no transaction data. It is workable for a one off, but if you do this monthly it is a poor trade against a converter that hands you the columns directly.

Method 3: Open as text and parse by hand

You can rename the file to .txt and open it in Excel's text import wizard, or open it in Notepad to inspect it. This is genuinely useful for diagnosing a bad file, since you can see whether the transactions are actually present and whether the dates look sane. It is not a practical way to build a spreadsheet. Extracting a few hundred transactions from tag soup by hand is exactly the kind of task where a transposition error slips in unnoticed.

Can I open an OFX file in Excel without converting it?

Technically yes, and practically no. Excel will display the contents of an OFX file if you force it open as text or, for OFX 2.x, as an XML table. But displaying is not the same as using. What you get is the markup rather than a usable table, with transaction fields scattered across columns that need manual restructuring before any total, filter, or pivot means anything. If your goal is to analyze the transactions, converting first saves time from the very first file.

What columns should the converted spreadsheet have?

A good conversion gives you the fields that make the sheet reconcilable, not just a dump of every tag in the file. These are the ones that matter:

That last row is the one to check before you trust the file. Compare the final balance in the sheet against the closing balance on your statement. If they match, nothing dropped during conversion. If they do not, you have a gap to find before you build anything on top of the data.

Why is my OFX amount column not adding up?

Usually because Excel imported the amounts as text rather than numbers. The giveaway is values sitting left aligned in the cell with SUM returning zero. It happens when a leading apostrophe, a stray space, or a non standard minus sign comes through in the file. Select the column, use Data then Text to Columns, and finish the wizard with the type set to General to force a numeric conversion.

The other cause is sign convention. OFX signs debits negative, so a column of card charges should sum to a negative number. If your total looks inflated and positive, something in the pipeline stripped the minus signs, and the running balance will not reconcile until you restore them.

What about QFX files?

QFX is Intuit's Quicken specific variant of OFX. Structurally it is the same format with some extra Quicken fields, so everything above applies: Excel will not read it natively, and converting to CSV is the practical path. If you have a QFX exported from Quicken or downloaded from your bank, treat it exactly as you would an OFX.

Starting from a PDF instead of an OFX?

Plenty of people looking for this end up somewhere slightly different: they do not have an OFX at all, just a statement PDF, and what they actually want is the numbers in a sheet. In that case skip the OFX step entirely and turn the PDF statement straight into a spreadsheet, which avoids one conversion and one chance for something to go wrong. If your software needs an account file rather than a sheet, the bank statement to OFX converter goes the other direction and builds the OFX from the PDF.

The practical workflow

If you do this regularly, the sequence that holds up month after month is short. Convert the OFX to CSV. Import it into Excel through the Data tab rather than double clicking, setting the description column to Text. Check the final balance against the statement. Then build your categories, pivots, and totals on top of a sheet you have already proven is complete.

That balance check is worth the ten seconds. Every downstream number you produce inherits any error in the import, and it is far cheaper to catch a missing page now than to trace a reconciliation difference three steps later.