Wrong exchange rates for HKD

rayhan

Hi Vidas,

I am holding a few Hong Kong stocks and wish to convert them to USD for easier tracking. However, while I can override the cost basis for calculation, the value of holdings seem to be incorrectly priced. I took a look at the script and it appears that there is some error with the HKD-USD conversion, leading to outsized position sizes. As I do not invest in Canadian stocks, CAD would not be relevant for my case. Looking forward to your reply. Thank you and have a great week ahead.

Best Regards,
Ray

VidasMatelis

Hi Ray,

In the .bat file you specify a script that is used to extract currency exchange rates. If one data source provides you with currency exchange rates that are not right, you can choose different data source. Please read more here: http://portfolioslicer.com/scripts/currencyexchange-datasource.html

rayhan

Hi Vidas,

I still seem to have problem with HKD, while changing the source has led to correct exchange rates, the HKD does not seem to be recognized in my file (highlighted red) despite stated it under the src tab. Thanks once again.

Best Regards,
Ray

VidasMatelis

Hi,

This red highlighted can be ignored as this is just formatting warning. If you will select cell C2 in Symbol table, then choose "Conditional Formatting", then "Manage Rules", then select last rule for column s = "$c$2...", you will see the rule that checks for currency in the list $N5$N5

=AND(COUNTIF(src!$N$5:$N$5, C2)=0, LOWER(C2)<> "cash")

This list should be $N5$N7. If you carefully change that formula to:

=AND(COUNTIF(src!$N$5:$N$7, C2)=0, LOWER(C2)<> "cash")

This issue will go away.
Excel supposed to adjust that formula when the related table has records removed/added, but sometimes this does not happen.

Javier

Hi Vidas,

Since it's my first comment I want to deeply thank you for this project.

I also have some HKD positions so I also run into this problem. I just found out that stooq, for the HKDUSD pair actually gives HKDUSD X 100 (Hong Kong Dollar / U.S. Dollar 10^2:1 (HKDUSD)).
Since stooq is the best option to get currency quotes I wonder if this could be fixed in the script.

Regards,

Javier

VidasMatelis

Hi Javier,

This is a bug with Stooq data. Could you try to contact them so they can fix it on their side?

Javier

Hi Vidas,

I don´t think it's a bug. I believe that for some pairs they give 1:1 quotes and for some others, when the first currency of the pair has a much lower value compared to the second, they give a 100:1 quote.

See some examples below:

HKDUSD Hong Kong Dollar / U.S. Dollar 10^2:1 Currency 12.7436 -0.00%
HKDJPY Hong Kong Dollar / Japanese Yen 1:1 Currency 14.0324 +0.25%
HKDAUD Hong Kong Dollar / Australian Dollar 10^2:1 Currency 16.6547 -0.42%

CNYUSD Chinese Renminbi / U.S. Dollar 1:1 Currency 0.15658 +0.13%
CNYEUR Chinese Renminbi / Euro 10^2:1 Currency 13.3124 -0.38%
CNYJPY Chinese Renminbi / Japanese Yen 1:1 Currency 17.2439 +0.47%
CNYHKD Chinese Renminbi / Hong Kong Dollar 1:1 Currency 1.22870 +

JPYUSD Japanese Yen / U.S. Dollar 10^2:1 Currency 0.90789 -0.28%
JPYEUR Japanese Yen / Euro 10^2:1 Currency 0.77172 -0.68%
JPYCHF Japanese Yen / Swiss Franc 10^2:1 Currency 0.89612 -0.03%
JPYHUF Japanese Yen / Hungarian Forint 1:1 Currency 2.46006 -0.66%
JPYGBP Japanese Yen / British Pounds 10^2:1 Currency 0.67607 -0.49%
JPYNZD Japanese Yen / New Zealand Dollar 10^2:1 Currency 1.28977 -0.44%
JPYCAD Japanese Yen / Canadian Dollar 10^2:1 Currency 1.17286 -0.68%

VidasMatelis

Javier, I asked Maxim to look into this. But this might take some time.

Tum

Hi Vidas,

Since this thread was talking about currency, I would like to know you opinion. Currently, I use 2 currencies : USD and THB from stooq. The ratio is 10^2:1. Currently, I have to use extra script to convert ratio to 1:1 and write as txt file.

Is there anyway I can hard code to MaximT script to convert it for me? Or which column in your excel can I modify formula?

VidasMatelis

I asked Maxim to see if he can help. I am hoping that he will provide script soon.