Partially trimming data file to last 10 years (edit)

jgackson

My data file goes back to about 1980, including all the accounts and assets that have come and gone since then. Now and then it's required V&R, but that's gone reasonably smoothly. And it's appeared to be working fine for some months now, maybe even a couple of years.

But, silly me, when I started getting those "enter a valid date" popups I thought it might be wise to to a V&R even though everything seemed fine. Big mistake: the V&R told me there were several unknown accounts, and then lots of problem transactions involving those accounts, and then created placeholder names for about 10-15 such ghost accounts (all with zero balances) and almost doubled in size (from 300MB to 500MB).

Looking at the transactions in the ghost accounts, I could identify most of them—they were credit-card and asset accounts that we'd stopped using and I'd closed years ago. I went through and laboriously renamed the ghost accounts, then re-closed them, but then realized that the V&R apparently had duplicated lots of transactions involving the ghost accounts, which presumably is why the file size grew so much.

In the event I decided not to work with the "repaired" file, and instead restored a backup from a few days ago. Looking at the restored file, I can see that old accounts are missing from the account list (even showing hidden accounts), and those lost accounts seem to correspond to the ghost accounts the V&R found. Since the lost accounts are all closed, they're not having any effect on my current operations, and so I'm continuing to use the restored file.

But I think the right answer, if there's an easy way to do it, is to somehow trim my working file of all transactions that are, say, more than 10 years old, but to do that without affecting the balances in accounts I'm still using—sort of the "year end archive" process, but keeping 10 years instead of 1, or the "create a copy" process, but keeping the OSU connections live.

Anyone know how to do that, other than by creating a copy trimmed to 10 years and then reactivating all the OSU connnections?

Quicken Kristina

Hello @jgackson,

The tools in Quicken that can trim a file are Year end Archive, Create a Copy, and in investment accounts, there's an option to archive transactions. None of those tools would do what you are wanting, since even the one that comes the closest (create a copy), would still require you to reconnect your financial institutions.

If the restored file is working as needed, then perhaps the easiest path forward is to keep using the restored file, and make sure you have frequent backups to restore from if needed.

If the file isn't working as needed, then you can try removing older accounts and transactions manually. Unfortunately, that requires a lot of work, so it wouldn't count as an easy fix.

Another possibility is to start a new Quicken file. The benefit of doing this is starting fresh would get rid of any data issues (and as long as you don't delete or overwrite the old file, it would still be available for reference). The down side (aside from having to set everything back up again) is not all financial institutions would offer the amount of history you want. You may be able to download .QFX files from the financial institutions web sites (if they offer that option) to get additional history, but even then, you may not be able to get a full 10 years worth of data.

I hope this helps!

jgackson

Problem is, the "repaired" file is almost twice as large as the pre-validation version, and I suspect it contains lots of duplicated transactions, so I'm reluctant to depend on it. I'm going to experiment with activating OSU in a "create a copy" trimmed version.

For those of us who depend on Quicken for history, the suggestion to create a new file is pointless. Some of the institutions that hosted my old accounts no longer exist!

jgackson

Having tried these and various other suggestions, I'm now totally frustrated. Let me summarize the problem, what I'm trying to achieve, and what I've tried, see if anyone else has useful thoughts.

Problem: I've got my financial history since around 1980 in a Quicken file that's grown, through additions and updates, to about 300MB. It includes lots of long-gone accounts, some of which don't matter (old department store accounts) and some of which do (houses, bank accounts, investments). I periodically validate my data file, which usually just uncovers a few easily fixed problems. But the last validation was different: it found lots of mystery accounts, which I gradually traced to old bank and asset accounts, and in the process of trying to salvage those it apparently created lots of duplicate transactions that had been transfers—at least that's my best guess. And the "repaired" file grew to over 500MB. Rather than try to fix all the broken transactions, I decided to simply keep using my existing pre-validation file, and to try to break it into (a) an archive of transactions before, say, 1/1/2010, and (b) an active file including everything since then.

It looked as though the "year-end archive" function could achieve that, since you can in theory replace your active file with one that includes only transactions since a given date. So I tried that.

Two new problems: First, it wouldn't create the year-end archive unless I told it to ignore errors—which seemed okay. Second, and not okay, it simply wouldn't create the trimmed active file, just gave a "file NOT copied" error, with no code or explanation, and left the file intact.

So I'm still stuck with an active file that's got some corruption (but works fine day-to-day), and a "repaired" file that's apparently even more problematic because of broken transactions.

Anyone have any thoughts how I can get where I want to be, namely an archive of older transactions plus an active file with only transactions since then (but balances and so forth accurate)? There are lots of non-downloadable transactions, so the suggestion that I create a new file that way (that is, a blank one, then let my institutions download everything) is a non-starter.

Rocket J Squirrel

We always used to advise performing a File Copy before performing Validate & Repair, then validating the new copy. The File Copy command is the only operation that leaves behind deleted items, reducing file size. Your current file still has all those old deleted accounts and who knows what other junk; you just can't see them. I guess we stopped advising the pre-validation copy when Copy painfully started disconnecting online accounts. But doing so may be worth the effort.

Ps56k2

my simple comment - I would NOT try to trim any Quicken file for any reason -
doesn't make any sense to me….. doesn't impact performance, and probably not large enough to impact disk space -
so - why bother - better to have ALL your financial data in one single QDF file for easy access and reporting -

jgackson

As to File Copy, yes, that's always been my practice before and I'd tried it with the partially corrupted file. Quicken says "copy file unsuccessful", with no further error message; the "copy" is about half the size of the original, and when I open it it contains no accounts and no transactions. So, fail.

And as to just continuing with the big file, that's what I've been doing, but it's corrupted slightly (as I mentioned earlier) and that's worrisome. The size causes it to load slowly, and of course the automatic backups rapidly take up lots of disk space and so need to be pruned now and then.

Rocket J Squirrel

Did you try File Copy with the "ignore errors" option? It's available via Ctrl-Shift-Next on the Backup or Copy File dialog when "Create a copy or template" is selected.

jgackson

yup, did that. It’s what yielded the half-size file with no transactions or accounts.

Chris_QPW

The very fact that copy won't work really suggests a data file that quite corrupted as you suggested. And note it probably wouldn't be the file size that makes it load slower, it would be the corruption.

It might be a lot of work, but you might want to consider something like this:

https://www.quicknperlwiz.com/changetransfers.html

jgackson

Yes, it does look like an account-by-account QIF transfer is the likely end strategy. I tried doing a QXF export, but it failed two accounts in without any explanation (although I haven't delved into the logs yet). My next experiment, although I'm not crazy about it, is to open a copy of my current (working but somewhat corrupted) file, delete all of the closed and "recovered" files with zero balances, and then try validating the result to see if the corruption was perhaps in the deleted accounts; if it works, that should give me a non-corrupted file that at least has all my current accounts. I can keep the old partially-corrupted file around for archival purposes, but I'll have lost the ability to do, say, long-term net-worth tracking, or see how our restaurant v. market spending ratio has evolved over the years, etc.

Sad, since that's been a major benefit of having used Quicken for decades. Also sad that Quicken seems totally uninterested in helping.

If that fails, then it'll be account-by-account qifs, probably omitting the deleted/closed ones, which presumably will unearth the problem accounts since they presumably won't export/import successfully.

On verra…