Why is Quicken booking unrealized gains for investment accounts?

dave344

Quicken appears to be recording periodic _unrealized_ gains/losses in the database.

Why would it do this? The general rule is that you do not store calculated values in a database. You store the values from which they are calculated (price, date, cost, shares, etc.) but not the calculated values themselves.

When I run a YTD transaction report on a small investment account with 7 securities and do not include unrealized gains, I get 11 transactions; all dividend or interest distributions. But when I run the same report with unrealized gains, I get 352 transactions: the 11 dividend/interest distributions plus 341 unrealized gains. Basically it looks like Quicken records an unrealized gain every Friday for each security.

What purpose does it serve to actually book an unrealized gain? You want to calculate unrealized gains when you run a report but you don't want to store them in your database. They are essentially redundant, they can be calculated from existing data. If you store this type of data you're like a man with two watches: unless they're in exact agreement, you can never be sure what time it is. What happens if you correct a security's price history? Does Quicken recalculate all the unrealized gains?

The reason I ask is that my Quicken reports are inaccurate. All of my account balances in the "Accounts" section of the application are correct. But when I generate a net worth report, several of the investment account balances are widely off. The differences appear to be due to unrealized gains.

So my questions are:
1. Why is Quicken recording unrealized gains? Is this something new? (I have been using Quicken since 1992 and have never encountered this issue before).
2. Is it possible to turn off this "feature" and have Quicken calculate unrealized gains only "on the fly" (i.e., when report is generated)?
3. Is deleting all of the unrealized gain transactions a possible solution to my report accuracy problem?

Attached screen shots that help illustrate the problem.

Thank you for any insights.
Dave

Tom Young

I wonder if you have some sort of data corruption issue?  I just ran an Investment Transactions report and looked at the included Categories under the Categories tab and _UnrlzdGains was included, but there were no entries for unrealized gains in the report.  

As far as I know unrealized gain has always been calculated on the fly and I'm not seeing anything different here using R34.24.

dave344

Thanks for that insight Tom.

So just to be clear, you do not have an _UnrlzGain entries in your transaction report?

Attached is a screenshot that shows what I am seeing. Just looking at the first month of the year, it does appear that the report writer is aggregating all of the individual transactions to a compute an "unrealized gain" value.

This would be a horrible database design if this is the case. But it clearly looks like that is the intent. I do not think it is "data corruption" which I would expect to be more random and unpredictable. These entries are calculated and recorded on a regular basis, and in most cases they produce accurate reports. However such a design is highly susceptible to errors that would be difficult to find and correct.

What could possibly cause Quicken to start booking unrealized gains like this? I have 13 investment accounts and this appears to be occurring in at least 4 of them.

BTW I am using Version R34.24/Build 27.1.34.24 on the PC (Windows 10 Enterprise).

dave344

Just for clarification, attached is a simple illustration of why recording calculated values in a database is bad design.

This example uses a price change to introduce an error in an aggregated value. Errors could also be introduced through changes in the number of shares, the security, purchase date, etc.

What could have happened to make Quicken start implementing this methodology (at least in my case)? As I mentioned in my original post, I have been using Quicken since 1992 and I have never encountered this before.

Tom Young

"So just to be clear, you do not have an _UnrlzGain entries in your transaction report?"

That is correct.  Running a year to date Investment Transactions report for all my investment Accounts I do not see any _UnrlzGain line items.  I also ran a couple of Investment Transaction reports on some individual Accounts without seeing unrealized gain line items.

To the best of my knowledge and belief only transactions actually recorded in a transaction list should show up on this report.  You don't say that you're seeing unrealized transactions recorded in your Accounts so I have to think this is a program or data issue. 

I think the first thing to try is a Validate and Repair with investment lots recalculated and see if that puts things to right.  If that doesn't work then a reinstall of Quicken would be my next attempt.  A superuser has posted a real "belt and suspenders" approach to uninstalling then reinstalling Quicken that you might try:

https://community.quicken.com/discussion/7892218/can-i-safely-uninstall-all-previous-versions-of-quicken-on-my-laptop

Sherlock

In my experience with Include unrealized gains checked, the report includes entries for unrealized gain calculated using the securities price history.  They are not actual transactions entered in the account's transaction list.

dave344

Thank you very much Tom.

I have done numerous supervalidations with no effect. They consistently report a single exception regarding a stock split that I am not sure how to interpret. The data in the register looks correct. Otherwise, the validations do not report any data issues (see most recent attached).

I will take a look at the uninstall/reinstall instructions and see if that helps.

Thank you.

dave344

Here is a summary of my issue:

Investment account balances in Quicken reports do not agree with their actual balances

Investment balances shown in the "Accounts" column in Quicken DO reflect the correct account balances

Only investment accounts are misreported; all other asset and liability accounts are correct in both the "accounts" column and in reports

The discrepancies between the correct values and those shown in a recent Net Worth report range from $890 to $145K

There are numerous "Unrealized gains" appearing in a transaction REPORT

These unrealized gain transactions do NOT appear in the account REGISTER

The data in the account register appears complete and accurate; the problem exists only with reports

In some cases these unrealized gains appear in the reports as regular and recurring transactions; in other cases they are random and irregular

The reports showing unrealized gains are consistent and reproduceable

Unrealized gains are reported for any investment account with buy/sell transactions; those accounts with only passive income (interest and dividends) do not show unrealized gain transactions even when they have open positions (i.e., outstanding securities with unrealized gains)

The problem exists whether the data is manually input or downloaded from the broker

All investment accounts in Quicken are reconciled to their statement balances

Numerous "Supervalidations" have been performed with and without the "Delete and Rebuild Prices" option and consistently produce a clean report with one exception related to a stock split

The Quicken data file covers 20 months of data (12/31/19-8/11/21) and includes over one thousand investment transactions including short sales, options, and futures

Quicken Version is R34.24/Build 27.1.34.24 on the PC (Windows 10 Enterprise).

Rocket J Squirrel

Tom Young said:

"So just to be clear, you do not have an _UnrlzGain entries in your transaction report?"

That is correct.  Running a year to date Investment Transactions report for all my investment Accounts I do not see any _UnrlzGain line items.  I also ran a couple of Investment Transaction reports on some individual Accounts without seeing unrealized gain line items.

To the best of my knowledge and belief only transactions actually recorded in a transaction list should show up on this report.  You don't say that you're seeing unrealized transactions recorded in your Accounts so I have to think this is a program or data issue. 

I think the first thing to try is a Validate and Repair with investment lots recalculated and see if that puts things to right.  If that doesn't work then a reinstall of Quicken would be my next attempt.  A superuser has posted a real "belt and suspenders" approach to uninstalling then reinstalling Quicken that you might try:

https://community.quicken.com/discussion/7892218/can-i-safely-uninstall-all-previous-versions-of-quicken-on-my-laptop

When I check "Include unrealized gains" in an investment transactions report, I see hundreds of entries for _UnrlzdGains. As in one per trading day for every security. It sounds like you didn't check the box, @Tom Young. I don't think validation or reinstallation are necessary.

Tom Young

I don't think I've ever, ever looked at what Categories are included in an Investment Transactions report because the report always worked properly.

Maybe I'm wrong but that box sure looks checked to me and nary an _UnrlzdGain to be seen on the report.

Rocket J Squirrel

Not that box, this box:

dave344

Based on the comments I've read here, I've come to believe that the unrealized gains are NOT being stored in the database but are prepared when the report is generated. Maybe this is done in anticipation of having to change the reporting interval. For example, switching from annual to weekly intervals quickly, Who knows? But they do not appear to be recorded in the database and are probably not related to my issue.

So my problem appears to be with the report writer: my register transactions seem correct, my account balances show correct within the application; all my accounts reconcile, my validation report is clean. I just can't produce accurate reports.

Tom Young

Rocket J Squirrel said:

Not that box, this box:

AH!  The "Advanced" tab, not the "Category " tab!  I picked up the wrong piece of string.  {face palm] 🤦‍♂️

I have never looked deeply at the customization options - beyond the Accounts, Securities and Actions - as this report always worked for me just fine.  Clicking that particular box ballooned my Investment Transactions for one month from 39 lines in Excel to 1,184.

It makes no sense to me to have this option in the report but inertia being what it is I should have realized that option was available.

gary22

I have more than 25 client files in Quicken. I have the same problem, but in only 1 client. Quicken help was helpless. For the time being, I am just disregarding that line in the Investment Income report.