Is there way to optimize my Quicken Mac data?

Michael 23

I'm using Quicken Classic Deluxe (7.5.2) on macOS 14.3. My issue is that opening and closing my data file takes a long time, like 60 seconds.

My data file has 21,267 items (as displayed on All Transactions), which is 1.26 GB, as it appears in Finder. I'm sure the file size is why it takes so long to open and Close. The file is stored under the Application Support / Quicken folder.

I don't store images, attachments, or anything else. It's just the transaction data.

I looked to see if there might be a utility that might restructure the file or optimize it in some way, but I couldn't find anything. I'd sure appreciate it if anyone has a suggestion.

Jon

Something's off there - I have twice as many transactions in my file (over 43K) but it's only 160MB (when uncompressed).

You might try exporting a QXF and reimporting it into a new file to see if that reduces the size.

jacobs

I agree with @Jon. To provide another data point, my data file contains 71,000 transactions and is only 95 MB in size. It opens in about 5 seconds and closes in about 2 seconds.

Note that the approach of exporting to a QXF file and importing that into a new data file all preserve all your transacitons, but you will need to re-configure your settings, register columns, and custom reports, so it's often a last resort type of solution. But if it solves whatever is wrong with your data file, it might be worth the temporary extra work to get it set up again. If you decide this cure is too problematic, you can just keep using your current file. But even doing it as a test could help pinpoint that there's a problem in your data file.

dan.greenberg.ct

Is it possible that the transaction count only shows what's displayed for accounts shown on the left and not for closed accounts? While I'm not having any slowness opening Quicken running on a 2019 MacBook Pro with 16gb RAM, I only show 22k transactions but my file size is 1.73GB when Quicken is open but then shrinks to 494MB after closing. Quicken is slower quitting than opening which I always attributed to creating the backup file. I have 20+ years of data in the file I think.

Edit: It actually took me about 30 seconds to open the file. I guess it never bothered me because I usually keep it open. Also, I'm known for my patience /s.

jacobs

Aha! I think you may have revealed a key clue: the changing file size. Do you have your data file stored on iCloud or DropBox? Or in your Documents folder, and you have iCloud Drive enabled in your macOS Settings, such that the Desktop and Documents folders are actually stored on iCloud? Storing the data file on a cloud storage service is a no-no for several reasons, one of which can be slowness — and changing file size between open and closed state. Try creating a new Quicken folder in your Home folder (e.g. Macintosh HD > Users > [your user account name]), quit Quicken, and move your data file there. Then double-click on the file to launch Quicken and see what happens. I'm guessing it will open quickly. Edit a transaction or create a dummy transaction, then quit Quicken, and I bet it will make the backup and quite quickly as well.

In terms of number of transactions, if you click on All Transactions in the left sidebar, the number of transactions on the right side of the screen under the Search box will reflect all transactions in all accounts, including closed accounts — because All Transactions includes closed account so you can do a Search in your data without having to unhide any accounts.

Side note: it's good to always quit Quicken at the end of a day or session, so that it automatically generates a backup file. In Settings > General, also make sure you change the preference for File Backup to keep at Last 20 Files or more. Of course, with it taking so long to launch and quit, I understand why you choose to leave Quicken running day to day, but once we get to the bottom of the long times, I recommend you change your habit so that you quit Quicken when not working on it for a while.

Oh, and it is fine to have your Quicken Backup folder located on iCloud, if you want your backups to reside in the cloud in case of disaster. You just can't have your live data file there.

dan.greenberg.ct

Jacobs, I think you may be conflating my comment about leaving the file open and changing file sizes with Michael23 who originally posted it. Regardless, your suggestions are definitely something he should look at.

jacobs

@dan.greenberg.ct Yes, you're correct that I was excited about seeing a possible cause of a problem and failed to note it was two different people who had posted. 🤦🏻‍♂️ Sorry for the confusion!

So that still leaves me wondering about your situation: why is the file size changing, and why does it take so long? If you don't have your file stored in the Cloud the only other explanation I can think of is if you keep your file on an encrypted volume; I don't do that, so I don't know if it affects the file size and start-up/backup times.

dan.greenberg.ct

I do use FileVault but I always assumed that any file gets compressed as it's closed, of course with no evidence to support that other than the Quicken database.

Jon

@dan.greenberg.ct @jacobs The file size changing is typical for me - I believe it's the option on the Security tab in Settings to encrypt the file, it also compresses it at the same time. Mine goes from 50MB when Quicken is closed to 160MB when it's open. I don't think it has anything to do with Mac OS or FileVault.

Mine takes a couple seconds to open/close on a Mac Studio, a couple seconds longer on a MacBook Air.

jacobs

Thanks, @Jon. But if encryption isn't the issue, and the file location is in the default User Library folder, do we have any ideas why @Michael 23 has 21,000 transactions (with no attachments), a data file size of 1,260 MB, and a launch time of about 60 seconds, while my file has more than triple the transactions (71,000) but is less than a tenth the size (95 MB) and launched in less than a tenth the time (about 5 seconds)? I'm stumped. (I do think your original idea to try exporting to QXF and importing into a new file would be a good diagnostic tool and possible solution.)

Jon

I don't have any idea, no. This has come up at least once before, we didn't get any resolution then.

On suggestion from that older thread was to save a backup & then restore. That would be better than the QXF option if it works.

jacobs

One suggestion from that older thread was to save a backup & then restore. That would be better than the QXF option if it works.

I suppose it’s worth a shot, since it would take only a minute to try — but I’d be surprised if that accomplished anything. A Quicken Mac backup doesn’t output the file record by record; it simply duplicates the file and zips it. Similarly, restoring simply removes the ‘backup wrapper’. I can’t see where that process would eliminate any problem with the file.

jacobs

@Michael 23 One thing we haven't looked at yet is what is accounting for the unexpected bloat in your data file. Based on my data, I'd expect your data file to be about 50 MB or less, not 25 times larger (1.26 GB). Here's a quick thing to take a look at…

In the Finder, navigate to where your Quicken data file lives. Then Control-click (once) on the file, and from the pop-up menu, select "Show Package Contents". A Quicken data file is actually not a single file, but is a "package" file which is like a wrapper around any number of files and folders so that the Finder sees the collection as a single file. When you Show Package Contents, you'll see a regular looking folder window like this:

The "data" document here is the actual Quicken Mac database. Note that in my case, "data" is 90 MB in size, almost all of the 94.8 MB of the data file's size. (If I do a Get Info on the other folders, most are blank, and 4.7 of the remaining 4.8 MB is in the "reports" folder.) If you use features of Quicken which I don't, you may notice some minor variations, but all we're interested in here is whether the "data" document — the actual SQLite database — accounts for almost all of your file size. If not, then look at the other folders, particularly "attachments" and "converted" to see if either one of those is large.

If your "data" document indeed accounts for most of the 1.26 GB, then whatever bloat exists is inside the database, and there are no tools to dig more deeply into that; the export/import process we've described above would be expected to result in a tiny file by comparison.

Please post back what you find.

Jon

Note that if you have the setting to encrypt the file enabled, the contents of your Quicken file won't look like the picture @jacobs posted unless you look at it while Quicken is running. When Quicken is closed you'll have just one big "catalog" file along with a very small "data" file & a "locks" folder, which won't really tell us anything.

dan.greenberg.ct

@Jon @jacobs Just adding my two cents again, I did as suggested with my file open (I'm not Michael23) and my data file is 1.72 GB. I am tempted to see if I could use MySQL to query a copy of the database to see why it's so big compared to yours. I wonder since I leave it open and it's updating stock prices every hour, if it retains all of the intraday prices even though you can only see current and prior day ending prices. Given then my transaction count is on par with both of yours, relatively speaking, I'm not sure what else could account for the difference.

dan.greenberg.ct

Well, I got bored and nerdy. I loaded a backup, copied the data file out of the package, and opened it using a trial version of SQLLiteFlow to look at the database. It has an option to look at all of the table sizes. I sorted by size and the ZFIPosition table was 1.07GB with almost 5 million records. I browsed it to see if I could figure out what it was and sorted by ZSECURITYNAME and noticed that each security is listed a ton so I'm guessing if I bought a stock 20 years ago, it shows up in that table at a minimum for each daily update. Multiply that with 20 years of different holdings and it's possible it gets that big. I just did a select count(*) from ZFIPOSITION where ZSECURITYNAME like "APPL%" to get a count of Apple records that I've had for a while and that alone got almost 90,000 records.

jacobs

Interesting… I thought Quicken Mac purged older prices to weekly and then monthly over time, sort of like Apple's Time Machine backups start as hourly, then get thinned to daily, and then to weekly.

When I look at my Apple Price History in Quicken, there are daily prices going back a few years, but then weekly (mostly Friday) prices in 2020 and earlier, and monthly (month-end) prices for 2019 or earlier. BUT… that goes back to when I was using the predecessor Quicken Mac 2007, so I don't know if it kept the history this way, or if I simply didn't have it automatically downloading prices at all and these older prices were imported when I moved to modern Quicken Mac. My Apple prices go back to 1999, but probably comprise only about 1,200 price records.

dan.greenberg.ct

I'm trying now to figure out how to convert the sql timestamps to something readable. Do you update just once per day? I'm looking at one stock that I only held for 7 months. That has over 6500 records in that table. I'm still not sure it's pricing that causes the updates though. There is another table called ZSECURITYQUOTE which sounds like the more likely candidate. I also wonder if the history you're referring to in the pricing screen just displays the last quote for the day and perhaps the tables contain more intraday data. But obviously I'm just guessing and mostly pursuing this because I have a similar database size to the person originally posting the question.

jacobs

I don't update every day; I don't have the "automatically update investment quotes every 60 minutes" preference checked, and I have the "Download quotes only for indexes and securities held" checkbox on the Securities window checked. And I don't have a ton of securities — just under 100. So I probably have a lot less stock quote data than you and many others.

dan.greenberg.ct

I just looked and apparently I also go back to 1993 for the oldest transactions. I also have the same Securities window checkbox set the same as you. IIRC, there used to be an option in an old version of Quicken to compact the database. I'm guessing that it may do the purging of some of the excess records that I'm seeing.

Jon

@jacobs "I thought Quicken Mac purged older prices to weekly and then monthly over time, sort of like Apple's Time Machine backups start as hourly, then get thinned to daily, and then to weekly."

No. Quicken will download and keep daily prices for all securities in the Securities list unless you check the box in the Securities window telling it to only download prices for securities you currently hold (I believe this box is checked by default); if that box is checked it will stop downloading daily prices once you sell off all your holdings but the daily prices already downloaded will remain & do not get cleared out. I have daily stock prices for some stocks in my file going back as much as 20 years.

If you add a new security to your list, for that security Quicken will download daily prices for the last month, then end of week prices for the 11 months preceding that, then end of month prices for the 4 years preceding that. (I believe this is also what you will get if you select "Rebuild price history" option in the security prices window.)

Jon

As for automatically updating prices every 60 minutes, I do have that box checked (is that the default?) but I don't intentionally leave Quicken running when I'm not actively using it so having it checked probably doesn't do much for me.