How do I get a correct Portfolio Balance and Cost Basis Report?

26@harrich

For 1 of 40 securities in this report I get a correct [Shares], [Quote/Price], [Cost Basis], but it reports a balance of 3,077.44, where the correct balance is 3,436.74, with a corresponding error in [Gain/Loss] based on [Shares]*[Quote/Price], [Balance] in Portfolio, or Vanguard, whichever you prefer. The [Quote/Price] in the account Holding dialog matches that in Price History. Repairing the database, rebuilding investment lots, rebuilding price history, and recreating the report all fail to correct the problem.

I'm running Deluxe R36.23 Build 27.1.36.23 on a very stable system with 64 bit Windows 10 Home and an Intel(R{ Core(TM) i5-9400 @ 2.90 GHz.

26@harrich

Just for the fun of it, I tried validating the file and rebuilding investment lots. No change.

I then tried deleting and then reentering the return of capital transaction. This eliminated the difference between the Account Overview and the Portfolio Balance Report.

This reinforces my suspicion that there is background accounting of unrealized gain, which, like all black magic, sometimes goes wrong. In my mind, it would be better if the reports did not needlessly rely on this accounting.

My thanks to those who responded.

imdcareys

You may be missing transactions. If you have a recent backup that is correct, try exporting to Excel (or make reports) out of the current file and the backup. Then compare the exports (reports) to see if you can identify it or eliminate that as a cause. 

q_lurker

Let's start here

• The Portfolio Value & Cost Basis report does not show [Shares] and [Quote/Price] nor [Gain/Loss].  That report only shows [Security name], [Cost Basis], and [Balance]. 
• The Portfolio Value report does show all those fields - [Security name], [Shares], [Quote/Price], [Cost Basis], [Gain/Loss], and [Balance].  

So which report are you referring to?  Which report is presenting the incorrect [Balance] for this one security?  Or do you mean something different than a "Report"; something like a Portfolio or Holdings view?

... with a corresponding error in [Gain/Loss] based on [Shares]*[Quote/Price], [Balance] in Portfolio, or Vanguard, whichever you prefer. 

What is the meaning of the Portfolio or Vanguard reference? 

Presumably whatever you are looking at is as of 'today'.  If so, for prior dates, is the information correct?  When does the change take place?  When does the balance go awry?  What transactions are entered for that date?  What price changes occur on or around that date?

26@harrich

I need the report because I am exporting it to Excel to import into Access. I located the date things started going wrong by doing a binary search comparing the Account Overview and Portfolio Value Report over 15 years of transactions on the problem security.

While the error started appearing in 10/2021 the transaction date at which things first deviated is12/27/2013. Below are data for the problem security from the Account Overview, the Portfolio Value Report, Deviations, and the 12/27/2013 transaction and its computed effect. I tried to attach the spreadsheet, but no luck.

Account Overview
Date Quote Shares Value Quote*Shares Basis
12/26/2013 15.480000 284.134489 4,398.40 4,398.40 4,488.95
12/27/2013 15.740002 287.925467 4,531.95 4,531.95 4,548.62

Portfolio Value Report
Date Quote Shares Balance Quote*Shares Basis
12/26/2013 15.480000 284.134 4,398.40 4,398.39 4,488.95
12/27/2013 15.740000 287.925 4,172.95 4,531.94 4,548.62

Deviation Quote Shares Value/Balance
12/26/2013 0.000000 0.000 -
12/27/2013 -0.000002 0.000 (359.00)

Transaction Quote Shares Amount
12/27/2013 15.740002 3.790978 59.67 --Reinvest Dividend
12/27/2013 15.740002 287.925467 4,548.62 --12/26 Values + Transaction
12/27/2013 0.000000 0.000000 - --Deviation from Account Overview

I checked the Transaction History for the security on that 12/27/2013 and it matches the account. I entered a 359.00 return of capital on the security for 10/18/2013, which matches the difference in valuation, but it was correctly applied to the basis. Further, the error didn't show up until 2021 and it is not reflected in the report until after the 12/27 transaction.

I'm still in the dark about what's going on and wondering why the Portfolio Value Report can't simply mark to marked when calculating balance.

q_lurker

Point 1 -- Reports (in this case your Portfolio Value Report) are subject to a preference setting for how many decimals to present on share quantity and price/share.  It appears yours is set to 3.  Thus your 284.134489 gets rounded in the report to 284.134.  Changing the preference setting to 6 might help.

Point 2 -- Some time ago (I forget when, maybe in the last year), Quicken revised some of their internal precision such that (I believe) they were more consistent in using 6 decimals for many investment computations.  That too may be a factor, in particular for something that is different now than before.

Point 3 -- My personal approach.  Decimals on the share price are pretty much immaterial.  Daily closing prices downloaded or entered are fine to the penny or in same cases half-penny.  Prices for a transaction are whatever gets calculated from Dollars divided by shares.  Thus I consider the distinction you see between 15.740002 and 15.740000 immaterial.

But shares are significantly different.  I maintain ALL share quantities to 0.001 precision.  Thus the 12/27/13 reinvestment I would have definitively entered (or edited) as $59.67 buying 3.791 shares a computed $15.73991/share.  I have found over the years in these forums that many users have gotten into problems letting Quicken track share quantities to precisions beyond what the financial institution is using.  (My 3-decimal choice matches my applicable financial institutions).

26@harrich

Thans for your effort. The only deviation I indicated as significant was the $359 difference between the actual market value shown in the Account Overview and the Balance shown in the Portfolio Balance report. I don't think that this, a little less than 10% of the investment, is due to differences in roundoff. For the record, the share balance in the DRIP account was being maintained to 6 digits.

What I suspect is that some background data on unrealized gain is being maintained or calculated, the Portfolio Balance report calculates Balance = [Cost Basis} + [Unrealized Gain], and the $359 increase in unrealized gain which came with the $359 return of capital somehow goes lost on 12/27/2013 in the more recent Quicken versions. Still, I have no clue how to track this down since the unrealized gain income on 12/27/13 is positive rather than negative. The only other returns of capital I've recorded are for securities no longer held, so the transaction is unique to the security being misreported.

26@harrich

Just for the fun of it, I tried validating the file and rebuilding investment lots. No change.

I then tried deleting and then reentering the return of capital transaction. This eliminated the difference between the Account Overview and the Portfolio Balance Report.

This reinforces my suspicion that there is background accounting of unrealized gain, which, like all black magic, sometimes goes wrong. In my mind, it would be better if the reports did not needlessly rely on this accounting.

My thanks to those who responded.

q_lurker

The RtrnCap transaction has had some 'black magic' issues over the years.  The issues have never been well documented and changes to that transaction have slipped in sometimes.  I have had several circumstances where once I find the problem transaction, deleting it and re-entering it corrected the problem.  That experience is not limited to RtrnCap transactions.  I do think they are getting batter with the RtrnCap transactions. 

Well done on getting focused in on your issue and finding the fix.