QDF File Corruption and Repair – Why and What To Do? (LONG POST)

wsalomon

The premises of the questions/issues are the following:

• Current version – R-56.9
• Long-time Quicken user since roughly 1999.
• Some data back to 1991 for historical purposes (basis for rolled over securities, basis for house improvements over 30 years)
• Contemporaneous downloads from Banks and Brokerages since 2000
• Some terminated Brokerages hidden, but not deleted (to get basis transactions)
• All Brokerages treated as such - *not* held as IRA’s
• Back-ups made every several weeks to an internal D:/ drive partition, archived to Cloud FTP periodically (every 1-2 months, in cash of catastrophic local crash
• A number of laptop “adverse shutdowns” have occurred of the years. (“Adverse shutdown” means something like cord suddenly pulled out without a battery, battery falling out without AC power. Implication – insufficient time to mount a Windows “sleep” response when software is “mid-cycle” – AKA mid-read and or mid-write for a database that may not have “rewrite” logs).
• Over time, “mysteriously-named” (5-character names) Categories have appeared in Quicken with no apparent associated Transactions. Same for Bank accounts with the same type of names.
• On one day (only), a number of Brokerage Transactions occurred with "0 shares+, and nothing appearing in the Security Buy/Sell log.
• Multiple Validations done of the years (but only recently found out about Super Validation) – At present, every other Validation results I n the messages shown below.

My level of understanding:

• 40 years computer experience, since 1982 with first IBM PC’s at hardware level.
• Some software development for Electronic Medical Records.
• Relational Database (SQL Server, My SQL, Oracle, etc.) at database construction level, levels of table normalization, writing to database with enforced referential integrity using primary/foreign key constraints, and “cheating” using Stored Procedures (a terrible idea going back to Object-Oriented Database in the 90s, resulting in update anomalies if poorly done – a debugging nightmare)
• My understanding is the Quicken has, for years, used SQL-Lite as its embedded database engine (DBE). For those unfamiliar, SQL-Lite bills itself as “light-weight” DBE that is installed with the Quicken .exe, not as a standalone like SQL-Server, My SQL, etc. with “hooks” into application for transfer of data. As a result, what processes occur between the DBE and Quicken “app” is opaque. Also, there is apparent no way to access the data outside the tools, reports, etc. that Quicken provides. As a result, there is no what to use ordinary SQL Tools to access the data model (Entity Relationship Diagrams), and to pull data out directly to see where errors have occurred. Of course, I understand as well as anyone the “proprietary” nature of Quicken (and its Intellectual Property), but with almost 30 years it is virtually guaranteed that data will be corrupted. At that point, the trope of “go back to your last uncorrupted back-up” is not necessary a fix as (depending where you are on the “learning curve”) you may not realize until months or years later that corruption occurred until one learns to use a new “feature”, or that one of the database read-write processes has changed in a way that impinges on your data.

Over the past 2 years have suspected that my data has slowly been getting corrupted.

The main “offenders” have been several IRA taxable Brokerage Accounts with TIAA - accounts initiated in 6 years ago as Brokerage IRA Rollovers from Fidelity and from TIAA itself as 403(b) accounts to standard IRA and taxable Brokerage accounts. TIAA uses a version of double-entry accounting to keep track of an accounts Cash Balance.

That is, any transaction results in a credit or debit to the Cash Account, then a corresponding Purchase or Sale of $1/share Cash Account Shares with a nonsensical/proprietary Ticker Symbol/CUSIP number. As a result, when one looks at the Brokerage Account in the Investment Portfolio view, one sees the usual Stock/Fund holdings AND the Cash Account Shares with a “Cash” value at the bottom of the Account as $0.00.

I find this really “unhelpful” for planning at a glance, where I would like to see all the Cash values at the bottom of each account so I know how to talk with my Account Manager, and plan transfers To/From IRA to Brokerage and/or Checking Accounts (such as in the Figure).

The only way I could see to deal with is to manually delete Cash Deposit Account Shares EXCEPT for the first one of the month which was the Cash Balance Interest which was converted from “Cash” to “Interest_Income” so at the end of the year, it would agree with my 1099-INT.

I can *usually* do these without error (using a Ctrl-D / Delete) for the transaction. The “check” on this after a download is that each account shows a Share Mismatch – the number of shares equaling to Cash Balance of the account.

The second “check” is to download the Activity Report from TIAA to check for errors. However, unlike many Brokerages, TIAA only reports 17 months of “Activity”. Failing that (and it did for one account) meant having to go back to PDF Statements from the beginning, and by pulling one statement a year, then ones in between, found one wrong transaction in 2020 and comparing the running totals by comparing Excel Spreadsheets.

Straightening Credit Cards was far simpler as a dump from CitiBank was easily reformatted to match the Quicken Export.

The second thing I noticed was the presence of oddly named Categories in the Category Listing. Each of these started with “14” followed by 3 more alphanumeric characters (like “1458a”). None of these had any transactions.

Interestingly, these same transactions were listed when running Reports (like Spending), when customizing reports using the pick-lists for Categories. In that case, some of the these “unusual” items were marked as “Bank”, the rest as Expense.

When running a Report (for all dates), the are no Transactions.

Also, when running Validations (eventually), I start to see in every other “run” that one of these odd categories would show up as an error, then disappear on the next run, only to have a different one show up.

I have repeated the process numerous time, and new "defective category is repaired each time. I’m not sure in what order they have appeared, or even if I’ve run through them all, or if new ones are appearing (now that I have been sensitized to it).

Lastly, a some point (more than 3 years ago, 9 “Added” transactions appeared on one date (4/9/2019) with the Memo “0 shares added to account“. (The Figure is from a Register Export)

All Securities had been added since the account inception (10/1/2018), and one was missing – the Security that had been sold 2 months before 4/9/2019. None of these 9 appear as an entry in the Security Detail View Transaction History table.

Another continuing issue is that of incomplete or prematurely terminated One Step (or single) Updates, especially for TIAA. Other “bad actors” have been BarclayCard, Capital One 360, and rarely CitiBank. Over the past several months there has a been a re-authentication process for some of these that no longer store the actual password in the Password Vault (indicated by No Longer Required) (not true of TIAA).

My questions:

• Why is all this happening in the first place?
• Is there a better way to fix these corrupt files beyond Validiate and Super Validate?
• What is the difference between Validate and Super Validate
• Are there any of SQL-type query tools beyond Reports and individual Register Exports accessible by a hidden menu (such as like using Ctr-Shft to access Super Validate).

Thanks!

[Edited - Personal information removed from screenshots]

wsalomon

1. I'm not sure why the "ACCT_14xxx"'s were removed as "Personal Information" (the was nothing personal about it - the xxx being Hex digits that the system randomly assigned, and I put it there as a exemplar of exactly what Quicken was doing wrong for debugging purposes).
2. This post is a comprehensive rehash with new information from a post I made in November 2023. It was suggested at that time the Validations and Super Validations would remover the "offending" Accounts.
3. I tried removing these "accounts" in the Category List without success. Also those that were alternatively listed as Bank from the Report customization widget are NOT seen in the Account List.

Chris_QPW

The ACCT_ accounts are temporary accounts that Quicken creates while doing certain operations. In normal practice they would be removed at the end of that operation. If something crashes Quicken in the middle of such an operation the temporary account will not get deleted.

As to what can cause problems?

My bets would be on, a) Bugs in Quicken, b) external disruption.

Quicken was definitely designed with the assumption that the disk it is working on is 100% reliable. There isn't any kind of fault tolerance built into it.

The database (which is in fact a _QDF file inside of the QDF file which is a compressed file like a Zip file) is a very old database. Have no idea if it is SQL or not. Do know there isn't any way you are going to use any tool on it. Also 99% that it has no rollback features.

Validate & Repair among other things is looking for consistency problems like when it has records that point to other records, that go missing (or the "pointer" is just corrupted).

For these reasons this why Quicken Inc suggest not keeping your data file on any remote device that might not be 100% reliable.

When things get corrupted all bets are off.

How much can be repaired? Unknown.

Will the "repair" actually cause more problems? Possible. For instance, to make things "consistent" Validate & Repair might remove data that it can't find a valid connection to. You will notice that Validate & Repair always makes a backup before doing anything. The developers clearly know that it is a "dangerous operation". Note there is only one backup of this kind in the VALIDATE folder where your data file is, so running it more than once will remove the original copy of the data file without prompting you to overwrite it.

If one is lucky enough to catch a corruption the best action is to restore from a backup, not use Validate & Repair even though it gets suggested like it is some kind of magic.

If a data file gets too corrupted, then maybe you have to consider something like this (this is a lot of work and might not work for everyone):

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

wsalomon

Chris - Thanks for your thoughts (and you offline ones as well.

To bring you up to date…

A Partial Fix –

(Call to Quicken Support on 2024-05-10 Trouble Ticket #11052556)

The above with discussed with Quicken Support.

1. The list of “unusual” Categories was brought up and deleted one-by-one in the Category List.

2. Validation was run, with no new unusual Categories.

3. Validation was rerun, result in a new unusual Category.

4. New unusual category deleted.

5. File was copied to a new file.

6. File size of new file was down from 214 MB to 200 MB.

7. Validation was run on the new file.

8. Validation ran in *half* the time, and the status display never disappeared (as it had previously).

9. *No* new unusual Categories appeared.

10. Validation was run again, again *no* new unusual Categories appeared (as they had every time previously.)

11. File was copied again and was 2 MB smaller than previous copy.

12. This file is considered the final product.

This did *not* fixed the second problem (the addition of 9 "0 shares added" Transactions on 04-19-2019)

13. Tried two runs of "Super Validation" which did not get rid of the 9 added transactions.

Conclusion – a limited copy of the file (not Complete) omits some defective data and probably reorganizes/defragments it. Such a copy should be made after major file clean-ups.

Problem is now 95% fixed for continuing purposes.

NotACPA

If you're going to reject responses without explaining why … you're probably not going to get much more help, because we don't know what was wrong with the prior reply.