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!

There Are 74 Comments

  1. 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.

    • I tried import of a Quicken 2002 exported QIF to Wesabe, didn’t work. Sent an email to them.

    • Quicken 98. I was able to download a OFX file from my bank on line. There is no way (that I could find) to import it from my download file to Q98.

  2. That might work…thanks for the info.

    • I have Quicken 2008 and have been able to import QIF files in the past. The QIF option appears in the Import box but when I click on Next after indicating the file to import, nothing happens. I am using a Cash account so it should work. Quicken support just tells me that QIF is no longer supported. Will your method work or did Quicken find a way to prevent QIF import into any account? Thanks.

  3. 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.

  4. Ken wrote:
    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 can not believe this either This may be my last year trying to make quicken work for me it has become a burden it is no longer a user freindly tool. It is easier to switch to another software than to switch banks. TOOO BAAAD Quicken you suck!!!!

  5. 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.

  6. 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.

  7. Dude – you rock! It works like a charm. I guess I’ll stay with Intuit a little longe. Thanks.

  8. I’m glad to see this is helping some other people out there!

  9. 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?

  10. Jay wrote:
    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.

  11. 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.

  12. Chris wrote:
    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.

  13. 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….

  14. Scoobydeux wrote:
    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!

  15. Thank you! This worked perfectly for getting my PayPal history into Quicken 2007.

  16. Great tutorial, thanks! Dunno why qif was removed from Quicken anyway. That decision was retarded.

  17. 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.

    • I have the same issue as you do. My checking account (or any other account) is not listed. all I see is when I click it there is no accounts listed. Just noticed that there is a statement that you cant import a qif to checking, savings, 401K, credit card and all other brokerage accounts. It wants a OFX file but I can’t find a way to get the OFX file to import either.

  18. after downloading XL2QIF and try to unzip it, I get a error saying that it is corrupted. Do you have another copy? Thank-you

  19. Anonymous wrote:
    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.

    I’m guessing that you didn’t provide the account name in the file.

    sfc wrote:
    after downloading XL2QIF and try to unzip it, I get a error saying that it is corrupted. Do you have another copy? Thank-you

    Unfortunately I do not have another copy.

  20. >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.

  21. :D YES!! I love it. Thank you, Ryan, it worked like a charm.

  22. :?: I must be missing something! You say to go to Quicken, File> Import> QIF File. But, on my 2008 version of Quicken, there is no option for importing QIF. Just 3 others…Please help me out! I’m despirate to do this. (Or, is it that when you do the editing outside of Quicken it magically appears in the Quicken menu?? I’m confused!
    Thanks in advance.

  23. murrayphd wrote:
    :?: I must be missing something! You say to go to Quicken, File> Import> QIF File. But, on my 2008 version of Quicken, there is no option for importing QIF. Just 3 others…Please help me out! I’m despirate to do this. (Or, is it that when you do the editing outside of Quicken it magically appears in the Quicken menu?? I’m confused!
    Thanks in advance.

    Maybe search the help in Quicken 2008. There’s gotta be an import option somewhere.

  24. 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.

  25. Steve wrote:
    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!

  26. 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!

  27. 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

  28. 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

  29. Doug wrote:
    So,with all the free advice,how do you make a living? I’d be glad to contribute.

    We make money with advertisements on the site. Feel free to visit any of our sponsors to help support us.

  30. 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”

  31. Doug wrote:
    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

    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

  32. 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.

  33. 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.

  34. I have Quicken Personal Plus (Australian)it will let me import .qif files but I want to export .qif files (to transfer to Mac Quicken) but the only export format is for ‘addresses’

    Any ideas? Thankx

  35. I just purchased Quicken 2009 Home & Business and you can no longer import.qif into Checking, savings, credit cards, 401k and brokerage accounts.

  36. gid – I have the same version.
    You may not have read the article carefully. It describes a workaround for that very problem. With it you can import into those accounts.
    If you look at the screenshot at step 4 that is exactly what the dialog displays under the question “Don’t see your account?”

    Good luck.

  37. Thank you so much for your tip. I had this issue with several banking centers that only offered the QIf format. This also worked well for importing PAYPAL data. Hopefully Quicken/Intuit and the banks will get their act together and make everything work easier.

    Hey Intuit – don’t discontinue something before the new format has been widely adopted – it might ‘force’ the banks to change, but it also leaves your customers ‘forced’ to waste time looking for solutions and possibly other software.

    • Do you really believe that Intuit-Quicken cares? It appers to me that they are primarily interested in getting you on-line so that they can collect server/service fees and resurect “corrupted files”..at a fee, of course.
      I had a billminder go bad on me in Q-2007 and then agaon in q-2009 a couple of years later. They made the same suggestions both times…didn’t work either time. I’m trying to go back to 2006, which is the last working (reasonably well)version I have

  38. Great work-around that has convinced me to upgrade from 2004 to 2009 (Quicken user since 1992). I just downloaded xl2qif and it doesn’t seem to handle splits. Also, it appears from the xl2qif input panel that only one tier of sub-category can be processed. Another conversion product (from Big Red Consulting) appears to handle both of the aforementioned issues quite well, but costs $49 (too much, in my opinion, especially since Quicken Premiere 2009 currently sells for $60). Does anybody know of a less expensive product that works as well as the BRC add-on? Thanks…

  39. This is very interesting information. After using quicken for 5 years I switched to Microsoft Money 2 years ago because of the QIF import issue, I would go back to quicken in a heart beat if my bank & quicken could solve this problem. I was angry then & I’m still irritated over Intuits’ policies on this.

  40. If this helps anyone you can use this VBS script to quiclky modify the QIF file everytime you export using EXCEL to QIF addon.

    ‘Change to path of QIF file to convert
    strFileName = “C:\Path to QIF File\file.qif”
    ‘Change to your account name
    strAccount = “NBarclays”
    ‘Change to whatever currently appears in the top of the QIF file
    strOldText = “!Bank”

    Set objFSO = CreateObject(“Scripting.FileSystemObject”)
    Set objFile = objFSO.OpenTextFile(strFileName, 1)

    strQIF = “!Option:AutoSwitch” & VbCrLf & “!Account” & VbCrLf & strAccount & VbCrLf _
    & “TChecking” & VbCrLf & “^” & VbCrLf & “!Type:Checking”
    strNewText = strQIF

    strText = objFile.ReadAll

    objFile.Close
    strNewText = Replace(strText, strOldText, strNewText)

    Set objFile = objFSO.OpenTextFile(strFileName, 2)
    objFile.WriteLine strNewText
    objFile.Close

    msgbox strFileName & VbCrLf & VbCrLf & “Was successfully updated with the following:” & _
    VbCrLf & VbCrLf & strQIF

  41. Here is the updated version, a bit more robust with easier to change options. Tested with Quicken 2009:

    ‘Path to you QIF file
    strFileName = “C:\somepath\file.qif”

    ‘Old text to find and replace
    strOldText = “!Type:Bank”

    ‘Amend depending on your QIF file
    strLine1 = “!Option:AutoSwitch”
    strLine2 = “!Account”
    strLine3 = “NBarclays”
    strLine4 = “TChecking”
    strLine5 = “^”
    strLine6 = “!Type:Checking”

    ‘Set to Yes to open QIF file after conversion or No
    strOpenFile = “Yes”

    ‘No more configuration options here

    Set objFSO = CreateObject(“Scripting.FileSystemObject”)
    Set objFile = objFSO.OpenTextFile(strFileName, 1)

    strQIF = strLine1 & VbCrLf & strLine2 & VbCrLf & strLine3 & VbCrLf _
    & strLine4 & VbCrLf & strLine5 & VbCrLf & strLine6
    strNewText = strQIF

    strText = objFile.ReadAll

    objFile.Close

    strFound=InStr(1,strText,strOldText,0)

    If strFound = “1″ Then

    strNewText = Replace(strText, strOldText, strNewText)

    Set objFile = objFSO.OpenTextFile(strFileName, 2)
    objFile.WriteLine strNewText
    objFile.Close

    MsgBox strFileName & VbCrLf & VbCrLf & “Was successfully updated with the following:” & _
    VbCrLf & VbCrLf & strQIF,64,”Yeeeeehaaaaa!”

    If strOpenFile = “Yes” Then
    Set WshShell = CreateObject(“WScript.Shell”)
    WshShell.Run “”"%Windir%\notepad.exe”"” & strFileName, 1
    Set WshShell = Nothing
    End If

    Else

    MsgBox strOldText & VbCrLf & VbCrLf & “Was not found in:” & _
    VbCrLf & VbCrLf & strFileName,16,”Oh Noes!”

    End If

  42. Very helpful. I was thinking of switching to Microsoft Money, but that product has been discontinued. This work around of yours addresses one major issue I had with Quicken.

  43. I have Quicken 2009 Starter Edition and when I get to step 4, there is no “all accounts” to choose. The box is empty.
    Any thoughts?

    thanks,
    john

  44. Great solution!

    Operating System: Windows Vista
    Quicken Version: Quicken Home & Business 2009
    QIF File: Download from PayPal account

  45. Got Money 2006 Canadian on my computer and want to upgrade to Quicken 2010. Am told that must upgrade to Quicken 2007/08 or Money 2007/08 before. Trouble is, Money 2007/08 doesn’t come in a Canadian version and Quicken 2007/08 Canadian version is impossible to find. Don’t want to download from some site I don’t know. Any chance you or someone else has a solution or knows of a place in Canada where I can get Quicken Canadian 2007/08. Tried Ebay and Amazon but no luck. Thanks for any help.

  46. My husband and I just realized that we don’t have July -Oct for 2008 and 2009 in Quicken. Is there an easier way to get the data then to enter the data manually from our bank statement. Thanks for you help.

  47. Thanks for this information.

    In general, I find Quick 2009 to be a major disappointment – the interface reminds me of software designed 10 years ago, and its inability to import from Excel is a ridiculous omission.

  48. Hi I used your solution but had to import into a cash account inorder to bring in all transactions… I then have to manually edit these to contain the right categories since the memory piece is not working.. I really like quicken but this may be my last year too much work Quicken you SUCK……

    I have tried to stay true but they are not bugdeting and this sucks not even a workqround how difficult can it be for them… Sheeesh repeat customers are not respected. It’s easier to change softwares than change banks…

    Really disappointed….

    Your tool helps but not entirely… It’s a great help.. thxs but Quicken still sucks!!!!

  49. Previous message put anonymous even thought I put in my name… continuation if I sdon’t use the cash account to import into then the transcations are put in the bottom in downloaded transactions.. and not all are read in… if I put it in an cash account when importing all transactions are read in and put in the register by passing the downloaded area… how can I get these inthe downloaded area to avoid haveing to manually update them?
    Quicken still sucks!!!

  50. Will this method work for Interactive Brokers(investment brokerage statements not a bank statement)on Quicken 2008 or 2009 home & business Cdn edition? I have download OFX files from IB already and see that their identifier is 4705. Also each buy/sell have its own sub-accounts for margin calculation as this account is a T-Reg USD Margin account where Cdn funds are in another sub account.

  51. Hi guys

    I just paid for a file to be able to save excel spreadsheets into QIF format – how do I get it – when I hit the link in paypal to take me back it gave me a could not connect page

  52. I read all your comments and it worked.

    Here is what I imported to my Fidelity Cash Account.

    !Account
    NFidelity Investments-Cash
    TCash
    ^
    !Type:Cash
    D12/16/2009
    T-40.00
    C*
    N2596
    PMy Own Caterers
    MCheck Paid #0000002596
    LEducation:Food
    ^

  53. You rock! — nice workaround, which saved a lot of headache. I wish Intuit would trust its own users more and not intentionally disable useful features — sigh! — without QIF there is often no way to import more than 90 days of history from financial institutions.

  54. I cannot get this to work. First of all, when I go to save the changes to the file, it will not save as a QIF file – it wants to save as a text file. second, when I set the import account to “all accounts”, Quicken says I MUST choose an account, but it does not give me the option to choose my investment account.

  55. Nice, this worked like a champ. I would recommend using a “nice” text editor like Notepad++, but other than that this worked great. Looks like you really helped a lot of people (me included) – have a great Thanksgiving!

  56. Thanks for this great work-around. Been missing QIF for years. I downloaded my transactions into the wrong file & didn’t feel like re-typing them. I created export QIFs from each account exactly as in step 2. I opened the correct file & imported exactly as in step 4. Didn’t get a list of accounts; Quicken 2010 just said it found transactions for such-and-such an account, did I want to load them. Could not have been easier. Note that investment accounts may create dups, as there’s no “matching” option. Again, thanks for sharing your knowledge.

  57. My credit union, USAA, etc. don’t support the Web Connect file format so I download the transactions as QIR for Quicken. I created a QIF import only account > Property & Debt : Liability with no financial institution. That way Checking, Savings, Credit Cards, etc., can be imported into one location.

    Once imported, to move the transactions to the correct account, highlight all the rows then right click the mouse and transfer to the account of your choice.

  58. I also found out that you can create a dummy account under loans/debts and import the QIF there and just copy/paste the transactions into the account you need the data to go in.

    • This absolutely works with Q-98. Now all I want to do is to edit my checking account information to have Q-98 think it is a loan and then I will be able to continue to download qif files from my bank and import them into my register. If anybody has any hints on how to edit the account type from checking to loan please post it.

    • Just figured out how to edit a checking account to make Q98 think it is a loan account so you can import QIF files that you have downloaded. Right click on your account name and select edit account. Then change it from cash flow to property debt or something like that. After two hours I am ready to post 2011 to my account register. Hope this helps.

    • Forget my second post even though I changed it from Cash Flow to Property and also edited it to change checking to loan it does not recognize it as a loan account. So for now I guess I will have to follow the method posted by Anonymous.

  59. Ryan, you continue to rock! This worked perfectly for me. Many thanks!

  60. Does this work for Mac OSX? I have OSX 10.5.8, Quicken 2004 and Excel 2004. I tried to install xl2qif — Excel 2004 for Mac still has VBA and Add-Ins and it finds file corrupt and workbook not valid; it did try to install it, but did not finish, so I just uninstalled it from the Excel ‘Add-Ins’. Nowhere in the xl2qif literature or your helpful blog does it state which platforms supported.

  61. Bank2QIF at [bank2qif.com] converts QIF, OFX, QFX, files into QIF format aceeptable by all Quicken versions up to the latest 2011 one

  62. I have a bank that doesn’t have qfx downloads, but only direct connect and csv files. Any suggestions?

Leave Your Comment


Message is the only required field.
Emails are not published.