Quicken Community is moving to Single Sign On! Starting 1/22/21, you'll sign in to the community with your Quicken ID. For more information: http://bit.ly/CommunitySSO

Where can I find an accurate description of the QIF file layout?

I'm going to try to create a utility that pulls data from a google sheet and creates a QIF file. Where can I find the original QIF file format layout? I've found a few online layouts but they look more like reverse engineering efforts rather that the true source from the Quicken company. Thank you!

Best Answer

  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    Accepted Answer
    xrbbaker said:
    I did see this one. What makes me think this is just someone trying to figure out the file format vs the official format is the description of "U", which happens to be a key to what I'm trying to figure out. 
    The isn't any "official format".  The QIF format was never a standard.
    The QIF format was created to help with Quicken support personal.

    Once it got out that it could used, other companies jumped on it, and in some cases modified it.  What's more Intuit felt no need "adhere" to the original format either and add to the format as they saw fit.

    For what is worth I think the intent was Taxed and Untaxed.  But that is just a guess.
    In practice for importing into Quicken (have no idea what other programs do) if both are there Quicken uses the U value.  You can use either of them if you just want to put one in.  And of course the safest is to put them both in set to the same value.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/

Answers

  • Rocket J Squirrel
    Rocket J Squirrel SuperUser, Windows Beta ✭✭✭✭✭
    Note that XLS-to-QIF and CSV-to-QIF converters do exist already. I don't know whether they work with Google Sheets. You can find them easily with a web search.
    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • xrbbaker
    xrbbaker Member ✭✭
    I did see this one. What makes me think this is just someone trying to figure out the file format vs the official format is the description of "U", which happens to be a key to what I'm trying to figure out. Their description is below. I can't imagine the author of this file format would state that the U parameter "seems identical to T field." I'm surprised it isn't accessible on the Quicken website or through the Quicken help function on the app.

    I've also seen the CVStoQIF converters. I may well pursue them. I just thought I'd start by understanding the extract that my 401k provider yields - which includes the "U" parameter. Thanks very much. -RB

    T Amount of the item. For payments, a leading minus sign is required. For deposits, either no sign or a leading plus sign is accepted. Do not include currency symbols ($, £, ¥, etc.). Comma separators between thousands are allowed. All T-1,234.50

    U Seems identical to T field (amount of item.) Both T and U are present in QIF files exported from Quicken 2015. All U-1,234.50
  • Rocket J Squirrel
    Rocket J Squirrel SuperUser, Windows Beta ✭✭✭✭✭
    I see what you mean about U. I have a few QIF files lying around and in all of them, the U amount is an exact duplicate of the T amount in every transaction. I never noticed that before.
    I did find Google's cached version of Intuit's documentation. It has no mention of U.
    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    Accepted Answer
    xrbbaker said:
    I did see this one. What makes me think this is just someone trying to figure out the file format vs the official format is the description of "U", which happens to be a key to what I'm trying to figure out. 
    The isn't any "official format".  The QIF format was never a standard.
    The QIF format was created to help with Quicken support personal.

    Once it got out that it could used, other companies jumped on it, and in some cases modified it.  What's more Intuit felt no need "adhere" to the original format either and add to the format as they saw fit.

    For what is worth I think the intent was Taxed and Untaxed.  But that is just a guess.
    In practice for importing into Quicken (have no idea what other programs do) if both are there Quicken uses the U value.  You can use either of them if you just want to put one in.  And of course the safest is to put them both in set to the same value.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • xrbbaker
    xrbbaker Member ✭✭
    Thanks Chris. Fair enough Rocket. I'll go with the Wiki version that Rocket pointed out. Hopefully it will be close enough. My real problem is that our 401k provider, Paychex, doesn't allow their system to work with Quicken (and similar tools) so that I can aggregate all of my retirement data into one view. Thus I'm trying to stand everything on its head instead of working with a system that has graduated past the 1990s. :)

    Thanks again.
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    To see what to import for Quicken, one of the best ways to tell what is needed is just put transactions into it, and then export them.  Note that investment transactions look quite a bit different than non investment transactions.

    Here is one such program you can use for this:
    FAQ: A program to add the account name and type to QIF files so that Quicken will import them. — Quicken

    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • xrbbaker
    xrbbaker Member ✭✭
    I'm maybe doing things differently from others. In my case I simply want to look at all of my investments in one place. I want to do this so I can see how my over arching portfolio looks. That is, 60/40 or whatever, how much in equities, how much US vs International etc. The Quicken Investments page is perfect for this. Quicken works with all of my banks, Charles Schwab etc. EXCEPT for Paychex, which holds my 401k. Paychex doesn't play nice with any of the aggregators.

    I'm using Quicken solely as a portfolio analyzer. I don't care about transaction history, performance etc., just what does my mix look like. It looks like my best best will be: 1) copy my 401k current investments page 2) paste it into XLS 3) run some kind of utility to pull the XLS data and create a QIF file 4) delete the current QB investments and then 5) import the new QIF file. This makes me sad. By the time I do all of this it might be quicker to enter it all manually. However it's about 15 fund positions times 2 accounts so it is very tedious. Woe is me. :)

    Thanks again.
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    Yes you do seems to have a special case here where you might just like transactions like add and remove shares.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    P.S. Quicken Inc recently added a feature "Simple Investment" that has the same "goal".  But of course that is based on being able to download from the financial institution.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • xrbbaker
    xrbbaker Member ✭✭
    Hey Chris - I just checked and I already have the simple investment beta flag turned out. Thanks very much for the help. I'll get their some day I hope! :) Cheers.
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    If you want to use the Simple Investing mode you need that preference set and to go to the Account Details of each account you want to use it in and turn it on there.

    Note turning it on the account register is not displayed, instead you just get graph and tables, so it probably isn't useful if the account is manual entry.  I'm not sure how it will behave for QIF imports.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • xrbbaker
    xrbbaker Member ✭✭
    Aha. I hadn't done the second step. I now have my two accounts set to simple. Actually, I think I'm going to just delete both of them and then set them up again fresh. The problem with the existing accounts there are more than one entry for the same equity - so it will be a pain to update from here. I like the sound of a fresh start with this new simpler profile. I had never heard of this. Thanks so much for bringing it to my attention!
  • xrbbaker
    xrbbaker Member ✭✭
    Chris - sorry to keep bothering you. I deleted my old accounts. I've created a new one from the get go, as simple. I mistyped the ticker for one of the funds. Now for the life of me I can't figure out a way to add that fund to the account that I just set up.

    When I go to that account and click on settings I see: 3 options to update (cash, shares, price). I can edit account details. that doesn't help. I can look at the security list which seems to be an overarching list of all securities across all accounts. that doesn't help. Why is this so hard to add another security to this account? Thank you.
  • xrbbaker
    xrbbaker Member ✭✭
    I'm pinging customer service on this one as I'd really like to keep moving forward! :)
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    Securities are always "global" they aren't per account.  You should set up the security in the security list.  As an "aid" if you download a security that Quicken doesn't know about it will prompt you to create it.  That doesn't imply it is for that account only.
    Signature:
    (I'm always using the latest Quicken Windows Premier subscription version)
    This is my website: http://www.quicknperlwiz.com/
  • xrbbaker
    xrbbaker Member ✭✭
    For completeness here is what they had me do. I had to first switch it over from Simple to Complete. Once in Complete I go to transactions and then can add a security. Then I have to switch it back to Simple. It's hard for me to believe that this is the brilliant method QB has figured out to add a security to a simple profile, but that's what I was told.

    Now that I have everything loaded and in the simple profile I hope it's not as hard to update the share balance as it was to add a new security. :) Thanks.
Sign In or Register to comment.