Quicken Reports and bad math????

UserinMD

Having a major issue with Quicken bookkeeping. I am running reports to keep a summary in an Excel spreadsheet. Here's what I am seeing:

For my Schwab portfolio, all holdings are correct, and matched perfectly (or within maybe a few cents) of what I see on the Schwab website. So shares, share prices and balances are correct. But when I go to run a summary report, several balances are off.

As an example, I have 616.412 shares of a security, price is 29.09. Value is therefore $17,931.43---and this is what shows up under holdings and on Schwab. But in the report, the balance shows $20,405.58. I have another security that should be worth $9539.02, but shows a balance of $10,202.52. This is a simple multiplication issue (shares x value = balance) so I don't understand what is going on here.

Obviously I can "correct" the value on the spreadsheet---but why is Quicken incorrectly calculating the balance on the report, when all the shares and prices are correct???

Jim_Harman

Is the ending date for the report today?

What report are you using? the Investing > Portfolio Value report grouped by account and ending today should match the Holdings view. Start with the built-in report and not a saved report.

UserinMD

> @Jim_Harman said:
> Is the ending date for the report today?
>
> What report are you using? the Investing > Portfolio Value report grouped by account and ending today should match the Holdings view. Start with the built-in report and not a saved report.

Yes, the report date is today. I tried to "rebuild" the report. The weird thing is that the report shows the correct holdings (shares and values), but not the correct balances, and it is only for some, not all, of the investments. I have attached a portion of the spreadsheet---the first 2 columns are shares and price.
The next to last column is the balance reported by Quicken. The last column is my calculation of
shares x price...you can see where the differences are. The total number I come up with in the far column (in bold) is almost an exact match for what the Schwab site has (maybe a dollar or two off, based on fractional error or rounding somewhere). But the report (bold value in next to last column) has the value more than $3000 above that. Very puzzling.

UserinMD

I think I just figured it out. The balance reported is the sum of the Cost basis and the gain/loss (columns 3 and 4) NOT the market value of the holdings.

Jim_Harman

See those asterisks? They indicate that Placeholders in your data are affecting the results. If you let it, Quicken puts them in your data to "cover up" for missing or incorrect transactions.

Go to Edit > Preferences > Investing Transactions and make sure the Show hidden transactions box is checked.

Then look through your Transaction list (register) to find the gray Placeholder transactions and resolve them as described here
https://community.quicken.com/discussion/7267839/quicken-faq-managing-placeholder-entries-in-quicken-for-windows

Please post back if you have further questions.

UserinMD

> @Jim_Harman said:
> See those asterisks? They indicate that Placeholders in your data are affecting the results. If you let it, Quicken puts them in your data to "cover up" for missing or incorrect transactions.
>
> Go to Edit > Preferences > Investing Transactions and make sure the Show hidden transactions box is checked.
>
> Then look through your Transaction list (register) to find the gray Placeholder transactions and resolve them as described here
> https://community.quicken.com/discussion/7267839/quicken-faq-managing-placeholder-entries-in-quicken-for-windows
>
> Please post back if you have further questions.

So I see your point--but that doesn't quite explain why in the first row, there is still several hundred dollars difference even without placeholder entries (that particular investment is newer, and I know is up to date without placeholders).

I also don't get why the balance wouldn't show as just the shares x price, because that it what is reflected in portfolio holdings. The cost basis and gain/loss doesn't really come into play until you sell the shares, right? If you just want a simple report on your holdings and current value, why isn't that being captured on the report?

Jim_Harman

UserinMD said:

> @Jim_Harman said:


So I see your point--but that doesn't quite explain why in the first row, there is still several hundred dollars difference even without placeholder entries (that particular investment is newer, and I know is up to date without placeholders).

I also don't get why the balance wouldn't show as just the shares x price, because that it what is reflected in portfolio holdings. The cost basis and gain/loss doesn't really come into play until you sell the shares, right? If you just want a simple report on your holdings and current value, why isn't that being captured on the report?

Back up your data in case something goes wrong. Then, to find the problem in the first row, first I would do a File > Validate and Repair to see if that fixes it.

If not, set the report date and the As of date in the Holdings view back in time until the numbers match, then forward until they just start to disagree. Look for a transaction on that date that would explain the issue. Edit the transaction to fix any errors, and If the transaction looks OK, try deleting and re-entering it.

As to the report calculations, Cost Basis + unrealized Gain/Loss should also = Market Value, so in a way it is a check on the Holdings calculations. 

There are other reports that should show the same information, such as Net Worth & Balances > Account Balances with the Account detail option selected, and Net Worth & Balances > Net Worth, again with the Account Detail option selected. I don't know the details of how these are calculated, but I do know that they sometimes show results that are different by a few pennies even when your data is OK. 

Doug_Trager

Hello Jim. First post here. Considering Quicken and read many posts. You and others have thoughtful, knowledgeable replies. I guess my questions boils down to...

Can Crypto, ETFs, Mutual Funds, Puts & Calls, and Stocks in a mix of regular accounts as well as IRA, Roth-IRA, HSA and 401(k)s be downloaded into Quicken?

If it helps, know that the accounts are at Betterment, Coinbase Pro, Fidelity, Tastyworks and Wealthfront.

Jim_Harman

Please start a new discussion in the Before you Buy section for this new topic and I will be happy to answer.

UserinMD

Circling back to this @Jim_Harman ....I finally figured out the problem.
At the Schwab site, I looked at the cost basis and lot reports. Turns out in the recent updates and downloads for a couple of my accounts there were "double entries" in Quicken (for example, LT gain reinvestment also appearing as a duplicate "cash" ReinvInt. So basically, Quicken's cost basis was off from the actual. Once I deleted the duplicate entries, everything matched up properly in all reports.

Whew.

Jim_Harman

Glad you got it sorted out!

I recently had some duplicated transactions from a different FI and they can be tough to straighten out.

AndrewQ

> @UserinMD said:
> Circling back to this @Jim_Harman ....I finally figured out the problem.
> At the Schwab site, I looked at the cost basis and lot reports. Turns out in the recent updates and downloads for a couple of my accounts there were "double entries" in Quicken (for example, LT gain reinvestment also appearing as a duplicate "cash" ReinvInt. So basically, Quicken's cost basis was off from the actual. Once I deleted the duplicate entries, everything matched up properly in all reports.
>
> Whew. :)

Just to let you know, I also had the same problem, and I thank you for this thread and the other posts provided within. I just found it after believing for three months my Q reports of the Schwab account matched their paper statements; I don't see how I could have missed this for three months since I check religiously each month.

Be that as it may, it is absolutely not acceptable simply to have to manual search for and find duplicate transactions (whether they are being entered in Q automatically incorrectly or downloaded from Schwab with bad data from their end.

No one happened to get back to Schwab with this by any chance, did they? Or can we just excuse it knowing they just transition during this time to a new download paradigm within Q?