Foreshadowing Analyst Revisions using LinkUp's Job Postings Data in Refinitiv Quantitative Analytics

    	
            !pip install matplotlib pyodbc sqlalchemy
        
        Copy
    

    	
            
import datetime
import matplotlib
import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import pandas as pd
import pyodbc
import sqlalchemy
from sqlalchemy import create_engine # ' create_engine ' is a sub library that we can use to connect to our server.
 
for w,z in zip(["matplotlib", "pandas", "sqlalchemy"], [matplotlib, pd, sqlalchemy]): # ' datetime' and ' pyodbc ' do not have '.__version__'s
    print("The Python library " + w + " used here is version " + z.__version__)
 
pd.options.display.max_columns = 0 # This line stops Pandas from truncating columns.

        
        Copy
    

    	
            
server = 'c5zjhr0qacpdeus2mi4a01.public.dabc3424290b.database.windows.net' # This is the Refinitiv Quantitative Analytics trial server. It's possible you may be assigned a different server.
port = '3342' # This is the port that was assigned to you, think of it as an extension to the server address provided.
database = 'qai' # Within the Refinitiv Quantitative Analytics database to which you have access, there are many databases - we are interested in the 'qai' one.
username = '1a.john.smith'
password = 'enterpassword'

        
        Copy
    

    	
            
# Refinitiv Quantitative Analytics requires an encrypted connection, so the "Encryption=Yes" part is very important
(connection_str) = ("mssql+pyodbc://" + username +
                    ":" + password + "@" + server +
                    ":" + port + "/" + database +
                    "?driver=sql server?Encryption=Yes")
connection_str

        
        Copy
    

    	
            
mssql_eng = create_engine(connection_str)
mssql_eng.connect()

        
        Copy
    

    	
            
orgpermid = 4295894740 # BP's Organization PermID
period_end = '2020-12-31'
period_type = 4 # Annual
measure = 164 # Measure code for Oil Production Per Day
measure_name = 'Oil Production Per Day' # For human-friendly chart labels
plot_date_start = '2020-01-01'
plot_date_end = '2021-01-26'
lookback_days = 30 # The change window for the Analyst Awareness metric

        
        Copy
    

    	
            
def lookup_companyid(orgpermid, plot_date_start):
    
    # Let's setup our SQL query in the string ' companyid_sql ':
    companyid_sql = f"""
    SELECT CompanyID, CompanyName
    FROM qai2.dbo.LinkUpCmpInfo
    WHERE LUOrgPermID='{orgpermid}'
    AND StartDate<='{plot_date_start}'
    AND (EndDate>='{plot_date_start}' OR EndDate IS NULL)
    """
    
    # Now let's collect our ' companyid_sql ' query with our authenticated connection ' mssql_eng ':
    companyid_df = pd.read_sql(sql = companyid_sql,
                               con = mssql_eng)
    
    # Now we can retrieve the information we are looking for from the reverted data:
    companyid = companyid_df.iloc[0]['CompanyID']
    company_name = companyid_df.iloc[0]['CompanyName']
 
    return (companyid, company_name)

        
        Copy
    

    	
            
# Lookup LinkUp CompanyID for PermID
(companyid, company_name) = lookup_companyid(orgpermid, plot_date_start)
(companyid, company_name)

        
        Copy
    

    	
            
def grab_linkup_data(companyid):
    
    # Let's setup our SQL query in the string ' linkup_sql ':
    linkup_sql = f"""
    SELECT *
    FROM qai2.dbo.LinkUpCmpAnalytics
    WHERE CompanyID={companyid}
    """
    
    # Now let's collect our ' linkup_sql ' query with our authenticated connection ' mssql_eng ':
    linkup_df = pd.read_sql(linkup_sql, mssql_eng)
    
    # Strongly type the date for charting in pandas later on
    linkup_df['Date_'] = pd.to_datetime(linkup_df['Date_']).dt.date
    
    return linkup_df

        
        Copy
    

    	
            linkup_df = grab_linkup_data(companyid)
        
        Copy
    

    	
            
# We're going to focus on UniqueActiveJobCount in later code
linkup_df.tail()

        
        Copy
    

    	
            
def lookup_estimateid(orgpermid, plot_date_start):
    
    # Let's setup our SQL query in the string ' perm_sql ':
    perm_sql = f"""
    SELECT EstPermID
    FROM qai.dbo.TREInfo
    WHERE OrgPermID={orgpermid}
    AND EffectiveDate<='{plot_date_start}'
    AND (ExpireDate>='{plot_date_start}' OR ExpireDate IS NULL)
    """
    
    # Now let's collect our ' perm_sql ' query with our authenticated connection ' mssql_eng ':
    perm_df = pd.read_sql(perm_sql, mssql_eng)
    
    # Now we can retrieve the information we are looking for from the reverted data:
    estimateid = perm_df.iloc[0]['EstPermID']
    
    return estimateid

        
        Copy
    

    	
            
# Lookup EstimateID for LinkUp Stock_Ticker
estimateid = lookup_estimateid(orgpermid, plot_date_start)

        
        Copy
    

    	
            estimateid
        
        Copy
    

    	
            
def grab_ibes_data(estimateid, measure, period_end, period_type):
    
    # Let's setup our SQL query in the string ' ibes_sql ':
    ibes_sql = f"""
    WITH intraday
    AS
    (
        -- Focus on one Security / Measure / PeriodEndDate / Periodicity
        SELECT *
        FROM qai.dbo.TRESumPer
        WHERE EstPermID = {estimateid}
        AND Measure = {measure} 
        AND PerEndDate = '{period_end}'
        AND PerType = {period_type}
    ),
    day_ends
    AS
    (
        -- Dailyize the intraday time series by picking the last estimate of the day
        SELECT CONVERT(DATE, EffectiveDate) AS AsOfDate, MAX(EffectiveDate) AS LastTime
        FROM intraday
        GROUP BY CONVERT(DATE, EffectiveDate) 
    )
    SELECT d.AsOfDate, i.*
    FROM intraday i
    INNER JOIN day_ends d
    ON i.EffectiveDate = d.LastTime
    ORDER BY i.EffectiveDate
    """
    
    # Now let's collect our ' ibes_sql ' query with our authenticated connection ' mssql_eng ':
    ibes_df = pd.read_sql(ibes_sql, mssql_eng)
    
    # Strongly type the date for charting in pandas later on
    ibes_df['AsOfDate'] = pd.to_datetime(ibes_df['AsOfDate']).dt.date
    
    return ibes_df

        
        Copy
    

    	
            ibes_df = grab_ibes_data(estimateid, measure, period_end, period_type)
        
        Copy
    

    	
            ibes_df.tail()
        
        Copy
    

    	
            
def grab_se_data(estimateid, measure, period_end, period_type):
    
    # Let's setup our SQL query in the string ' se_sql ':
    se_sql = f"""
    WITH intraday
    AS
    (
        -- Focus on one Security / Measure / PeriodEndDate / Periodicity
        SELECT *
        FROM qai.dbo.TRESmartSumPer
        WHERE EstPermID = {estimateid}
        AND Measure = {measure} 
        AND PerEndDate = '{period_end}'
        AND PerType = {period_type}
    ),
    day_ends
    AS
    (
        -- Dailyize the intraday time series by picking the last estimate of the day
        SELECT CONVERT(DATE, EffectiveDate) AS AsOfDate, MAX(EffectiveDate) AS LastTime
        FROM intraday
        GROUP BY CONVERT(DATE, EffectiveDate) 
    )
    SELECT d.AsOfDate, i.*
    FROM intraday i
    INNER JOIN day_ends d
    ON i.EffectiveDate = d.LastTime
    ORDER BY i.EffectiveDate
    """
    
    # Now let's collect our ' se_sql ' query with our authenticated connection ' mssql_eng ':
    se_df = pd.read_sql(se_sql, mssql_eng)
    
    # Strongly type the date for charting in pandas later on
    se_df['AsOfDate'] = pd.to_datetime(se_df['AsOfDate']).dt.date
    
    return se_df

        
        Copy
    

    	
            se_df = grab_se_data(estimateid, measure, period_end, period_type)
        
        Copy
    

    	
            se_df.tail()
        
        Copy
    

    	
            
def chart_jobs(company_name, period_end, linkup_df, ibes_df, se_df, measure_name, plot_date_start, plot_date_end):
    # Focus on recent times only
    startdate = datetime.datetime.strptime(plot_date_start,'%Y-%m-%d').date()
    enddate = datetime.datetime.strptime(plot_date_end,'%Y-%m-%d').date()
    single_active_jobs = linkup_df[(linkup_df['Date_']>=startdate) & (linkup_df['Date_']<=enddate)]
    single_smart_est = se_df[(se_df['AsOfDate']>=startdate) & (se_df['AsOfDate']<=enddate)]
    single_ibes_est = ibes_df[(ibes_df['AsOfDate']>=startdate) & (ibes_df['AsOfDate']<=enddate)]
 
    chart_title = company_name + ", PeriodEnd:" + period_end
    
    # Plot I/B/E/S Estimates
    estimates_ax = single_ibes_est.plot(x='AsOfDate', y='DefMeanEst', label=measure_name+' (Mean Estimate)', drawstyle='steps-post', color='purple', figsize=(15,10), title=chart_title)
    estimates_ax.set_ylabel('Estimated ' + measure_name)
    
    # Plot SmartEstimates
    smart_est_ax = single_smart_est.plot(ax=estimates_ax, x='AsOfDate', y='DefSmartEst', label=measure_name+ ' (SmartEstimate)', drawstyle='steps-post', color='orange', figsize=(15,10))
            
    # Plot LinkUp
    active_jobs_ax = single_active_jobs.plot(ax=estimates_ax, x='Date_', y='UniqueActiveJobCount', label="Active Jobs", drawstyle='steps-post', color='yellow', figsize=(15,10), secondary_y=True)
    active_jobs_ax.set_ylabel('Active Job Postings')
    
    # Some final formatting
    estimates_ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %d %y'))
    estimates_ax.set_xlabel('As Of Date')
    estimates_ax.set_facecolor('Black')

        
        Copy
    

    	
            chart_jobs(company_name, period_end, linkup_df, ibes_df, se_df, measure_name, plot_date_start, plot_date_end)
        
        Copy
    

    	
            
def chart_analyst_awareness(lookback_days, company_name, period_end, linkup_df, ibes_df, measure_name, plot_date_start, plot_date_end):
    # Figure out the past date that will be used for trailing job calcuation
    linkup_df['Date_past'] = linkup_df['Date_'] - pd.to_timedelta(lookback_days, unit='d')
 
    # Self-join the dataframes, offset by the trailing window
    linkup_trailing_df = linkup_df.merge(linkup_df, left_on='Date_past', right_on='Date_', suffixes=('_present', '_past'))
 
    # Calculate the change over the trailing window
    linkup_trailing_df['trailing_job_chg'] = (linkup_trailing_df['UniqueActiveJobCount_present'] - linkup_trailing_df['UniqueActiveJobCount_past']) / linkup_trailing_df['UniqueActiveJobCount_past']
 
    # Create a daily time series for all the days you want to populate
    daily_dates = pd.DataFrame({'EffectiveDate':pd.date_range(ibes_df['EffectiveDate'].min(), plot_date_end, freq='D')})
 
    # Create a date-type from datetime, for easier merging
    daily_dates['AsOfDate'] = pd.to_datetime(daily_dates['EffectiveDate']).dt.date
    ibes_df['AsOfDate'] = pd.to_datetime(ibes_df['EffectiveDate']).dt.date
 
    # Daily-ized I/B/E/S Estimates, carrying forward the estimates
    daily_ibes_df = daily_dates.merge(ibes_df, how='left', on='AsOfDate').ffill()
 
    # Figure out the past date that will be used for trailing job calcuation
    daily_ibes_df['AsOfDate_past'] = daily_ibes_df['AsOfDate'] - pd.to_timedelta(lookback_days, unit='d')
 
    # Self-join the dataframes, offset by the trailing window
    ibes_trailing_df = daily_ibes_df.merge(daily_ibes_df, left_on='AsOfDate_past', right_on='AsOfDate', suffixes=('_present', '_past'))
 
    # Calculate the change over the trailing window
    ibes_trailing_df['trailing_est_chg'] = (ibes_trailing_df['DefMeanEst_present'] - ibes_trailing_df['DefMeanEst_past']) / ibes_trailing_df['DefMeanEst_past']
 
    # Now combine the LinkUp Jobs Chgs with the I/B/E/S Estimates changes
    analyst_awarness_df = linkup_trailing_df.merge(ibes_trailing_df, left_on='Date__present', right_on='AsOfDate_present')
    
    # Finally, create the Analyst Awareness metric
    analyst_awarness_df['AnalystAwareness'] = analyst_awarness_df['trailing_job_chg'] - analyst_awarness_df['trailing_est_chg']
    
    # Focus on recent times only
    startdate = datetime.datetime.strptime(plot_date_start,'%Y-%m-%d').date()
    enddate = datetime.datetime.strptime(plot_date_end,'%Y-%m-%d').date()
    single_analyst_awareness = analyst_awarness_df[(analyst_awarness_df['Date__present']>=startdate) & (analyst_awarness_df['Date__present']<=enddate)]
 
    chart_title = company_name + ", PeriodEnd:" + period_end
 
    # Plot Analyst Awareness
    aa_ax = single_analyst_awareness.plot(x='Date__present', y='AnalystAwareness', label='AnalystAwareness', drawstyle='steps-post', color='purple', figsize=(15,10), title=chart_title)
    aa_ax.set_ylabel('Analyst Awareness for Changes in ' + measure_name)
 
 
    # Some final formatting
    aa_ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %d %y'))
    aa_ax.set_xlabel('As Of Date')
    aa_ax.set_facecolor('Black')

        
        Copy
    

    	
            chart_analyst_awareness(lookback_days, company_name, period_end, linkup_df, ibes_df, measure_name, plot_date_start, plot_date_end)
        
        Copy
    

    	
            
def chart_jobs_complete(orgpermid, measure, measure_name, period_end, period_type, plot_date_start, plot_date_end, lookback_days):
    (companyid, company_name) = lookup_companyid(orgpermid, plot_date_start)
    linkup_df = grab_linkup_data(companyid)
    estimateid = lookup_estimateid(orgpermid, plot_date_start)
    ibes_df = grab_ibes_data(estimateid, measure, period_end, period_type)
    se_df = grab_se_data(estimateid, measure, period_end, period_type)
    chart_jobs(company_name, period_end, linkup_df, ibes_df, se_df, measure_name, plot_date_start, plot_date_end)
    chart_analyst_awareness(lookback_days, company_name, period_end, linkup_df, ibes_df, measure_name, plot_date_start, plot_date_end)

        
        Copy
    

    	
            chart_jobs_complete(orgpermid, 9, "EPS", period_end, period_type, plot_date_start, plot_date_end, lookback_days)
        
        Copy
    

    	
            chart_jobs_complete(4295916199, 20, "Revenue", '2020-12-31', period_type, plot_date_start, plot_date_end, lookback_days)
        
        Copy