代做ACCOUNTG 512F: Fundamentals of Financial Analysis Spring 2024代做Python编程

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))