Assignment #3, due Dec 16, 9am: Calculate and visualize CAPM betas

[joachim-gassen]

After the tough coding challenge of the second assignment it is now time again to do something visual with data. I want you to pull daily stock prices for the current Dow Jones 30 firms from public sources. Calculate daily returns based on adjusted prices and market returns as the equal weighted average of daily returns of all firms in your sample. Then calculate rolling 250-day window CAPM regressions for each firm for which you have continuous return data for the period 2000 to 2019. To complete the assignment, you should create a figure that looks roughly like this.

For fun and “extra credit”, you can make this visual interactive to allow users to assess the development of beta for a given firm. Plotly and Shiny come to mind. Here is a link to a very simple shiny app: https://jgassen.shinyapps.io/betas/ as an example.

Like always: Make sure that your data and code is reproducible (no data uploads please). Feel free to use the issue to clarify and discuss the assignment. Please document all resources that you use in code. The submission deadline for this assignment is December 16, 9 am.

[NDelventhal]

Has anyone tackled the task yet? I am not sure if it is due my data source (yahoo finance), but after filtering out stocks with no continuous data from 2000 to 2019, solely 27 stocks are considered further in my script.

As I assume Joachim’s chart to be the sample solution, my computed beta average and the confidence interval vary slightly due to this missing variable.

However, even another filtering seems reasonable based on the data I use, as the figure, we are supposed to replicate, appears to already start in 2000. If the rolling beta indication for all considered stocks starting in 2000 is supposed to be based on 250 prior returns, than I would even need to drop another stock.

As it might be a data source issue I am facing, has someone else stumbled over these issues, solved them or replicated the figure without any problems?

[joachim-gassen]

Apologies for the delay. Yahoo finance is a decent resource for our purpose. Do not worry to much when you have fewer firms than I do. The main task is to implement the analysis not to exactly replicate my findings.

[fengzhi22]

Following Joachim’s instructions and the graphical output, I obtained a constant average beta of 1. Graphically my result looks like this:

I wonder where I misunderstood about the variable definitions so that my average beta (blue line) ended up constant. Here is my calculation process:

I obtained excess daily returns for individual stock (R_i,t-Rf_t, where R_i,t is daily return for stock i at day t and Rf_t is risk-free rate at day t) and excess market returns (as the equal weighted average of excess daily returns of all 28 firms in my sample). The following table is a partial illustratin of excess daily returns for only 5 firms, where R_mkt = average(R_MMM, R_AXP, R_AAPL, R_BA, R_CAT).

date	MMM	AXP	AAPL	BA	CAT	mkt
2000/1/4	-0.06073	-0.05876	-0.10531	-0.02256	-0.03385	-0.05624
2000/1/5	0.007965	-0.0464	-0.00637	0.041306	0.002438	-0.00021
2000/1/6	0.059429	-0.00063	-0.10754	-0.01074	0.02989	-0.00592
2000/1/7	-0.00115	-0.00644	0.026369	0.008028	0.011688	0.007699
2000/1/10	-0.02587	-0.00706	-0.03859	-0.0351	-0.05383	-0.03209
2000/1/11	-0.03812	-0.0117	-0.07215	-0.0396	-0.03676	-0.03966
2000/1/12	-0.01851	-0.02621	-0.08097	-0.01663	-0.00376	-0.02922
2000/1/13	-0.021	-0.01576	0.088678	-0.03696	-0.0101	0.000969
2000/1/14	-0.03527	0.001435	0.017114	0.017348	-0.02579	-0.00503
2000/1/18	-0.04303	-0.07076	0.013847	0.001727	-0.04732	-0.02911
2000/1/19	-0.00363	-0.03832	0.004256	0.037333	-0.03352	-0.00677
2000/1/20	-0.05832	-0.00442	0.044103	-0.04462	-0.06536	-0.02572

Then I obtained beta from rolling 250-day window CAPM regressions for 28 firms with continuous return data for 2000-2019. The following table is a partial illustratin of 5-day rolling betas, where beta_mean = average(beta_MMM, beta_AXP, beta_AAPL, beta_BA, beta_CAT). As you can see, even in this small sample illustration, my average betas mechanically result to 1.

date	MMM	AXP	AAPL	BA	CAT	mean
2000/1/10	1.269177	0.512382	1.43287	0.783975	1.001595	1
2000/1/11	1.174815	-0.20984	1.260154	1.347347	1.427521	1
2000/1/12	1.289735	0.238868	1.194905	0.911267	1.365225	1
2000/1/13	0.508473	0.083508	2.945355	0.545394	0.917269	1
2000/1/14	0.097868	0.147415	3.651373	0.571249	0.532095	1
2000/1/18	0.229815	0.621403	3.401217	0.304169	0.443397	1
2000/1/19	0.361685	1.216374	3.095993	0.120571	0.205378	1
2000/1/20	1.136276	1.018108	0.890376	0.543905	1.411335	1

It might be a simple conceptual mistake, but I have been struggling for some time, anyone could give me some hints?

Btw, I also encountered with @NDelventhal's problem (only 27 firms remains when extracting data from 1999 in Yahoo finance). My final trade-off is to let my raw data start from 2000 so that I can keep one more firm in my sample, and because of this my 250-days rolling beta starts from around 2001.

[joachim-gassen]

Congratulations! You got me. I had a bug in my code. Your code is fine. Guess what I did: I flipped Y and X in the regression. Sorry for the confusion. Once I saw your picture I had the hunch that your result is right as we are estimating the betas for the full market and they should average to one. Apologies for the confusion but let's take this as an example that replication matters ;-)

I updated the shiny app to reflect the correct analysis. You will see that my results do not exactly add up to one this is as I allow some missingness in returns.

[fengzhi22]

Thanks for the clarification @joachim-gassen ;).

[joachim-gassen]

Hi there, everybody. Thank you to everybody who took a shot at this assignment. I already sent you my remarks individually.

I just pushed my solution to Github. It can be found here. Feel free to raise questions about the code here.