Net Worth is incorrect

M$D

The Net Worth and Account Balances report and associated widgets are more wrong than usual recently. The Accounts column reports correctly.

Reports->Net Worth is off by very large amounts. When looking at the report, one checking account with 20+ years of data is negative by close to the same value. The "Account Balances" report also shows a negative amount for the account starting in 2016. The checking account has never had a negative balance.

The amount seems to be close to the amount transferred out of the account to Savings during that time. If I customize the report and remove that checking account or transactions which include the [Checking Account] category, the Net Worth is much closer.

Removing/adding the dashboard widgets no longer corrects the situation.

Jim_Harman

If you have used the Paycheck Wizard and you had R39.17 and/or 39.21 installed, you may have encountered the issue discussed here
https://community.quicken.com/discussion/7909601/r39-17-r39-21-paycheck-corruption-edited

If so, you will have to restore a backup from before you installed R39.17 (around March 4) and re-enter all the transactions since then.

M$D

Thank you, Jim, for the pointer.

This is quite likely my problem, as a validate showed 6 years of paychecks that I would need to recreate (the duration of my current employer). I suppose I should be grateful it wasn't the entire 22+ years in the datafile.

This will definitely change my upgrade plans going forward. I think I have spent more time curating and fixing data for Quicken this year than in the previous 20.

Jim_Harman

Too bad you experienced this problem, you are not alone!

To simplify re-entering transactions after restoring the backup, please try these suggestions:

---

Before restoring the backup, you will want to print a report that shows all the transactions you have entered since the problem occurred, in case you need to re-enter them. 

Go to Reports > Banking transactions. Set the Date range to Custom and set the starting date to 3/4/2022. Choose Subtotal by Account. Click the Customize gear at the top right of the report and check the Show splits box. Under Show Columns, you can un-check the Tax item box to make the report narrower. Click OK to display the report. Adjust the column widths if necessary, then print the report.

Then you should back up your current data file in case you want to return to it and restore a backup from before you installed R39.17. This would be about March 4. Verify that the problem is no longer present in the restored backup.

If the problem is still present in the restored backup, you have a different problem. You should return to your newer file and investigate further.

If the problem is not present in the restored backup, you will have to keep the backup and re-enter any transactions you entered since then. 

Accept Reminders as you normally would. Be careful when accepting Bill Pay reminders to avoid double payments. Then you can download transactions and accept them as you normally would. Compare the transactions in your registers to those in the report you printed earlier and enter anything that is missing. 

---
Please post back with any comments about the the restoration process and I will update the recommendations for future reference.

M$D

As is often the case in math, the remedy for the problem listed above was necessary, but was not sufficient. I no longer have 5 years of incorrect paychecks. However, I have a new definition of the problem.

My NetWorth as reported for today, is variable, depending on the start date in the report. Several investing accounts go negative if the start date of the report is after purchasing an equity but before it was transferred out or sold - it seems to treat it as a short sale.

Regardless of how Net Worth is calculated, the answer for today's value must be the same. If I include "All Dates", the net worth report is very close to the account bar total, and only one investing account is negative by more than a few cents (rounding).

Jim_Harman

Make sure you are using the report at Reports > Net Worth & Balances > Net Worth and not a report accessed elsewhere in Quicken or one you have saved.

If another report gives incorrect results, please let us know which one it is.

M$D

I am using Reports > Net Worth & Balances > Net Worth.

Additional information: The Net Worth total changes when I change the reporting interval.

I am now using V R39.23, Build 27.1.39.23

M$D

Additional note: I do not see the issue when using only Banking and Property & Debt accounts. If I add an investment account to the account list, the problem appears.

Jim_Harman

I think the problem you are seeing now is not related to the paycheck corruption.

Your "more wrong than usual" in the original post indicates that you have been having problems with the Net Worth report for some time. Is that correct?

One thing you could try (after doing a backup) would be to Validate and Repair the file.

If that doesn't fix it, are the differences confined to one or a small number of accounts or securities? Because "Earliest to date" works OK, you may be able to find problem transactions by customizing the report to show the Account details and adjusting the report starting date to see when the discrepancy first occurs. Check the account to see if there is something unusual on that date - a Split, Mutual Fund Conversion, Spin-off, Return of Capital, etc. Sometimes correcting or re-entering a transaction will fix things. 

M$D

Thank you again for your reply.

I believe you are correct that the Paycheck corruption is repaired. That alone cost me 5 hours with 2 back-up files - since the Net Worth errors remained after the first clean-up. I have (carefully) run validate/repair/fix-lots with no effect since then. I am shy of doing a super-validate.

The real problem with this issue is that the symptoms are variable. Today's Net Worth depends on the left side of the graph (start date) or the interval in the report. I do not understand how corrupted input data records could have this effect on a report unless they haven't accounted for a situation like Shares Added/Removed from investment account movement. BTW - We could have a long discussion about how the same security transactions are downloaded by specific firms in a different way.

I have built computer and SW systems for my entire career, but I do not have access to the tools necessary to fix this. I cannot spend an indeterminant number of hours looking for a needle in a haystack. I will, of course, attempt some mitigations to assuage my angst over the incorrect reports. To fix it properly, this must be debugged and remedied by the writers of the reports. I will file a formal case.

P.S. I understand some of the challenges in writing Finance SW. I once wrote a checking/budgeting package in the early 80s which served me well into the 90s. While it didn't understand individual stocks, it did monitor spending by category and also told me things like when I should get my oil changed in the car.

Jim_Harman

Do the Net Worth, Account Balances, and Investing > Portfolio value reports agree when the ending dates are set to today?

If not, does one agree with the Accounts bar and the others not? These reports are apparently calculated differently and can help to uncover issues in your data.

Hopefully it is only one or a few transactions that are causing the problem. If so, setting the report interval to None and adjusting the date range as described above should help find the problem(s) quite quickly.

The links below point to some similar issues and how they were resolved.
https://community.quicken.com/discussion/comment/20230337
https://community.quicken.com/discussion/comment/20233680#Comment_20233680

M$D

After further review, while some Investing accounts are negative (easy to see), all have incorrect totals.

I've recreated the problem in the Account Balances report with a new account and a couple transactions. See the attached. I deposit $14K into an investment account, make a purchase of an equity, and the account balance never exceeds $14.

I've not yet tried this in a new QDF file.

Jim_Harman

That looks odd.

Is this a newly created account, or did you tack these transactions onto an old account that had a zero balance?

Take a look at the Price History for the security you bought. Presumably it should be $110.185 on 10/18/20. Does it stay in that range, or does it go way down shortly after the purchase date?

Also click on Holdings in the test account and adjust the As of date before and after the purchase date. Does everything look correct there, or does the balance go way down right after the purchase?

Make sure there are no hidden Placeholders in the account that are messing with the balances. Go to Edit > Preferences > Investment Transactions and make sure Show Hidden transactions is checked.

M$D

This is a newly created account.

The price history is correct during this time frame; the price is 110.* during this period.

The holdings are correct by date, recognizing all the cash until the purchase (which was a Sunday, oops) after which the security details are correct.

Hidden transactions are shown in preferences.

If I change the "Deposit" to an "Interest" or "Transfer" from a banking account, the cash is seen correctly and is used to purchase the shares.

I have checked and unchecked report options. If I select "Include only selected Securities", with a single choice of the purchased security, the Account balance report includes the Security value, but not the "Deposit"ed cash.

Jim_Harman

[edited]
Aha. Do the security selections for the Net Worth report include "No Security (includes cash)"? That will have a major impact on the report. If it is not selected, any transactions such as deposits that move cash in or out of the account will be ignored. 

With the report limited to this account, you should be able to choose "Include only the Selected securities" and select all the securities. Does that make a difference?

See this discussion when I learned about this.
https://community.quicken.com/discussion/7768118/treatment-of-cash-in-net-worth-report

Jim_Harman

I think what will happen in your example if "No security (Includes cash)" is not selected in the report securities is:
-- the deposit is ignored
-- the small dividend is included because it is generated internally.
-- the Buy makes the cash balance negative, offset by the value of the security
-- after that as the security price fluctuates the account value will go up and down by the amount of the unrealized gain.

This seems consistent with what you show in the report. You should be able to see this if you customize the report as I suggested above, selecting the Account detail option on the Display tab.

M$D

First: The problems pictured are with the Account balances Report, though this string started with Net Worth. Thank you for the pointer to "Treatment of Cash in Net Worth report", which seems what I have been encountering. I want Account Balances to include all cash and securities in an account, which I can't get currently. Perhaps a bug was re-introduced. Why would Net Worth or Account Balances not include cash deposited but not yet invested? I believe I am seeing this effect more than before because several accounts were transferred last year.

To your questions:

If "No Security" is checked but the security is, or "All securities" is checked, I see the result you expect if it wasn't checked. Cash is negative, Security is present as seen via "Account detail"

If "No Security" is NOT checked, the cash is not seen in the report, but the value of the security is seen.

In Account Detail in the Net Worth report, several securities' value is negative - which makes no sense to me.

Jim_Harman

If you don't mind and to make sure we are both looking at the same thing, let's try to work on one issue and report at a time. What would you like to work on first?

M$D

Update: I ran 100 variations of the reports, then Q crashed. I restarted Q this morning and so far, the reports are within 1% for Net Worth, and only about 10% of accounts having issues in Account Balances.

I am going to work on the equities that list a negative value and see if I can crack that issue. Then it will make sense to work on the report.

Thank you for your time. I'll report back in a few days on any progress.

Jim_Harman

Thanks for the update - it sounds like you are making progress!

Did you have to modify or re-enter any transactions to get the Net Worth report to be correct, or was it a matter of the report settings?

M$D

[Quicken Community - Thanks for holding my draft!]

TL;DR - Net Worth report is really close, Net Worth Widgets have issues.
TL;DR - Account Balances has equity valuation issues; report does not handle cash deposits in Investing Accounts properly.


Net Worth - Status: The Report is almost correct, subject to rounding.

This is pertaining to the report, not the widgets which have other issues.

1) I do not know that each column of Net Worth is correct but comparing the last column to the total of the Accounts bar shows that 13 of 14 accounts agree with a weekly interval. When accounts do not agree there is usually significant cash movement, and the difference is small (a hundred $ or so).

2) Using a weekly interval makes the Net Worth report more accurate in the last column. The delta is no longer very large. It seems the value listed in the bottom right is actually the end of some internally reached interval, and not the date listed in the column which is still slightly variable depending on start date and interval. Also note that the number of columns is capped at 250, which causes the start date to differ from what was requested - which causes variability.

3) I did solve a few problems by editing transactions in Investment accounts, e.g. Return of Capital, ADR receipts and taxes, plus shares transferred between accounts (which became Removed/Added) were sometimes not handled well. I attacked easily identified issues because account cash or securities went negative. I also swam a while with some red herrings. There is likely some more tuning to be done.


Account Balances Report - Significant Problems

4) Changing ONLY the sampling period significantly changes the results of the report - see attachment of APPL in an account for a 2 month period. The weekly interval seems to be close, but why does the value of APPL change over two weeks when sold in a single transaction on 9/4? The prices are good, a qty of 11 shares were Added/Removed and there is a 4:1 split in the period. The value of APPL shares should never be negative. This should be reproducible with public data.

5) As a result of issues causing #4, many Investing Accounts become negative: also strongly suspect that cash "Deposited" into an investment account is never credited properly. For example, reconciliation Cash additions or non-itemized, non-paycheck employer contributions deposited into investing accounts seem to be missing in the longer report. While mostly invested in securities the report can appear to be OK, but when closing accounts and selling all securities and transferring the proceeds to another account, the closed account becomes and remains negative. The Account view shows a closed account value of zero.