Mind. Music. Muse.

Import Quicken QFX files into Excel

Let me guess: You are searching for a way to import QFX (typically used for financial programs like Quicken, QuickBooks, or Microsoft Money) into Excel, but have been frustrated because the only thing you seem to find is websites telling you to pay for a utility to do this conversion for you.

Well, I have good news: you can do this very easily yourself. It will cost you nothing, and you don’t need anything other than what is already on your computer.

WHY I NEEDED TO DO THIS

I’ve recent moved away from using Quicken to do my bills, because I realized that I’m spending money every year for a program that lets me do pretty much what my credit union already provides online. I was spend a lot of time looking at my bills and reconciling accounts and realized something: I was putting all this effort into analyzing my spending after the fact, when what I wanted to do was proactively plan and forecast my spending.

So I dumped Quicken, and starting using Excel to do a forecast of the next 6 pay periods. Instead of spending hours figuring out where my money went and why, I can now easily see that my next pay periods would leave me with X dollars for discretionary spending.

But, sometimes it’s nice to be able to do some reporting. For instance, how much am I really spending on dining and groceries and gas? Time to import into Excel.

HERE’S HOW TO DO IT

STEP 1: Go to your bank’s site and download your transactions. Every bank offers this capability today, so just specify the account(s) you want to download and the time period, and save the QFX file to your computer.

STEP 2: Unlike all those commercial companies that make it seem like QFX is a very difficult format to deal with, it really isn’t. It’s actually just a plain vanilla XML file that has a non-standard header!

STEP 3: Open the QFX file with a text editor. For Windows users, this will be Notepad. For Mac users, this is TextEdit.

STEP 4: You will see several lines of text, followed by a line that contains <OFX>. It will look something like this:

OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

<OFX>

The <OFX> tag is the beginning of the actual XML data that Excel understands, so we just need to get rid of that no-xml info above it.

STEP 5: Replace EVERYTHING above the <OFX> line with this:
<?xml version=”1.0″ encoding=”ISO-8859-1″ ?>

Now the start of your QFX file looks like this:
<?xml version=”1.0″ encoding=”ISO-8859-1″ ?>
<OFX>

STEP 6: Save this as a new file, and change the extension from QFX to XML.

STEP 7: Open Excel, and then open your new file as an XML Table.

Ta-da! Simple, isn’t it?

You will have a bunch of columns and data that you probably don’t need, but if you are using Excel, I’m sure you know enough to delete the columns you don’t need, and change the number formats of the others to suit your needs.