FX rate FMV not correct

ryano

Good Morning,

I have noticed that the FMV fx rate being used on my USD holdings is incorrect. As of today, it is using 1.343 but should be using something closer to 1.452. I see that the correct rate is being pulled into through the scripts but it is just not being applied.

Any thoughts?

Thanks,
Ryan

VidasMatelis

Ryan,
Could you please clarify:
1. What is the currency pair (from/to) you are trying to use for conversion?
2. What is the latest date for this currency pair in CurrencyConv.csv file? What is the date before that?
3. Are you looking at latest day transaction, that is 2020-03-20?
4. Do you have access to PowerPivot model in Excel (Do you see PowerPivot button)? If so, can you go into that model, select CurrencyConv table and check latest dates for your currency pair in that table.

ryano

thanks Vidas,

Here are the answers to your questions:

1. USD to CDN

2. "2020-03-19 1.45222 USD CAD"
March 18th is right before this one

3. my latest transaction was entered for the beginning of March

4. this is the second last line:
2020-03-19 USD CAD 1.45222 2020-03-18 0.00408 2020-03-18 0.00408

the last line shows:

2014-12-31 N/A N/A 1.0000000001 1 1

VidasMatelis

So you do have latest exchange rates.
Can you please clarify - are you looking at "Holdings" report? If so, could you please confirm that slicers in "Year" and "Month In Year" are not selected?

ryano

yes, i am looking at the holdings tab. 2020 was selected so i cleared them. I reran everything but it did not change.

VidasMatelis

Is it possible for you to send me your workbook and all files from PSData folder? My email is posted on this page: http://www.portfolioslicer.com/contact.html

VidasMatelis

Hi,

Thanks for the workbook - I found why you are seeing this behavior.
You have symbols that you manually update date once a month. So last update for your symbol was 2020-02-29. When this quote is loaded into PS, it is automatically converted to other exchange rates with exchange rate of the quote - that is 2020-02-29. So when quote is looked up, we look for latest quote in the last 40days and that quote is used, but that also means that exchange rate is used from that quote date, not from latest day.
For the months, when there is such significant exch rate changes, I would recommend updating manually maintined quotes more often and that will resolve your issue. So simply add todays date quotes (even if they do not change over the month) and new exchange rate will be picked up.

ryano

ok great! thanks so much for your help.