Treatment of Cash in Net Worth report

Jim_Harman

In QWin 2018, something strange appears to be going on with investment accounts in the Net Worth report if "No Security (includes Cash)" is not selected as one of the securities.

I am trying to make a report that shows the value of my holdings in a particular security over time.

I customized the Net Worth Report to show the desired time interval, just investing accounts,  and just the selected security.

The resulting report shows huge negative balances in some accounts, including ones that have never held the selected security. The balances look much more reasonable if I add "No security (includes Cash)" to the selected securities, but then the report includes any actual cash that is in the accounts. 

I was able to eliminate the problems in the accounts that do not hold the selected security by removing split cash transactions from those accounts, but something else is going on here as well.

In each account that holds the selected security, the cash discrepancy first appears on the date the security was purchased and is equal to the total amount of cash that has ever been deposited or transferred into the account minus the cash balance at the end of the reporting period.

Weird!

I realize there may be other ways to get at the desired information, but can anyone explain why the Net Worth report is behaving this way?

Unknown

The problem is in not having a clear definition of the term "No security (includes Cash)" and how to implement it.

In the past deselecting this would still see "cash securities", for instance cash balances or if you had a security marked as "Cash".

And of course it was been years long complaints on this.


So they changed starting in one of the 2018 patches, but not in the way most users would expect.

I suspect your negative balances come from what I have found.

Here is a report with "Cash" included.


Here it is without "Cash" included.


The difference is the first transaction is missing.  A "ContribX", which is for $2,000 (which BTW you can't see the total of and you should be able to see it!)

So they removed that "cash transaction".  And of course that throws everything off.

In the strictest sense they did what they said they would do.  They removed any transaction, including cash ones, that don't have a security associated with it.

The underline problem is how do you know what cash transactions have to be there because they were used to buy the securities that are included in the report.

When I think about this, I think they might be doing the right filtering, but need extra processing that "assumes" the cash exists in the account for each buy (and only that amount.

It is in fact extremely complicated.

Jim_Harman

QPW,

I think I get what you are saying, but you are looking at the Investment Transaction report. My problem is with the Net Worth report. 

For each reporting date in the Net Worth report, I think it should just show a snapshot of the value of each selected security in each account. If "no security" is selected, it should include the actual cash balance in the account as of that date. Essentially it would be the same as the "Market Value" column in the account's Holdings view on that date. If "no security" is not selected, the cash balance should be excluded from the report.

It looks like the problem is in computing the cash balance for the account if "no security" is not selected. If that causes it to ignore all the Deposit and Xin transactions and display that odd result, then of course the cash balance will be wrong.

I think for the Net Worth report it could compute the cash balance the odd way it does today, but just not include it in the report if "no security" is not selected.

Unknown

Jim Harman said:

QPW,

I think I get what you are saying, but you are looking at the Investment Transaction report. My problem is with the Net Worth report. 

For each reporting date in the Net Worth report, I think it should just show a snapshot of the value of each selected security in each account. If "no security" is selected, it should include the actual cash balance in the account as of that date. Essentially it would be the same as the "Market Value" column in the account's Holdings view on that date. If "no security" is not selected, the cash balance should be excluded from the report.

It looks like the problem is in computing the cash balance for the account if "no security" is not selected. If that causes it to ignore all the Deposit and Xin transactions and display that odd result, then of course the cash balance will be wrong.

I think for the Net Worth report it could compute the cash balance the odd way it does today, but just not include it in the report if "no security" is not selected.

No matter what report you select they are most likely going to search the database using the given critical and then run totals.

If the basic premise of how to do this is wrong, it will be wrong in lots of places.

In fact the way I decided to use the investment transaction report to show what was happening was first do drill down on the net worth report and notice that the "underline data" is in fact the investment transactions.  Any of the reports are generated by totalling the transactions that they have.  If you have a buy of XXX security for $2000 there has to be another transaction that put that $2000 into the register and is selected by the report, otherwise you are going to end up with a negative total.

One of the things that I think makes this complicated is because they have combined "no security" and "cash" into one selection.

They are in fact quite different.

"no security" is an investment transaction that isn't associated with a security.  For instance I can have interest that is and isn't associated with a security.

"Cash" on the other hand is a completely different animal, especially since it can mean different things in different contexts.

For instance one concept (the one I think you expect, and I would want too) would be "hide any information I didn't select", but keep totals of what I did select unchanged.

Since the reports are generated by totaling transactions this would have to be done by first including all the cash transactions.  And then afterwards remove/hide the lines that have cash values in them.

Whereas the current developers that changed this clearly took the selection text literally.  As in remove the cash/non investment transactions before you do the totaling for the report.  And the ones that created the report/selection in the past clearly thought it was too much trouble/complicated do this "second pass".

To illustrate the difference I will go talk about the differences between what some old MS Money users (and others) wanted in the Quicken register filters, and how Quicken does them.

Go into a register where not all the transactions are reconciled/cleared.
Notice the balance column values.  Select Unreconciled from the filters.
Notice that balance values have changed.

That is because they just total what is seen.

What a lot of people expect is that when you hide transactions they are just not visible, but they still affect the balance.  So for that you actually have to either store the running balance for every transaction, or you have to include them in the calculation, but just not show them.

To me this is a case where the developers (and many others) didn't actually understand the use case.  And frankly if they did, they might never have made the change.

To filter all of the cash transactions out was probably nothing more than changing the query to the database.  Changing it so that they are included, but later filtered out would probably mean a fair amount of work changing how the reports work.  And it would probably affect quite a few reports.

Just as the "hiding" of transactions in the register and keeping the balance unchanged would be a lot more complicated than most people think.  And it would upset a lot of people that expect it to work the way it currently does.

Jim_Harman

Thanks for your detailed explanation. I see what you mean about drilling down in the report to see the underlying transactions and how they affect the higher level report.  

I'll have to study this further. 

Thanks again.

Jim_Harman

OK, I see that when "No Security (includes Cash)" is not selected, the Net Worth report is excluding all external cash transactions - Deposit, Withdraw, Transfer to/from another account, etc. Internal cash transactions like Buy, Sell, and Dividends are still included in the report.

I can't see why anyone would want a report like that.

The very similar Account Balances report, however, behaves the way I am looking for. If you don't include "No security (includes Cash)" it simply shows zero as the cash balance in the account.

Note that both of these reports also have problems if there are cash transactions with category splits and "no security" is not selected. In this case, the transactions with splits are included in the report while non-split transactions are not. It is applying its exclusion rule differently for split and non-split transactions. That is definitely a bug I think.

Unknown

Jim Harman said:

OK, I see that when "No Security (includes Cash)" is not selected, the Net Worth report is excluding all external cash transactions - Deposit, Withdraw, Transfer to/from another account, etc. Internal cash transactions like Buy, Sell, and Dividends are still included in the report.

I can't see why anyone would want a report like that.

The very similar Account Balances report, however, behaves the way I am looking for. If you don't include "No security (includes Cash)" it simply shows zero as the cash balance in the account.

Note that both of these reports also have problems if there are cash transactions with category splits and "no security" is not selected. In this case, the transactions with splits are included in the report while non-split transactions are not. It is applying its exclusion rule differently for split and non-split transactions. That is definitely a bug I think.

I agree it is a bug.

Jim_Harman

Jim Harman said:

OK, I see that when "No Security (includes Cash)" is not selected, the Net Worth report is excluding all external cash transactions - Deposit, Withdraw, Transfer to/from another account, etc. Internal cash transactions like Buy, Sell, and Dividends are still included in the report.

I can't see why anyone would want a report like that.

The very similar Account Balances report, however, behaves the way I am looking for. If you don't include "No security (includes Cash)" it simply shows zero as the cash balance in the account.

Note that both of these reports also have problems if there are cash transactions with category splits and "no security" is not selected. In this case, the transactions with splits are included in the report while non-split transactions are not. It is applying its exclusion rule differently for split and non-split transactions. That is definitely a bug I think.

Update: The remaining problems related to split cash transactions in investing reports all appear to be resolved in R14.21. 

Thanks, Quicken!