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:
- 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.
- 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.
- 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:CCardNow 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)
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.
- 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)
- Now all that you have to do is accept the transactions that you just imported from the QIF:
(Click to Enlarge)
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. 
Whether you’re trying to import Microsoft Money data or import an Excel spreadsheet this trick should be just what you’re looking for!
This is just one of the hundreds of CyberNotes we have done. You can find more of them by visiting our CyberNotes category, or by subscribing to our CyberNotes feed. We also have a full feed available if you want to receive all of our articles in your reader!
Tags: CyberNotes, Software, How To, Tips And Tricks
Related Posts:
- Your Week in Review with Sunday’s Summary
- CyberNotes: Fast Uploads to FTP, Imageshack, TinyPic, and Rapidshare
- Quicken Online - How do you Manage your Finances?
- Your Week in Review with Sunday’s Summary
- Import Documents from Google Docs to Zoho
I think another way to do this is to upload the QIF file to Wesabe.com and then export it as an OFX file. I haven’t tried this myself, so can’t promise that it works. But Wesabe is said to have very good import/export functions.
That might work…thanks for the info.
I just upgraded from Quicken 2008 from the 2002 version. I was sure disappointed that I could not import QIF files. Your instructions worked for me. Thanks a million!
I can’t believe that Quicken does not want to fix this major customer dissatisfaction item.
It is pretty idiotic, but I was just thankful that I was able to find a workaround. Glad it worked for you, too.
I was unable to see the transactions in teh “accept field” for downloaded transactions, I do not know where it went thogh it said opened 27 items successfully.
Thanks a lot for this workaround. It worked like a charm and I was able to transfer stuff from MS Money in a similar fashion. Appreciate.
Dude - you rock! It works like a charm. I guess I’ll stay with Intuit a little longe. Thanks.
I’m glad to see this is helping some other people out there!
Has anyone gotten this to work for investment accounts (specifically 401k)? After I import, I get a message stating x number of transactions were imported, but when I go to verify…nothing is there.
Any ideas?
Sounds like it’s either being put in a different account, or something isn’t right in your file.
I’m trying to use this workaround to import .QIF data from Quicken 2002 to the 2006 version. When I go to import after making the changes, I don’t get the “All Accounts” option. Does this workaround not work with 2006? Do I need to upgrade to 2008? Thanks.
Unfortunately I don’t have version 2006 to test whether it works or not. Sorry.
While all this is true, it looks like all the top part is unnecessary. I think if you just put:
!Account
NMy Mastercard
TCCard
^
!Type:CCard
Where the name of your CC in Quicken is My Mastercard, and followed the same steps, it would also work. The previous steps (Autoswitch, clear, etc) are just to import your account lists, if I am not mistaken….
It could be possible to do it that way, but I did some research as to how these files are created and many of those things Quicken likes to have. I guess if my method doesn’t work for someone they can also try yours. Thanks for the heads up!
Thank you! This worked perfectly for getting my PayPal history into Quicken 2007.
Great tutorial, thanks! Dunno why qif was removed from Quicken anyway. That decision was retarded.
I have Quicken 2008 - it requires me to select an account. If I just leave the selection it will not allow me to proceed. Any suggestions? I’m disappointed this won’t work.
after downloading XL2QIF and try to unzip it, I get a error saying that it is corrupted. Do you have another copy? Thank-you
I’m guessing that you didn’t provide the account name in the file.
Unfortunately I do not have another copy.
>I have Quicken 2008 - it requires me to select an account.<
Make sure you scroll to the top of the drop-down list - that’s where “All Accounts” is located, at the very top.
Thanks in advance.
Maybe search the help in Quicken 2008. There’s gotta be an import option somewhere.
THANK YOU! Yes, I shouted on purpose. I just updated my 401k in Quicken 2008 after 2-1/2 years of placeholder entries because there was no way I was entering everything by hand. By the way, when using this method in 2008 it doesn’t matter which account you select while importing. I had accidentally chosen my cash account, and it prompted me that it found data for my 401k account, and asked if I wanted to import it. Great solution.
Yep, that’s because it stored the account information in the text file that you created. I’m really glad that it worked for you especially after not having imported the data for so many years. Chalk that up as another success story!
Thank you!!!!! I searched a while ago to see if I could find a solution but nothing worked, except your solution works flawlessly. This solution works for those who need or want to move from Money Plus to Quicken 2008. Well Done!
Ryan,
Great tool! One problem I am hoping you can help me with. No matter what format for the date I am using (ie MM/DD/YYYY, DD/MM/YYYY), the converter is always putting the DD at the beginning of the transaction. Any ideas?
Thanks,
Chris
Almost worked for me! Similar problem to Chris on 2 September. I’m in NZ. My bank is dd/mm/yy and I download in dd/mm/yy QIF (all they have). My 2008 Quicken Premier is set to dd/mm/yyyy. The download as seen on notepad is fine - dd/mm/yy, but when I import it to Quicken, I get mm/dd/19yy (and any dd over 12 blanks out the date as quicken thinks it is mm. Any ideas?
So,with all the free advice,how do you make a living? I’d be glad to contribute.
Cheers,
Doug
We make money with advertisements on the site. Feel free to visit any of our sponsors to help support us.
I cannot get quicken to recognize the file type. It’s now a text file. Am I doing something wrong when saving the file? I receive the message, “please enter filename”
I am having the same problem as Doug. Any resolution?? It’s importing all the files as 1907 instead of 2007 !!! I don’t want to have to change each transaction one by one! Please help.
Thanks! ~Rochelle
The answer to the year problem of yy instead of yyyy, is to do a “find/replace”. Open the file in Notepad. Go to the Edit menu and click on “Replace” or use the keyboard shortcut(Ctrl-H) to bring up the Find and Replace dialog box. In the “Find What:” text field, type “/07″ and in the “Replace with:” text field type “/2007″, then save. It will then import properly as 2007, and not 1907.
Hi. I used your technique above, and it works great with one exception: it doesn’t import transactions that have an account name after the “L” as opposed to a category. I have a QIF file from PayTrust, which I edited per your instructions. All of the payments that had a category name after the “L” imported into my checking account just fine. But all of the payments that have an account name enclosed in brackets ([]) after the “L” don’t import.
To try to narrow down the problem, I created a QIF file with a single transaction and set up new Quicken file with a dummy checking account in Quicken named TestChecking and a credit card account called Discover. Here is the body of the QIF file:
!Option:AutoSwitch
!Account
NTestChecking
TBank
^
!Clear:AutoSwitch
!Account
NTestChecking
TBank
^
!Type:Bank
D09/24/2008
T-244.0
PDiscover
N98765
L[Discover]
M Ref #:IBVB6FGS
^
I then go File->Import->QIF, select the file, click “Transactions”, “Account List” and then “Next”. I then get a message that says 1 transaction was imported successfully. But nothing is imported into my test account. I have tried deselecting “Account List” and “Special Handling”. Nothing works.
Any ideas? Thanks.