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

[Deleted User]

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!

Chris_QPW

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.

Rocket J Squirrel

https://en.wikipedia.org/wiki/Quicken_Interchange_Format

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.

[Deleted User]

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

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.

Chris_QPW

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.

[Deleted User]

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

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

[Deleted User]

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

Yes you do seems to have a special case here where you might just like transactions like add and remove shares.

Chris_QPW

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.

[Deleted User]

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

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.

[Deleted User]

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!

[Deleted User]

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.

[Deleted User]

I'm pinging customer service on this one as I'd really like to keep moving forward! :)

Chris_QPW

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.

[Deleted User]

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.

Platinum Platypus

I must disagree with one, minor, point in this discussion;

>>The isn't any "official format". The QIF format was never a standard.<<
>>The QIF format was created to help with Quicken support personal. (etc)<<

There most definitely was a QIF file format standard maintained by Intuit in the 1990's. The last version that was publicly available was "Copyright (c) 1999 Intuit, Inc." It was available for download from the Intuit web site as QIF99.PDF ... that said, even in 1999, QIF files exported from Quicken showed undocumented syntax not found in the published version. It has been extended periodically since then. For example, the Investment account actions "Contrib" and "ContribX".

There are a few tables of QIF syntax that can be found on the web. Some based on the 1999 PDF some based on one of its predecessors, a couple include equivalent syntax gleaned from other regions. None is complete, but that's not really necessary.

The reliable way to catalog the actions and identifiers currently used by QIF files is to create a sample file containing every record type. That includes all account types, memorized transactions, payees, split types ad nauseum. Quicken treats tax exempt vs taxable accounts and investment accounts with & without an attached checking account differently creating more variations to be created.

The QIF import routine recognizes syntax exported by older versions and versions for other regions that can not be cataloged by exporting a QIF from any single copy of Quicken. This is a concern only when creating a utility to translate QIF files to a form suitable for another program. For creating QIF files to be imported into Quicken there's nothing gained from cataloging alternative forms of QIF syntax that result in the same transaction records in the Quicken database file as long as the syntax used is likely to continue to function in future versions. The simplest solution for a developer encountering syntax not used by their version is to import it into and export it from the current version of Quicken and compare the QIF files in order to determine how the equivalence functions.

After more than 3 decades its conceivable, if not likely, that there is QIF import syntax not found in any QIF export that has never been publicly documented. Internal documentation for features without public documentation tends to be incomplete, making it all but impossible to determine the effect of removing it from a future version of Quicken. Cleaning up the code base by removing undocumented features tends to be a career-limiting idea for a developer maintaining an old application. The obverse is there are along with strings that do something interesting or useful will also will be strings for features that were never fully implemented, that were never de-bugged for general use, were only for development debug or were created for product support and only function reliably in specific circumstances.

Chris_QPW

Platinum Platypus said:

I must disagree with one, minor, point in this discussion;

>>The isn't any "official format". The QIF format was never a standard.<<
>>The QIF format was created to help with Quicken support personal. (etc)<<

There most definitely was a QIF file format standard maintained by Intuit in the 1990's. The last version that was publicly available was "Copyright (c) 1999 Intuit, Inc." 

You misunderstood this statement:

The isn't any "official format".  The QIF format was never a standard.

"Standard" is referring to being standardized by an official standards board.

For instance there is a OFX standard, there isn't a QIF standard.

A company posting a format of a file isn't a standard.

The main difference being that once a format is standardized then a programmer can count on data files adhering to that standard for the most part.  With a non standardized format like QIF the programmer has to take into account all kinds of exceptions that were made by this or that QIF creating software.

And BTW Intuit's/Quicken Inc's QIF parser is one of the worst in the industry.  So, so much for being the company that is the "standard".  Other companies have to have better parsers so that they can get data from programs like Quicken (to try to take customers).  But with the exception of Quicken Mac that allows a one time conversion from QIF to a Quicken data file, Quicken Inc (or Intuit before them) isn't really interested in this format to get customers from competitors.

Unknown

This content has been removed.