Daily Currency Exchage Rate

patr0kxxx

I have a CAD/USD portfolio. In the "Daily" tab, the chart "Daily Currency Exchage Rate", display the CAD/USD value (currently 1.28xxxx). I would like to display the USD/CAD value (currently 0.77xxxx). Is this currently possible with 2.3, how ?

VidasMatelis

Hi,

In the Daily worksheet if you go to the right far enough (column AI5), you will see Pivot Table that is used to build this report. There is a filter for CurrencyID = 2 - that is probably USD currency and CurrencyID = 1 probably your CAD currency).
If you have Excel 2010 or Excel 2013/2016 with full PowerPivot support (you see PowerPivot menu) then you can easily edit measure "Exch Rate to Primary" - I believe it is used just in this report.
So you could change formula from:

=IF(NOT(HasOneValue(ReportCurrency[ReportCurrency])), Blank()
	, IF(VALUES(ReportCurrency[ReportCurrency]) = CALCULATE(VALUES(ReportCurrency[ReportCurrency]), ReportCurrency[CurrencyID]=1)
		, Blank()
		, CALCULATE(AVERAGE(CurrencyConv[ExchRate])
			, FILTER(ALL(CurrencyConv[CurrencyTo]), CurrencyConv[CurrencyTo] =VALUES(ReportCurrency[ReportCurrency]))
			, FILTER(ALL(CurrencyConv[CurrencyFrom]), CurrencyConv[CurrencyFrom] = CALCULATE(VALUES(ReportCurrency[ReportCurrency]), ReportCurrency[CurrencyID]=1, ALL(ReportCurrency)))
		  )
	  )
)

to formula

=IF(NOT(HasOneValue(ReportCurrency[ReportCurrency])), Blank()
	, IF(VALUES(ReportCurrency[ReportCurrency]) = CALCULATE(VALUES(ReportCurrency[ReportCurrency]), ReportCurrency[CurrencyID]=2)
		, Blank()
		, CALCULATE(AVERAGE(CurrencyConv[ExchRate])
			, FILTER(ALL(CurrencyConv[CurrencyTo]), CurrencyConv[CurrencyTo] =VALUES(ReportCurrency[ReportCurrency]))
			, FILTER(ALL(CurrencyConv[CurrencyFrom]), CurrencyConv[CurrencyFrom] = CALCULATE(VALUES(ReportCurrency[ReportCurrency]), ReportCurrency[CurrencyID]=2, ALL(ReportCurrency)))
		  )
	  )
)

And then in the Pivot Table I talked about above you would change filter condition to CurrencyID = 1.

What version of Excel do you have and do you have PowerPivot full support?

patr0kxxx

Excel 2016 with full powerpivot support

VidasMatelis

In Excel 2016:
PowerPIvot->Measures->Manage Measures-> Select "Exch Rate Impact"-> Edit. In the formula bar enter formula from above.

Refresh and you are done.

patr0kxxx

Thanks for the quick followup Vidas,

I modified the formula as instructed but then the graph simply disappeared along with the value in column AI5. Do i need to modify psConfig.txt or similar to get USD/CAD instead of CAD/USD ?

VidasMatelis

YOu should already have CAD->USD and USD-CAD rates - you could double check in the file.
You should also change filter for that report from "CurrencyID : 2" to "CurrencyID: 1"

patr0kxxx

changing the filter to 1 did the trick, thanks a lot for the great support