›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links

Categories

New release - 2.4.08

VidasMatelisVidasMatelis
in General
Issues fixe:
- Excel 2016 "Expd Return % (Alloc)" value for first year or at all level was not correct
- Capital Gain % and Profit % calculations was sometimes not correct for "* Cash" symbol
- Value for "Expd Return % (TMTR)" was sometimes not right for the first year or for all years calculation

Updated Formulas: 
Expd Return % (TMTR)
=IF((Report[Total Value]<> 0 || Report[Start Value] <> 0 || COUNTROWS(Trans)>0)
	, DIVIDE(
	    CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Config[TMTRIndex]), LASTDATE(Dates[Date]), All(Dates), All(Symbol))
	  , IF(PreviousDay(Dates[Date]) = Blank()
			, CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Config[TMTRIndex]), FIRSTDATE(Dates[Date]), All(Dates), All(Symbol))
			, CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Config[TMTRIndex]), PreviousDay(Dates[Date]), All(Dates), All(Symbol))
        )			
	  ) - 1
)
Expd Return % (Alloc)
=IF(PreviousDay(Dates[Date]) <> Blank() && (Report[Total Value]<> 0 || Report[Start Value] <> 0 || COUNTROWS(Trans)>0)
  , IF(COUNTROWS(VALUES(Symbol[Allocation]))=1
    , IF(CALCULATE(VALUES(Allocation[Index]), FILTER(Allocation, Allocation[Allocation] = VALUES(Symbol[Allocation]))) <> ""
		, CALCULATE(
			IF(CALCULATE(Report[Symbol Price], LASTDATE(Dates[Date]), All(Dates))<> 0 && CALCULATE(Report[Symbol Price], PreviousDay(Dates[Date]), All(Dates)) <> 0
		    ,DIVIDE(
			   CALCULATE(Report[Symbol Price], LASTDATE(Dates[Date]), All(Dates))
		     , IF(PreviousDay(Dates[Date]) = Blank()
					, CALCULATE(Report[Symbol Price], FIRSTDATE(Dates[Date]), All(Dates))
					, CALCULATE(Report[Symbol Price], PreviousDay(Dates[Date]), All(Dates))
			   )
		    ) - 1
			)
		    , ALL(Symbol)
			, FILTER(ALL(Symbol), Symbol[Symbol] = CALCULATE(VALUES(Allocation[Index]), FILTER(ALL(Allocation), Allocation[Allocation] = VALUES(Symbol[Allocation]))))
		  )
	  )
    , SUMX(FILTER(Allocation, Allocation[Index] <> "" && Report[Total Value]<>0)
		, CALCULATE(EXP(SUMX(FILTER(VALUES(Dates[Month]), Report[Total Value] <> 0 
		                    && CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), LASTDATE(Dates[Date]), All(Dates), All(Symbol)) <> 0
							&& CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), PreviousDay(Dates[Date]), All(Dates), All(Symbol)) <> 0
		                 )
		   , LN(1 +
				CALCULATE(
				    (DIVIDE(CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), LASTDATE(Dates[Date]), All(Dates), All(Symbol))
						  , IF(PreviousDay(Dates[Date]) = Blank()
								, CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), FIRSTDATE(Dates[Date]), All(Dates), All(Symbol))
								, CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), PreviousDay(Dates[Date]), All(Dates), All(Symbol))
							)
					     ) - 1  
					)
					* ROUND(DIVIDE(CALCULATE(Report[Total Value], All(Symbol), VALUES(Allocation[Allocation]), VALUES(Symbol[Allocation]))
				 		 , CALCULATE(Report[Total Value], All(Allocation), All(Symbol)))
					  , 2)
				)
			)
		  )) - 1)
      )
   )
)
Compare To Value
=Report[Start Value] +
	IF((Contains(VALUES(SymbolSector[Sector]), SymbolSector[Sector], "* Cash") || Not ISCROSSFILTERED(SymbolSector[Sector])) && CONTAINS(Symbol,Symbol[Symbol],"* Cash")
     , IF(HASONEVALUE(Symbol[Symbol])
		, CALCULATE(SUMX(Trans, Trans[TotalAmnt] * Report[TransReportExchRate]), TransType[CashFlag]=1)
	     +CALCULATE(SUMX(Trans, Trans[TotalAmnt] * Report[TransReportExchRate]), TransType[DividendFlag]=1, ALL(Symbol))
	  	, CALCULATE(SUMX(Trans, Trans[TotalAmnt] * Report[TransReportExchRate]), TransType[DepositTransSign]<>0)
	   )
	 , CALCULATE(SUMX(Trans, Trans[TotalAmnt] * Report[TransReportExchRate]), TransType[BookValueSign]=1)
    )




Comments

  • VidasMatelisVidasMatelis
    For Excel 2016 
    Expd Return % (Alloc)
=IF(COUNTROWS(VALUES(Symbol[Allocation]))=1
  , var AllocIndex = IF(COUNTROWS(VALUES(Symbol[Allocation]))=1, CALCULATE(VALUES(Allocation[Index]), FILTER(Allocation, Allocation[Allocation] = VALUES(Symbol[Allocation]))), Blank())
    var res= IF(AllocIndex <> ""
        , DIVIDE(CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, LASTDATE(Dates[Date]), All(Dates), All(Symbol))
               , IF(PreviousDay(FIRSTDATE(Dates[Date])) = Blank()
			         , CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, FIRSTDATE(Dates[Date]), All(Dates), All(Symbol))
			         , CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, PreviousDay(FIRSTDATE(Dates[Date])), All(Dates), All(Symbol))
				 )
          ) - 1
      )
     RETURN IF(res=0 || res = -1, BLANK(), res)
  , SUMX(FILTER(Allocation, Allocation[Index]<>"" && Report[Total Value]<>0)
        , var AllocIndex = Allocation[Index]
          RETURN
          CALCULATE(
            PRODUCTX(FILTER(VALUES(Dates[Month]), Report[Total Value] <> 0
                            && CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, LASTDATE(Dates[Date]), All(Dates), All(Symbol)) <> 0
                            && CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, PreviousDay(Dates[Date]), All(Dates), All(Symbol)) <> 0
                           )
            , 1 + CALCULATE( (DIVIDE(CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, LASTDATE(Dates[Date]), All(Dates), All(Symbol))
                                    , IF(PreviousDay(FIRSTDATE(Dates[Date])) = Blank()
										  , CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, FIRSTDATE(Dates[Date]), All(Dates), All(Symbol))
										  , CALCULATE(Report[Symbol Price], Symbol[Symbol]=AllocIndex, PreviousDay(FIRSTDATE(Dates[Date])), All(Dates), All(Symbol))
									   )
                            ) - 1)
                    * ROUND(DIVIDE(CALCULATE(Report[Total Value], All(Symbol), VALUES(Allocation[Allocation]), VALUES(Symbol[Allocation]))
                                 , CALCULATE(Report[Total Value], All(Allocation), All(Symbol))), 2)
                    )
            ) - 1
         )         
    )
 )
  • patr0kxxxpatr0kxxx
    Vidas could you give us the exact Excel field that need to be modified ? I would like to simply "monkeypatch" my 2.4.07 version instead of doing a manual update.

    Thanks :)
  • VidasMatelisVidasMatelis
    Hi,

    At the first line of each formula there is a name of the measure.
    Formula start with symbol '=' and you should replace old formula from where it starts with '='.
    Keep in the mind that for Excel 2016 one measure formula is slightly different - second post in this thread.
  • patr0kxxxpatr0kxxx
    Thanks for the quick reply Vidas.

    Can you provide raw instruction on where to edit formulas in the Excel 2016. Is the option in the ribbon ? Does i need to edit the field directly ? I cant seem to view the formulas used.

    Sorry but you are the Excel wizard here :smile:
  • ivanmivanm
    I am having the same issues. I cannot find the specific cells which contain the formulas that I need to modify. I even tried using the "Show Formulas" function but still had no luck.
  • VidasMatelisVidasMatelis
    To be able to edit formulas, you need Excel 2016 version that has "FULL" PowerPivot support:

    http://portfolioslicer.com/docs/excel2013-powerpivot.html

    If you have full PowerPivot support, then you will see (after enabling) menu "Power Pivot". There will be button "Measures" and choose "Manage Measures..." option and then select measures you want to edit.
  • CNHCNH
    I have just moved my data across to the new excel v2.4.08. No apparent issues here.

    I note that the PowerBI file in the download area is dated (2019-Jan-11) and shows the tag <<<***New***>>>.

    Can I check if this file is different to previous PowerBI file.

    The reason I ask is that I have modified/tailored the PowerBI for my own reporting purposes, to migrate the PowerBI is a bigger job for me
  • VidasMatelisVidasMatelis
    Hi,

    PowerBI uses same formula as Excel 2016. So for Expd Return % (Alloc) use formula in second post.
Sign In or Register to comment.