Analyzing Relationships Between ESG Data and Stock Performance

Reading Time: 41 minutes

Companies accrue ESG metrics that theoretically measure their performance on things like good governance, environmental stewardship, and fair employee treatment. I wanted to look for relationships between these metrics and company stock performance. Here’s the cumulative result so far!

Millenials and Investment: an Ongoing Exploration

In case you haven’t heard, millenials are killing everything from diamonds to department stores to designer crap to grocery chains.

Why? Sure, the recession had an impact. But also, millenials pay more attention to ethics than many multinational corporations bargain for. They cite the blood diamond trade as a major reason to spring for non-traditional engagement rings. They opt for grocery providers that can tell them where their food is coming from and under what conditions it was produced. They’re ditching the fast fashion industry for higher-priced items purchased secondhand on sites like Poshmark and ThreadUp.

For millenials, investing and values go hand in hand

And as millenials reach the age where they might accrue some savings, it makes sense that they would care about where that is going, too. In addition to millenial attendance at the NoDAPL protests, we saw thousands of millenials divest from Western Union, Bank of America, and other banks that loaned money to the project. Maybe megacorps won’t change their tunes because a few thousand people stood in a field to get mowed down by water cannons, but they’re more likely to sit up and listen when those same people take their hard-earned doll hairs to another playhouse.

So we see that millenials are surveying their options to spend and save according to their values. What about investing? Any personal finance 101 that isn’t taught by a financial advisor will recommend a low cost index as the place to stick extra money so it can grow with the market. Most index funds, including the most recommended one (Vanguard), decide their investments via index-matching: matching their holdings to the S&P500 by market cap, with no other variables. Thing is, plenty of investors are expressing interest in taking ethical considerations into account. Some portfolios do this by blanket blocking investments in certain industries like tobacco or porn. Other more advanced optsions, like Betterment’s AutoSRI portfolio, use actual ESG data to determine where they invest the money. There isn’t (yet) a fully customizable option to allow folks to automatically invest their funds based on a checklist of their individual values. For a while, I’ve thought about building a toy version of what that might look like.

When I talk about the idea with friends and relatives, I get the following objection: ‘What about the returns?’ Touche. Nobody wants to lose out on their potential earnings. At first, I figured I’d build a tolerance into the system that allowed investors to say ‘These are my values, but please don’t invest in a way that will trail general market performance by more than x percent.’ The algorithm would then predict stock performance for each company, somehow blend that with ESG rating, and come up with a combined weight for divvying up investment money.

Before I build that, though, I need to test the assumption that high ESG ratings do correlate negatively with returns. If they don’t, there’s no need for the tolerance measure in the first place.

I’m not the first person to run correlations along these lines. Dorfleitner, Utz, and Wimmer published a paper on this just last year. Their analysis suggests that higher corporate social responsibility ratings increase returns over a long period of time (“long” being a 12 year period from 2002-2014). They even identify three specific areas that correlate with higher than average returns: emission and resource reduction, workforce, and society. So in my exploration, I’ll dig into some specific CSR breakdowns with the data I have on S&P 500 companies.

In [1]:
import pandas as pd
import numpy as np

Correlating KLD ESG Ratings to Stock Performance, 1990-2005

Let’s determine whether we notice any correlation between companies’ environmental, social, and governmental ratings and their stock performance.

First, we pull in the ESG data.

These come from KLD and are now distributed by MSGI. I pulled them from an academic database. Don’t rerun this notebook because I didn’t push the actual data to Github, on account of it is large and on account of both data providers ask corporations to pay for the data. So I’m not going to undermine that.

In [2]:
y91 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1991 HistoricalSpreadsheet_STATS.xls')
y92 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1992 HistoricalSpreadsheet_STATS.xls')
y93 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1993 HistoricalSpreadsheet_STATS.xls')
y94 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1994 HistoricalSpreadsheet_STATS.xls')
y95 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1995 HistoricalSpreadsheet_STATS.xls')
y96 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1996 HistoricalSpreadsheet_STATS.xls')
y97 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1997 HistoricalSpreadsheet_STATS.xls')
y98 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1998 HistoricalSpreadsheet_STATS.xls')
y99 = pd.read_excel('../stockproject/12231046.1990-1999.stats/1999 HistoricalSpreadsheet_STATS.xls')

nineties = [y91, y92, y93, y94, y95, y96, y97, y98, y99]
In [3]:
y00 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2000 HistoricalSpreadsheet_STATS.xls')
y01 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2001 HistoricalSpreadsheet_STATS.xls')
y02 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2002 HistoricalSpreadsheet_STATS.xls')
y03 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2003 HistoricalSpreadsheet_STATS.xls')
y04 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2004 HistoricalSpreadsheet_STATS.xls')
y05 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2005 Historical Spreadsheet_STATS.xls') #wth KLD
y06 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2006 Historical Spreadsheet_STATS.xls')
y07 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2007 HistoricalSpreadsheet_STATS.xls')
y08 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2008 HistoricalSpreadsheet_STATS.xls')
y09 = pd.read_excel('../stockproject/12231046.2000-2009.stats/2009 HistoricalSpreadsheet_STATS.xls')

two_thousands = [y00, y01, y02, y03, y04]

I wanted to run 1990-2009, but evidently starting in 2005 these spreadsheets no longer represent whether a company was in the S&P500 in the same way. That’s okay: we can do this for a fifteen-year period wrangle in more data later if we would like to see a longer period of time.

Now let’s pull out the companies that belong to the S&P 500. We’ll begin by examining their ESG scores on four metrics: employment policy strengths, employment policy concerns, environmental impact strengths, and environmental impact concerns. These companies get a score of zero (0) or one (1) each year on each of several sub-metrics. For example, employment policy strengths include specific scores for workplace safety, compensation, union management, et cetera.

We’re going to sum up all of the sub-metrics for each metric per company, and then we’re going to sum that company’s total scores in that metric across our fifteen year time span. We’ll end up with a company score of cumulative strengths and concerns in employment and environmental practices over the course of the fifteen years.

In [4]:
twenty_years = nineties + two_thousands

def filter_500(df):
    return df[df['SP500'] == True]

sp500_90s = []    
for data in twenty_years:

def aggregate_columns_for(category, dataframe):
    relevant_columns = [column_name for column_name in dataframe.columns.values if column_name.startswith(category)]
    return dataframe[relevant_columns].sum(axis=1)

aggregate_data = pd.concat(sp500_90s)
aggregate_data['emp_str_sum'] = aggregate_columns_for('EMP-str', aggregate_data)
aggregate_data['emp_con_sum'] = aggregate_columns_for('EMP-con', aggregate_data)
aggregate_data['env_str_sum'] = aggregate_columns_for('ENV-str', aggregate_data)
aggregate_data['env_con_sum'] = aggregate_columns_for('ENV-con', aggregate_data)

aggregate_data['alc_con_sum'] = aggregate_columns_for('ALC-con', aggregate_data)

aggregate_data['cgov_str_sum'] = aggregate_columns_for('CGOV-str', aggregate_data)
aggregate_data['cgov_con_sum'] = aggregate_columns_for('CGOV-con', aggregate_data)

aggregate_data['com_str_sum'] = aggregate_columns_for('COM-str', aggregate_data)
aggregate_data['com_con_sum'] = aggregate_columns_for('COM-con', aggregate_data)

aggregate_data['div_str_sum'] = aggregate_columns_for('DIV-str', aggregate_data)
aggregate_data['div_con_sum'] = aggregate_columns_for('DIV-con', aggregate_data)

array(['ALC-con-#', 'ALC-con-A', 'ALC-con-X', 'BMS', 'CGOV-con-#',
       'CGOV-con-B', 'CGOV-con-F', 'CGOV-con-G', 'CGOV-con-H',
       'CGOV-con-I', 'CGOV-con-X', 'CGOV-str-#', 'CGOV-str-A',
       'CGOV-str-C', 'CGOV-str-D', 'CGOV-str-E', 'CGOV-str-X', 'COM-con-#',
       'COM-con-A', 'COM-con-B', 'COM-con-D', 'COM-con-X', 'COM-str-#',
       'COM-str-A', 'COM-str-B', 'COM-str-C', 'COM-str-D', 'COM-str-F',
       'COM-str-G', 'COM-str-X', 'CUSIP', 'Com-con-A', 'CompanyName',
       'DIV-con-#', 'DIV-con-A', 'DIV-con-B', 'DIV-con-X', 'DIV-str-#',
       'DIV-str-A', 'DIV-str-B', 'DIV-str-C', 'DIV-str-D', 'DIV-str-E',
       'DIV-str-F', 'DIV-str-G', 'DIV-str-X', 'DS400', 'EMP-con-#',
       'EMP-con-A', 'EMP-con-B', 'EMP-con-C', 'EMP-con-D', 'EMP-con-X',
       'EMP-str-#', 'EMP-str-A', 'EMP-str-B', 'EMP-str-C', 'EMP-str-D',
       'EMP-str-F', 'EMP-str-G', 'EMP-str-X', 'ENV-con-#', 'ENV-con-A',
       'ENV-con-B', 'ENV-con-C', 'ENV-con-D', 'ENV-con-E', 'ENV-con-F',
       'ENV-con-X', 'ENV-str-#', 'ENV-str-A', 'ENV-str-B', 'ENV-str-C',
       'ENV-str-D', 'ENV-str-F', 'ENV-str-G', 'ENV-str-X', 'FIR-con-#',
       'FIR-con-A', 'GAM-con-#', 'GAM-con-A', 'GAM-con-X', 'HUM-con-#',
       'HUM-con-A', 'HUM-con-B', 'HUM-con-C', 'HUM-con-D', 'HUM-con-F',
       'HUM-con-G', 'HUM-con-X', 'HUM-str-#', 'HUM-str-A', 'HUM-str-D',
       'HUM-str-G', 'HUM-str-X', 'LCS', 'MIL-con-#', 'MIL-con-A',
       'MIL-con-B', 'MIL-con-C', 'MIL-con-X', 'NUC-con-#', 'NUC-con-A',
       'NUC-con-C', 'NUC-con-D', 'NUC-con-X', 'PRO-con-#', 'PRO-con-A',
       'PRO-con-D', 'PRO-con-E', 'PRO-con-X', 'PRO-str-#', 'PRO-str-A',
       'PRO-str-B', 'PRO-str-C', 'PRO-str-X', 'Russell1000', 'Russell2000',
       'SP500', 'TOB-con-#', 'TOB-con-A', 'TOB-con-X', 'Ticker',
       'emp_str_sum', 'emp_con_sum', 'env_str_sum', 'env_con_sum',
       'alc_con_sum', 'cgov_str_sum', 'cgov_con_sum', 'com_str_sum',
       'com_con_sum', 'div_str_sum', 'div_con_sum'], dtype=object)

OK, so here’s our data. Let’s take a look at this data and make sure we’re getting what we want: a sum of the ESG scores on a company-by-company basis.

In [5]:
def sum_scores_for(dataframe, esg_marker):
    grouping = dataframe.groupby(['Ticker'])[esg_marker].sum()
    return pd.DataFrame({esg_marker : grouping}).reset_index()

esg_markers = [

def aggregate_sums_for(esg_markers):
    esg_marker_data = sum_scores_for(aggregate_data, esg_markers[0])

    for esg_marker in esg_markers[1::]:
        esg_marker_data[esg_marker] = sum_scores_for(aggregate_data, esg_marker)[esg_marker]
    return esg_marker_data
esg_marker_data = aggregate_sums_for(esg_markers)

Ah, these sums look like what we would expect to see!

In [6]:
esg_marker_data.sort_values(by=['emp_con_sum'], ascending=False).head()
Ticker emp_str_sum emp_con_sum env_str_sum env_con_sum alc_con_sum cgov_str_sum cgov_con_sum com_str_sum com_con_sum div_str_sum div_con_sum
150 CAT 32 50 2 26 0 0 13 0 6 0 4
847 UNP 8 38 4 56 0 0 12 0 8 14 0
841 UIS 0 38 8 0 0 0 6 2 18 28 1
501 LPX 2 38 16 52 0 0 7 0 0 8 0
649 PD 0 35 0 60 0 0 4 0 16 20 2

Second, we pull in stock performance data.

This data contains stock returns by quarter for S&P500 companies dating back to 1979. We’ll pull the columns for the ’90s for now.

In [7]:
price_data = pd.read_excel('../stockproject/Cleaned_Researcher_Dataset.xlsx')
In [8]:
new_header = price_data.iloc[0] #grab the first row for the header
content = price_data[1:] #take the data less the header row
content.columns = new_header #set the header row as the df header

tickers = content.iloc[:,0:2]
tickers.columns = list(new_header)[0:2]

dates = content.iloc[:,45:106]
dates.columns = list(new_header)[45:106]

result = pd.concat([tickers, dates], axis=1)
Ticker Company Name 1989-12-31 00:00:00 1990-03-31 00:00:00 1990-06-30 00:00:00 1990-09-30 00:00:00 1990-12-31 00:00:00 1991-03-31 00:00:00 1991-06-30 00:00:00 1991-09-30 00:00:00 2002-09-30 00:00:00 2002-12-31 00:00:00 2003-03-31 00:00:00 2003-06-30 00:00:00 2003-09-30 00:00:00 2003-12-31 00:00:00 2004-03-31 00:00:00 2004-06-30 00:00:00 2004-09-30 00:00:00 2004-12-31 00:00:00
1 A Agilent Technologies Inc. NaN NaN NaN NaN NaN NaN NaN NaN 13.06 17.96 13.15 19.55 22.11 29.24 31.63 29.28 21.57 24.1
2 AA Alcoa Inc 75 64.75 63.75 62.63 57.63 65.5 67.5 63.88 19.3 22.78 19.38 25.5 26.16 38 34.69 33.03 33.59 31.42
3 AAL American Airlines Group NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 AAL (Alex) Alexander & Alexander 30.63 26.75 24.38 17.88 23.13 26.25 22.5 20.88 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
5 AAP Advance Auto Parts Inc NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

5 rows × 63 columns

Third, we translate these stock prices into returns.

I want a metric that I can use to compare all the companies that a) belonged to the S&P 500 and b) earned some kind of KLD scores during the 1990-2005 period. Some companies only belong to the S&P500 for a subset of the years in question. We want a metric that will not penalize companies based on having spent less time in the S&P500, so a cumulative score won’t work for us. I decided to calculate quarterly returns based on the stock prices. This fairly compares each company’s stock performance during the period that an index-matching ETF would have held it, however long or short that was.

This is also nice because our mean function will only consider, for each company, those cells that have a number. So we don’t have to do as much data skullduggery to get the equation functions to spit out something meaningful.

In [9]:
def quarter_return(start, end):
    if start == 0 or end == 0:
        return 0
    return end / start

#WARNING: This has to go column by column because the sequence in time matters.
#Such an iterative operation takes longer than async-per-column pandas operations.
#Expect this block of code to take several seconds to run.
raw_stock_prices = result
returns_df = raw_stock_prices[['Ticker', 'Company Name']]

#We cannot easily index the columsn by name
#because the column names are datetimes rather than strings,
#so we use column index instead.
for column_name in raw_stock_prices.iloc[:,2:]:
    loc = raw_stock_prices.columns.get_loc(column_name)
    this_column = raw_stock_prices.iloc[:,loc]
    next_col = loc + 1
        next_column = raw_stock_prices.iloc[:, next_col]

        temp_df = pd.concat([this_column, next_column], axis=1)
        temp_df.columns = ['a', 'b']
        returns_df['quarter_starting_' + column_name.strftime('%m/%d/%Y') + '_roi'] = (
        temp_df.apply(lambda row: quarter_return(row['a'], row['b']), axis=1))
        print('End of dataframe reached')

//anaconda/lib/python3.6/site-packages/ipykernel/ SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation:
End of dataframe reached
Ticker Company Name quarter_starting_12/31/1989_roi quarter_starting_03/31/1990_roi quarter_starting_06/30/1990_roi quarter_starting_09/30/1990_roi quarter_starting_12/31/1990_roi quarter_starting_03/31/1991_roi quarter_starting_06/30/1991_roi quarter_starting_09/30/1991_roi quarter_starting_06/30/2002_roi quarter_starting_09/30/2002_roi quarter_starting_12/31/2002_roi quarter_starting_03/31/2003_roi quarter_starting_06/30/2003_roi quarter_starting_09/30/2003_roi quarter_starting_12/31/2003_roi quarter_starting_03/31/2004_roi quarter_starting_06/30/2004_roi quarter_starting_09/30/2004_roi
1 A Agilent Technologies Inc. NaN NaN NaN NaN NaN NaN NaN NaN 0.552220 1.375191 0.732183 1.486692 1.130946 1.322479 1.081737 0.925703 0.736680 1.117293
2 AA Alcoa Inc 0.863333 0.984556 0.982431 0.920166 1.136561 1.030534 0.94637 1.007827 0.582202 1.180311 0.850746 1.315789 1.025882 1.452599 0.912895 0.952148 1.016954 0.935397
3 AAL American Airlines Group NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 AAL (Alex) Alexander & Alexander 0.873327 0.911402 0.733388 1.293624 1.134890 0.857143 0.92800 0.981801 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
5 AAP Advance Auto Parts Inc NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

5 rows × 62 columns

Fourth, we combine the data into one dataframe.

We find all the companies for which we have both stock price data and ESG data, and we put the information together.

In [10]:
tickers = list(esg_marker_data["Ticker"]) #get all the company tickers for which we have esg data
prices_for_esg_companies = returns_df[returns_df["Ticker"].isin(tickers)] #get the stock data from companies in that list
Ticker emp_str_sum emp_con_sum env_str_sum env_con_sum alc_con_sum cgov_str_sum cgov_con_sum com_str_sum com_con_sum div_str_sum div_con_sum
0 A 16 16 0 0 0 3 5 8 0 19 0
1 AA 48 2 30 80 0 10 8 18 0 8 0
2 AAL 2 0 0 0 0 1 4 6 0 0 2
3 AAPL 16 14 4 0 0 4 7 22 8 38 8
4 ABC 0 2 0 0 0 0 2 0 0 2 0
In [11]:
relevant_esgs = esg_marker_data[esg_marker_data["Ticker"].isin(tickers)]
relevant_esgs = relevant_esgs.fillna(0.0) #no esg score to zero esg score
array([16, 48,  2,  0, 22, 12, 18,  8,  6,  4, 28, 10, 20, 14, 36, 26, 30,
       24,  1, 42, 32, 11, 44, 52, 38, 13, 43, 54,  9, 72, 55,  7, 34, 40,
       60,  5, 25, 46, 58, 33, 50, 29,  3, 70, 62])
In [12]:
all_data = pd.concat([relevant_esgs, prices_for_esg_companies.iloc[:,1:]], axis = 1) #put the esg and stock data in one dataframe
all_data = all_data[np.isfinite(all_data['emp_str_sum'])]
In [13]:
all_data['emp_str_sum'].unique() #making sure the list of EMP-str-sum values is the same before and after concatenation to ensure that all the esg data made it over
array([ 16.,  48.,   2.,   0.,  22.,  12.,  18.,   8.,   6.,   4.,  28.,
        10.,  20.,  14.,  36.,  26.,  30.,  24.,   1.,  42.,  32.,  11.,
        44.,  52.,  38.,  13.,  43.,  54.,   9.,  72.,  55.,   7.,  34.,
        40.,  60.,   5.,  25.,  46.,  58.,  33.,  50.,  29.,   3.,  70.,

Fifth, we look at the data.

In [14]:
import matplotlib.pyplot as plt
import random
import seaborn as sns
import math
from scipy.stats import t
import numpy as np

%matplotlib inline  

Our research question, recall, is this: do companies with low aggregated ESG scores have better stock performance than companies with high aggregated ESG scores?

We have so far calculated the return on investment of every stock, every quarter. We’re not ready to use that data, though. If we use each return individually, we’ll end up comparing each example (that is, each company) multiple times. Multiple comparisons in statistics gives you more opportunities to end up with false positives because it gives you more cracks at hitting that 5% probability lottery (if p-0.05) of your results being a fluke.

Instead, we want a way to generalize over all of its quarterly returns for those quarters in which each stock had a return.

Easy! Take an average, right? Well, not quite.

First of all, an average skews toward big outliers. If most quarters showed an ROI of 1.01 and then one quarter has an ROI of 2, the average will skew much higher than 1.01.

Pursuant to that, smaller numbers of measurements skew toward the extremes. So if a company only has three returns, each individual return affects the average a lot. That means, if one of the three is super high, the whole average will be super high. Comparatively, if a company has 200 returns and one of them is an outlier, it won’t drag the average nearly as far. In datasets where examples possess a different number of measurements, the highest and lowest values for the target variable often come from samples with few measurements.

We are in a prime position to encounter that gotcha, because some of these companies are much more measured (many more quarterly returns) than others.

So instead, we’re going to do something that might look a little weird.

Confidence Intervals

Instead of going off the average of our set of returns, we’ll take the confidence interval for it. A confidence interval attempts to take into account the fact that our data doesn’t quite match the real world: it samples the real world such that we can attempt to represent the real world in studies. So the “true average” of something might not look like the average of our measurements of it, and the fewer measurements we have the less sure we can be of the discrepancy. A confidence interval takes our average and says ‘based on this average, the true average lies between this number and this number with this probability.’

So, for our stock return measurements, we’ll get the confidence interval for each company. For companies with few measurements, that confidence interval will be wide. For companies with many measurements, it will be more narrow. We’ll then sample that pessimistically and compare the bottoms of all the companies’ confidence intervals.

It’s worth noting that this is going to give us a very low number for companies with few returns and without a high outlier. We’ll address that as well.

In [15]:
# Calculate average roi
returns = all_data[all_data.columns.difference(['emp_str_sum', 'emp_con_sum', 'env_str_sum', 'env_con_sum', 'div_str_sum', 'Ticker','Company Name'])]
alc_con_sum cgov_con_sum cgov_str_sum com_con_sum com_str_sum div_con_sum quarter_starting_03/31/1990_roi quarter_starting_03/31/1991_roi quarter_starting_03/31/1992_roi quarter_starting_03/31/1993_roi quarter_starting_12/31/1994_roi quarter_starting_12/31/1995_roi quarter_starting_12/31/1996_roi quarter_starting_12/31/1997_roi quarter_starting_12/31/1998_roi quarter_starting_12/31/1999_roi quarter_starting_12/31/2000_roi quarter_starting_12/31/2001_roi quarter_starting_12/31/2002_roi quarter_starting_12/31/2003_roi
0 0.0 5.0 3.0 0.0 8.0 0.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 0.0 8.0 10.0 0.0 18.0 0.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0.561279 1.226236 0.732183 1.081737
2 0.0 4.0 1.0 0.0 6.0 2.0 0.984556 1.030534 1.076312 1.085271 0.479049 1.18438 1.066667 0.977693 0.552441 0.846386 1.073134 1.061603 0.850746 0.912895
3 0.0 7.0 4.0 8.0 22.0 8.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 0.0 2.0 0.0 0.0 0.0 0.0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

5 rows × 66 columns

In [16]:
def confidence_interval_for(samples=[], confidence=0.95):
    sample_size = len(samples)
    degrees_freedom = sample_size - 1
    outlier_tails = (1.0 - confidence) / 2.0
    t_distribution_number = -1 * t.ppf(outlier_tails, degrees_freedom)

    step_1 = np.std(samples)/math.sqrt(sample_size) 
    step_2 = step_1 * t_distribution_number
    low_end = np.mean(samples) - step_2
    high_end = np.mean(samples) + step_2

    return low_end, high_end
In [17]:
lower_conf_intervals = []
upper_conf_intervals = []

for (idx, row) in returns.iterrows():
    maybe_measurements = row.tolist()
    measurements = [float(x) for x in maybe_measurements if (math.isnan(float(x)) == False)]
    bottom, top = confidence_interval_for(measurements)
In [18]:
all_data['avg_quarterly_roi'] = returns.mean(axis=1, skipna=True)
all_data['num_measurements'] = returns.count(axis=1)
all_data['lower_conf_interval'] = lower_conf_intervals
all_data['upper_conf_interval'] = upper_conf_intervals

Ticker emp_str_sum emp_con_sum env_str_sum env_con_sum alc_con_sum cgov_str_sum cgov_con_sum com_str_sum com_con_sum quarter_starting_06/30/2003_roi quarter_starting_09/30/2003_roi quarter_starting_12/31/2003_roi quarter_starting_03/31/2004_roi quarter_starting_06/30/2004_roi quarter_starting_09/30/2004_roi avg_quarterly_roi num_measurements lower_conf_interval upper_conf_interval
0 A 16.0 16.0 0.0 0.0 0.0 3.0 5.0 8.0 0.0 NaN NaN NaN NaN NaN NaN 2.666667 6 -0.520271 5.853605
1 AA 48.0 2.0 30.0 80.0 0.0 10.0 8.0 18.0 0.0 1.130946 1.322479 1.081737 0.925703 0.736680 1.117293 2.242704 24 0.547832 3.937575
2 AAL 2.0 0.0 0.0 0.0 0.0 1.0 4.0 6.0 0.0 1.025882 1.452599 0.912895 0.952148 1.016954 0.935397 1.113084 66 0.925537 1.300631
3 AAPL 16.0 14.0 4.0 0.0 0.0 4.0 7.0 22.0 8.0 NaN NaN NaN NaN NaN NaN 8.166667 6 1.038311 15.295023
4 ABC 0.0 2.0 0.0 0.0 0.0 0.0 2.0 0.0 0.0 NaN NaN NaN NaN NaN NaN 0.333333 6 -0.448870 1.115536
5 ABI 22.0 2.0 0.0 0.0 0.0 0.0 4.0 0.0 0.0 NaN NaN NaN NaN NaN NaN 0.666667 6 -0.897740 2.231073
6 ABK 12.0 0.0 0.0 0.0 0.0 0.0 2.0 0.0 0.0 1.087093 1.031371 1.265325 1.203402 1.190842 1.661935 0.994680 66 0.887836 1.101523
7 ABS 18.0 29.0 0.0 0.0 0.0 0.0 11.0 0.0 0.0 NaN NaN NaN NaN NaN NaN 3.833333 6 -1.863877 9.530544
8 ABT 0.0 2.0 2.0 38.0 0.0 2.0 9.0 4.0 0.0 0.779380 1.038853 0.973820 1.093270 0.898461 1.092534 1.786700 19 0.740532 2.832867
9 ABX 8.0 6.0 0.0 6.0 0.0 7.0 1.0 15.0 0.0 NaN NaN NaN NaN NaN NaN 5.000000 6 -0.651384 10.651384

10 rows × 77 columns

Before we go on, let’s talk statistical power.

The statistical power of a dataset describes the likelihood that you will be able to detect a meaningful difference with this data given that the difference is there. It’s an important precursor step to data analysis because it determines your ability to find the effect you’re looking for.

Calculating statistical power is a little complicated, and it’s generally a good idea to get someone who knows what they’re doing to help you. I’m not confident in my ability to evaluate statistical power calculation strategies yet, but I figured I’d start with the all-around method and used this online calculator.

I performed a one-tail statistical power test. For this we will need the following data:

In [19]:
count    925.000000
mean      -0.221716
std        1.515036
min      -16.108048
25%       -0.688611
50%        0.228662
75%        0.799898
max        3.885452
Name: lower_conf_interval, dtype: float64

I took the mean of the bottom end of confidence intervals on stock returns—0.92. Suppose this were only the mean for companies with favorable ESG scores, and suppose that companies with unfavorable ESG scores had a higher quarterly return of 1 standard deviation higher—0.934. So 0.92 is my sample average and 0.934 is my test value.

In calculating your statistical power, you have two variables under your control: the number of examples in your data and your effect size. The more examples you have, the smaller effect size you can reliably detect. This means that, if you have already collected your data (as we have), to have a statistically powerful study, the effect size you’re seeking needs to be large enough that the number of examples you have could reliably detect it. The bigger the effect size, the less data you need to detect it. For small effect sizes, you may need thousands of examples. But if we had only ten stock examples (5 with a low ESG aggregate score, 5 with a high one) and the 5 with low ESG aggregate scores showed triple the returns of the high ones, that effect size is huge, so our tiny study could still reliably detect it. (to check this, I plugged it into the power calculator with an absurdly high standard deviation of 1. My power was 100%).

Anyway, back to our real data. The sample size is how many examples we have…

In [20]:
(925, 77)


Our standard deviation for sample is 0.14. so we plug that in.

I left the confidence level at 5% and ran the calculator.

My result: 91.9%

That’s the probability, if there is a 1.4 percentage point difference or more between stock returns for companies with favorable and unfavorable ESG scores, we have a 91.9% chance of detecting it. Statistical power expectations vary, but a rule of thumb (and standard practice at some more rigorous scientific journals) is to expect a statistical power of 80% or higher to take a study seriously.

OK, time to make some pictures! Let’s plot our ESG score sums against average roi and see if we notice any trends.

In [21]:
f, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, sharex='col', sharey='row', figsize=(20, 10))
f.suptitle('ESG Sums Plotted Against Returns')
ax1.scatter(x=all_data['emp_str_sum'], y=all_data['lower_conf_interval'])
ax2.scatter(x=all_data['emp_con_sum'], y=all_data['lower_conf_interval'])
ax3.scatter(x=all_data['env_str_sum'], y=all_data['lower_conf_interval'])
ax4.scatter(x=all_data['env_con_sum'], y=all_data['lower_conf_interval'])
(925, 77)

We have a couple of really low ones on those charts. Let’s look at them:

In [22]:
all_data[['Company Name','lower_conf_interval', 'num_measurements']].sort('lower_conf_interval').head(10)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  if __name__ == '__main__':
Company Name lower_conf_interval num_measurements
768 NaN -16.108048 6
373 NaN -12.950150 6
889 NaN -8.698689 6
616 NaN -7.937033 6
450 NaN -7.851837 6
497 NaN -7.185177 6
338 NaN -7.147846 6
837 NaN -6.547796 6
755 NaN -6.405443 6
659 NaN -5.944303 6

We spoke a little bit earlier about the way our measuring strategy would especially penalize companies with only a few measurements. We’re seeing that here. Out of these bottom 10 scores for the lower end of stock return confidence intervals, only one has more than 7 data points. Compare this to the mean number of measurements:

In [23]:

But there’s another thing to note about this data: five of the values are negative. These scores are low, yes, but a negative value for a stock return does not make sense. The value of a stock can drop to zero, but it does not turn into debt to the company owed by the stockholders. The lowest a stock price can drop is to zero. To accurately represent that, we’ll clip these values at zero.

In [24]:
all_data['lower_conf_interval'] = all_data['lower_conf_interval'].clip_lower(0)

Let’s check the means too to make sure we don’t have negative values in the means:

In [25]:
all_data[['Company Name','avg_quarterly_roi', 'num_measurements']].sort('avg_quarterly_roi').head(10)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  if __name__ == '__main__':
Company Name avg_quarterly_roi num_measurements
85 NaN 0.0 6
580 NaN 0.0 6
305 NaN 0.0 6
839 NaN 0.0 6
601 NaN 0.0 6
619 NaN 0.0 6
413 NaN 0.0 6
802 NaN 0.0 6
223 Crown Castle Int’l Corp. 0.0 6
651 NaN 0.0 6

Cool: we don’t. This means that none of the upper ends of the confidence intervals will have negative values either, as the upper end of the confidence interval is always higher than the mean on which it is based.

Let’s check out our plot again with those adjustments:

In [26]:
f, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, sharex='col', sharey='row', figsize=(20, 10))
f.suptitle('ESG Sums Plotted Against Returns')
ax1.scatter(x=all_data['emp_str_sum'], y=all_data['lower_conf_interval'])
ax2.scatter(x=all_data['emp_con_sum'], y=all_data['lower_conf_interval'])
ax3.scatter(x=all_data['env_str_sum'], y=all_data['lower_conf_interval'])
ax4.scatter(x=all_data['env_con_sum'], y=all_data['lower_conf_interval'])

Let’s look at the general trends of the returns. They look pretty flat to me: I don’t see an upward or downward trend in quarterly ROI based on any of the 4 ESG metrics.

Let’s dig a little deeper and see if the numbers themselves support that.

In [27]:
f, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, sharex='col', sharey='row', figsize=(20, 10))
f.suptitle('Histograms of Aggregated ESG Scores')
(925, 77)

The thing about a lot of our data is that it skews heavily toward low ESG sums. For example, there are a lot more companies with low employment policy strength scores than high ones. How do we account for this when we do our aggregations?

Well, we’ll look at the individual ESG scores first. But later we’ll bucket those ESG scores by fives so that we have small groups of scores, and then we’ll look at the confidence intervals around those (which will, of course, be wider for smaller groups).

Here’s a question though—why do buckets at all? Why not stick to a linear regressor?

Good question! A regressor can frequently be a preferable option to drawing arbitrary distinctions in continuous data. This isn’t exaclty continuous data as the ESG aggregations are all natural numbers, but that is not the reason for the buckets. The reason for the buckets is to have some kind of aggregate at each stage across our data examples, so our data is not so broken up by a single ESG score with no examples, or just one example. If we run a regressor, the bottom of the regressor represents a ton of data points, and as we go higher it represents fewer and fewer data points such that the expected value up there means very little.

I’d be very concerned to artificially divide this data into only two buckets such that points close to the arbitrary division get lumped in with very different means. But breaking it up by fives gives us several buckets such that each bucket of data points contains data points that are roughly similar to one another in their ESG scores.

In [28]:
all_data['emp_str_buckets'] = all_data.apply(lambda row: int(row['emp_str_sum'] / 5), axis=1)
all_data['emp_con_buckets'] = all_data.apply(lambda row: int(row['emp_con_sum'] / 5), axis=1)
all_data['env_str_buckets'] = all_data.apply(lambda row: int(row['env_str_sum'] / 5), axis=1)
all_data['env_con_buckets'] = all_data.apply(lambda row: int(row['env_con_sum'] / 5), axis=1)
In [29]:
def get_grouping_for(esg_metric, dataframe):
    return dataframe.groupby(esg_metric).agg({'lower_conf_interval': ['count','mean','std']}).reset_index()

emp_str_sum_group = get_grouping_for('emp_str_sum', all_data)
emp_con_sum_group = get_grouping_for('emp_con_sum', all_data)
env_str_sum_group = get_grouping_for('env_str_sum', all_data)
env_con_sum_group = get_grouping_for('env_con_sum', all_data)

f, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, sharex='col', sharey='row', figsize=(20, 10))
f.suptitle('ESG Sums Plotted Against Returns')
ax1.plot(emp_str_sum_group['emp_str_sum'], emp_str_sum_group['lower_conf_interval']['mean'])
ax2.plot(emp_con_sum_group['emp_con_sum'], emp_con_sum_group['lower_conf_interval']['mean'])
ax3.plot(env_str_sum_group['env_str_sum'], env_str_sum_group['lower_conf_interval']['mean'])
ax4.plot(env_con_sum_group['env_con_sum'], env_con_sum_group['lower_conf_interval']['mean'])

Hmm. I’m not sure I see much of a trend here. Let’s see if any of these ESG sums correlate with their mean lower confidence interval return.

In [30]:
all_data[['emp_str_sum', 'emp_con_sum', 'env_str_sum', 'env_con_sum', 'lower_conf_interval']].corr()
emp_str_sum emp_con_sum env_str_sum env_con_sum lower_conf_interval
emp_str_sum 1.000000 0.213114 0.342342 0.321241 0.055385
emp_con_sum 0.213114 1.000000 0.201488 0.415433 0.036110
env_str_sum 0.342342 0.201488 1.000000 0.486349 0.082377
env_con_sum 0.321241 0.415433 0.486349 1.000000 0.116089
lower_conf_interval 0.055385 0.036110 0.082377 0.116089 1.000000

We’re interested in the correlations between the esg sums on the left and the lower_conf_interval (last column). These are all very small correlations. A perfect direct correlation is 1. A perfect inverse correlation is -1. Zero means no correlation whatsoever. These correlation numbers arepretty darn close to zero.

We’ll have to dig deeper into the data.

Sixth, we determine what our results could mean.

OK friends, it’s time to figure out what our data means, or rather, whether it means anything at all with respect to our question: do companies with higher str ESG scores and lower con ESG scores have lower stock returns than companies that perform more poorly on ESG metrics?

First, let’s calculate our confidence intervals for stock performance and determine if the stock performances for companies low ESG scores fall outside the confidence intervals for the stock performance of companies with high ESG scores.

In [31]:
import math 
from scipy.stats import t
import numpy as np

First, let’s make ourselves a method to get confidence intervals from summary statistics. That way, we can make interpretable groupings for all of our esg scores and get the confidence intervals on those scores based on how many companies had each esg score.

In [32]:
def confidence_interval_for_collection(sample_size=[], standard_deviation=[], mean=[], confidence=0.95):
    degrees_freedom = [count - 1 for count in sample_size] 
    outlier_tails = (1.0 - confidence) / 2.0
    confidence_collection = [outlier_tails for _ in sample_size]
    t_distribution_number = [-1 * t.ppf(tails, df) for tails, df in zip(confidence_collection, degrees_freedom)]

    step_1 = [std/math.sqrt(count) for std, count in zip(standard_deviation, sample_size)]
    step_2 = [step * t for step, t in zip(step_1, t_distribution_number)]

    low_end = [mean_num - step_num for mean_num, step_num in zip(mean, step_2)]
    high_end = [mean_num + step_num for mean_num, step_num in zip(mean, step_2)]

    return low_end, high_end

Now let’s make ourselves some convenience methods to wrhangle our data. One of them will create the grouping we talked about before, aggregating each ESG metric to tell us a) how many companies had each score on the ESG metric, b) the mean return in that group of companies, and c) the standard deviation on returns in that group of companies.

We’ll use that data to get a lower limit and an upper limit on a confidence interval around those returns based on how many measurements we have at each score.

Finally, we’ll make a method to plot the results of these groupings so we can eyeball our results.

In [33]:
def prepare_confidence_data_for(esg_metric, grouping):
    sample_size = grouping['lower_conf_interval']['count']
    standard_deviation = grouping['lower_conf_interval']['std']
    mean = mean = grouping['lower_conf_interval']['mean']
    low_end, high_end = confidence_interval_for_collection(sample_size, standard_deviation, mean)
    df_with_confidence_limits = pd.concat([labels, sample_size, standard_deviation, mean], axis=1)
    df_with_confidence_limits['.95 confidence level min'] = low_end
    df_with_confidence_limits['.95 confidence level max'] = high_end
    return df_with_confidence_limits
def plot_grouping(group_name, grouping, axes):
    mini = axes.plot(grouping['.95 confidence level max'], color='r', label="Max of 0.95 Confidence Interval")
    mean = axes.plot(grouping['mean'], color='g', label="Mean")
    maxi = axes.plot(grouping['.95 confidence level min'], color='b', label="Min of 0.95 Confidence Interval")
    legend = axes.legend(loc='upper right', shadow=True) 

Let’s eyeball some charts!

In [34]:
emp_str_confidence_interval_data = prepare_confidence_data_for('emp_str_sum', emp_str_sum_group)
emp_con_confidence_interval_data = prepare_confidence_data_for('emp_con_sum', emp_con_sum_group)
env_str_confidence_interval_data = prepare_confidence_data_for('env_str_sum', env_str_sum_group)
env_con_confidence_interval_data = prepare_confidence_data_for('env_con_sum', env_con_sum_group)

f, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, sharex='col', sharey='row', figsize=(20, 10))
f.suptitle('ESG Sums Plotted Against Returns')
plot_grouping('Employment Strength Sums', emp_str_confidence_interval_data, ax1)
plot_grouping('Employment Concern Sums', emp_con_confidence_interval_data, ax2)
plot_grouping('Environmental Strength Sums', env_str_confidence_interval_data, ax3)
plot_grouping('Emnvironmental Concern Sums', env_con_confidence_interval_data, ax4)

Worth noting: You can tell from the cart the rough relative number of data points that we had for a given ESG score. Where you see no red, blue, or green lines, we had no data points for that ESG score. Where there is a green line but no red or blue lines, we had only one data point for that ESG score. Where there are red and blue lines and they are far apart, we had a few data points, but a small enough number that the confidence interval is still very wide. Where the red and blue lines flank the green line very closely, we had a lot of data: the confidence interval for these is very close to the mean.

This is a little hard to read with the skips in it. Let’s bucket the scores and see how that looks:

In [35]:
emp_str_bucket_group = get_grouping_for('emp_str_buckets', all_data)
emp_con_bucket_group = get_grouping_for('emp_con_buckets', all_data)
env_str_bucket_group = get_grouping_for('env_str_buckets', all_data)
env_con_bucket_group = get_grouping_for('env_con_buckets', all_data)

emp_str_bucket_data = prepare_confidence_data_for('emp_str_buckets', emp_str_bucket_group)
emp_con_bucket_data = prepare_confidence_data_for('emp_con_buckets', emp_con_bucket_group)
env_str_bucket_data = prepare_confidence_data_for('env_str_buckets', env_str_bucket_group)
env_con_bucket_data = prepare_confidence_data_for('env_con_buckets', env_con_bucket_group)

f, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, sharex='col', sharey='row', figsize=(20, 10))
f.suptitle('ESG Sums Plotted Against Returns')
plot_grouping('Bucketed Employment Strength Sums', emp_str_bucket_data, ax1)
plot_grouping('Bucketed Employment Concern Sums', emp_con_bucket_data, ax2)
plot_grouping('Bucketed Environmental Strength Sums', env_str_bucket_data, ax3)
plot_grouping('BucketedEmnvironmental Concern Sums', env_con_bucket_data, ax4)

Check out those widening confidence intervals as we get to the higher point ranges! You can practicelly see the number of exaxmples falling off.

So what am I looking for here? I’m looking to see if the highest point on the bottom of the confidence intervals on either side of the graph is higher than the top of the confidence intervals on the other side. This break in overlap between the confidence intervals would provide a leading indication of a strong and meaningful difference in stock returns for companies with high versus low scores in any of these ESG metrics.

I don’t see that happen in any of these charts. The confidence intervals overlap significantly all the way across. So we can’t be confident at all that stocks from the high ESG score groups will have higher or lower returns than the stocks from the low ESG score groups.

But wait! That doesn’t necessarily mean that there is no meaningful difference. The confidence intervals can overlap for two series of points whose data still has a meaningful difference. To determine whether it does, we need to perform additional significance testing—in this case, a t test.

How to do the independent samples t test

In [36]:
def t_test_for(num_samples_1, standard_deviatcion_1, mean1, num_samples_2, standard_deviation_2, mean2, confidence=0.95, num_comparisons=1):
  alpha = (1 - confidence)/float(num_comparisons)
  total_degrees_freedom = num_samples_1 + num_samples_2 - 2

  t_distribution_number = -1 * t.ppf(alpha, total_degrees_freedom)

  degrees_freedom_1 = num_samples_1 - 1
  degrees_freedom_2 = num_samples_2 - 1
  sum_of_squares_1 = (standard_deviation_1 ** 2) * degrees_freedom_1
  sum_of_squares_2 = (standard_deviation_2 ** 2) * degrees_freedom_2

  combined_variance = (sum_of_squares_1 + sum_of_squares_2) / (degrees_freedom_1 + degrees_freedom_2)
  first_dividend_addend = combined_variance/float(num_samples_1)
  second_dividend_addend = combined_variance/float(num_samples_2)

  denominator = math.sqrt(first_dividend_addend + second_dividend_addend)
  numerator = mean1 - mean2
  t_value = float(numerator)/float(denominator)

  accept_null_hypothesis = abs(t_value) < abs(t_distribution_number) #results are not significant

  return accept_null_hypothesis, t_value           

We have written a method that will tell us, firstly, whether to accept or reject the null hypothesis, which assumes no meaningful difference between the two sets of data we want to compare. I have named that output ‘accept_null_hypothesis’ because I don’t love the ubiquitous use of the confounding phrase ‘reject the null hypothesis’ in scientifi inquiry. It’s a double negative (reject the absence of meaningful difference), which adds an unnecessary additional piece of mental acrobatics to the (already frequently herculean) task of determining what, exactly, the scientists are trying to say in their conclusion paragraph.

We are going with accept the absence of meaningful difference as the variable name for two reasons. First of all, we remove the double negative this way. Second of all, accepting the null hypothesis is (or should be) the outcome of the vast majority of scientific inquiry. Scientists, collectively, test a whole bunch of stuff to see what has an effect. Most of the things tried, it turns out, don’t have that effect. So our accept_null_hypothesis value will usually be true. When it’s false, we should sit up and pay attention.

In [37]:
mean1 = all_data.sort('emp_str_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].mean()
std1 = all_data.sort('emp_str_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].std()

mean2 = all_data.sort('emp_str_sum').iloc[0:20, :]['lower_conf_interval'].mean()
std2 = all_data.sort('emp_str_sum').iloc[0:20, :]['lower_conf_interval'].std()

n1 = 20
n2 = 20

accept_null_hypothesis, t_value = t_test_for(n1, std1, mean1, n2, std2, mean2)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  if __name__ == '__main__':
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  from ipykernel import kernelapp as app
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
In [38]:
mean1 = all_data.sort('emp_con_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].mean()
std1 = all_data.sort('emp_con_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].std()

mean2 = all_data.sort('emp_con_sum').iloc[0:20, :]['lower_conf_interval'].mean()
std2 = all_data.sort('emp_con_sum').iloc[0:20, :]['lower_conf_interval'].std()

n1 = 20
n2 = 20

accept_null_hypothesis, t_value = t_test_for(n1, std1, mean1, n2, std2, mean2)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  if __name__ == '__main__':
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  from ipykernel import kernelapp as app
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
In [39]:
mean1 = all_data.sort('env_str_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].mean()
std1 = all_data.sort('env_str_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].std()

mean2 = all_data.sort('env_str_sum').iloc[0:20, :]['lower_conf_interval'].mean()
std2 = all_data.sort('env_str_sum').iloc[0:20, :]['lower_conf_interval'].std()

n1 = 20
n2 = 20

accept_null_hypothesis, t_value = t_test_for(n1, std1, mean1, n2, std2, mean2)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  if __name__ == '__main__':
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  from ipykernel import kernelapp as app
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
In [40]:
mean1 = all_data.sort('env_con_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].mean()
std1 = all_data.sort('env_con_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].std()

mean2 = all_data.sort('env_con_sum').iloc[0:20, :]['lower_conf_interval'].mean()
std2 = all_data.sort('env_con_sum').iloc[0:20, :]['lower_conf_interval'].std()

n1 = 20
n2 = 20

accept_null_hypothesis, t_value = t_test_for(n1, std1, mean1, n2, std2, mean2)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  if __name__ == '__main__':
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  from ipykernel import kernelapp as app
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
//anaconda/lib/python3.6/site-packages/ipykernel/ FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)

How about that! So far, accepting the null hypothesis everywhere. It appears that we cannot meaninfgully separate the stock performance of companies on either end of the spectrum for each of their employment policy ESG scores.

Interim Conclusions

On this pass, we have taken a much more thorough look at our data with respect to how well it approximates the real world and how certain we are about that approximation.

This did not mean tacking on a test for statistical significance at the end: it meant rethinking our analysis from the very beginning, including what our target variable would be (previously extrapolated annual roi, now back to mean quarterly roi) and also how we would represent it (with the bottom end of the confidence interval instead of the mean itself).

It also meant checking our results more thoroughly along the way, identifying unrealistic values in the data, understanding why those values were what they were, and replacing them with values that better approximate reality.

Finally, it meant conducting a t test rather than eyeballing the results.

The result here provides an excellent example of the difference between how data might be interpreted or perceived versus what it really represents. When we use the bottom of the confidence intervals, we’re looking at a lot of losses in shareholder value. It’s important to note that this isn’t really what happened. It is, more accurately, a statistically derived worst-case scenario for a company that did everything that a given example company did, based on the metrics we have about how that company actually did. That is not useful for predicting what the stock returns for the example company will be in the future (fun fact: trying to predict stock returns in general is not that useful, as straight-up index matching outperforms the vast majority of shrewder, prediction-based investment strategies in the long term. I don’t miss our loss of predictive value because there wasn’t much demonstrated value there to begin with.)

At any rate, what it is useful for is accurately representing the fact that there is a lot we do not know and attempting to account for that in a comparison of stock returns relative to ESG ratings. We’re not trying to predict returns by company: we’re trying to determine if companies with favorable ESG scores, in aggregate, underperform on the stock market relative to companies with unfavorable ESG scores. For this, our data is useful.

And, as we’ve established, we don’t see in this analysis a meaningful difference in any of our cases. This bodes well for the application question—can I create a socially conscious investment portfolio without sacrificing returns on my investment?

There’s more to look at to get a more definitive answer to this question. But so far, what we’re seeing points to yes.

Code Setup for Next Part

We will need to calculate p values for this next section of the analysis. P values are not a perfect metric: they can be tough to interpret and vulnerable to truth inflation. That said, we’ll do our best to take those things into account as we move along. Here you’ll see our method for getting a p value from some summary statistics.

In [ ]:
import scipy.stats as stats

def p_value_for(num_samples_1, standard_deviation_1, mean1, num_samples_2, standard_deviation_2, mean2, confidence=0.95, num_comparisons=1):
  alpha = (1 - confidence)/num_comparisons
  total_degrees_freedom = num_samples_1 + num_samples_2 - 2

  degrees_freedom_1 = num_samples_1 - 1
  degrees_freedom_2 = num_samples_2 - 1
  combined_degrees_freedom = degrees_freedom_1 + degrees_freedom_2

  variance_1 = (standard_deviation_1 ** 2)
  variance_2 = (standard_deviation_2 ** 2)

  combined_variance_1 = (variance_1 / num_samples_1)
  combined_variance_2 = (variance_2 / num_samples_2)
  mean_standard_error = math.sqrt(combined_variance_1 + combined_variance_2)
  t_statistic = float(mean1 - mean2)/float(mean_standard_error)
  p_value = stats.t.sf(np.abs(t_statistic), combined_degrees_freedom) * 2  # two-sided p value = probability abs(t) > tt)
  comparison_adjusted_p_value = p_value/float(num_comparisons)
  return comparison_adjusted_p_value

I also want a better way to save, store, access, and analyze results for each ESG metric. So let’s make an object to store summary statistics, render plots, and run analyses by ESG metric. You’ll see the utility of this object in the following cells:

In [125]:
class EsgMetricAnalysis():
    def __init__(self, name): = name
        self.low_esg_metric_mean = 0
        self.low_esg_metric_std = 0
        self.low_esg_metric_num_examples = 0
        self.high_esg_metric_mean = 0
        self.high_esg_metric_std = 0
        self.high_esg_metric_num_examples = 0
        self.num_comparisons = 1
        self.esg_marker_sum_grouping = {}
        self.esg_marker_confidence_interval_data = {}
        self.esg_marker_bucket_group = {}
        self.esg_marker_bucket_data = {}
    def plots(self):
        f, ((ax1, ax2), (ax3, ax4), (ax5, ax6)) = plt.subplots(3, 2, sharex='col', sharey='row', figsize=(30, 20))
        f.suptitle('ESG Sums Plotted Against Returns')
        ax1.scatter(x=all_data[ + '_sum'], y=all_data['lower_conf_interval'])
        ax2.hist(x=all_data[ + '_sum'])
        ax3.plot(self.esg_marker_sum_grouping[ + '_sum'], self.esg_marker_sum_grouping['lower_conf_interval']['mean'])
        plot_grouping('Employment Strength Sums', self.esg_marker_confidence_interval_data, ax4)
        plot_grouping('Bucketed Employment Strength Sums', self.esg_marker_bucket_data, ax5)
    def t_test(self):
        return t_test_for(
    def p_value(self):
        return p_value_for(

OK, here is where we create all of our EsgMetricAnalysis objects. This method is messy, hard to interpret, and very dependent on the state of the notebook. I’d ultimately like to encapsulate this work in a series of pipelines and possibly extract a separate script for data preparation. That said, I promised to share incremental progress with you, and I have enough progress now that it’s time to share it.

In [ ]:
def do_the_whole_dance_with(esg_metric=None, num_comparisons=1):
    analysis = EsgMetricAnalysis(esg_metric)
    esg_marker_data[esg_metric + '_sum'] = sum_scores_for(aggregate_data, esg_metric + '_sum')[esg_metric + '_sum']
    all_data[esg_metric + '_buckets'] = all_data.apply(lambda row: int(row[esg_metric + '_sum'] / 5), axis=1)
    analysis.esg_marker_sum_grouping = get_grouping_for(esg_metric + '_sum', all_data)
    analysis.esg_marker_confidence_interval_data = prepare_confidence_data_for(esg_metric + '_sum', analysis.esg_marker_sum_grouping)
    analysis.esg_marker_bucket_group = get_grouping_for(esg_metric + '_buckets', all_data)
    analysis.esg_marker_bucket_data = prepare_confidence_data_for(esg_metric + '_buckets', analysis.esg_marker_bucket_group)

    analysis.low_esg_metric_mean = all_data.sort_values(by=esg_metric + '_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].mean()
    analysis.low_esg_metric_std = all_data.sort_values(by=esg_metric + '_sum', ascending=False).iloc[0:20, :]['lower_conf_interval'].std()

    analysis.high_esg_metric_mean = all_data.sort_values(by=esg_metric + '_sum').iloc[0:20, :]['lower_conf_interval'].mean()
    analysis.high_esg_metric_std = all_data.sort_values(by=esg_metric + '_sum').iloc[0:20, :]['lower_conf_interval'].std()

    analysis.low_esg_metric_num_examples = 20
    analysis.high_esg_metric_num_examples = 20
    analysis.num_comparisons = num_comparisons

    return analysis

Let’s make sure our thing works:

In [136]:
analysis = do_the_whole_dance_with('emp_str')

Sweet! Now that we have created a way to run our analysis quickly on several ESG metrics, let’s try it on several of them at one time.

Ah! But when we do this, there’s something we have to watch out for: multiple comparisons! For each individual ESG metric, we are comparing the same examples. Remember that, if we test enough variables about our companies, eventually some of them will demonstrate a meaningful-looking effect by pure chance. A p-value of 0.05 still means a 5% probability of just such a chance occurrence, and when you do 100 comparisons the probability of at least one such false positive rises to…99%.

We are not making 99 comparisons, but we are making 11. For 11 comparisons at a p value of 0.05, the probability of a meaningful-looking result occurring by chance is…

In [50]:

…0.43. That is, there’s a 43% chance we get a false positive. That’s a fairly high probability.

There are a few ways to adjust our calculations for multiple comparisons. The most hamfisted of these, the Bonferroni Correction, adjusts by dividing our starting p value by the number of comparisons we make and using that as the working p-value.

The Bonferroni correction controls the familywise error rate—the probability, assuming all the variables have identical distribution in the two groups, that a significant-looking difference happens purely by chance.

So in this case, our working p value would be…

In [52]:
0.05 /11

…0.004. Very, very tiny.

Unsurprisingly, when we run our analysis with the Bonferroni correction, we do not find a single statistically significant difference in stock prices among the lot. Check it out:

In [129]:
esgs = [

analyses = []
for esg in esgs:
    analyses.append(do_the_whole_dance_with(esg, num_comparisons=11))

That is a lot of ‘true’values for accepting the null hypothesis.

We can also check our resuts with the Benjamini-Hochberg procedure.

The Benjamini-Hochberg correction controls the False discovery rate—the expected proportion of false positives among the variables for which you claim the existence of a difference. For example, if with FDR controlled to 5% 20 tests are positive, on average one of these tests will be a false positive (because 1 in 20 is 5%).

To run this tesst, we choose a false positive rate (Q) to which we wish to control. Let’s choose 10% to start and see what we come up with.

In [176]:
p_values = {}
for esg in esgs:
    analysis = do_the_whole_dance_with(esg) #We leave num_comparisons at 1 since we're about to correct for it with a different procedure.
    p_values[] = analysis.p_value()
{'alc_con': 0.52149743027205497,
 'cgov_con': 0.12443279432304483,
 'cgov_str': 0.023657375782642983,
 'com_con': 0.60932126282257471,
 'com_str': 0.029299680869613209,
 'div_con': 0.33570341442667906,
 'div_str': 0.086155730251918514,
 'emp_con': 0.42023821025024821,
 'emp_str': 0.37141831606394926,
 'env_con': 0.1098226363187968,
 'env_str': 0.58368331428660714}

How to do this test:

  1. Rank all your p values lowest to highest.
  2. Calculate (Q * rank)/number of comparisons.
  3. Go down the list until the calculated value Qi/n is lower than p.
In [177]:
benjamini_hochberg_data = pd.DataFrame({'name': list(p_values.keys()), 'p_value': list(p_values.values())})
benjamini_hochberg_data = benjamini_hochberg_data.sort_values(by='p_value')
benjamini_hochberg_data['rank'] = range(11)
benjamini_hochberg_data['rank'] = benjamini_hochberg_data['rank'] + 1
benjamini_hochberg_data['Qi/n'] = (0.1 * benjamini_hochberg_data['rank']) / float(11)
name p_value rank Qi/n
5 cgov_str 0.023657 1 0.009091
7 com_str 0.029300 2 0.018182
9 div_str 0.086156 3 0.027273
3 env_con 0.109823 4 0.036364
6 cgov_con 0.124433 5 0.045455
10 div_con 0.335703 6 0.054545
0 emp_str 0.371418 7 0.063636
1 emp_con 0.420238 8 0.072727
4 alc_con 0.521497 9 0.081818
2 env_str 0.583683 10 0.090909
8 com_con 0.609321 11 0.100000

Check it out: even on the very first one, the p value is larger than Qi/n. So when we analyze with this procedure, we’re still not finding a meaningful difference between stock performances based on company ESG score.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.