"Division by zero" Error

BeesMD

Hi Vidas, thanks so much for work on Portfolio Slicer.

I've recently been getting an error upon refreshing the data (http://puu.sh/zEhLF/147d0df982.png) which persists even when starting up a new portfolio and inputting from scratch.

Would love your help,

Thanks

VidasMatelis

Hi,

To investigate this I will need you to send me your Excel workbook. But I am on vacation till 16th of March, so I will be able to look at this just after vacation.

BeesMD

Thanks for your help

VidasMatelis

Could you please confirm that in psdata/quotes.csv file you have quotes for CBA from 1996-07?

BeesMD

The CBA quote starts at 1996-07-31 and ends at 2018-03-08 after updating today

Many thanks

VidasMatelis

Hi,

Could you please send me again Excel workbook and also zipped files from the folder c:\PortfolioSlicer\PSData. My email is posted on this page: http://www.portfolioslicer.com/contact.html
With just an Excel file, internal refresh of Trans table worked without any error. So I want to try to refresh with external data and confirm if that is the cause for the issue.

BeesMD

Thanks so much for your help!

VidasMatelis

Hi,

I am able to reproduce error on Excel 2013 with your data. I am looking into this, as it appears as a bug with combination of data and cash tracking flag. For now you can quickly get rid of this error by simply removing column "Annlzd RoR" from Holdings tab report.
If you for some reason cannot remove it, I can send you updated workbook with that issue resolved.

BeesMD

Thanks so much!

VidasMatelis

Finally after multiple hours of investigation I found an issue and solution. This behaviour is due to the way Excel generates queries when filter on Symbol is applied (removing filter removes error). I found workaround in my code to eliminate this Excel issue. You (or anyone affected by the same error message) can either update code yourself, or send me your Excel workbook and I'll update code for you. This issue is just for Excel 2010/2013. Excel 2016 version is not impacted.

For reference - old code:

Annlzd RoR:=IF((Contains(VALUES(SymbolSector[Sector]), SymbolSector[Sector], "* Cash") || Not ISCROSSFILTERED(SymbolSector[Sector])) && CONTAINS(Symbol,Symbol[Symbol],"* Cash")
 , IF((COUNTROWS(Trans)=0 && Report[Total Value] = 0) || LASTDATE(Dates[Date]) = Report[XIRRFirstDate] || COUNTROWS(VALUES(Symbol[Symbol]))=1, Blank()
   , MINX(TOPN(1, ALL(XIRR)
			, ABS(
				  CALCULATE(IF(PreviousDay(Dates[Date])<>Blank(), (-1)* CALCULATE(Report[Total Value], PreviousDay(Dates[Date]))))

				+ CALCULATE(
					IF(PreviousDay(Dates[Date]) = Blank()
							|| CALCULATE(Report[Total Value], PreviousDay(Dates[Date]), All(Dates)) = 0
					  , CALCULATE(
						  SUMX(Trans, (-1)* DIVIDE(Report[Trans Ext Cash Flow Amnt]
													,((1+AVERAGE(XIRR[XIRR])) ^ (Int(Trans[Date] - Report[XIRRFirstTransDate]  )/365) )
												  )
						  )
						  , DatesBetween(Dates[Date], FIRSTDATE(Dates[Date]), LASTDATE(Dates[Date]))
						  , All(Dates)
						  , TransType[ExternalImpactPortfolioSign]<> 0
						)

					  , CALCULATE(
						  SUMX(Trans, (-1) * DIVIDE(Report[Trans Ext Cash Flow Amnt] 
													, ((1+AVERAGE(XIRR[XIRR])) ^ (Int(Trans[Date] - CALCULATE(PreviousDay(Dates[Date]), All(Trans), VALUES(Dates[Date])) )/365))
												   )
						  )
						  , DatesBetween(Dates[Date], FIRSTDATE(Dates[Date]), LASTDATE(Dates[Date]))
						  , All(Dates)
						  , TransType[ExternalImpactPortfolioSign]<> 0
						)
					)
				  )

				+ CALCULATE(DIVIDE(Report[Total Value], ((1+AVERAGE(XIRR[XIRR])) ^ ((Int(LASTDATE(Dates[Date]) - Report[XIRRFirstDate])/365)))))
				)
			, 1
			)
		, XIRR[XIRR]
		)
	)
  , IF((COUNTROWS(Trans)=0 && Report[Equity Value] = 0) || Report[XIRRFirstDate] = LASTDATE(Dates[Date]) || NOT(HASONEVALUE(Symbol[Symbol])) || Report[Cash Value]<>0, Blank()
       ,    MINX(TOPN(1, ALL(XIRR)
			, ABS(         
				  CALCULATE(IF(PreviousDay(Dates[Date])<>Blank(), (-1)* CALCULATE(Report[Equity Value], PreviousDay(Dates[Date])) ))

				  + CALCULATE(
					IF(COUNTRows(Trans)=0 , Blank()
					  , IF(PreviousDay(Dates[Date]) = Blank()
							|| CALCULATE(Report[Equity Value], PreviousDay(Dates[Date])) = 0
							, SUMX(Trans
								, DIVIDE(ROUND(Trans[TotalAmnt] * Related(TransType[CashImpactSign]) * Report[TransReportExchRate], 2)
										, ( (1+AVERAGE(XIRR[XIRR]))
											^ (Int(Trans[Date] 
												- CALCULATE(MIN(Trans[Date]), AllExcept(Trans, Trans[Symbol], Trans[Account]), VALUES(Symbol[Symbol]), VALUES(Account[Account]), VALUES(Dates[Date]))
												)/365)
										  )
								  )
							)
							, SUMX(Trans
								, DIVIDE(ROUND(Trans[TotalAmnt] * Related(TransType[CashImpactSign]) * Report[TransReportExchRate], 2)
										, ( (1+AVERAGE(XIRR[XIRR]))
												^ (Int(Trans[Date] 
													- CALCULATE(DateAdd(FIRSTDATE(Dates[Date]),-1,day), All(Trans), VALUES(Dates[Date]))
												   )/365)
										  )
								  )
							 )
						)
					)
				  )
				 
				  + CALCULATE(IF( AVERAGE(XIRR[XIRR]) = -1, Blank(), Divide(Report[Equity Value]  , ((1+AVERAGE(XIRR[XIRR])) ^ ((Int(LASTDATE(Dates[Date]) - Report[XIRRFirstDate])/365)))) ))
			)
			, 1
			)
		, XIRR[XIRR]
         )
	  )
)

Updated code:

Annlzd RoR:=IF((Contains(VALUES(SymbolSector[Sector]), SymbolSector[Sector], "* Cash") || Not ISCROSSFILTERED(SymbolSector[Sector])) && CONTAINS(Symbol,Symbol[Symbol],"* Cash")
 , IF((COUNTROWS(Trans)=0 && Report[Total Value] = 0) || LASTDATE(Dates[Date]) = Report[XIRRFirstDate] || COUNTROWS(VALUES(Symbol[Symbol]))=1, Blank()
   , MINX(TOPN(1, ALL(XIRR)
			, ABS(
				  CALCULATE(IF(PreviousDay(Dates[Date])<>Blank(), (-1)* CALCULATE(Report[Total Value], PreviousDay(Dates[Date]))))

				+ CALCULATE(
					IF(PreviousDay(Dates[Date]) = Blank()
							|| CALCULATE(Report[Total Value], PreviousDay(Dates[Date]), All(Dates)) = 0
					  , CALCULATE(
						  SUMX(Trans, (-1)* DIVIDE(Report[Trans Ext Cash Flow Amnt]
													,((1+AVERAGE(XIRR[XIRR])) ^ (Int(Trans[Date] - Report[XIRRFirstTransDate]  )/365) )
												  )
						  )
						  , DatesBetween(Dates[Date], FIRSTDATE(Dates[Date]), LASTDATE(Dates[Date]))
						  , All(Dates)
						  , TransType[ExternalImpactPortfolioSign]<> 0
						)

					  , CALCULATE(
						  SUMX(Trans, (-1) * DIVIDE(Report[Trans Ext Cash Flow Amnt] 
													, ((1+AVERAGE(XIRR[XIRR])) ^ (Int(Trans[Date] - CALCULATE(PreviousDay(Dates[Date]), All(Trans), VALUES(Dates[Date])) )/365))
												   )
						  )
						  , DatesBetween(Dates[Date], FIRSTDATE(Dates[Date]), LASTDATE(Dates[Date]))
						  , All(Dates)
						  , TransType[ExternalImpactPortfolioSign]<> 0
						)
					)
				  )

				+ CALCULATE(DIVIDE(Report[Total Value], ((1+AVERAGE(XIRR[XIRR])) ^ ((Int(LASTDATE(Dates[Date]) - Report[XIRRFirstDate])/365)))))
				)
			, 1
			)
		, XIRR[XIRR]
		)
	)
  , IF((COUNTROWS(Trans)=0 && Report[Equity Value] = 0) || Report[XIRRFirstDate] = LASTDATE(Dates[Date]) || NOT(HASONEVALUE(Symbol[Symbol])) || Report[Cash Value]<>0, Blank()
       ,    MINX(TOPN(1, ALL(XIRR)
			, ABS(         
				  CALCULATE(IF(PreviousDay(Dates[Date])<>Blank(), (-1)* CALCULATE(Report[Equity Value], PreviousDay(Dates[Date])) ))

				  + CALCULATE(
					IF(COUNTRows(Trans)=0 , Blank()
					  , IF(PreviousDay(Dates[Date]) = Blank()
							|| CALCULATE(Report[Equity Value], PreviousDay(Dates[Date])) = 0
							, SUMX(Trans
								, DIVIDE(ROUND(Trans[TotalAmnt] * Related(TransType[CashImpactSign]) * Report[TransReportExchRate], 2)
										, ( (1+AVERAGE(XIRR[XIRR]))
											^ (Int(Trans[Date] 
												- CALCULATE(MIN(Trans[Date]), AllExcept(Trans, Trans[Symbol], Trans[Account]), VALUES(Symbol[Symbol]), VALUES(Account[Account]), VALUES(Dates[Date]))
												)/365)
										  )
								  )
							)
							, SUMX(Trans
								, DIVIDE(ROUND(Trans[TotalAmnt] * Related(TransType[CashImpactSign]) * Report[TransReportExchRate], 2)
										, ( (1+AVERAGE(XIRR[XIRR]))
												^ (Int(Trans[Date] 
													- CALCULATE(DateAdd(FIRSTDATE(Dates[Date]),-1,day), All(Trans), VALUES(Dates[Date]))
												   )/365)
										  )
								  )
							 )
						)
					)
				  )
				 
				  + CALCULATE(IF( AVERAGE(XIRR[XIRR]) = -1, Blank(), Divide(Report[Equity Value]  , ((1+AVERAGE(XIRR[XIRR])) ^ ((Int(LASTDATE(Dates[Date]) - Report[XIRRFirstDate])/365)))) ))
			)
			, 1
			)
		, XIRR[XIRR]
         )
	  )
)

BeesMD

Thanks so much for your help, Vidas!

This error hasn't happened to me in the new updated version of the slicer.

http://puu.sh/AbRdA/a2da1e93e9.png

I have, however, started getting this message instead - is this a similar issue with a filter?

Many thanks!

VidasMatelis

Hi BeesMD,

THis is different issue.
So Error

"Report[Profit %] An argument of function "LN" has the wrong data type or the result is too large or too small"

This error usually indicates that Portfolio Slicer (PS) was not able to calculate Profit % or Capital Gain% or Dividend %, because at one or more months data did not make any sense. In many cases this happens when you track cash and in some month your cash balance is negative - that makes calculation of Profit % impossible, as negative cash balance makes any calculation meaningless.
In your case - you do not track cash, so there is a different issue. In your case issue is that you have symbol buy transaction, but you do not have quotes for that symbol, so there is a huge capital gain drop at the end of the "buy" month of total value to 0 and then next month that symbol gets dividends, but symbol total value is 0. So in PS profit % calculation goes nuts and reports an error.

So in Quotes.csv file you have symbol CBA, but in the Symbol table you have symbol "CBA.AX".
To fix this:
in Symbol table change CBA.AX to CBA and then remove Symbol Alias record that maps CBA to CBA.AX and then do refresh. First refresh attempt might still fail, but continue refresh second time and it should work.

BeesMD

Thanks for your help yet again