Is it possible to get a currency adjusted price from CF_last using get_data?

gabriel31

Hello, I know you can get a stock's currency adjusted price if you use fields such as ['TR.PriceClose' and 'TR.PriceOpen'], however, is it possible to get a currency adjusted price if you use the field 'CF_LAST'?

I want to be able to see the currency adjusted price in USD in real time for foreign-dominated securities.

Alex Putkov.1

No, this is not possible directly from Eikon. You'd need to retrieve the stock price and the exchange rate separately and convert the stock price to USD. Here's an example. It handles the fact that, while most currencies in the FX market are quoted in currency units per 1 USD, there are some currencies (e.g. GBP, EUR, AUD etc.) that are quoted inversely, i.e. in USD per 1 currency unit. The example also handles the case where stocks are quoted in minor rather than major currency units (pennies and cents rather than pounds and dollars). I know this to be the case in the UK, Israel and South Africa. There may be other markets which require similar handling.

#Retrieve stock prices
stk_prc_df, err = ek.get_data(['VOD.L','LHAG.F','PEUP.PA',
                               'LKOH.MM','0001.HK','ASX.AX'],
                              ['CF_LAST','CF_CURR'])
#Create the list of currencies and remove duplicates
curr_list = stk_prc_df['CF_CURR'].tolist()
exch_rates_df = pd.DataFrame(index=list(dict.fromkeys(curr_list)),
                             columns=['RIC', 'Scale'])
#Create dictionary of minor currency units that stocks may be traded in
exception_dict = {'GBp':['GBP=',100], 'ILa':['ILS=',100], 'ZAc':['ZAR=',100]}
#Add exchange rate RICs and scaling factors to exchange rates dataframe
for x in exch_rates_df.index:
    exch_rates_df.loc[x, ['RIC', 'Scale']] = exception_dict.get(x, 
                                                                [x + "=", 1])
#Retrieve exchange rates from Eikon
tmp_df, err = ek.get_data(exch_rates_df['RIC'].tolist(),
                          ['MID_PRICE', 'BASE_CCY'])
#Calculate exchange rates to apply to stock prices
exch_rates_df['Exch rate'] = np.where(
    tmp_df['BASE_CCY']=='USD',
    tmp_df['MID_PRICE'], 1 / tmp_df['MID_PRICE'])
#Calculate stock prices in USD
for x in stk_prc_df.index:
    stk_prc_df.loc[x, 'Price USD'] = (stk_prc_df.loc[x, 'CF_LAST'] / 
    (exch_rates_df.loc[stk_prc_df.loc[x, 'CF_CURR'],'Exch rate'] * 
     exch_rates_df.loc[stk_prc_df.loc[x, 'CF_CURR'],'Scale']))
stk_prc_df