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

Howard Roark

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

Unknown

Reread his first post in this thread.

The answer is there.

Unknown

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.

Unknown

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.

Unknown

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 Roark

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

Unknown

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 Roark

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_lurker

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?

Unknown

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.

Unknown

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.

Unknown

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

Unknown

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.

Unknown

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

Unknown

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. 

Unknown

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_L

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.

Unknown

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.

Unknown

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

Unknown

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

Unknown

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_L

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.

Unknown

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!

Unknown

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.

Unknown

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.

Unknown

You might want to look at this thread, this program might help you:
https://qlc.intuit.com/post/show_full/cnHSz4qlWr4j5kacfAYHXM/new-version-of-importqif-with-more-advanced-features?ppid=143952830

Unknown

You might want to look at this thread, this program might help you:
https://qlc.intuit.com/post/show_full/cnHSz4qlWr4j5kacfAYHXM/new-version-of-importqif-with-more-advanced-features?ppid=143952830

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?

Unknown

Thanks.

Unknown

This content has been removed.