Load data

beta  <-  read_excel(here::here("data","Problem_Set_4_data.xls"), # Excel filename
                            sheet="Question 4", # Sheet name
                            skip=2) # Number of rows to skip

beta = beta %>% 
  rename("GOOD"="GOOG...2","SPY"="SPY...3","r_GOOG"="GOOG...4","r_SPY"="SPY...5","er_GOOG"="GOOG...8","er_SPY"="SPY...9")

CAPM model plot

r(GOOG) - rf = beta * (r(SPY) - rf)

beta %>% 
  ggplot(aes(x=er_SPY, y=er_GOOG)) +
  geom_point()+
  geom_smooth(method="lm", se=FALSE) +
  scale_x_continuous(labels = scales::percent) +
  scale_y_continuous(labels = scales::percent) +
  labs(x = "excess return of SPY", 
       y= "excess return of GOOG", 
       title= "How is the relationship between Google (GOOG) and S&P 500 ETF (SPY)")

Calculate beta

#regression
model_beta=lm(er_GOOG ~ er_SPY, data= beta)
mosaic::msummary(model_beta)

##             Estimate Std. Error t value Pr(>|t|)    
## (Intercept)  0.00152    0.00185    0.82     0.41    
## er_SPY       0.94595    0.05803   16.30   <2e-16 ***
## 
## Residual standard error: 0.0298 on 259 degrees of freedom
##   (1 observation deleted due to missingness)
## Multiple R-squared:  0.506,  Adjusted R-squared:  0.504 
## F-statistic:  266 on 1 and 259 DF,  p-value: <2e-16

From the result of regression, we can see the beta of Google is 0.94595 and the Adjusted R-squared of this model is 0.504.