Exercise - Equity Returns

---

1. Calculating Returns

Use the data in data/equity_data.xlsx

• tab prices AAPL: columns Unadjusted Price and Adjusted Price

• tab dividends AAPL: columns ex_date and dividend_amount

1.1

For the most recent 2 dividend dates, calculate the one-day return to AAPL using the unadjusted price and the dividend amount.

That is, for a given dividend, calculate the return for the day prior to the “ex date” and ending on the “ex date”. Do this for at least the most recent two dividends. (Though it is probably just as easy to adjust the return on every dividend date.)

How close is this to the return calculated from the adjusted price percentage change?

1.2

For the most recent stock split, (see multiple in dividend_amount and indicated in the column dividend_type,) calculate the return to AAPL using the unadjusted price along with this split and dividend info.

How close is this computed return to that calculated from the adjusted price growth?

---

2. Stock Sensitivity

Use the data in data/equity_data.xlsx, in the tab etf history.

For the ETFs listed in etf history, calculate their percentage change over time. (These are adjusted prices, so this is a measure of their returns.)

2.1

Report the correlation table.

2.2

In equities, there is an important metric: a stock’s beta:

\[r_t = \alpha + \beta r^{\text{SPY}}_t + \epsilon_t\]

This beta is analyzed with respect to many factors, but it is most widely analyzed with respect to the S&P 500 (or a similar, broad equity index. Thus the notation \(r^{\text{SPY}}_t\) on the right-hand side.

Calculation

For each ETF return series, estimate the regression above. (You will estimate many regressions, in a loop, each using SPY as the right-hand-side factor.)

Report these betas. Which ETF has the most overall stock-market risk?

Hint:

To estimate the regression, consider using the following approach:

from sklearn.linear_model import LinearRegression

LinearRegression().fit(X,y).coef_

---