I just "succeeded" in recreating my data file using a QIF export/import
QPW
Member ✭✭✭✭
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.
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.
0
This discussion has been closed.
Comments
Questions? Check out the Quicken Windows FAQ list
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.
You can't QIF ESPPs or employee stock options. You also can't QIF reminders.
Questions? Check out the Quicken Windows FAQ list
Questions? Check out the Quicken Windows FAQ list
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).
Questions? Check out the Quicken Windows FAQ list
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.
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.
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.
Questions? Check out the Quicken Windows FAQ list
Questions? Check out the Quicken Windows FAQ list
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. :-)
Questions? Check out the Quicken Windows FAQ list
I also did Validate & Repair and that didn't make any difference either.
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.
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.
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.
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".
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.
http://www.quicknperlwiz.com/changetransfers.html
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.
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.