Calculate within EIKON API request

Florian92

Hi,

I am currently trying to retrieve the yearly median of bid-ask spreads for given firms. In Datastream this expression looks like this: MED#((X(PA)-X(PB)/(X(PA)+X(PB)/2)),01/01/2010,31/12/2015) with X being the respective company identifier and frequency set to "Yearly" to get the yearly median.

Is there any way to do the same in the EIKON API? I tried calculating with:

data, error=ek.get_data('MSFT.O', [('TR.ASKPRICE'-'TR.BIDPRICE')/(('TR.ASKPRICE'+'TR.BIDPRICE')/2)], parameters={'SDate':'2010-01-01', 'EDate':'2015-12-31'}) but received an error:

unsupported operand type(s) for -: 'str' and 'str'

Alex Putkov.1

You have a syntax error in your expression. The second argument of get_data method must be a string or a list of strings. If you'd like the timeseries of the ratio of bid/ask spread to the mid price use:

ek.get_data('MSFT.O',
            ['TR.BIDPRICE.date',
             '2*(TR.ASKPRICE-TR.BIDPRICE)/(TR.ASKPRICE+TR.BIDPRICE)'],
            {'SDate':'2010-01-01', 'EDate':'2015-12-31'})

If you just need the median of the series use:

ek.get_data('MSFT.O',
             '2*MEDIAN((TR.ASKPRICE-TR.BIDPRICE)/(TR.ASKPRICE+TR.BIDPRICE))',
            {'SDate':'2010-01-01', 'EDate':'2015-12-31'})

Florian92

Thank you very much!! Ok, I see my mistake. I tried both of your suggestions but I get the same (daily) values for both requests. In the very end I am only interested in yearly median of my bid-ask spreads.I think I have to run each year separately right? Or is there any way to use lets say a five year period

1. ek.get_data('MSFT.O',
2. '2*MEDIAN((TR.ASKPRICE-TR.BIDPRICE)/(TR.ASKPRICE+TR.BIDPRICE))',
3. {'SDate':'2010-01-01', 'EDate':'2015-12-31'})

which retrieves only the median value for each year (MSFT.O, 2010, XX; MSFT.O, 2011, XX; MSFT.O, 2012, XX....)?

Alex Putkov.1

https://community.developers.refinitiv.com/discussion/comment/57737#Comment_57737

To get the annual median you either need to submit separate get_data request for the median for each year or you can retrieve the entire daily series and calculate the annual median using capabilities of pandas.

Florian92

https://community.developers.refinitiv.com/discussion/comment/57763#Comment_57763

Ok, thats what I expected. Should not be a problem using pandas. Using your (corrected) expression above however still gives me dialy values even when using the MEDIAN operator. Do I need to specify frequency 1Y or "Yearly" as in Datastream somewhere?

Alex Putkov.1

https://community.developers.refinitiv.com/discussion/comment/57744#Comment_57744

I'm not sure I understand. My original example that uses MEDIAN function in the field expression returns pandas dataframe with a single row, which contains the median for the daily series framed by the values of SDate and EDate parameters. It does not return daily series.

Florian92

https://community.developers.refinitiv.com/discussion/comment/57738#Comment_57738

I had another look at your code and you are right. I missed the opening and closing paranthesis on the median function. This resulted in daily data instead of one median datapoint!
Thanks again for your help, appreciate it!