nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Find more posts tagged with

Comments

unknown

I got a bit of a surprise when I was looking at my transactions in the new data file yesterday night.
"Most" of the transfers were "linked". Note I used my program to change all the transfers to categories like xfrAccountName in the QIF file.

In my haste to setup for the QIF import I forgot to turn off transfer detection. It was in its default mode of being on, and not confirmed.

And so it was run for the QIF import and did a quite good job. I said "most" above because transfer detection isn't applied to transactions that have a transfer in a split transaction.

Transfer detection works quite well in my data because I seldom have the same amount repeated around the same date. Your results might vary.

BUT let me say this doesn't happen in the 2018 release of Quicken.
I did this conversion using Quicken 2013 (any version up to Quicken 2017 should do the same).

In the 2018 release of Quicken they have this bug/change in behavior for the QIF imports where the transaction go directly into the register with no processing. That speeds up the process alot because you don't have to do Accept All in each account, but it also means that Transfer detection isn't run for QIF imports.
https://getsatisfaction.com/quickencommunity/topics/qif-importing-into-quicken-windows-2018-is-broken-in-the-r2-3-patch-update

unknown

Last night I decided to take my newly created data file and activate the accounts for downloading.

Note the assumptions here are that I have all my accounts up to date and reconciled. So unless there are any "real new" transactions, the main goal I had was to get the unique Ids recorded in the registers so that they wouldn't be imported again, and to basically get rid of the "overlapping" transactions.

For people that aren't using the automatic entry just deleting downloaded transactions can cause problems because then your old transactions will still show up on the manual match list. But with the way the automatic entry transaction mode's manual match this isn't a problem, so I'm ignoring that problem. For a discussion on this see this thread:
https://getsatisfaction.com/quickencommunity/topics/how-can-i-filter-or-remove-cleared-or-reconciled-items-from-the-manually-match-list

The process of setting up the account for downloading transactions and the removal of transactions in the non investment accounts went through without any real problem (only had a couple of real new transactions to deal with). Note I'm using automatic entry mode since I find it much faster to deal with these.

When I got to the investment accounts is was a different story.

Some accounts nothing downloaded and so there was nothing to do.

But in a 401K account there were a bunch of automatic matches, and a couple of new transactions (there are no "real" new transactions in this account).

This wouldn't have been a problem except that Merrill Lynch downloads reinvested dividends as buys. So each month when these come in I change them to reinvesting dividends.

When these transactions were matched, Quicken changed these to buys. Still not real problem, right? Just unmatch them. Nope I unmatched them and when Quicken split them back apart it changed my reinvestment transaction in the register to a buy.

So, I went to a backup and tried this again. This time with automatic entry mode off, and then I just deleted the transactions in the Downloaded Transactions tab. There wasn't that many of them so that worked.

Then I got to a Roth IRA account the had transactions most of which were matches, that I figured I would deal with the same way. Nope!

As soon as I touch the first transaction Quicken pops up a dialog saying that the cash amount isn't correct and to type in the correct amount. I do that and boom all the transactions are accepted into the register as if I had click Accept All.

So, automatic entry is no good for this account and so is non automatic entry.

Time to pull out the new trick!

Go to back before account was setup for downloading of transactions.
This time for that problem account instead of linking it to the existing account, create a new one.
Accept all the "garbage" transactions/adjustments into the register.
Delete everything in the register (using the banking transaction report trick).
Deleted all the placeholders.
Use the new move investment transaction function to move the transactions from the old account to the new one. Delete old account.

Now that account is setup for downloading and the unique Ids are recorded in it, and it has my correct transactions in it.

unknown

Oh forgot to mention one thing in the process. There is a bug in the QIF import for investment accounts. Even if all your transactions are marked as reconciled in the QIF file, Quicken will not mark all of the transactions it imports into an investment account as reconciled.

So to make sure that you know what transactions are what, be sure to run a reconcile on each investment account you are going to setup for automatic transaction downloaded to make sure all transactions are marked reconciled. Having them marked reconciled makes it a lot easier to see what transactions are added in an given case.

unknown

Well I have used my converted data file some more and wanted to update the "gotchas".

First as implied above the tax line settings don't come through so to have the tax reporting correct you need to go through and reset every tax line on those categories (or at least the categories you use). They will be marked as being tax related, but with no tax line.

Also there seems to be no way to edit the tax line on the built in categories.

Second since my process chances transfers into categories there is part where this has a pretty big negative impact. A transfer to a 401K causes a hidden entry that is used for tax reporting, basically subtracting that amount from your wages, so that it isn't included in the amount taxed.

I made assigned the category created for that transfer to the wages tax line, which makes it subtract from the wages in the tax planner. It isn't perfect, but I think it is good enough for me. For others it won't be and they might have to change it to a real transfer and delete the transaction with the "transfer category" in the 401K account.

Also paychecks are turned into normal split transactions. I have not tested this, but I do believe that means anyone using "Paycheck/Net Pay" in a budget will find that will not be working right.

The bottom line of all of this is that the process will leave you will a pretty big mess, and as such probably only would be done by someone that has a data file that is corrupted beyond fixing, and this is there only resort. And maybe the better way would be just to abandon that data file, and start a new one or go to another product that is better at importing QIF files.

splasher

Would it have helped if you had used the File Operations->Copy with a future date to create an empty file with all of the accounts and categories already in it?

unknown

Would it have helped if you had used the File Operations->Copy with a future date to create an empty file with all of the accounts and categories already in it?

Hmm.. That is something I didn't think of. It would help with making sure that all the account types were correct. And it probably work fix the tax line problem. I will have to try it.

What it won't do of course is make Quicken's QIF import work correctly with transfers.

splasher

Would it have helped if you had used the File Operations->Copy with a future date to create an empty file with all of the accounts and categories already in it?

I just played with a QIF built file too. I had transfers that went from Checking to a Brokerage account which was then split into two transfers into the wife's and my Roth accounts. All of the split transfers were duplicated with half of them being reconciled and the other half having no status at all. Using a transaction report to delete the unreconciled transactions made for an easy cleanup.

unknown

Would it have helped if you had used the File Operations->Copy with a future date to create an empty file with all of the accounts and categories already in it?

OK, I have tried a quick pass at this the results are very promising.
I will work through it over the next few days to give more details, but I wanted to give a "first impression".

Now if someone is going to be able to use this really depends on what is corrupted in their data file, but they can try rebuilding those parts with the QIF import.

I used the copy operation. The first thing I noticed is that Quicken put in an Opening Balance transaction in all my current accounts to get them to my current net worth value. Since I was going to import all the transactions, I deleted each of these.

Next I ran Validate & Repair using both the Validate file and Rebuild investing lots options (but not the deleting of investing price history). And sure enough it cleared out a lot of "QEL" entries. I think the only things those would do is confuse Quicken on the QIF import (or just be junk laying around in my data file).

Next I exported to a QIF file (everything selected just in case) from a "pre copy" data file.

I ran my program to change the transfers in the QIF to regular categories.

I then imported that QIF (only selecting transactions) into the copied data file from above.

This was the 2018 release of Quicken, so everything imported directly into the registers skipping any kind of transfer matching or accepting transactions into the register.

The results are "interesting".

First off the really good points. Since this is a copy of the data file.
Accounts, categories (and tax lines), reminders, reports, and account level attachments, are all there. And also hidden accounts are "hidden" and same for hidden categories.

A lot less work to fix!

All my current account totals are correct.

Now here is where it gets strange.

Some of my "hidden" long ago zeroed accounts are now not zero.

I had a little of this with a couple of accounts before when I did this in a brand new data file, but these are "larger" amounts and more accounts affected, but still not a large amount of accounts to fix.

I will be digging into those accounts and seeing if I can pin down why they are off, but frankly it could simply be long hidden problems. Over the years I have "closed" accounts and created new ones because of "account corruption" so that might be part of it.

At this point I definitely think this is the best approach yet to keep as much information as possible and have the least amount of rework.

unknown

Would it have helped if you had used the File Operations->Copy with a future date to create an empty file with all of the accounts and categories already in it?

P.S. I just thought of one big "problem" with this approach, and most likely why I didn't think of it before. And that is if your goal is to go back to an older version of Quicken your options will be very limited.

If you are on Quicken 2018 you can go back as far as Quicken 2016, but no earlier.

That means for instance you will not be able to go back to a version of Quicken that doesn't have the Quicken Id.

unknown

Would it have helped if you had used the File Operations->Copy with a future date to create an empty file with all of the accounts and categories already in it?

Well I have tracked down a few of the problems in the accounts and about all I can do to "connect them" in different accounts is to say, when you copy the accounts "their problems follow you, and can resurface".

For instance in a credit card account I know that at one point I put in a transaction of $20, but then deleted it because I was trying to zero it before I found some more transactions needed to be imported. The old data file doesn't show that transaction, the QIF file doesn't show that transaction, importing into a new data file/account doesn't show that transaction. But importing into the "copied" data file/account that transaction appears.

In another account it seems for some period of time my paycheck transaction are all missing the split line with the gross pay. The result being a split that is all negative.

Again if I import this into a new data file/account the split imports correctly.

I tested this with both Quicken 2017 and 2018 to make sure it wasn't just an import problem of Quicken 2018.

unknown

Would it have helped if you had used the File Operations->Copy with a future date to create an empty file with all of the accounts and categories already in it?

Well I tried deleting all the transactions in one problem register and then importing just that account through the QIF import, and it worked.

So I decided to try do it for all my accounts, but as I was setting up to do this Quicken crashed. And when I tried to open the data file again, it crashed again.

This is something that has been happening in my data file since Online Bills or whatnot corrupted it. But in has not yet happened in full rebuilt data file.

So I think basically this problem "followed" to because the start of this process was a copy of a problem data file.

unknown

Would it have helped if you had used the File Operations->Copy with a future date to create an empty file with all of the accounts and categories already in it?

More information on the bad tax lines in a brand new data file for hidden/builtin categories that the user can't edit later.

First off let me say that the tax lines are in the QIF export, it is the QIF import that is broken, and doesn't assign these tax lines. Note that if you look at (with a text editor) TAX.SCD in C:\Program Files (x86)\Quicken you will find this entry:
Revision: 057
By: ke
Date: 10/17/2007

Clearly it isn't just a matter of the tax lines changing or something. That date also shows you how well the tax lines are being kept up to date.

So the problem can't be fixed by just changing something in the QIF file.

I noticed that when I created a new data file, the hidden categories weren't in it.
So the question is how do they get created?
And the answer is when an account of the right type is created.
For all the hidden/builtin categories in my data file, creating a 401K account caused them all to be created. For home and business/rental you might need to create some combination of business accounts.

I changed my procedure to create the new data file, create a 401K, and then delete that account (because mine was a dummy one, but if you create one in your real file you can leave it), and then delete the non hidden categories, leaving the hidden/builtin categories.

The main problem here is basically the same problem with creating accounts through QIF. If they are already there then nothing happens, but if they are created by QIF you will run into the limitations of what the QIF import will "properly" create.

Note if you aren't a long time user (somewhere around Quicken 2012) they changed the categories. If you have been using their list and just pruning it back or adding to it, you might be able to skip the whole deleting of categories.

Another thought is to leave the list unchanged and fix things up after the import.
For instance instead of going in and fixing the tax lines one might reassign the category to the "new" category name that replaced it, thereby picking up the tax line it has on it.

Edit:
Note that if you have accounts already created in the data file when you go to delete categories, you have to make sure you do not select them (the transfer category like [Account]) or delete will not be available.