For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
christoph.b avatar image
23 ●1 ●2 ●3

Query to retrieve average FX Rates

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"')
eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikonpythonforex
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

4 Answers

Upvote
Accepted
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@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


1605174087388.png (4.3 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
christoph.b avatar image
23 ●1 ●2 ●3

Thank you for the answer, works!

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
Alex Putkov.1 avatar image
39.2k ●75 ●11 ●27

@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.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
christoph.b avatar image
23 ●1 ●2 ●3

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?

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Alex Putkov.1 avatar image 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 avatar image christoph.b Alex Putkov.1

Understood, thank you!

Click below to post an Idea Post Idea