FAQ: How to Import QIF Files Into Non-cash Accounts, post-Q2004

Howard RoarkHoward Roark Member
edited December 2018 in FAQs
The following assumes you have downloaded a qif file for a single account
from a financial institution, or have a qif file exported from a single
account in a Quicken data fileset.

Open the qif file in Notepad and add the following four lines at the very
beginning of the file:

!Account
NMyAccount
Txxxx
^

substituting the exact Quicken name of the account you want to import to for "MyAccount";and substituting from the following list for "xxxx":
1.) "Bank" for checking or savings accounts
2.) "CCard" for credit card accounts
3.) "Invst" for investment accounts

"Save" the qif file.

Then in Quicken:
File > Import > QIF file
Enter (or browse to) the name of your qif file in the "Location of QIF file"
box.
Select the name of any "cash", "asset", or "liability" account in the
"Quicken account to import to" box. (If you have no such account types,
create one).
Click Next.

You should get a message saying "Transactions found for MyAccount. Import
transactions into MyAccount?". Click Yes.


[If you have difficulty importing using the above criteria, you can try adding one more record to the QIF file.  Immediately after the fourth record you added above (the one containing just the caret), add a fifth record that looks like this:

!Type:xxxxx

where you substitute for "xxxxx" using the same criteria as above.]


[For investment accounts, you may not get the option to review the
transactions or to match them to existing register transactions. They may
all be imported as "New" transactions. This can be important if you manually
enter transactions in those accounts before downloading the real-world
transactions.]

[Note: IGNORE the text in the QIF Import dialog that says "QIF import is not available for checking, savings, ...".]
[EDIT 01-16-16: Users of Quicken versions later than Q2010 need to insure that the Quicken Register Preference to "Automatically add ..." downloaded transactions to registers (or Transaction Lists) is not selected.]
[EDIT 07-04-16: Q2016 R8 appears to have solved the problem with automatically adding downloaded transactions to registers/transaction lists.]
[EDIT 07-04-16: The QIF file import option, "Special handling for transfers" should be off.
[EDIT 07-15-16. You will definitely need the extra "!Type:Invst" ("!Type:xxxxx") record mentioned earlier, if you are importing a QIF file of investment transactions.]

Comments

  • gmalis1gmalis1 Member
    edited April 2018
    Reread his first post in this thread.

    The answer is there.
  • ThanksThanks Member
    edited July 2016
    gmalis1 said:

    Reread his first post in this thread.

    The answer is there.

    I reread the post and tried all the said options more than 10times.  But it is NOT working.
  • OtherHorsesOtherHorses Member
    edited May 2018
    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.
  • skintighskintigh Member
    edited July 2016

    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.

    I used to use a program to convert US Savings Bond info into QIF files and then import the values in older versions of Quicken.

    I tried this method, both adding four lines and five, but I get the same result: Quicken tells me it found transactions, asks me if I want to use them, I click yes, the screen flickers, but I'm still at the same dialog window and no values are updated.

    Am I doing something wrong?  As far as I can tell all the data and formats are correct and what I have always used, they just don't seem to load.  I know when I upgraded from Quicken 2001 to 2006 I had to change the date format, did Intuit change the date format again with 2009?  Is there a guide on QIF format I can look at?
    ...
    I'm looking at Wikipedia's QIF page and their format looks nothing like the files I've bee using...
  • Howard RoarkHoward Roark Member
    edited August 2016

    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.

    I haven't gone over the Wikipedia formats with a fine tooth comb, but when I have looked at them (quite a few times), they have always appeared correct to me.

    If you do not format your QIF file correctly, Quicken will not import it.

    I don't believe there has been a single change in QIF file formats for many years, almost certainly not since Q2006.  Probably not since well before the decision was made to restrict QIF file imports.

    [The QIF file two-digit-year date format did change sometime around 2000.  Using four-digit years avoids the concern.]

    It's outside the scope, or intent, of the original post in this thread, to debug QIF file data: it assumes you already have a valid QIF file.  

    [It's very easy to make a minor mistake in creating a QIF file and it can be very difficult to find that mistake.  Only a person who has the QIF file can effectively do the grunge work of debugging it.]
  • skintighskintigh Member
    edited July 2016

    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.

    The date change I ran into was that with 2001 Quicken required bond value dates to be m/yyyy.  Sometime after 2001 it required m/d/yyyy.

    The US gov't releases data in this format:
    E2009061980010633010633010633010633010741010741                                    
    E2009061979010828010828010828010828010933010959010744010744010744010744010845010845
    E2009061978012845012845010854010854010667010694010697010697010697010697010798010827
    [...]
    They helpfully explain their format
    http://www.treasurydirect.gov/indiv/tools/tools_savingsbondvalues.htm

    My program, and my friend's, convert that into a QIF file:
    !Type:Prices
    198001E,4.2532,6/1/2009,4.2532,4.2532,0
    198002E,4.2532,6/1/2009,4.2532,4.2532,0
    198003E,4.2532,6/1/2009,4.2532,4.2532,0
    [...]

    That has worked for over ten years, but no longer works.  Honestly, I don't even remember what all the numbers coincide with anymore, and I can't find any information on that format we used.  I think I just copied an example somewhere.  What I do know is this worked and it updated the prices *without* adding any transactions to the bonds

    After much tinkering I made one of my bonds update with this QIF file:
    !Account
    NSavings Bonds
    TInvst
    ^
    !Type:Invst
    D6/1/2009
    Y199907EE
    I0.7316
    NBuy
    Q0
    ^

    Thank you very much for showing me how to trick Quicken into importing investment transactions!!!

    However, that makes a "buy" transaction of 0 shares for every bond every month.  So it works, but I don't like that it's sloppy.  I changed NBuy to NIntInc but it still shows.  If you don't include the N-action the action displays in Quicken as "Cash" in one view and "MiscExp" in another and it doesn't update the value anymore.  No success with N0, either.

    I manually deleted all those entries from Quicken and the price updates do remain in the price history, so the transactions remain unnecessary to update the price, I just haven't figured out how to trick Quicken into taking the price without a transaction action.

    Any suggestions?

    Thanks again!
  • Howard RoarkHoward Roark Member
    edited August 2016

    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.

    I'm not sure I can help, but how about starting a new discussion, with the contents of your last post here?

    This thread is only concerned with importing Quicken "transactions" from QIF files; not importing "prices".
  • q_lurkerq_lurker SuperUser ✭✭✭✭
    edited December 2016

    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.

    To Seth:  See my comment in your other discussion thread -- https://qlc.intuit.com/post/show_post_full_view/aV1iraVNOr3QyjacfArQA8?
  • Reagan78666Reagan78666 Member
    edited July 2016

    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.

    NoWayJose you are a lifesaver! Thank you. This worked very well.
  • mspaularenemspaularene Member
    edited July 2016

    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.

    I use this Quicken for convenience an accuracy. Adding 12 more steps, changing file types, adding to files ect. does not support either of my reasons for using this type of software.
  • tedlarson92tedlarson92 Member
    edited July 2016

    re: "Thanks" problem

    Make sure the 401k account is already set up in Quicken, and you're typing the exact Quicken name of the account in the QIF file.  For example, if the account is called "Old 401k" in Quicken, you should add these 4 lines to the top of the QIF file:
    !Account
    NOld 401k
    TInvst
    ^

    Also, on the QIF Import screen, after you Browse and find your (edited) QIF file, do NOT select an "Account to import into" -- just leave it alone and click Next.

    I tried the procedure above to import an investment account under Quicken 2008, that was exported from another QDF file I have....trying to consolidate two quicken files into one here.  When I click the "Next>" button on the QIF import, the screen flickers, but it does nothing.  Does not import the file.  I added the lines:
    !Account
    NSchwabTrader
    TInvst
    !Type:Invst

    to the top of my file.  I have an account in my accounts list named SchwabTrader, which is an investment account.  But, nothing works when I try to import.

    Suggestions on how I can get this procedure to work?  I really need to consolidate my two Quicken data files.

    Thanks,

    -Ted
  • SamskiSamski Member
    edited April 2018
    Another way to import QIF files into non-cash accounts is summarized below.  This information was obtained from a Quicken chat session.
    Create a temporary cash account.
    Import the QIF file into the new cash account.
    Review for accuracy, etc.
    Select all transactions to be added to the non-cash account.
    This is done by selecting the first transaction, hold SHIFT, select the last transaction.
    Right-click these selected transactions.
    Select 'Move Transactions'.
    Select the proper destination file.
    This technique is very easy.  I have used it to import transaction into my credit card account from a smart receipt scanner.
  • edited November 2016
    Samski said:

    Another way to import QIF files into non-cash accounts is summarized below.  This information was obtained from a Quicken chat session.
    Create a temporary cash account.
    Import the QIF file into the new cash account.
    Review for accuracy, etc.
    Select all transactions to be added to the non-cash account.
    This is done by selecting the first transaction, hold SHIFT, select the last transaction.
    Right-click these selected transactions.
    Select 'Move Transactions'.
    Select the proper destination file.
    This technique is very easy.  I have used it to import transaction into my credit card account from a smart receipt scanner.

    Boy I thought I found a way around the fact my credit union only provided downloads in QIF. However Quicken 2010 will not allow importing a QIF from any financial instutions.

    Has anyone found a way around this?

    Thanks Bill
  • AnilAnil Member
    edited July 2016
    Samski said:

    Another way to import QIF files into non-cash accounts is summarized below.  This information was obtained from a Quicken chat session.
    Create a temporary cash account.
    Import the QIF file into the new cash account.
    Review for accuracy, etc.
    Select all transactions to be added to the non-cash account.
    This is done by selecting the first transaction, hold SHIFT, select the last transaction.
    Right-click these selected transactions.
    Select 'Move Transactions'.
    Select the proper destination file.
    This technique is very easy.  I have used it to import transaction into my credit card account from a smart receipt scanner.

    It failed for me. see https://qlc.intuit.com/post/show_mini/djFsSYl5yr36fAacfAYHXM?iscomment=true&replied=true&rpoc=true&seymore=false&show_answer_button=false&show_comment_button=false
  • darchanddarchand Member
    edited July 2016
    Samski said:

    Another way to import QIF files into non-cash accounts is summarized below.  This information was obtained from a Quicken chat session.
    Create a temporary cash account.
    Import the QIF file into the new cash account.
    Review for accuracy, etc.
    Select all transactions to be added to the non-cash account.
    This is done by selecting the first transaction, hold SHIFT, select the last transaction.
    Right-click these selected transactions.
    Select 'Move Transactions'.
    Select the proper destination file.
    This technique is very easy.  I have used it to import transaction into my credit card account from a smart receipt scanner.

    Quicken Essentials 2010 doesn't offer an "import QIF" option, and doesn't recognise QIF, CSV, TXT file types it seems.  Halp!!
  • Bob_LBob_L SuperUser
    edited November 2017
    Samski said:

    Another way to import QIF files into non-cash accounts is summarized below.  This information was obtained from a Quicken chat session.
    Create a temporary cash account.
    Import the QIF file into the new cash account.
    Review for accuracy, etc.
    Select all transactions to be added to the non-cash account.
    This is done by selecting the first transaction, hold SHIFT, select the last transaction.
    Right-click these selected transactions.
    Select 'Move Transactions'.
    Select the proper destination file.
    This technique is very easy.  I have used it to import transaction into my credit card account from a smart receipt scanner.

    FYI, ran a test of NWJ's approach today using QW2010 R10 to export a qif file from one brokerage account into a new brokerage account.  Worked exactly as advertised using just 4 additional lines (5th line was already there after the ^).  Thanks NWJ!

    One thing that could confuse some or possibly make some believe it did not work (when it really did) is an opening cash balance.  That is the original account opening cash balance will show up as a transfer from the original account to the new "MyAccount".  You can correct that in the new account you are importing into by simply deleting it.  Or, when you first open the qif file in Wordpad look at the first transaction there and change the line " L[OriginalAccount]" to "L[MyAccount]", where Original account is the account you exported from and MyAccount is the one you are importing into.
    Quicken Premier Subscription, Windows 10 Pro
  • AtheistAtheist Member
    edited July 2016
    Samski said:

    Another way to import QIF files into non-cash accounts is summarized below.  This information was obtained from a Quicken chat session.
    Create a temporary cash account.
    Import the QIF file into the new cash account.
    Review for accuracy, etc.
    Select all transactions to be added to the non-cash account.
    This is done by selecting the first transaction, hold SHIFT, select the last transaction.
    Right-click these selected transactions.
    Select 'Move Transactions'.
    Select the proper destination file.
    This technique is very easy.  I have used it to import transaction into my credit card account from a smart receipt scanner.

    Thanks. I exported from a "fake" 403B to qif, and so it had those 4 lines at the top, but with the wrong account name (the fake one). So, I think that the idea is to "change" and not necessarily 'add" those 4 lines, correct?  In any case, your answer has saved me some time.
  • SteveSBESteveSBE Member
    edited August 2016
    Thanks NoWay Jose. It took me a few steps to get it imported but it finally worked!

    My procedures was to:

       1. Use SnagIt v10 to capture the text into an Excel 2007 file (xlsx format...I suspect xls will work too).

       2. Use the old Excel AddIn called XL2QIF (http://xl2qif.chez-alice.fr/xl2qif_en.php) to convert my investment transactions to a QIF file.
            The fields included Action; Transaction Date; Security Name; Number of Shares; Price Paid; Amount; and Memo.

       3. I modified the QIF file using Notebook and imported it per your instructions.

    For others' use, one transaction's format would be (remove comments in parenthesis):
               !Account (per NoWay Jose's instructions)
               NMyAccountName (per NoWay Jose's instructions)
               TInvst (per NoWay Jose's instructions)
               ^ (per NoWay Jose's instructions)
               !Type:Invst (per NoWay Jose's instructions)
               D06/09/2010   (Transaction Date)
               U144.45      (Total Amount)
               T144.45   (Total Amount taken from Mutual Fund's cash)
               NBuy (Buy shares transaction)
               YSecurityName (Exact Security Name here)
               I15.4308 (Price paid per share)
               Q9.36120 (Number of Shares)
               MMemo (Memo or comments)
               ^ (End of transaction caret)

    I'd recommending testing it on a test version of your Quicken file before doing it on your permanent file since there are a few things you need to "discover" by playing around.

    One of the things to discover was to learn all the QIF codes to put in front of each line.  Is there a COMPLETE list of them anywhere?

    Again....thanks much NoWay Jose.

    SteveSBE
  • edited July 2016
    SteveSBE said:

    Thanks NoWay Jose. It took me a few steps to get it imported but it finally worked!

    My procedures was to:

       1. Use SnagIt v10 to capture the text into an Excel 2007 file (xlsx format...I suspect xls will work too).

       2. Use the old Excel AddIn called XL2QIF (http://xl2qif.chez-alice.fr/xl2qif_en.php) to convert my investment transactions to a QIF file.
            The fields included Action; Transaction Date; Security Name; Number of Shares; Price Paid; Amount; and Memo.

       3. I modified the QIF file using Notebook and imported it per your instructions.

    For others' use, one transaction's format would be (remove comments in parenthesis):
               !Account (per NoWay Jose's instructions)
               NMyAccountName (per NoWay Jose's instructions)
               TInvst (per NoWay Jose's instructions)
               ^ (per NoWay Jose's instructions)
               !Type:Invst (per NoWay Jose's instructions)
               D06/09/2010   (Transaction Date)
               U144.45      (Total Amount)
               T144.45   (Total Amount taken from Mutual Fund's cash)
               NBuy (Buy shares transaction)
               YSecurityName (Exact Security Name here)
               I15.4308 (Price paid per share)
               Q9.36120 (Number of Shares)
               MMemo (Memo or comments)
               ^ (End of transaction caret)

    I'd recommending testing it on a test version of your Quicken file before doing it on your permanent file since there are a few things you need to "discover" by playing around.

    One of the things to discover was to learn all the QIF codes to put in front of each line.  Is there a COMPLETE list of them anywhere?

    Again....thanks much NoWay Jose.

    SteveSBE

    I succeeded in importing the account but would like to know how I can alter the Account Type to reflect the fact it is a bank account rather than a Cash Account? Is this possible? Thanks
  • Chris54322Chris54322 Member
    edited April 2018
    candiceleehall  it is not possible to change the account type with the exception of sometimes you can change a regular brokerage account into a IRA or 401K account.  For the others you have to create another account.
  • Bob_LBob_L SuperUser
    edited December 2016

    candiceleehall  it is not possible to change the account type with the exception of sometimes you can change a regular brokerage account into a IRA or 401K account.  For the others you have to create another account.

    candiceleehall,

    As a practical matter there is little difference between the 2 account types other than the use of columns named spend and receive instead of payment and deposit.  

    You can create a new checking account and then move the transactions to that new account.  To do that use shift+click to highlight all of the transactions in the cash register (click on top one then shift click on bottom one to highlight all of the transactions in between). Then right click the selection and choose move transactions.
    Quicken Premier Subscription, Windows 10 Pro
  • JC1JC1 Member
    edited August 2016
    I tried this and I get the msg that 56 transactions were imported but they don't show up in the register.  Any ideas where they went?

    UPDATE: I figured out that I should not have an account with the same name in the new file and then I was able to see the transactions show up in the cash account.

    p.s. Thank you so much to NoWayJose!  This is great!
  • cagilbertcagilbert Member
    edited July 2016
    JC1 said:

    I tried this and I get the msg that 56 transactions were imported but they don't show up in the register.  Any ideas where they went?

    UPDATE: I figured out that I should not have an account with the same name in the new file and then I was able to see the transactions show up in the cash account.

    p.s. Thank you so much to NoWayJose!  This is great!

    I'm not finding that Payee Addresses (lines starting with "A") are being imported.
  • Chris54322Chris54322 Member
    edited August 2016
    JC1 said:

    I tried this and I get the msg that 56 transactions were imported but they don't show up in the register.  Any ideas where they went?

    UPDATE: I figured out that I should not have an account with the same name in the new file and then I was able to see the transactions show up in the cash account.

    p.s. Thank you so much to NoWayJose!  This is great!

    Cagibert,

    I tried exporting the payee list with one having an address, deleting the payee and importing it and the address did come back in.  Make sure you select the check box for the Memorized payees.  If they still don't import then maybe they are not it the right format from where every you are getting them from.  Export just your Payee list from Quicken with one that has an address and compare that to the one that is not importing.
  • cjwebercjweber Member
    edited July 2016
    This seems like a lot of work to change a retirement account from "Mine" to "Spouse".  A good program would make this an option in the setup screen.  I have only been using Quiken for 6 months and am not that impressed.  I was using another product before which I greatly preferred.

    So what can I do if I have all the account information in the system and it is not available for download from a financial institution to get my spouses retirement account setup correctly?
  • edited January 2018
    Thanks.
  • skeleton567skeleton567 Member
    edited April 2018
    gmalis1 said:

    Reread his first post in this thread.

    The answer is there.

    in Q2014, I selected only the investment account I wanted to move, exported only  'transactions' from the old file, created the same account in the receiving file.  Inserted the following lines in the exported file:

    !Account
    NPIPER IRA INV - Simon
    TInvst
    ^
    !Type:Invst

    In Quicken, opened the receiving file, selected the investment account to receive the import, opened the import file, which had the account already created with type investment.  When I started the import process, the receiving account was not visible, and the warning about account types appeared.  But I selected the <all accounts> option. 

    The process ran and appears to have correctly imported all of the investment transactions from the old file. 

    Background:

    I had not carried forward some of my investment history as I changed from broker to broker several times, and now wish to have my complete retirement savings history in my current file.  This means I have to move investment transactions forward through a number of yearly files.

     Note that I did, in fact, need to add the fifth line,

    !Type:Invst

    that I suspect maybe allowed me to circumvent the problem of not being able to select the account to receive the records.  I'm not sure yet that everything, such as lot information, is correct, but that is not so critical in the old history.  And there will be no price history, but buys, sells and splits should all have correct prices.

    I'm a retired computer freak with 42 years of experience, and I can tell you there is no excuse for it being so difficult to move investment data.  As we always said, just 'Make It Happen'.  If you can put the data in, and it is there where you can see it, you can always take it out and put it back in if you want to bad enough.  The 'investment accounts don't have a register' is total BS.  They just don't try hard enough.  I'll be consolidating all of my investment history for 32 years this way.



This discussion has been closed.