@Aleniles

I have created an example to get the Total Return Index (RI) and Price - Trade (P) data types of the following items.

'@AAPL,@MSFT,@AMZN,@TSLA,@GOOGL'

You can use the Datastream navigator to search for items and data types.

I used the DatastreamDSWS python package and the get_data method to get historical data.

import DatastreamDSWS as DSWS
username = "username"
password = "password"
ds = DSWS.Datastream(username = username, password = password)

data = ds.get_data('@AAPL,@MSFT,@AMZN,@TSLA,@GOOGL', ['P','RI'], start='-2Y', end='0D', kind=1, freq='D')

df = data.iloc[: , data.columns.get_level_values(1) == 'P'].melt(col_level=0, ignore_index=False, value_name='P')

tmp_df = data.iloc[: , data.columns.get_level_values(1) == 'RI'].melt(col_level=0, ignore_index=False, value_name='RI')

df["RI"] = tmp_df["RI"]
df.reset_index(inplace=True)
df.Dates = df.Dates.apply(lambda x: x.replace('-',''))
df[["Instrument","Dates","P","RI"]]

The output is:

Fantastic!

I just amended a little bit the code so to have also the shift I need in the return column (last column).

Many thanks!

data = ds.get_data('@AAPL,@MSFT,@AMZN,@TSLA,@GOOGL', ['P','RI'], start='-2Y', end='0D', kind=1, freq='D')

df = data.iloc[: , data.columns.get_level_values(1) == 'P'].melt(col_level=0, ignore_index=False, value_name='P')
tmp_df = data.iloc[: , data.columns.get_level_values(1) == 'RI'].melt(col_level=0, ignore_index=False, value_name='RI')
df["RI"] = tmp_df["RI"]

# shift total return backward one period
df["CHG"]=(100*(df['RI']/df['RI'].shift(1)-1).shift(-1))

df.reset_index(inplace=True)
df.Dates = df.Dates.apply(lambda x: x.replace('-',''))
df[["Instrument","Dates","P","RI"]]
# set instrument as an index
df.set_index('Instrument',inplace=True)

df.head(10)

One last question, how to adjust the code for tickers like these one ?

LHUSFRN is the price index

LHUSFRN(IN)+100 is the total return index

or

MSUSAML(MSPI) Price Index

MSUSAML(MSRI) Total Return Index

I mean how to rewrite the part below for those types of tickers ?

data = ds.get_data('LHUSFRN,@MSFT,@AMZN,@TSLA,@GOOGL', ['P','RI'], start='-2Y', end='0D', kind=1, freq='D') 

@Aleniles

It uses different data types to get the Price Index and Total Return Index values so I use a dictionary to map fields.

fields_map = {'P':"PI", "IN+100":"RI","MSPI":"PI","MSRI":"RI"}

The code looks like this:

fields_map = {'P':"PI", "IN+100":"RI","MSPI":"PI","MSRI":"RI"}


data = ds.get_data('LHUSFRN(P), LHUSFRN(IN)+100,MSUSAML(MSPI),MSUSAML(MSRI), @AAPL(P), @AAPL(RI)', 
                   start='-2Y', end='0D', kind=1, freq='D')

#Rename the multi-index columns
column_index0 = [x.split('(')[0] for x in data.columns.get_level_values(0)]
column_index1 = [x.split('(')[1].replace(')','') for x in data.columns.get_level_values(0)]

#use the dictionary to rename data types
column_index1_rename = []
for dt in column_index1:
    if dt in fields_map:
        column_index1_rename.append(fields_map[dt])
    else:
        column_index1_rename.append(dt)


data.columns = data.columns.from_tuples(list(zip(column_index0,column_index1_rename)),names=['Instrument', 'Field'])

df = data.iloc[: , data.columns.get_level_values(1) == 'PI'].melt(col_level=0, ignore_index=False, value_name='PI')

tmp_df = data.iloc[: , data.columns.get_level_values(1) == 'RI'].melt(col_level=0, ignore_index=False, value_name='RI')

df["RI"] = tmp_df["RI"]

df.reset_index(inplace=True)

df.Dates = df.Dates.apply(lambda x: x.replace('-',''))

df[["Instrument","Dates","PI","RI"]]

The output is:

Very kind of you!

One more question: what if I need to retrieve same kind of output for a couple of formulas instead? Like the following (even by writing another separate code if it is not possible to integrate it into the previous one):

(0.6*REBE#(S&PCOMP(RI)))+(0.4*(REBE#(LHAGGBD(IN)+100)))

(REBE#(CPRD#(1+((PCH#(MSACWF$(RI),1M)*0.006)+((PCH#(LHAGGBD(IN)+100,1M))*0.004)))))-100

I mean do you think is possibile to pull out data from a formula as well or do I need to convert it into python code?

Thanks!