CyberNotes
Tutorial Thursday




I was recently approached by a friend who had just installed Quicken 2008 onto his computer, and previously he had kept his transactions in a complex Microsoft Excel spreadsheet much like myself. He thought that the import process would be a piece of cake, but that didn’t turn out to be the case since Intuit, the parent company behind Quicken, decided to start phasing out Quicken Interchange Format (QIF) file support a few years ago.

The decision came about because QIF was seen to have many inefficiencies that the new format, Open Financial Exchange (OFX), is able to conquer. Intuit wanted to make it extremely difficult to import QIF files into Quicken 2008 (as well as 2005, 2006, and 2007) so that banks and credit card companies would start using the new format. This has angered many people because some banks only provide their data in the QIF format.

But wait, why do I even care about QIF if I’m trying to import data from Excel, which doesn’t have the ability to save a QIF. Actually there is a nifty little add-on for Excel called XL2QIF that will convert your spreadsheet to a QIF file using the data that you specify. In just a few seconds I had the Excel data converted into the needed format.

TIP: All QIF files can be edited and examined in any text editor, such as Notepad or WordPad. This is important because we’ll be doing some manual editing in a minute, but don’t be intimidated because it is pretty straightforward.

Here are the steps you need to take to import QIF files into Quicken 2008:

  1. Open Quicken and create an account for the data that you want to import. You don’t have to create another account if you already have one made.
  2. Now go to File -> Export -> QIF File, and select the account that you want to deal with from the Quicken Account to Export from. This should be the account that you created/used in Step 1. It’s crucial that you check the Account List box.
    Quicken QIF Export
  3. Now open the QIF file from Step 2 in a text editor, such as Notepad. Copy all of the text up to the line that specifies the date for the first transaction (highlighted in yellow below). The data we want should look something like this:

    !Option:AutoSwitch
    !Account
    NCyberNet Credit Card
    TCCard
    L0.00
    ^
    !Clear:AutoSwitch
    !Account
    NCyberNet Credit Card
    TCCard
    ^
    !Type:CCard

    Now open the QIF file that you want to import. Paste the text that you just copied, and replace all of the data up to the date on the first transaction. The entire process should look something like this:

    (Click to Enlarge)
    Quicken QIF File Merge

    Wondering why you just did that? The data that you just copied is used by Quicken to identify which account the transactions belong to. The original file was missing that critical information, and without it Quicken will have no idea where to put the transactions. In older versions of Quicken it would let you select which account to apply the transactions to, but that doesn’t work anymore. Needless to say it’s an important part of the process if you want to import a QIF file into Quicken.

  4. In Quicken go to File -> Import -> QIF File. Browse for the file location on your computer, select All Accounts as the account to import into, and make sure the Account List box is checked. Then hit the Next button!

    (Click to Enlarge)
    Quicken QIF Import

  5. Now all that you have to do is accept the transactions that you just imported from the QIF:

    (Click to Enlarge)
    Quicken QIF Accept Transactions

It may seem like a long process, but once you understand how it works you can probably import a QIF file into Quicken in under 30 seconds. Just save the account information that you copied in Step 3 if you regularly download QIF files from your bank. By saving that information you will never have to go through the export process in Step 2.

Believe it or not it took me upwards of two hours to figure out that this trick would work. I tried dozens of different techniques mentioned around the web, and then I finally figured that if Quicken can still export the data as a QIF that it should also be able to import it. Turns out that I was right. :D

Whether you’re trying to import Microsoft Money data or import an Excel spreadsheet this trick should be just what you’re looking for!