Report Currency Error

joelm

Hey there,

I am getting some nonsensical values when I change report currency. For example, my capital gains go from negative to positive upon changing currency which, unfortunately for me, cannot be right.

I had this problem once before but it was because the CurrencyConv.csv wasn't being brought into the data model. This time however, I can see clearly all the currency conversions in the data model and they look reasonable. I have done about 6 refreshes and that doesn't correct the error.

Any ideas on where to look? I really don't understand DAX syntax so I can't figure it out.

Thanks,

Joel

VidasMatelis

Joel,

Can you send me your workbook and files from PSData folder? I can take a quick look. My email is posted on this page: http://www.portfolioslicer.com/contact.html
Please indicate one symbol that I can take a look at and let me know why do you think calcs are not right.

Cap gains between different reporting currencies can change the sign. I have this in my portfolio.

VidasMatelis

Hi Joel,

I believe I understand what is your concern. You check report using Reporting Currency as CAD and you see Capital Gain = -50$. Then you switch to BPL currency and you see Capital Gain = 300.
But when you change reporting currency, you do not just change final calculation result - that is Capital Gain amount, but you actually convert to reporting currency ALL amounts, including, your purchase amount and current price and even all dividends as they were paid.
Lets say in 2008 you bought 100 shares of symbol ABC with per share price of 1USD. Today that symbol price is 0.9USD.
When you choose reporting currency as USD capital gain for this symbol will be calculated:
Cost Basis (Buy price): 1 x 100 = 100USD
Current price: 0.9 x 100 = 90USD
Capital gain: 100USD-90USD = -10USD.

If you switch your calculations to reporting currency CAD, then in 2008 USD to CAD exch rate was 1CAD=1USD, but now the exchange rate is 1CAD = 0.7USD or 1USD = 1.43CAD
Cost Basis (Buy Price): 1 x 100 x 1(CAD) = 100CAD
Current price 100 x 0.9x1.43=128.7CAD
Capital gain: 128.7CAD-100CAD=28.7CAD

If you keep any USD symbols in any open account, then as Canadian when you sell symbol, you would have to calculate capital gain just like PS calculates.

Does this explanation helps?

joelm

Hi Vidas,

Your examples make this clear.

The gain from the deteriorating BRL/CAD exchange rate was greater than the capital gain (loss) on my CAD denominated ETFs causing the change in sign.

You mentioned that I have to calculate capital gains in this way as a Canadian? I suppose the CRA considers gain from exchange rate as a capital gain.

I hadn't thought of this before.

Thank you,

Joel