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'
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'})
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
which retrieves only the median value for each year (MSFT.O, 2010, XX; MSFT.O, 2011, XX; MSFT.O, 2012, XX....)?
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.
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?
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.