Low Level Calculation Precision

Smith

After spending several hours over the past two weeks I have all my historical data loaded into Portfolio Slicer (PS). All but one of my account balances in PS are equal to or within +- $0.01 cent of my actual account balance as reflected by my financial institution.

Here are the actual transactions from the one account where the balance differs:

Date, Shares, Price, Action
28 Mar 13, 23.3326, 139.559243, Buy
28 Mar 13, 02.0516, 139.559368, Buy
25 Apr 13, 02.8987, 141.132232, Buy
25 Apr 13, 01.6557, 141.136679, Sell
30 Apr 13, 00.0016, 143.750000, Sell
30 Apr 13, 00.0072, 141.666667, Sell
31 May 13, 00.0016, 150.000000, Sell
31 May 13, 00.0073, 145.205479, Sell
28 Jun 13, 00.0017, 141.176471, Sell
28 Jun 13, 00.0073, 142.465753, Sell
09 Jul 13, 03.7132, 147.832059, Buy
09 Jul 13, 00.8479, 147.835583, Buy

With the closing price on 13 May 16 being 194.150873 the account balance should be: $6050.05. PS reports the balance at $6050.13.

I believe the disparity is caused by a rounding issue as there are abnormal transactions in the list. In most transactions one would not see share quantities of 00.00XX being sold. Since this is a mutual fund and fees were deducted from the share balance it resulted in some very small quantities being sold.

VidasMatelis, I e-mailed you a sample portfolio in case you want to review this.

VidasMatelis

Hi Smith,

I have not received your Excel workbook with sample transactions - please send me. I'll investigate.
Also, please let me know if you are using Excel 2010 or Excel 2013/2016 and if Excel 2013/2016 - if you have full PowerPivot support.

Smith

You should have the e-mail now. I'm using Excel 2013 Pro Plus so I have full PowerPivot support.

VidasMatelis

Hi Smith,

I found that discrepancy is because I round quantity to 3 decimal points. Future releases will include rounding up to 5 decimal points. To fix this issue on your existing installation.
1. Go to "Holdings" Tabl. On the right side you should see "PivtTable Fields" (if you do not, right mouse click on any cell in the pivot and then choose "Show Field List".
2. From the list find table "Report" and select measure "Qty Held", right mouse click and choose "Edit Measure".
3. In the "Measure" window change Formula's last string from ",3))" to ",5))". So formula will be:

=IF(HasOneValue(Symbol[Symbol]), ROUND(
	CALCULATE(SUM(Trans[QtyChange])
	, DATESBETWEEN(Dates[Date], BLANK(), MAX(Dates[Date])), All(Dates)
	, ALL(TransType), TransType[QtySign]<>0
    )
,5))

4. Save measure "Qty Held" by clicking "OK" button.
5. From the list find table "Report" and select measure "Equity Value", right mouse click and choose "Edit Measure".
6. In the "Measure" window change formula's 7th line from ",3)" to ",5)". So formula will be:

=ROUND(SUMX(VALUES(Symbol[Symbol])
			, ROUND(
					CALCULATE(SUM(Trans[QtyChange])
					, DATESBETWEEN(Dates[Date], BLANK(), MAX(Dates[Date])), All(Dates)
					, ALL(TransType), TransType[QtySign]<>0
					)
				,5)
	
			  * IF(ISFILTERED(SymbolSector[Sector]) || ISFILTERED(SymbolSector[Sensitivity]), CALCULATE(SUM(SymbolSector[Percent])), 1)
			  * CALCULATE(
					IF(NOT(HasOneValue(ReportCurrency[CurrencyID])), VALUES(Quotes[Close])
						, SWITCH(VALUES(ReportCurrency[CurrencyID]), 1, VALUES(Quotes[Price1]), 2, VALUES(Quotes[Price2]), 3, VALUES(Quotes[Price3]), VALUES(Quotes[Close]) )
					)
					, LASTNONBLANK(
						DATESBETWEEN(Dates[Date], DateAdd(LastDate(Dates[Date]), -40, day), MAX(Dates[Date]))
					  , CALCULATE(COUNT(Quotes[Close]) )) 
				)
		)
, 2)

7. Save measure "Equity Value" by clicking "OK" button".
8. Refresh your data.

Smith

Thanks for the quick response.

The way you described how to edit the measure is exactly how it's described for Excel 2016. This must be one of the changes from 2013 to 2016 as I do not have this option available with I right-click the PowerPivot field name in the field list on the "Holdings" tab of the workbook. Instead I changed it in the PowerPivot table, Reports tab, in the Measurements area.

I'm brand new to PowerPivot (only aware of them because of Portfolio Slicer) and have done very little with pivot tables. I've been using Excel for some time and am familiar with its functions. I suspected this was an issue using the Round function because the problem doesn't show up until you're several decimals deep. I also viewed some of your calculations and noted your use of it.

Thanks again!! Really like the spreadsheet.

VidasMatelis

Sorry, you are right. I gave Excel 2016 instructions.
In excel 2013 you have to go to PowerPivot menu and click on "Calculated Fields" button and then "Manage Calculated Fields..." option and then choose "Qty Held" and Equity Value" measures and select "Edit...".