Master of Management Studies, Spring 2024
ACCOUNTG 512F: Fundamentals of Financial Analysis
Case II: Estimate the CAPM Beta
Learning objective:
• Work with real-world financial data using programming languages.
Case materials:
As we discussed in class, there are several ways to estimate Cost of Equity (CoE). An easy approach to estimate CoE is to use asset pricing equations, such as CAPM. In this case, you will have an opportunity to use real-world historical financial data to estimate Beta from the CAPM equation.
I have uploaded the necessary data files for you in both formats that accommodate Python and SAS. There are three files that you will need to use.
• msf0820_short, abbreviated as CRSP
This is the monthly stock return file from CRSP. You will find that this file contains stock identifiers (permno), date, and the monthly holding period return (ret). It also includes the market portfolio (vwretd,value weighted, with distribution), which represents the RM in CAPM.
• ff0820_short, abbreviated as FF-3-factor
This is the FF-3-factor dataset, in which you will find the risk-free rate rf used in CAPM. In addition, you can find mktrf—capturing market portfolio’s excess return (i.e., rM-rf).
• ccm0820_short, abbreviated as Compustat
This is the Compustat data file, covering reported annual fundamental data for all public firms. I have substantially simplified this dataset so that it just includes unique stock identifiers (permno), fiscal year (fyear), fiscal year enddate (datadate), ticker (tic, in fact,header ticker, that is, the latest ticker symbol), and several key financial accounting variables. In this exercise, we will treat permno as the stock ID for implementing the CAPM regression and use it to merge with CRSP. At the very end, we tabulate the betas of the firms of our interest (indicated using tickers (variable name: tic) below).
Requirement:
The CAPM regression should be estimated using a window of five years (i.e., 60 months) leading to the fiscal year end of 2018. For example, Apple’sfiscal year 2018 ends on the last working day of September in 2018. Then, to estimate Beta for Apple as of the end of 2018, you need to use observations from fiscal year 2014 to fiscal year 2018. That is, you need to use monthly returns from October 2014 (i.e., the first month in the five-year window) to September 2018 (i.e., the last month in the five-year window).
After running your program entirely, tabulate the CAPM beta you estimated for the following companies:
• Target (Ticker symbol: TGT)
• Apple (Ticker symbol: AAPL)
• Walmart (Ticker symbol: WMT)
• Microsoft (Ticker symbol: MSFT)
Programming assistance:
Python
• To get started, we have to import the following packages:
import pandasql as ps
import statsmodels.api as sm
import pandas as pd
import os
1. If you use Python, you want to import the files with extension name dta. The recommended approach to do so is the following:
# Set the file path for this exercise
basepath=r"D:\Teaching\Duke\2024\Data\Python"
# Import CRSP
rawmsf=pd.read_stata(os.path.join(basepath, r"msf0820_short.dta"))
# Some variables may contain upper-case letters. Standardize them all using lower-case letters.
rawmsf.columns=map(str.lower, rawmsf.columns)
rawmsf=rawmsf[['permno', 'ret', 'prc', 'date', 'vwretd']]
# Clean some potential duplicate observations
rawmsf=rawmsf.drop_duplicates(subset=['permno', 'date'])
2. In CRSP, each stock has one return value dated at the end of each month. To connect this month’s return to a risk-free rate from the FF-3-factor dataset, we will do some additional work on the date in the stock return dataset (i.e., the CRSP dataset). Specifically, we want to convert a single one end-of- month date in CRSP to a wider day window. For example, if CRSP has a date of Feb 28, 2010, we want to convert it to a window of [Feb 23, 2010, March 5, 2010]. This modification will allow easier connection to the FF-3-factor dataset, which also has one end-of-month date (not necessarily exactly the same as the CRSP end-of-month date, could be a day or two off). You can use the following code
to create the window:
# Align day windows
def MergeDateWindow(dset, datevar, interval):
dset['begdate']=dset[datevar]-pd.Timedelta(days=interval)
dset['enddate']=dset[datevar]+pd.Timedelta(days=interval)
return dset
rawmsf=MergeDateWindow(rawmsf, 'date', 5)
3. You then can happily connect this monthly stock return dataset with the FF-3-factor dataset using the following code. In addition, you want to create a new variable—retrf—capturing each stock’s excess
return (i.e., ri-rf). You may want to consider the following code:
rawff=pd.read_stata(os.path.join(basepath, r"ff0820.dta"))
crsp_ff_join='''select distinct a.*, b.mktrf, b.smb, b.hml, b.rf, b.dateff from rawmsf as a left join rawff as b
on a.begdate
order by permno, date'''
msf=ps.sqldf(crsp_ff_join, locals())
msf['retrf']=msf['ret']-msf['rf']
4. Up to this step, you have assembled a dataset containing each stock’s excess return and the market portfolio’s excess return. The text step is to let monthly stock returns from CRSP (already connected with FF-3-factor) “fall” into the fiscal year range of five years. Fiscal years are reported in the Compustat dataset. Therefore, you need to specify the rolling-window of 60 months for each firm and each fiscal year using the Compustat dataset. For example, for Apple in 2018, you have 60 returns covering fiscal year 2014 to fiscal year 2018. You can write some code like below that is similar to the one that I provide in Step (3). After your job in this step, the resulting dataset is called ccm_5year.
# Import Compustat
rawccm=pd.read_stata(os.path.join(basepath, r"ccm0820_short.dta")) rawccm.columns=map(str.lower, rawccm.columns)
smallccm=rawccm[[ 'fyear', 'gvkey', 'lpermno', 'at', 'sale', 'act', 'prcc_f',
'csho', 'seq', 'ceq', 'datadate', 'tic', 'conm']]
smallccm=smallccm.rename(columns={'lpermno':'permno'})
smallccm=smallccm[(smallccm['fyear']==2018)]
smallccm['beta_begdate']=smallccm['datadate']-pd.Timedelta(days=365*5)
# Form a 5-year rolling window
cond_join= '''select distinct a.gvkey, a.permno, a.fyear, a.tic, a.datadate, b.retrf, b.ret, b.vwretd, b.rf, b.vwretd-b.rf as mktrf, b.date
from smallccm as a left join msf as b
on a.permno=b.permno and a.beta_begdate
ccm_5year = ps.sqldf(cond_join, locals())
5. After the last step, each stock in each fiscal year has been assembled with 60 monthly returns. You then need to estimate the CAPM regression by fiscal year-firm. That is, for each firm-fiscal year combination, you execute the regression and produce the CAPM beta. Because you want to run each stock’s excess return (ri-rf in CAPM, named retrf in the above code) on the market portfolio’s excess return (ri-rM in CAPM, named mktrf in the above code), you first need to require that both variables in your ccm_5year dataset generated from the step (4) to be not missing. Then, you can use the following function to perform. the linear regression:
# Need to have available retrf and mktrf
ccm_5year=ccm_5year[(ccm_5year['retrf'].notnull()) &
(ccm_5year['mktrf'].notnull())]
def GroupRegress(dset, yvar, xvars):
Y = dset[yvar]
X = dset[xvars]
X['intercept'] = 1.
X = sm.add_constant(X)
result = sm.OLS(Y, X).fit()
stats= result.params
return stats
6. As you can see from the function, the parameters you need to specify are (1) the dataset (dset) with which you want to perform. the regression; (2) the dependent variable (yvar); and (3) the independent variable(s)(xvars). To invoke this function and do the CAPM estimation by firm-fiscal year combination, you can use the following code. Here, it is apparent that the regression is done by firm-
fiscal year combination, as indicated by the part groupby(['permno', 'fyear']). Sample code:
capm = ccm_5year.groupby(['permno', 'fyear']).apply(GroupRegress, 'retrf', ['mktrf']).reset_index()
capm = capm.rename(columns={'intercept': 'alpha', 'mktrf':'beta'})
ccm_beta=smallccm.merge(right=capm, how="left", n=["permno", 'fyear'])
7. The product from the last step should have five variables. Specifically, the variable intercept is the CAPM intercept and the variable mktrf captures the CAPM beta, that is, the coefficient on mktrf in the CAPM equation. To avoid confusion, I recommend that you immediately rename these two variables into alpha and beta, respectively, right after the last step. It’s also worth noting that the dataset produced by the last step does not actually have stock tickers. Instead, since the regression was estimated by permno and fyear, the only stock identifier is permno. To attach stock tickers to this dataset, we can them merge this dataset with the original Compustat dataset. Sample code:
# See Apple and MSFT
ticker_of_interest=[ 'TGT', 'AAPL', 'WMT', 'MSFT']
for ticker in ticker_of_interest:
beta=ccm_beta.loc[ccm_beta['tic']==ticker]['beta'].values
print( '{}\'s CAPM Beta is {}'.format(ticker, beta))