I just "succeeded" in recreating my data file using a QIF export/import

Unknown

I thought I would post this while it is fresh in my mind.
I have data going back to 1996 and up until now I have never been successful in recreating my data file through a QIF export/import.

There are fundamentally a couple of things that stop this from being successful.
The major one is that Quicken's handling of transfers is terrible.
The secondary one is that there are some security transactions that the QIF format just doesn't support.  The main one is that it can't keep track of "security lots".  And as such it can never do things like options correctly.  And of course there isn't any support for things like the ESPP wizard actions/hidden data.  It also doesn't support investment transaction that involve a "banking split".

Well my days of employee stock options and ESPPs are behind me, and I don't trade options.
So my biggest obstacle was the transfers.

I was thinking lately about the fact the Quicken Essentials didn't actually link transfers, and that Quicken Mac still allows this.  And in fact that as long as you have offsetting transactions in each register that is really all that is needed for it to come out OK.  Sure you can't do the jump to the other account action, and in reports/budgets you are doing something that requires filtering by transfers then you aren't going to be able to do that.  But I haven't really cared about that so I thought about it and decided that I could just export the QIF file, and then for every transfer change them to [Account I'm In].  Another possible is using categories like AccountA_Out, and AccountA_In.  With that some filtering could still be done.  But for my test case I just made them transfers to the same account (balance adjustments).

I wrote a small Perl program to do that for me.

With this change every transaction import is now new.

I create a new data file, canceled out of creating the first account.
And I went into the category list and deleted them all.
And then did the import selecting everything except for "special handling of transfers".

And this worked quite well.
I ran into just a few of problem.
One is that I had to run validate and repair a few times until it called the file "clean".
Some of this had to do with the category list and other minor things.
It cleaned it up with no real problems.

One problem I could have avoided if I hadn't started this during the day while the price of securities were changing (or by not updating my main data file).  What happened is that since the importing of the prices through QIF are considered "manual" they overrode any kind of update for the day.  I ended up deleting today's prices and updating to get it correct, but I could have just waited to tomorrow too.

The another problem I ran into was one of my long close brokerage accounts wasn't zero.
I found two duplicate transactions, I have no idea why these showed up.  But I just deleted them.

The last problem involved my employee stock option account.  For whatever reason it said I had 4 shares left.  So I did a remove, only to find that it had put in some kind extremely big negative amount at point, which I saw on my net worth graph.

I played around with the date and noticed that anything before that date got zero on the net worth graph.  So in the end I removed the 4 shares on the day of the first transaction in my data file, and everything looks fine now.

I'm sure if I looked at the details in say the accounts like the stock options account I would find something "not quite right".  And certainly if someone was not using first in first out the capital gains wouldn't be right.  But overall it all looks good to me, and now finally can recreate my data file if I want to.  I will probably play with this some more and make sure there isn't any big gotchas, before I actually commit to recreating my data file.

mshiggins

I am surprised you had any doubts. QIF is not hard once you know how it works, but it can be tedious getting everything just right.

Unknown

mshiggins said:

I am surprised you had any doubts. QIF is not hard once you know how it works, but it can be tedious getting everything just right.

"Doubts"?

I have done this many times over the years and never had a successful import.
As in an import that wouldn't have taken me days or weeks to fix.

The key being the transfers.
With the transfers you have a choice.
Select "special handling for transfers" and have all your split transactions that have transfers in the changed to multiple individual transactions, and even then still not work right.

Or you can not select that and have the task of trying to match all the transaction when Quicken is going to false match a lot of them, and not even give you the option to match in an investment account.

mshiggins

mshiggins said:

I am surprised you had any doubts. QIF is not hard once you know how it works, but it can be tedious getting everything just right.

I've always imported one account at a time and prepped the QIFs to remove one side of the transfer.


You can't QIF ESPPs or employee stock options. You also can't QIF reminders.

mshiggins

mshiggins said:

I am surprised you had any doubts. QIF is not hard once you know how it works, but it can be tedious getting everything just right.

Oh and you can't QIF Date Acquired if different than transaction date for Add Shares.

Unknown

mshiggins said:

I am surprised you had any doubts. QIF is not hard once you know how it works, but it can be tedious getting everything just right.

Sounds like a LOT of work, and I don't like my paychecks being split into 5 transactions.

My ESPPs and employee stock options actually came over fine.
Mind you I'm just talking about getting their value right at any given date for the net worth graph.  Not all the tax details and such, which I don't care about since they are long closed accounts.

One export of the QIF file.
One import of the QIF file.
Go to each account with a red flag and select Accept All.
Go into the Hidden Accounts dialogs and set those flags.
Fix up the things I mentioned above.

What I haven't done left would be to recreate the reminders, report, and connect for downloading transaction/removing downloaded transactions (wouldn't even try to match them).

mshiggins

mshiggins said:

I am surprised you had any doubts. QIF is not hard once you know how it works, but it can be tedious getting everything just right.

What are you talking about? I've never had any issues with my paychecks when importing via QIF.

Unknown

Care to explain to us non-techie Quicken users the reasoning for creating a .qif file for export?

Also, how did you handle closed and hidden accounts?  Do you un-hide them?

If this is a "bomb shelter" approach to Quicken's subscription policy, I'll just move on to another software and start fresh from there.  

Unknown

mshiggins said:

I am surprised you had any doubts. QIF is not hard once you know how it works, but it can be tedious getting everything just right.

It isn't paychecks exactly, it could be any split transaction that has transfers in it.

When you select the import option of "special treatment for transfers" what Quicken does is delete the "from" side of every transfer.  The reason this is done is because Quicken's transfer matching stinks.  So this avoid behavior avoids that by only having one side of the transfer entered during QIF importing (the "to" side of the transfer).

The problem I have with this is they should have picked the "to" side to delete.
For instance you have a paycheck and you have a transfer in it to your 401K account, and maybe one to a flexible spending account.  That kind of thing.

What it is going to do is delete the transfers out of that split transaction.
So how do these transfers get put back in?
When you import the transactions for "to" account it will create the transfers back to the first/paycheck account.  But in doing so you will have the original split transaction, plus one for each transfer that was in that account.  Pretty messy.

If you don't select the "special treatment for transfers" it will leave both sides of the transfer in and the split/paycheck transaction will not get split up, but you have the problem of dealing with matching up the transfers.  Something that Quicken if very poor at handling.

But I can tell you even with using the "special treatment for transfers" option and ignoring the messy splitting up of split transactions with my data file I have run into other problems.

Now clearly if I was to go one transaction at a time and work at it, I might have had better success, but I don't have the patience to go through over 20 years of transactions.  If Accept All can't be used, I don't want to use it.

And I don't thing most people do unless they are pretty desperate.
And I didn't really find importing one account at a time to be any better.

A long time ago I created a program to export each account into a separate QIF file (automated the pressing of the buttons).

And then try importing them one at a time.  And like I said I didn't see any real difference between that and just importing all the transactions at the same time, with maybe the exception of importing the investment accounts because with those the transactions don't even let you accept transactions, they go right into the account.

From what I have seen you have to have an exceptionally simple data file or be willing to basically look at every single transaction.

I don't have that kind of patience.

Unknown

gmalis1 said:

Care to explain to us non-techie Quicken users the reasoning for creating a .qif file for export?

Also, how did you handle closed and hidden accounts?  Do you un-hide them?

If this is a "bomb shelter" approach to Quicken's subscription policy, I'll just move on to another software and start fresh from there.  

My main reason for pursuing this line actually has to do with data corruption.
Up till now I have been able to recreate accounts or simply stop using them and transfer the current securities or such to another account.  I have a ESPP and a stock options account in that state.

And my data file is working fine.  But there is always a chance of a corruption that I can't workaround.  And backups are not a real solution because corruption like this can hide in there for a very long time, and then just "spring" up when you least expect it.

It isn't really a big concern, but it is enough to try recreating my data file.
Other people might have other reasons.
For instance Quicken 2016 can now read a Quicken 2017 data file, but what if you wanted to go back to say Quicken 2010 (I really liked that version. :-) )?

A QIF file doesn't care about what version of Quicken you are talking about.  Its format hasn't changed since before Quicken 2004, and it is a text file that can be changed.  But the drawback to that is what it does know is "minimal".  You can get your transactions and accounts, categories, and securities.   But you leave behind things like scheduled reminders, and reports.  Of course just converting from Windows to Mac you leave these behind too.

As for the handling of closed and hidden accounts, the QIF knows nothing of such notions.  If you export your accounts to a new data file none of them will be closed or hidden.  Once you have them in the new data file, you would set those kinds of options.

BTW for people that want to go to another application, but don't want leave their history behind, a QIF export is the way they go.  And in general the other applications are much better at import QIF files than Quicken is.  Mostly because whereas Quicken/Intuit could pretty much ignore the people that wanted to convert to Quicken, the same couldn't be said of the other way around.  And as such if their QIF imports didn't work right, they lost a customer.  This isn't universal though.  MoneyDance doesn't do a good job of this, at least it didn't the last time I checked.  But AceMoney does.

Interesting enough on the Quicken Mac side you have the people that worry about getting their data out and into another application because they don't support QIF exports.  But as it turns out Quicken Mac (starting with Quicken Essentials) actually provides a much more complete way of doing this migration.

The database used in Quicken Mac is very well known database.  As such the other applications just needed to write an export from that database to their own format.

Unknown

gmalis1 said:

Care to explain to us non-techie Quicken users the reasoning for creating a .qif file for export?

Also, how did you handle closed and hidden accounts?  Do you un-hide them?

If this is a "bomb shelter" approach to Quicken's subscription policy, I'll just move on to another software and start fresh from there.  

BTW another thing that will not come through is attachments.  But in my case I consider that a good thing.  I have been really thinking about removing them since 2/3 of my data file is attachments.

mshiggins

gmalis1 said:

Care to explain to us non-techie Quicken users the reasoning for creating a .qif file for export?

Also, how did you handle closed and hidden accounts?  Do you un-hide them?

If this is a "bomb shelter" approach to Quicken's subscription policy, I'll just move on to another software and start fresh from there.  

What is wrong with your ESPP and stock option accounts? I've had the same accounts since 1999 and they are still working fine.

mshiggins

gmalis1 said:

Care to explain to us non-techie Quicken users the reasoning for creating a .qif file for export?

Also, how did you handle closed and hidden accounts?  Do you un-hide them?

If this is a "bomb shelter" approach to Quicken's subscription policy, I'll just move on to another software and start fresh from there.  

Also Notes and Flags don't QIF.

Unknown

gmalis1 said:

Care to explain to us non-techie Quicken users the reasoning for creating a .qif file for export?

Also, how did you handle closed and hidden accounts?  Do you un-hide them?

If this is a "bomb shelter" approach to Quicken's subscription policy, I'll just move on to another software and start fresh from there.  

On the ESPP and stock option accounts.  What I was referring to is at one point they got corrupted.  Basically the "wizards" wouldn't do the right things in those accounts any more.  I had long since just started new accounts for them, and the old accounts are just for the history up to that point.  But I have also long since left that job, so even the replacement accounts are only for the history now.

But for the stock option accounts, if a person has accounts that are active, and they are thinking that they can use QIF to move them to another account they are in for a surprise.  The main problem is that stock options are very dependent on their "lot".  Without knowing which lot of the security you can't calculate the cost basis and such.
The QIF format has grants and vest and such, but no way to export/import what lot any given share is in.  But on top of that and this where the ESPP comes in there is "hidden" information in both of them that the wizards create to maintain things like the discounts and other tax ramifications.

When these kind of accounts are finally closed, and all the sells and expires and such are in, then basic QIF information is enough to capture the current value of the security/account at that point.  But not when it is active.
 
On the Flags and Notes, True, but I have never used those.  :-)

Unknown

Here are a few more things to be aware of.

1. The QIF format has a very limited amount of account types.  That means for any account that is a "special account type", you need to create those kind of accounts in Quicken before doing the QIF import.  There are very few cases where you will be able to change the account type after it is created.  For instance both traditional and Roth IRAs will be created as brokerage accounts, and will not be marked tax deferred.  For the traditional IRA it is probably just fine to just change the tax deferred status, but for the Roth IRA they are treated special in the Life Time Planner, so those should be created before the QIF import.  I didn't both with trying to have linked cash accounts (linked to investment account) be right, but they would certainly fall into this category if you want them to work as linked accounts.   And haven't tested any of the special business accounts, but I'm sure there are the same kind of problems there.
2. Categories that created by the QIF process will be marked for tax related or not, but they will not have the proper tax lines associated with them.
   

Unknown

BTW I thought I would mention something strange I see.  Opening my original data file takes about 9 seconds.  The new QIF created data file takes about 17 seconds.  Once open I don't see much difference in performance.  Not really a big deal, but it is pretty strange.

mshiggins

QPW said:

BTW I thought I would mention something strange I see.  Opening my original data file takes about 9 seconds.  The new QIF created data file takes about 17 seconds.  Once open I don't see much difference in performance.  Not really a big deal, but it is pretty strange.

How repeatable is it?

Unknown

QPW said:

BTW I thought I would mention something strange I see.  Opening my original data file takes about 9 seconds.  The new QIF created data file takes about 17 seconds.  Once open I don't see much difference in performance.  Not really a big deal, but it is pretty strange.

Very.  Not only can I repeat the timings over and over by restarting both data files, I have actually done the whole export/import process a second time, and it still does the same thing.

Rocket J Squirrel

QPW said:

BTW I thought I would mention something strange I see.  Opening my original data file takes about 9 seconds.  The new QIF created data file takes about 17 seconds.  Once open I don't see much difference in performance.  Not really a big deal, but it is pretty strange.

Perhaps File > Copy might clean out some cruft?

Unknown

QPW said:

BTW I thought I would mention something strange I see.  Opening my original data file takes about 9 seconds.  The new QIF created data file takes about 17 seconds.  Once open I don't see much difference in performance.  Not really a big deal, but it is pretty strange.

I tried it, but it didn't make any difference.
I also did Validate & Repair and that didn't make any difference either.

Unknown

QPW said:

BTW I thought I would mention something strange I see.  Opening my original data file takes about 9 seconds.  The new QIF created data file takes about 17 seconds.  Once open I don't see much difference in performance.  Not really a big deal, but it is pretty strange.

I tried a different experiment.  I imported my QIF file into Quicken 2010, which have running on a Windows XP virtual machine (just because it was the easiest place to install it).

The data file loads in 3 second into Quicken 2010.
Then took that data file to my main machine, and had Quicken 2017 convert it.
And it still takes about 17 seconds to load.

I might have to try deleting some accounts or something to see if I can see what offends Quicken 2017 so much about it.  I'm guess it has to do with the account that have the employee stock options in them.  But that is a wild guess, and really should be true since I'm not even opening those accounts.

Unknown

QPW said:

BTW I thought I would mention something strange I see.  Opening my original data file takes about 9 seconds.  The new QIF created data file takes about 17 seconds.  Once open I don't see much difference in performance.  Not really a big deal, but it is pretty strange.

Well I found it.  Removing the tax related flag from all the categories got me back to the normal loading speed.  I didn't try just setting them to right tax lines because it was just faster to remove the flag.

I then just started adding the tax flag back on a lot of categories and that didn't slow it down.

So the QIF import is some how corrupting the category data.
I think it has to do with (or at least manifests itself) with the fact that the QIF data does have the tax line information on it.  When the QIF is imported (even into Quicken 2010) the category tax related flag is set, but the tax line isn't.
So right there points to some kind of problem where the QIF import doesn't understand how to get the tax line set right.

BTW I tried deleting account, the price list, rebuilding the lot information, setting the account options like hiding on the account bar, I even did a Year End Copy which reduced the data file size in half, and all those kinds of things, but nothing else affected the speed of loading the data.

mistertheplague

gmalis1 said:

Care to explain to us non-techie Quicken users the reasoning for creating a .qif file for export?

Also, how did you handle closed and hidden accounts?  Do you un-hide them?

If this is a "bomb shelter" approach to Quicken's subscription policy, I'll just move on to another software and start fresh from there.  

BTW for people that want to go to another application, but don't want leave their history behind, a QIF export is the way they go.  And in general the other applications are much better at import QIF files than Quicken is.  

Of possible technical interest in this area: QIF imports from Quicken to MS Money which include Memorized Payees always seem to fail outright (i.e., Money terminates the entire import with one of several error messages), even though Money also memorizes payees.

Money generally ignores the account name at the top of a Quicken QIF file and prompts you to manually direct which Money account the import should go in.

Account List import is hit or miss, meaning some transfers register accurately in Money, others don't. I have no idea why this happens, but it creates the potential nightmare of trying to figure out which transfers imported and which didn't.

If you don't know better (like I didn't when I first did this), you'll try to import another QIF, compounding your misery. If you're especially stupid (like I was), you'll then delete the offending account in Money, thinking it will delete both ends of each transfer transaction and that you'll be able to start over. It/you won't. 

The Security List in a Quicken QIF isn't ignored by Money on import, but Money will prompt you to manually match each security named in the Quicken QIF with a security in your Money file, and if one hasn't been pre-populated in Money, Money will make you create the security at import before you can continue.

Transactions and Categories come over fine (more or less), but Quicken categories will be added to your Money Category list, creating an ungodly mess, because Money's Category List and Quicken's Category List are just similar enough (Bills vs. Bills & Utilities) to make spotting the imposters difficult. Weirdly, some Expense categories in Quicken get put in Income in Money, but not vice versa. Money doesn't have subcategories of subcategories so your Quicken sub-subs (sub-squared?) will appear in Money separated with a hashtag, i.e.: Category>subcategory#subsquared.

Memos and Tags (called "classes" in Money) always import without a hitch, correctly placed in their respective register fields. Hallelujah. 

Unknown

gmalis1 said:

Care to explain to us non-techie Quicken users the reasoning for creating a .qif file for export?

Also, how did you handle closed and hidden accounts?  Do you un-hide them?

If this is a "bomb shelter" approach to Quicken's subscription policy, I'll just move on to another software and start fresh from there.  

Actually one reason I put in the "in general" is because MS Money wasn't on my list of applications that are "much better".  I had tried importing into MS Money before and failed miserably.  I was really thinking more of the "little players".  Both MS Money and Quicken basically didn't really care that much about this kind of thing, but it is very importing to the "little players" if they are going to have any hope of getting any business from the "big two".

In my opinion, in some ways both Microsoft and Intuit purposely didn't make it easy, so that people couldn't jump between the two.  Not being able to change easily between the two was one of the biggest reasons the two groups tended to capture their customers "for life".

Unknown

I decided that instead of a program to change transfers to categories I would try do "Special handling for transfers" correctly.  In other words what this option on the QIF import does is remove one side of the transfer so that there isn't any need for matching transfers.  But the way Quicken does this is by removing the "from side", as in it removes the transfer out of split transactions, which results in all the split with transfers being broken up into multiple transactions.

So I have written a program to remove the "to side" of transfers so that the split transactions will stay intact.

In the process I ran into something that even though in some cases people might like, I'm sure no one thought was possible, and it means that basically other than "rough guessing in the same date range" no real matching of transfers can be done.

Here is a transaction that is a linked transfer, first in the checking account.

Now in the PayPal account:


Nothing strange until you look at the dates!

The way this created is that I imported a QIF file with the transaction on 12/17/2016, and with the category set as a transfer to my checking account.

On 12/19/2016 the transaction was downloaded into my checking account and matched to that transaction.  Notice it took on the posting date, instead of the date of the transaction that was already in the account.

When exported to QIF these two transactions aren't on the same date and as such I can't really identify them as the same transfer so that I can remove one of them.

So it looks like I'm back to my old method to make all this work and just change transfers to categories, and give up the linked transfers if I have to ever do a complete rebuild of my data.

Unknown

For anyone that wants it here is the program that will change transfers to a category named like xfrACCOUNT_NAME.
http://www.quicknperlwiz.com/changetransfers.html

Unknown

BTW speaking of transfers.  When you have accounts that have different currencies and transfer between them, there is a popup for every transfer transaction asking you about the currency conversion rate.  That would be a serious problem for years of transactions.

I haven't tested it, but I would think that method for converting the transfers to individual transactions would solve that problem too, but only if a person took the proper step or creating the accounts that are not in the home currency, before doing the import.  I'm going to add this to that thread.

Unknown

I just thought I would mention that the 2018 release of Quicken has a feature that might benefit people in some cases.

Say you create an account and it isn't the right account type, and Quicken doesn't provide a way to change the account type to what you want.  If the account isn't an investment account the general procedure for fixing this would be to create a new account with the right type, and moving the transactions from the old account to the new one.

Before the 2018 release of Quicken it wasn't possible to move investment transactions between accounts, but with the 2018 release of Quicken it is now possible.  So, this is another tool that people can use.  The move transactions menu option is on the settings/gear icon for the account, or on the "right click" menu.

Beijing Mac

could you also use a Cut & Paste of the transaction to move from one account to another?  or does that only work with a savings / checking / CC?  maybe I did not do that with investment account.

Unknown

Beijing Mac said:

could you also use a Cut & Paste of the transaction to move from one account to another?  or does that only work with a savings / checking / CC?  maybe I did not do that with investment account.

Cut & Paste only works in the non investment accounts (actually called Copy, and Move on the menus).  And in fact because of a problem of it asking about every single reconciled transaction that gets moved,, the recommended method is to use the copy, and then delete after that.

Unknown

Beijing Mac said:

could you also use a Cut & Paste of the transaction to move from one account to another?  or does that only work with a savings / checking / CC?  maybe I did not do that with investment account.

Note another way that people have dealt with the investment account being the wrong type is to create a new account and use the move securities function, which puts in transactions to remove the security in the old account, and transactions in the new account to add them.  But that method leave the old account/history and I also believe the cost basis isn't correct after the move.