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]
