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

bob.lee
bob.lee
in Eikon Data APIs

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.

Tagged:

Best Answer

  • m.bunkowski
    m.bunkowski
    Answer ✓

    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)


Answers

  • Jirapongse
    Jirapongse admin

    @bob.lee

    What is Eikon Excel API that you are using? Is it Eikon COM API or Eikon Excel functions?
  • bob.lee
    bob.lee

    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.

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

Categories