Upgrade from Eikon -> Workspace. Learn about programming differences.

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

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
1.6k 3 3 3

Get one of the values in the Excel API that returns a list of values

Hello,

I have a question specific to using the Eikon data API in Excel (i.e. Eikon Excel API). For API that returns a set of values (i.e. return a data frame when called from Python), how can I get a specific value within that set of values if I call the API from Excel?

For example: I will get a list of values if I call the Eikon data API as:

fund_TNA, dummy = ek.get_data('LP68331664',['TR.FundTotalNetAssets.Date', 'TR.FundTotalNetAssets'], {'SDate':'2000-01-01', 'EDate':'2023-04-30', 'Curn':'Native'})

The variable: fund_TNA will be a data-frame looks like:

    Instrument                  Date  Total Net Assets
0   LP68331664  2015-12-31T00:00:00Z      231271501.17
1   LP68331664  2016-03-31T00:00:00Z      134753836.25
2   LP68331664  2016-06-30T00:00:00Z      114594781.12
3   LP68331664  2016-09-30T00:00:00Z      114989303.67

In Python, I can get the value in the first row as fund_TNA.iat[0,2]. Can I do the similar thing in Eikon Excel to get the single value populated in a cell rather than the full list of values?

*This is a technical question, not about business logic, such as changing the dates to get only 1 row of data.

eikon-data-api#technologyexcel
icon clock
10 |1500

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

@bob.lee

What is Eikon Excel API that you are using? Is it Eikon COM API or Eikon Excel functions?

Hi @Jirapongse , I am referring to Eikon Excel functions. i.e. I want to type the formula in Excel's function bar like:

=@TR("LP68331664", "TR.FundTotalNetAssets.Date", ...

and get the TNA value at the top row as a single value. Hence, I need to do it in Excel, not in any programming language.

1 Answer

· Write an Answer
Upvote
Accepted
1.4k 5 3 6

Hi @bob.lee

In Eikon Excel there is no possibility to throw the selected row out of the whole table when using TR function. However you can limit the output area starting from the top left corner. If you try the formula below, you should get the single value.


=@TR("LP68331664","TR.FundTotalNetAssets;TR.FundTotalNetAssets.date","SDate=2000-01-01 EDate=2023-04-30 Curn=Native",C3:C3)


icon clock
10 |1500

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

Thanks @marcin.bunkowski01 for the answer and the tip for a potential work-around.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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