Trimming down my Quicken data files

Hi, Jeff.

You may not yet have read the MANY messages here that discuss the size of our Quicken files and ask "how big is too big?" Our near-unanimous conclusion is that big files do not slow Quicken down noticeably, and the ability to instantly recall years-old data far outweighs any benefits that might be gained from removing old transactions from the file.

You haven't told us how big is "large enough" to cause a slowdown on your "poor PC", which you also haven't described for us. For reference, I've been running Quicken since 1990 and now run Quicken 2007 Basic on Vista Ultimate x64 with 2 GB of RAM and over 600 GB of storage on 4 SATA hard drives. (My system has evolved over the decades; it wasn't this strong until last December.) My *.qdf file is 25 MB, .qel is only 48 KB (I don't download transactions), .qph is nearly 3 MB (that's the historical price file for about 30 securities), and my .qtx file (for income tax stuff) is

4,276 bytes and hasn't changed since 2004, which was probably the last time I tried to use the tax planner. My total fileset (what Quicken refers to collectively as a single file) is just about 30 MB. It's not quite as large as your fileset, but response is as quick - much quicker, in fact - than back in the Windows 98 days, when files were smaller and hardware was slower.

My recommendation is to be sure that you have a good BACKUP strategy, including plenty of hard drive and other storage space, then just let the fileset grow. Then if you need to remember where you bought that humongous

24" TV set back in the early '90s, just scroll back in your check register. You don't have to go searching FOR, and then loading, and then searching IN the 1993 file, then 1992, or was it 1991? ; Running Quicken Deluxe 2006 in Windows XP.

Hi, Jeff.

Sorry about the "you haven't told us" crack. I reread your message after typing that, but forgot to delete it. :^{

Thanks for the fuller explanation of your slowdown. I understand your concern now. But I probably can't help with it, so I'll step back and let John Pollard and others with downloading experience give you some tips.

RC

Hi RC

There is only so much info one should let loose on the web.

My *.qdf file is over 30 MB large. The other sizes have already given out. My PC is a Toshiba notebook with a Intel duo CPU T2050 running at

1.6GHz and 2 G ram running Windows XP MCE.

I do realize the advantages of having everything on one file, but in my case and on my PC - which is not as fast as yours - there has become a noticeable slowdown affecting my handling of downloads - something you may not have noticed because you do not use downloads. I too do not use the Quicken Tax planner.

It is not the Quicken opening file speed or the download speed that is too slow - although that is certainly not lightening fast. In my case, it is whenever I have to "accept" each individual downloaded transaction that is the main problem. When you accept a downloaded transaction, Quicken goes through the files checking where that equity was purchased or sold, how many are left or whatever - and the hourglass stays on while the hard drive light churns away till it finally lets me finally go on to the next downloaded transaction waiting for me to accept. This is what has begun taking much longer lately because there are so many accumulated thousands upon thousands of transactions for Q to sift through and compare.

Incidentally I have several separate investment accounts and in some there is much more investment transaction activities that others. I know - for a fact - that these busy accounts take much longer to even open than my more calm investment accounts. In some of my busy ones it can take as long as 2 minutes (!) as opposed to a few seconds on the less busy ones. So, after a point, size does matter. Any database manager would tell you that stating the reverse is just illogical. And adds to the risk of corruption which is further aggravated in my case because I use an encrypted drive which is why I have frequent backups to external drives.

So, in my case, because I have numerous transactions and accounts, it takes me "hours" (couple of days) to go through my monthly transaction downloads each month - much longer than I recall it using to take when these files were smaller.

So my questions are:

1. "How" does one archive only from a certain date (say 5 years) in the past?
2. How easily are these archives accessed, if ever necessary?
3. Do they have to be re-imported into the current file?
4. Any specific problems (other than loss of access to data on that TV you purchased 12 years ago) caused by eliminating transactions prior to a certain date.

Thanks.

Jeff

R. C. White wrote:

No problem, I know we are all just trying to help each other and I appreciate that. Absolutely no offense taken.

Since I first started using Quicken I have never done a year end procedure (whatever that entails) because - as you've mentioned - the advice on this newsgroup is to not bother with it. Now it is presenting a practical problem of Quicken taking very long to process accepting each individual downloaded sale transaction I tell it to accept. Not a theoretical problem but a practical and real one of time involved. Because I have never reduced the Q file sizes I have no experience and am not sure what the recommended procedure for reducing file size is: archiving, year-end procedure, or whatever and how irreversible (loss of access) these procedures are.

Thanks for your help.

Jeff

R. C. White wrote:

You use the "year-end copy" function from within Quicken. It can be used any time (not just at year end) and allows you to create a new data file containing only transactions after a given date.

They're just plain Quicken data files, so if you name them logically (i.e. using the years of data that are in them as the file name) it's pretty easy to open them. You just open the data file, and the information from that file is there. To open a different data file, just use the "file" menu.

No.

None that I've encountered. I do a "year-end copy" every 3-4 years or so.

My first choice would be to make sure my hardware was not involved in the slowdown; once you archive, it will be increasingly difficult to maintain access to the archived data. Before I archive to increase response time, I'll buy some better hardware.

I think R.C. already mentioned that the QEL file contains data relating to online features; in and of itself, I don't think it is a cause of slowness.

And I think R.C.pointed out that the QPH file is your price history; it definitely can be involved in slow response time. Someone in the Quicken Forums just posted that they wrote a program that would clean out the price history of unwanted prices ... you might try asking that poster if they would share the program.

Baring getting help from some other program, you could do some manual pruning of the price history your own, though it could be a bit cumbersome.

Before doing anything, if you'd like to test to see what effect the price history is having on processing your downloads, try backing up your current data to a new folder (to create a test file), using Windows to delete the QPH file from that new folder, have Quicken open the file in the new folder - then do a download to that new file (which will have an empty price history when you start the download). Since the only change you'll be making to the data (before the download) is to have an empty price history, you should get a feel for what the effect of your price history is on your download processing speed.

My guess is, as you have already noted (or implied) that the majority of your slowness is on account of investment data of one sort or another.

If that's true, your ability to improve your response time by archiving may be quite limited. Just using Quicken's archiving capabilities only works in chunks of data that can be defined by a range of dates; that works fine for non-investment data, but not so well for investment data.

Still assuming the major problem is in investment data; other than just pruning your price history, I think your best bet would be to work out some system where you decide which old investments you no longer need - such as you sold the last share

5 years ago - and manually delete all the transactions for those (and their price history which is not deleted automatically by Quicken). [Actually, if you get to the point of wanting to delete investment transactions, I think Q2006 has a way that you can delete multiple investment transactions at once. You can post back if you get to that point.]

Thank you. That is very helpful.

I would have thought that "archive" was the way to go, but I will look more closely at the "year-end copy" function.

Just scanning some of the recent messages on this newslist I read something about needing to " 'reconcile' each a/c first, the 'CLR' column should have an 'R' in it." or else the files will remain in your "trimmed down" file.

I hope that does not apply to investment or credit card accounts, unless it happens automatically when I "accept" the downloads. Perhaps John Pollard will clarifiy that point.

Jeff

MiR wrote:

I do not think it is a hardware thing because the notebook I am using is only 1 year old and not a cheap basic model. It even has 2 G Ram without using Vista. The hard drive is regularly fragmented and I have the same trouble when I use Quicken on my desktop PC which, though older, is also not a relic. It may have to do with the files being on an encrypted drive but I need to have that for security.

I will do that. Thanks for the pointer.

Would the accumulated price history in the QPH file consist of data I may have accidentally downloaded at one time from Quicken or it that data Quicken acquires from the monthly broker downloads which obviously give the security prices at the times of purchase and sales?

Just so I know what that data consists of, where and how do I do that.

Excellent idea. Will definitely do that. But, just for the record and to avoid a misunderstanding, it is not the download speed that is the problem especially as I have a very fast internet connection.

Where the problem lies for me is that a) when "accepting" each downloaded sale transaction

("Accept dopwnloaded transactions into transaction list", and I click on "accept" on each one to accept it)

Quicken takes a number of seconds to process "each" accepted transaction before I can move on to the next one to accept and these seconds add up if one has a lot of transaction to "accept" every month.

b) certain busy accounts definitely take much longer to open within Quicken than others - which though a nuiscance, I could live with because it only affects certain accounts and is a one time delay.

That is the only place I am having trouble: accepting transactions on certain busy investment accounts

I was afraid of that. It is in some investment accounts that I am having trouble. The rest are OK.

By your using the term "archiving" above, I assume you mean the year end reduction function. Correct?

I guess this form of deleting removes them forever rather than archive them for later availability.

Thanks John. As usual you are a fountain of knowledge. Do not know what this newsgroup would do without you. Thank you for taking the time to try to help.

Jeff

The "Year End Copy" procedure retains all unreconciled transactions and all investment transactions in the "New Year File". [That's per Q2007 (I recently uninstalled Q2006 during some testing and have not reinstalled it yet. Q2005 does not state that specifically, but it is my recollection that all New Year Files had that restriction, but my memory has been known to be wrong.]

You can accomplish essentially the same result (a backup and a new file with transactions removed), with more control of the "reconciled transactions" and the "investment transactions", by using Quicken's "Copy" feature. The "backup" is the file you have been using, your "New" file is the "Copy" Quicken makes.

Jeff

If you really want to do it you can trim your file - including investment transactions - by using the File Copy routine. In the Copy pop-up window, you specify the date range to be preserved and uncheck the two boxes - 'Include All Prior Uncleared Transactions' and 'Include All Prior Investment Transactions'.

The Copy routine handles non-investment accounts satisfactorily - the old transactions are deleted and the new beginning balance for each account is automatically adjusted.

The Copy routine wreaks havoc with an investment account:

1) For securities held on the cutoff date - all prior info is lost - acquisition date, acquisition cost [cost basis], any reinvested dividends, splits, ROC, etc, etc - this info is all deleted/lost - you can be left with meaningless data.

2) QW does not carry over the beginning cash balance [if it is other than zero].

You can get around these problems with some advance preparation - I have done this in a test file.

Assume a cutoff date of 01/01/2002 [5+ yrs history] Open a portfolio view for the cutoff date and note all security holdings as of this date - recommend you print a copy. Also, note the beginning cash balance for each investment account for this date - e.g., cash balances for 12/31/2001. Enter a Corporate Acquisition transaction for each security holding - e.g., MSFT buys MSFT at 1:1 sh/sh on 01/01/2002. Note that this procedure creates a register entry that will carry forward the acqusition date and cost basis for each security.to the cutoff date. Execute the File Copy routine per above. Open the new trimmed file and delete the 'Shares Removed' transactions datedl 01/01/2002. These are the Shares Removed created by the CorpAcq above. Insert an 'Opening Balance' transactions for each investment account dated 01/01/2002.

Your investment accounts should now be in balance.

As indicated above, I have done this in a test file and it works. It is tedious. It is also prone to input error so backup before launching. in to it.

Would I recommend all users do this - NO!! - I'll second RC's and John's earlier observations/comments.

There are at least 4 ways prices get in your price history: Quicken quotes/historical-price downloads, Quicken updates from your financial institution holdings downloads, the entry of investment transactions that involve a price ... if there is not already a price in your history for that date, and user manual updates of the price history.

The most straight forward method is to Edit Price History and delete the prices you do not need. You can apply some logic to choosing the prices to delete, such as deleting prices that date prior to your ownership of a security or after you sold all shares of a security. Possibly deleting non-month end prices for periods for which you only look at month end market values. Etc.

Sorry, my phrasing was not good (should have said something like, " ... your downloaded transaction processing speed" or your "Accept speed"). I did/do understand the problem you're having; none of my suggestions were addressed to the actual download time ... just to the "Accept" time.

Yes. But that feature seems to be broken in the newer versions of Quicken. The suggestion has been to use the Quicken "File Operations > Copy" feature, rather than the Year End feature. But both suffer from the same limitations for your purposes.

Yes it does; but what I forgot to mention in my previous post is that you can just keep a backup, prior to deleting any data, for your "archived" data.

Thank you. Will explore.

Jeff

Thank you very much John. That helps explain a lot.

Jeff

Thank you very much,

Jeff