How to Collect Datastream IBES Global Aggregate Earnings Data with Python and CodeBook - Part 1

    	
            
import DatastreamDSWS as dsws
 
# We can use our Refinitiv's Datastream Web Socket (DSWS) API keys that allows us to be identified by Refinitiv's back-end services and enables us to request (and fetch) data: Credentials are placed in a text file so that it may be used in this code without showing it itself.
(dsws_username, dsws_password) = (open("Datastream_username.txt","r"),
                                  open("Datastream_password.txt","r"))
 
ds = dsws.Datastream(username = str(dsws_username.read()),
                     password = str(dsws_password.read()))
 
# It is best to close the files we opened in order to make sure that we don't stop any other services/programs from accessing them if they need to.
dsws_username.close()
dsws_password.close()
 
 
# # Alternatively one can use the following:
# import getpass
# dsusername = input()
# dspassword = getpass.getpass()
# ds = dsws.Datastream(username = dsusername, password = dspassword)

        
        Copy
    

    	
            
import warnings # ' warnings ' is a native Python library allowing us to raise warnings and errors to users.
from datetime import datetime # This is needed to keep track of when code runs.
import pandas as pd # pandas is an open source data analysis and manipulation tool that allows us to use data-frames. Version 1.1.4 was used in this article.
import numpy as np # NumPy is a library allowing for array manipulations including linear algebra, fourier transform, and matrices. Version 1.20.1 was used in this article.
from tqdm.notebook import trange # ' tqdm ' allows loops to show a progress meter. Version 4.48.2 was used in this article.
import ipywidgets as widgets # ipywidgets is a library of interactive HTML widgets for Jupyter notebooks and the IPython kernel. Version 7.5.1 was used in this article.
from IPython.display import display # This allows us to display data-frames.
 
for i,j in zip([pd, np, tqdm, widgets],["pandas", "numpy", "tqdm", "ipywidgets"]):
    print(f"The library {j} imported is version {i.__version__}")

        
        Copy
    

    	
            
# # From the ' Reference data.xls ' file, one could run and use the following:
# xl_countries = pd.read_excel("Reference data.xls", sheet_name = "Countries")
# xl_regions = pd.read_excel("Reference data.xls", sheet_name = "Regions")
# xl_index = pd.read_excel("Reference data.xls", sheet_name = "Index Names 3")
# xl_columns = pd.read_excel("Reference data.xls", sheet_name = "Column Names 2")
 
# # If you don't have the ' Reference data.xls ' file, just use the bellow:
xl_regions = pd.DataFrame(data = {'Region': {0: 'EAFE', 1: 'EAFE + Canada',2: ...  '@:M1WLDXA'}})
xl_countries = pd.DataFrame(data = {'Region': {0: 'Argentina',1: 'Australia',2: ... ,50: '@:VEMSCIP'}})
# We will use the following to index and column our final data-frame on interest:
xl_index = pd.DataFrame(data = {'Category': {0: 'Energy', 1: 'Energy', 2: 'Energy', ... 88: 'M3MU', 89: 'M3WU'}})
xl_columns = pd.DataFrame({'Column 1': {0: 'PE-Ratio', 1: 'PE-Ratio', 2: 'PE-Ratio',  ... 31: '18M', 32: '', 33: ''}})
 
# # Merging the two data-frames together:
xl_regions = xl_regions.append(xl_countries, ignore_index = True)

        
        Copy
    

    	
            
def Region_to_DSWS_region_mnemonic_and_IBES_code(region = None, xl_regions = None):
    """Region_to_DSWS_region_mnemonic_and_IBES_code Version 1.0:
    This function returns a string of the Datastream Web Service (DSWS) region's mnemonic and IBES code.
    DSWS is Refinitiv's API retrieving data from Datastream to a Python Pandas data-frame. For information on DSWS, please visit 'https://developers.refinitiv.com/en/api-catalog/eikon/datastream-web-service'.
    For certain requests, DSWS needs a ticker specified with a nomenclature that includes DSWS region's mnemonic. For an example, see 'Examples' bellow.    
    
    
    Parameters:
    ----------
    
    region: str
        Region of choice's name (e.g.: 'EAFE-ex-UK').
        It has to be one of the elements in the following list: ['EAFE' 'EAFE + Canada' ...  'USA' 'Venezuela']
        Default: region = None
    
    xl_regions: Pandas data-frame
        The data-frame of regions to map with, including columns 'Region', 'Mnemonic' and 'Code'.
        This function (' Region_to_DSWS_region_mnemonic_and_IBES_code ') will map ' region ' to 'Mnemonic' and 'Code' in this data-frame and return them.
        If ' None ', a pre-defined data-frame is used.
        It is named with 'xl' at its start because it originally came from the Excel workbook 'Datastream IBES Global Aggregates MSCI.xlsm'.
        Default: xl_regions = None
    
    
    Dependencies:
    ----------
    
    pandas 1.0.3
    
    
    Examples:
    --------
    
    >>> Region_to_DSWS_region_mnemonic_and_IBES_code("EAFE + Canada")
    ('FC', '@:M1EAFEC')
    """
    if xl_regions == None: xl_regions = pd.DataFrame(data = {'Region': {0: 'EAFE', 1: 'EAFE + Canada', ... , 81: '@:VEMSCIP'}})
    
    if region not in xl_regions["Region"].to_list():
        print("Invalid ' region ' argument specified.")
    else:
        return xl_regions[xl_regions["Region"] == region]["Mnemonic"].values[0], xl_regions[xl_regions["Region"] == region]["Code"].values[0]

        
        Copy
    

    	
            Region_to_DSWS_region_mnemonic_and_IBES_code("United Kingdom")
        
        Copy
    

    	
            
ordered_mnemonic = "M1E1,M2E2, ... ,M3WU"
 
# The long string for ' IBESGA_fields ' (and subsequently ' IBESGA_tickers_str ') can be found in the ' Datastream IBES Global Aggregates MSCI .xlsm ' file.
IBESGA_fields = "ALNAME,AF0PE, ... ,ADVYLD"
IBESGA_fields = IBESGA_fields.split(",")
 
# Calling it with ' _str ' at the end to dissociate it from other objects and use it later:
IBESGA_tickers_str = "M1CD,M1CS, ... ,M3WU"
 
IBESGA_full_tickers = ["@:" + Region_to_DSWS_region_mnemonic_and_IBES_code("EAFE + Canada")[0] + i for i in IBESGA_tickers_str.split(",")]

        
        Copy
    

    	
            
# Defined our dsws data retrieval function
def ds_get_twice_data(tickers, fields, batch = 15, kind = 0):
    
    df = ds.get_data(tickers = tickers[0],
                     fields = fields[:batch],
                     kind = kind)
    _df = ds.get_data(tickers = tickers[0],
                      fields = fields[batch:],
                      kind = kind)
    df = df.append(_df, ignore_index = True)
    
    for i in tickers[1:]:
        _df1 = ds.get_data(tickers = i,
                          fields = fields[:batch],
                           kind = kind)
        _df2 = ds.get_data(tickers = i,
                          fields = fields[batch:],
                          kind = kind)
        _df = _df1.append(_df2, ignore_index = True)
        df = df.append(_df, ignore_index = True)
    
    return df

        
        Copy
    

    	
            
# Collect our data info data-frame ' df ':
df = ds_get_twice_data(tickers = IBESGA_full_tickers, fields = IBESGA_fields, batch = 15, kind = 0)
 
# Tidy our ' df ' and replace stings 'NA' with computationally recognisable nan values:
df = pd.DataFrame(
    index = df["Instrument"].unique(), columns = df["Datatype"].unique(),
    data = [list(df["Value"][df["Instrument"] == i]) for i in df["Instrument"].unique()]).replace('NA', np.nan, regex=True)
 
# Let's keep the tiker names:
df["Tickers"] = df.index
df.index = IBESGA_tickers_str.split(",")
df = df.T[ordered_mnemonic.split(",")].T
 
# Now re-index the data-frame:
_index = [list(xl_index.fillna(method='ffill').loc[i].values) for i in range(len(xl_index.index))]
df.index = pd.MultiIndex.from_tuples(_index)

        
        Copy
    

    	
            
# # re-column the data-frame:
 
# We need to add a column named 'ALNAME'. Since it's a column with 3 levels, it needs to be added thrice.
xl_columns2 = pd.concat([pd.DataFrame({'Column 1': {0: 'ALNAME'}, 'Column 2': {0: ''}, 'Column 3': {0: ''}}), xl_columns], ignore_index = True)
# We need to add a column named 'Tickers' similarlly.
xl_columns2 = pd.concat([xl_columns2, pd.DataFrame({'Column 1': {0: 'Tickers'}, 'Column 2': {0: ''}, 'Column 3': {0: ''}})], ignore_index = True)
# Collumns 'PEG-Ratio' 'Fiscal Year', 'FY0' to 'FY3' happen to be calculated, not pulled from DSWS, so we need to ignore them now and add them later
xl_columns3 = xl_columns2.drop([11,12,13,14]).reset_index(drop = True)
 
_columns = [list(xl_columns3.fillna(method='ffill').loc[i].values) for i in range(len(xl_columns3.index))]
df.columns = pd.MultiIndex.from_tuples(_columns)

        
        Copy
    

    	
            
# PEG-Ratio columns need to be calculated:
for i in range(4):
    df["PEG-Ratio", "Fiscal Year", f"FY{i}"] = df["PE-Ratio", "Fiscal Year", f"FY{i}"] / df["Earnings Growth", "Fiscal Year", f"FY{i}"]

        
        Copy
    

    	
            df["PEG-Ratio"]
        
        Copy
    

    	
            
# Create a list to append with our returned data-frame:
DSWS_IBES_GA = []
 
# Define our drop down specificities:
drop_down_IBES_GA_return = widgets.Dropdown(
    options = [""] + xl_regions["Region"].to_list(),
    value = "",
    disabled = False)
 
# Define our drop down specificities for predefined variables:
drop_down_predefined_variables = widgets.Dropdown(
    options = [""] + ["Yes", "No"],
    value = "",
    description = "Use predefined ordered_mnemonic, IBESGA_fields, xl_index and xl_columns2 variables?",
    disabled = False)

        
        Copy
    

    	
            
# Create the function to programmatically return the data-frame of interest
def Get_IBES_GA(area, # ' area ' example: "EAFE + Canada"
                loading_bar = True,
                display_df = True,
                append_DSWS_IBES_GA = True,
                ordered_mnemonic = "predefined",
                IBESGA_fields = "predefined",
                xl_index = "predefined",
                xl_columns2 = "predefined",
                max_row_col = True,
                export_to_excel = True):
    """Get_IBES_GA Version 1.0:
    If ' display_df ' is set to True, this function returns a dataframe of Datastream Web Service (DSWS) data of  Institutional Brokers' Estimate System (IBES) Global Aggregate earnings for country and regional sectors in an interactive way.
    If ' append_DSWS_IBES_GA ' is set to True, user need to have defined a Python list ' DSWS_IBES_GA ', it will then be appended.
    DSWS is Refinitiv's API retrieving data from Datastream to a Python Pandas data-frame. For information on DSWS, please visit 'https://developers.refinitiv.com/en/api-catalog/eikon/datastream-web-service'.
    
    
    Parameters:
    ----------
    
    area: str
        Region of choice's name (e.g.: 'EAFE-ex-UK' or 'EAFE + Canada').
        It has to be one of the elements in the following list: ['EAFE' 'EAFE + Canada' 'EAFE-ex-UK' 'EASEA (EAFE-ex-Japan)'  'EM (Emerging Markets)' 'EM Asia' 'EM Eastern Europe' 'EM Europe'  'EM Europe + Middle East' 'EM Europe, Middle East & Africa' 'EM Far East'  'EM Latin America' 'EMU (Euro)' 'EMU (US Dollar)' 'EMU + UK' 'Europe'  'Europe-ex-EMU' 'Europe-ex-UK' 'Far East' 'G7 Index'  'Kokusai (World-ex-Japan)' 'Nordic Countries' 'North America' 'Pacific'  'Pacific-ex-Japan' 'World' 'World-ex-Australia' 'World-ex-EMU'  'World-ex-Europe' 'World-ex-UK' 'World-ex-USA' 'Argentina' 'Australia'  'Australia' 'Austria' 'Belgium' 'Brazil' 'Canada' 'Chile' 'China'  'Colombia' 'Czech Republic' 'Denmark' 'Egypt' 'Finland' 'France'  'Germany' 'Greece' 'Hong Kong' 'Hungary' 'India' 'Indonesia' 'Ireland'  'Israel' 'Italy' 'Japan' 'Jordan' 'Korea' 'Malaysia' 'Mexico' 'Morocco'  'Netherlands' 'New Zealand' 'Norway' 'Pakistan' 'Peru' 'Philippines'  'Poland' 'Portugal' 'Russia' 'Singapore' 'South Africa' 'Spain'  'Sri Lanka' 'Sweden' 'Switzerland' 'Taiwan' 'Thailand' 'Turkey'  'United Kingdom' 'USA' 'Venezuela']
        It has no default values, but as per the Example bellow, you may want to use this function in conjuncture with ' ipywidgets.interact '.
    
    loading_bar: Boolean
        If set to True, then a loading bar will appear, keeping track of the dsws requests made - the steps that take longest in this function.
        Default: loading_bar = True
    
    display_df: Boolean
        If set to True, the resulting data-frame will be displayed.
        Default: display_df = True
    
    append_DSWS_IBES_GA: Boolean
        If set to True, user needs to have pre-created an empty Python lisy named ' DSWS_IBES_GA ' which will be populated with the data-frame returned.
        Default: append_DSWS_IBES_GA = True
    
    ordered_mnemonic: str or Pandas data-frame
        If set to "predefined", then a predefined Pandas data-frame of mnemonics is used.
        User may enter his/her own Pandas data-frame, but (s)he will need to change all of the following parameters accordingly: ordered_mnemonic, IBESGA_fields, xl_index, and xl_columns2.
        Default: ordered_mnemonic = "predefined"
    
    IBESGA_fields: str or Pandas data-frame
        If set to "predefined", then a predefined Pandas data-frame of Datastream IBESGA fields is used.
        User may enter his/her own Pandas data-frame, but (s)he will need to change all of the following parameters accordingly: ordered_mnemonic, IBESGA_fields, xl_index, and xl_columns2.
        Default: IBESGA_fields = "predefined"
    
    xl_index: str or Pandas data-frame
        If set to "predefined", then a predefined Pandas data-frame is used.
        User may enter his/her own Pandas data-frame, but (s)he will need to change all of the following parameters accordingly: ordered_mnemonic, IBESGA_fields, xl_index, and xl_columns2.
        Default: xl_index = "predefined"
    
    xl_columns2: str or Pandas data-frame
        If set to "predefined", then a predefined Pandas data-frame is used.
        User may enter his/her own Pandas data-frame, but (s)he will need to change all of the following parameters accordingly: ordered_mnemonic, IBESGA_fields, xl_index, and xl_columns2.
        Default: xl_columns2 = "predefined"
    
    max_row_col: Boolean
        If set to True, all of the resulted data-frame's columns and indices (rows) will be displayed.
        Default: max_row_col = True
    
    export_to_excel: Boolean
        If se to True, an excel workbook with one sheet of the returned data-frame will be generated where the python file is run.
        Default: True
    
    
    Dependencies:
    ----------
    
    pandas 1.1.4 as pd
    numpy 1.20.1 as np
    tqdm 4.48.2 via ' from tqdm.notebook import trange '
    DatastreamDSWS as dsws
    warnings
    
    Optional:
    
    ipywidgets 7.5.1
    
    
    Examples:
    ----------
    
    >>> import DatastreamDSWS as dsws
    >>> ds = dsws.Datastream(username = "insert dsws username here", password = "insert dsws password here")
    >>> from datetime import datetime
    >>> import warnings
    >>> from datetime import date
    >>> import pandas as pd
    >>> import numpy as np
    >>> import ipywidgets as widgets
    >>> from IPython.display import display
    >>> 
    >>> xl_regions = pd.DataFrame(data = {'Region': {0: 'EAFE', 1: 'EAFE + Canada',2: 'EAFE-ex-UK', ... ,30: '@:M1WLDXA'}})
    >>> xl_countries = pd.DataFrame(data = {'Region': {0: 'Argentina',1: 'Australia', ... ,50: '@:VEMSCIP'}})
    >>> xl_regions = xl_regions.append(xl_countries, ignore_index = True)
    >>> 
    >>> DSWS_IBES_GA = []
    >>> 
    >>> drop_down_IBES_GA_return = widgets.Dropdown(options = [""] + xl_regions["Region"].to_list(),value = "", disabled = False)
    >>> 
    >>> drop_down_predefined_variables = widgets.Dropdown(options = [""] + ["Yes", "No"], value = "", description = "Use predefined ordered_mnemonic, IBESGA_fields, xl_index and xl_columns2 variables?", disabled = False)
    >>> 
    >>> def Get_IBES_GA_predefined(area):
    >>>     _df = Get_IBES_GA(area)
    >>>     display(_df)
    >>> 
    >>> def IBES_GA_predefined_variables(predefined):
    >>>     if predefined == "Yes":
    >>>         widgets.interact(Get_IBES_GA_predefined, area = drop_down_IBES_GA_return);
    >>>     elif predefined == "No":
    >>>         widgets.interact(Get_IBES_GA, area = drop_down_IBES_GA_return);
    >>> 
    >>> widgets.interact(IBES_GA_predefined_variables, predefined = drop_down_predefined_variables);
    >>> 
    >>> # display(DSWS_IBES_GA[0])
    """
    
    # If the ' area ' is chosen in the following dropdown as an empty value (i.e.: ""), then we don't want to return anything:
    if area == "":
        pass
    else:
        
        # Set data-frame display conditions:
        if max_row_col == True:
            pd.set_option('display.max_row', None)
            pd.set_option('display.max_columns', None)
        
        # # Define reference Python objects:
        
        if ordered_mnemonic == "predefined": ordered_mnemonic = "M1E1,M2E2 ... ,M3MU,M3WU"
        else: ordered_mnemonic = ordered_mnemonic
        
        # The long string for ' IBESGA_fields ' (and subsequently ' IBESGA_tickers_str ') can be found in the ' Datastream IBES Global Aggregates MSCI .xlsm ' file.
        if IBESGA_fields == "predefined":
            IBESGA_fields = "ALNAME,AF0PE, ... ,ADVYLD"
            IBESGA_fields = IBESGA_fields.split(",")
            # Calling it with ' _str ' at the end to dissociate it from other objects and use it later.
            IBESGA_tickers_str = "M1CD,M1CS, ... ,M3WU"
            
            
            # # Define the list of tickers to pull data for from DSWS
            
            def Region_to_DSWS_region_mnemonic_and_IBES_code(region = None):
                """For description of this function, see 'How to collect Datastream IBES Global Aggregate Earnings Data' article on the Refinitiv/LSEG Developer Community Article Catalogue."""
                xl_regions = pd.DataFrame(data = {'Region': {0: 'EAFE', 1: 'EAFE + Canada' ... , 81: '@:VEMSCIP'}})
                if region not in xl_regions["Region"].to_list():
                    warnings.warn("Invalid ' region ' argument specified.")
                else:
                    return xl_regions[xl_regions["Region"] == region]["Mnemonic"].values[0], xl_regions[xl_regions["Region"] == region]["Code"].values[0]
            
            IBESGA_full_tickers = ["@:" + Region_to_DSWS_region_mnemonic_and_IBES_code(area)[0] + i for i in IBESGA_tickers_str.split(",")]
        
        else:
            IBESGA_full_tickers = IBESGA_fields
        
        # # Get coding:
        
        # Create 1st data-frame to subsequentially append
        df0 = ds.get_data(tickers = IBESGA_full_tickers[0],
                          fields = IBESGA_fields[:15],
                          kind = 0)
        df1 = df0.append(ds.get_data(tickers = IBESGA_full_tickers[0],
                                     fields = IBESGA_fields[15:],
                                     kind = 0),
                         ignore_index = True)
        
        ## Append our data-frame with each ticker
        
        # Leave the option for a loading bar:
        if loading_bar == True: J = trange(len(IBESGA_full_tickers), # colour = '#001EFF',
                                                         desc = 'requests', write_bytes = True)
        else: J = range(len(IBESGA_full_tickers[1:]))
        
        # Now request data:
        for i,j in zip(IBESGA_full_tickers, J):
            
            if i == IBESGA_full_tickers[0]:
                pass
            else:            
                # Create a placeholder data-frame ' _df ' to append onto our previously defined data-frame ' df1 '
                _df1 = ds.get_data(tickers = i,
                                  fields = IBESGA_fields[:15],
                                  kind = 0)
                _df2 = ds.get_data(tickers = i,
                                  fields = IBESGA_fields[15:],
                                  kind = 0)
                _df = _df1.append(_df2, ignore_index = True)
                df1 = df1.append(_df, ignore_index = True)
        
        # Rearrange our data-frame to have tickers as index and fields as columns
        df2 = pd.DataFrame(
            index = df1["Instrument"].unique(), columns = df1["Datatype"].unique(),
            data = [list(df1["Value"][df1["Instrument"] == i]) for i in df1["Instrument"].unique()])
        # Replace str 'NA' with numpy nan values
        df2 = df2.replace(['NA'], np.nan)
        for i in range(4):
            df2[f"PEGRatio{i}"] = df2[f"AF{i}PE"] / df2[f"AF{i}GRO"]
        # Need to Rearrange the columns in order
        df2 = df2.reindex(df2.columns[1:11].tolist() + [f"PEGRatio{i}" for i in range(4)] + df2.columns[11:-4].tolist(), axis=1)
        # We don't want to loose the data of which row is for which ticker with the next few lines, so let's create a column with that information
        df2["Tickers"] = df2.index
        # Rename our data-frame's index to fit a standard easier to work with
        df2.index = IBESGA_tickers_str.split(",")
        # Rearrange the order of the rows
        df3 = df2.T[ordered_mnemonic.split(",")].T
        
        # We will use the following ' xl_index ' data-frame to index our returned data-frame
        if xl_index == "predefined":
            xl_index = pd.DataFrame(data = {'Category': {0: 'Energy', 1: 'Energy', ... , 89: 'M3WU'}})
        else:
            xl_index = xl_index
        
        # Now re-index the data-frame:
        _columns = [list(xl_index.fillna(method='ffill').loc[i].values) for i in range(len(xl_index.index))]
        df3.index = pd.MultiIndex.from_tuples(_columns)
        
        ## Now we need to rename the columns:
        
        # If you have the accompanying excel workbook, you can run: ' xl_columns2 = pd.read_excel("Reference data.xls", sheet_name = "Column Names 2").fillna(method='ffill') ''
        if xl_columns2 == "predefined":
            xl_columns2 = pd.DataFrame(data = {'Column 1': {0: 'PE-Ratio', 1: 'PE-Ratio', ... 34: 'Tickers'}})
        else:
            xl_columns2 = xl_columns2
        
        _columns = [list(xl_columns2.loc[i].values) for i in range(len(xl_columns2.index))]
        df3.columns = pd.MultiIndex.from_tuples(_columns)
        
        # Append out previously defined list with the dataframe created
        try:
            if append_DSWS_IBES_GA == True: DSWS_IBES_GA.append(df3)
        except:
            warnings.warn("If ' append_DSWS_IBES_GA ' is set to True, user needs to define an empty Python list ' append_DSWS_IBES_GA ' before running ' Get_IBES_GA '.")
        
        # Export to excel sheet if asked:
        if export_to_excel == True:
            df3.to_excel(excel_writer = f"DSWS_IBES_GA_{area}.xlsx",
                         sheet_name = datetime.now().strftime("%Y.%m.%d_%Hh.%Mm"))
        
        # Just to check if this function worked expectedly, let's return the number of rows in the outputed data-frame
        if display_df == True: return df3

        
        Copy
    

    	
            
def Get_IBES_GA_predefined(area):
    _df = Get_IBES_GA(area)
    display(_df)
    
def IBES_GA_predefined_variables(predefined):
    if predefined == "Yes":
        widgets.interact(Get_IBES_GA_predefined, area = drop_down_IBES_GA_return);
    elif predefined == "No":
        widgets.interact(Get_IBES_GA, area = drop_down_IBES_GA_return);

        
        Copy
    

    	
            widgets.interact(IBES_GA_predefined_variables, predefined = drop_down_predefined_variables);
        
        Copy