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.


 

UPDATE: BETA-TEST UTILITY RELEASED 12/9/2014

Q2XMLFor two and a half years I have received countless emails from readers who struggle with this process. While the steps are simple, the nature of the QFX format itself, the variance in banking systems’ formatting standards, and even the operating systems and text editors used all can cause the result to be a mal-formed XML file.

So, I’ve decided to release a simple Windows utility program that will convert banking QFX files into standard XML files that can be opened with Microsoft Excel or any other XML editing program. This is beta test, so I would like to hear from you if you’ve decided to give it a spin.

This application will only run on Windows 7 or newer, and both 32bit and 64bit versions are supported. And as always, there is no adware, third party software, bundled search utilities, or spyware in my software.

INSTRUCTIONS:

  1. Download the Q2XML Installer here: Q2XML_Installer.exe
    (
    NOTE: Due to increased security to prevent accidental downloads, you may receive warnings about downloading software to your computer or installing software from an “Unknown Publisher.”. This is normal. If I were making money by selling this software I would buy a certification license to have this software signed and recognized by Microsoft, but since I have developed and am distributing this for free I can’t justify the expense.)
  2. Run Q2XML_Installer.exe to install.
  3. Open Q2XML, then drag one or more QFX files from Windows Explorer onto it.
  4. Click the “Convert” button.

XML files will appear in the same folder as the QFX files. You can either open the XML files inside Q2XML by double-clicking the name in the list, or just open them from Windows Explorer.