Query to retrieve average FX Rates

christoph.b

In the financials tab of the company profiles on the Eikon webtool, the balance sheet, income statement, etc. can be converted from the reported currency to USD. When converting, the avg. FX rate is displayed. I'm able to query the financials of an instrument via the Eikon API Proxy in python, but not the avg. FX rate. I received these sample queries from the Refinitiv support, but they seem to be in a different format than what the Eikon API Proxy expects:

=TR("CNY=","AVG(1/TR.FxRateComposite)","Sdate=2019-01-01 Edate=2019-12-31")
=TR("CNY="," TR.FxRateComposite","Sdate=2019-12-31")
=TR("CNY="," 1/TR.FxRateComposite","Sdate=2019-12-31")

What is the correct translation of these queries in order to make them work via in python?

I'm looking for something like this:

ek.get_data('"CNY=","AVG(1/TR.FxRateComposite)"', '"Sdate=2019-01-01 Edate=2019-12-31"')

jason.ramchandani01

@christoph.b Is this the kind of thing you are looking for:

df, err = ek.get_data("CNY=",["TR.FxRateComposite.date","TR.FxRateComposite"], {'SDate':'2019-01-01', 'EDate':'2019-12-31'})
avgFX = 1/(df['FX Rate Last'].mean())
avgFX

christoph.b

Thank you for the answer, works!

Alex Putkov.1

@christoph.b

As an alternative to retrieving the timeseries and averaging it in the dataframe, you could use server side AVG function, as you originally intended. You just need to slightly amend the syntax:

df, err = ek.get_data('CNY=', 
                      'AVG(TR.FxRateComposite(Sdate=20190101,Edate=20191231))')
avgFX = 1/df.iloc[0,1]
avgFX

The result of course comes up to the same figure as calculated using the method @jason.ramchandani suggested.

christoph.b

I have a follow-up question: I've tried out this query for various currencies and dates. I'm trying to reproduce the exact FX rate that I'm able to view in the Eikon Webtool in the Financial Tab of companies by converting from reported currency to USD. Do you know how to reproduce the exact values using the Eikon python API?

Alex Putkov.1

@christoph.b

When asking a new question on these forums, please always start a new thread. Old threads with accepted answers are not monitored by forum moderators.
The best resource for content explanation questions, such as this one, is Refinitiv Helpdesk, which you can reach by using Contact Us capability in your Eikon application or by visiting MyRefinitiv. This forum is dedicated to software developers utilizing Refinitiv APIs. The moderators on this forum are technical experts and do not possess deep expertise in every type of content available through Refinitiv products required to answer specific content explanation questions.
I opened case 09412498 with Refinitiv Helpdesk on your behalf. You will be contacted by Refinitiv Support with the answer to your question or if they need any clarification from you before providing the answer.

christoph.b

Understood, thank you!