A05_CAPM.Rmd

---
title: "Portfoliomanagement and Financial Analysis - Assignment 5"
subtitle: "Submit until Monday 2019-10-29, 13:00"
author: "Maurizio Sozzi"
output: html_notebook
---
  
```{r load_packs}
#remotes::install_github("braverock/PortfolioAnalytics",  build_vignettes = TRUE, force = TRUE)
pacman::p_load(tidyverse,tidyquant,FFdownload,PortfolioAnalytics,nloptr,readxl,quantmod,FFdownload,timetk, dplyr, xts)
```

**Please** remember to put your assignment solutions in `rmd` format using **many** chunks and putting readable text in between, similar to my examples given in Research Methods and Assignment 1! Also, each student has to select his own set of 10 stocks having data available as of `2000-01-01`. Select by Sharpe-ratio, dominance or any other method (e.g. matching your first name).

For all exercises: Please use the Assignment-Forum to post your questions, I will try my best to help you along!
  
## Exercise 1: Constraints
  
Have a look at `vignette("portfolio_vignette")`. Use your dataset to compute 

get the stocks
```{r}
stockselection <- c("AAPL", "MSFT", "JPM", "AMZN", "WMT", "PFE", "INTC", "BAC", "LOGI", "NKE")
```

get the stock prices and create the monthly return
```{r}
stockprices <- stockselection %>%
  tq_get(get = "stock.prices", from ="2000-01-01", to ="2020-08-31") %>%
  group_by(symbol)

stocks.returns.monthly <- stockprices %>%  
  tq_transmute(select = adjusted,
               mutate_fun = periodReturn,
               period="monthly",
               type="arithmetic",
               col_rename = "Stock.returns")

stock.returns.timeseries.xts <- pivot_wider(data = stocks.returns.monthly, names_from = symbol, values_from = Stock.returns)%>%
  tk_xts(date_var = date, silent = TRUE)
```




a) Minimum-Variance 
```{r}
portminvar1 <- portfolio.spec(assets = colnames(stock.returns.timeseries.xts))

portminvar1 <- add.constraint(portfolio = portminvar1,
type = "long_only")

portminvar1 <- add.objective(portfolio = portminvar1, type = "risk", name = "var")

opt_minvar1 <- optimize.portfolio(R=stock.returns.timeseries.xts, portfolio = portminvar1, optimize_method = "ROI", trace = TRUE)


print(opt_minvar1)
```
Efficient Frontier
```{r}
portminvar1EF <- create.EfficientFrontier(R=stock.returns.timeseries.xts, portfolio=portminvar1, type="mean-StdDev", match.col = "StdDev")
chart.EfficientFrontier(portminvar1EF, match.col="StdDev", type="b", rf=NULL, pch.assets = 1)
```
plot the MVP with long only constraint
```{r}
plot(opt_minvar1, risk.col="StdDev", return.col="mean",
      main="Minimum Variance Optimization long only", chart.assets=TRUE,
      xlim=c(0, 0.17), ylim=c(0,0.018))
```

rebalancing in the long only constraint
```{r}
rb_minvar1 <- optimize.portfolio.rebalancing(R=stock.returns.timeseries.xts, portfolio = portminvar1, optimize_method = "ROI", rebalance_on = "months", training_period = 156)

rb_minvar1

chart.Weights(rb_minvar1)
```

Minimum Varriance with full investment constraint. We go short in BJPM and Amazon.
```{r}
portminvar2 <- portfolio.spec(assets = colnames(stock.returns.timeseries.xts))

portminvar2 <- add.constraint(portfolio = portminvar2,
type = "full_investment")

portminvar2 <- add.objective(portfolio = portminvar2, type = "risk", name = "var")

opt_minvar2 <- optimize.portfolio(R=stock.returns.timeseries.xts, portfolio = portminvar2, optimize_method = "ROI", trace = TRUE)


print(opt_minvar2)
```
Plot the MVP with full investment contraints
```{r}
plot(opt_minvar2, risk.col="StdDev", return.col="mean",
      main="Minimum Variance Optimization full investment", chart.assets=TRUE,
      xlim=c(0, 0.17), ylim=c(0,0.018))
```

rebalancing in the full investment constraint
```{r}
rb_minvar2 <- optimize.portfolio.rebalancing(R=stock.returns.timeseries.xts, portfolio = portminvar2, optimize_method = "ROI", rebalance_on = "month", training_period = 156)

rb_minvar2

chart.Weights(rb_minvar2)
```
Group constraint 
```{r}
portminvar3 <- portfolio.spec(assets =colnames(stock.returns.timeseries.xts) )
portminvar3 <- add.constraint(portfolio = portminvar3,
                              type ="group",
                              groups=list(groupA=c(5,7,8,10),
                                          groupB=c(1,2,4),
                                          groupC=c(3,6,9)),
                              group_min=c(0.3,0.2,0),
                              group_max=c(0.8,0.6,0.5))
portminvar3 <- add.objective(portfolio =portminvar3, type ="risk", name ="var")

opt_minvar3 <- optimize.portfolio(R=stock.returns.timeseries.xts, portfolio = portminvar3, optimize_method = "ROI", trace = TRUE)


print(opt_minvar3)
```
```{r}
plot(opt_minvar1, risk.col="StdDev", return.col="mean",
      main="Minimum Variance Optimization group", chart.assets=TRUE,
      xlim=c(0, 0.17), ylim=c(0,0.018))
```

rebalancing group constraint
```{r}
rb_minvar3 <- optimize.portfolio.rebalancing(R=stock.returns.timeseries.xts, portfolio = portminvar3, optimize_method = "ROI", rebalance_on = "months", training_period = 156)

rb_minvar3

chart.Weights(rb_minvar3)
```
now we put two constrain together
```{r}
portminvar4 <- portfolio.spec(assets =colnames(stock.returns.timeseries.xts) )

portminvar4 <- add.constraint(portfolio =portminvar4, type ="box", min =0, max=1)
portminvar4 <- add.objective(portfolio =portminvar4, type ="risk", name ="var")
portminvar4 <- add.constraint(portfolio = portminvar4,
                              type ="group",
                              groups=list(groupA=c(5,7,8,10),
                                          groupB=c(1,2,4),
                                          groupC=c(3,6,9)),
                              group_min=c(0.3,0.2,0),
                              group_max=c(0.8,0.6,0.5))


opt_minvar4 <- optimize.portfolio(R=stock.returns.timeseries.xts, portfolio = portminvar4, optimize_method = "ROI", trace = TRUE)


print(opt_minvar4)
```

```{r}
plot(opt_minvar4, risk.col="StdDev", return.col="mean",
      main="Minimum Variance Optimization box & group", chart.assets=TRUE,
      xlim=c(0, 0.17), ylim=c(0,0.018))
```


```{r}
rb_minvar4 <- optimize.portfolio.rebalancing(R=stock.returns.timeseries.xts, portfolio = portminvar4, optimize_method = "ROI", rebalance_on = "months", training_period = 156)

rb_minvar4

chart.Weights(rb_minvar4)
```


b) Maximum Quadratic Utility Portfolios

adding (individually/together) all the different constraints and highlighting the different portfolios (portfolios performances) including rebalancing over an appropriate time-frame. The goal is, that after doing this exercise you are familiar with the constraints and their consequences.

###first we used the full investment constraint
###second we combined it with the long-only constraint
```{r}
# Create initial portfolio object
init_portf <- portfolio.spec(assets =colnames(stock.returns.timeseries.xts))
# Create full investment constraint
fi_constr <- weight_sum_constraint(type="full_investment")
# Create long only constraint
lo_constr <- box_constraint(type="long_only", assets=init_portf$assets)
# Combine the constraints in a list
qu_constr <- list(fi_constr, lo_constr)
# Create return objective
ret_obj <- return_objective(name="mean")
# Create variance objective specifying a risk_aversion parameter which controls
# how much the variance is penalized
var_obj <- portfolio_risk_objective(name="var", risk_aversion=1.50) #play around with the risk_aversion number and see how the weights changes
# Combine the objectives into a list
qu_obj <- list(ret_obj, var_obj)

```

```{r}
# Run the optimization
opt_qu <- optimize.portfolio(R=stock.returns.timeseries.xts, portfolio=init_portf,
                             constraints=qu_constr,
                             objectives=qu_obj,
                             optimize_method="ROI",
                             trace=TRUE)
opt_qu
```
```{r}
bt_qu <- optimize.portfolio.rebalancing(R=stock.returns.timeseries.xts, portfolio=init_portf,
                                        constraints=qu_constr,
                                        objectives=qu_obj,
                                        optimize_method="ROI",
                                        rebalance_on="months",
                                        training_period=156)
print(bt_qu)

chart.Weights(bt_qu)
returns_qu <- Return.portfolio(R = stock.returns.timeseries.xts, weights = extractWeights(bt_qu))
```



## Exercise 2: Estimating the CAPM

In this exercise we want to estimate the CAPM. Please read carefully through the two documents provided (right hand side: files). Then we start to collect the necessary data:
  
a) From Datastream get the last 10 years of data from the 100 stocks of the S&P100 using the list `LS&P100I` (S&P 100): total return index (RI) and market cap (MV)
b) Further import the Fama-French-Factors from Kenneth Frenchs homepage (monthly, e.g. using `FFdownload`). From both datasets we select data for the last (available) 60 months, calculate returns (simple percentage) for the US-Stocks and eliminate those stocks that have NAs for this period.
c) Now subtract the risk-free rate from all the stocks. Then estimate each stocks beta with the market: Regress all stock excess returns on the market excess return and save all betas (optimally use `mutate` and `map` in combination with `lm`). Estimate the mean-return for each stock and plot the return/beta-combinations. Create the security market line and include it in the plot! What do you find?
d) In a next step (following both documents), we sort the stocks according to their beta and build ten value-weighted portfolios (with more or less the same number of stocks). Repeat a) for the ten portfolios. What do you observe?
e) In the third step you follow page 6-8 of the second document and estimate the second-pass regression with the market and then market & idiosyncratic risk. What do you observe? Present all your results in a similar fashion as in the document.


  
a) From Datastream get the last 10 years of data from the 100 stocks of the S&P100 using the list `LS&P100I` (S&P 100): total return index (RI) and market cap (MV)

```{r}
sp100_daily_RI <- read_excel("sp100 daily RI_2.xlsx")
head(sp100_daily_RI, n=10)
```

b) Further import the Fama-French-Factors from Kenneth Frenchs homepage (monthly, e.g. using `FFdownload`). From both datasets we select data for the last (available) 60 months, calculate returns (simple percentage) for the US-Stocks and eliminate those stocks that have NAs for this period.

```{r}
# FF_worker because the sstoeckl/FFdownload had an issue in the converter (daily,..)
FF_worker <- function(output_file = "data.Rdata", tempdir=NULL) {
csv_files <- list.files(tempdir, full.names = TRUE, pattern = "\\.csv$", ignore.case = TRUE) # full path
csv_files2 <- list.files(tempdir, full.names = FALSE, pattern = "\\.csv$", ignore.case = TRUE) # only filenames
vars <- paste0("x_", gsub("(.*)\\..*", "\\1", csv_files2)  )
FF_worker <- mlply(function(y) converter(y), .data=csv_files, .progress = "text")
names(FF_worker) <- vars
save(FF_worker, file = output_file)
}
converter <- function(file) {
data <- readLines(file)
data[1] <- paste0(data[1],",")
index <- grep(",", data)
new_index <- sort(unique(c(index - 1, index)))
headers <- new_index[!(new_index %in% index)]
headers1 <- headers + 1
headers2 <- c(headers[-1] - 1,  max(new_index))
headers_names <- headers; headers_names[1] <- 1
l <- mapply(function(x, y) data[x:y], headers1, headers2, SIMPLIFY = FALSE)
names <- gsub("(.*) -- .*",  "\\1" , trimws(data[headers_names]))
names <- tolower(gsub(" ", "_", names))
if(any(names == "average_market_cap")) {
  other <- (which(names == "average_market_cap") + 1):length(headers)
  headers[other] <- paste0(headers[other] - 5, ":", headers[other])
  names <- vector()
  for(i in seq_along(headers)) names[i] <- gsub("(.*) -- .*",  "\\1" , paste0( trimws( data[eval(parse(text = headers[i]))]),collapse = "" ))
  names <- tolower(gsub(" ", "_", names))
}
if(any(names == "")){for (i in 1:length(names)){if(names[i]==""){names[i] <- paste0("Temp",i)}}}
names(l) <- names
datatest <- try(lapply(l[2:length(l)], function(x) na.omit(read.csv(text = x,  stringsAsFactors = FALSE, skip=0))), silent = TRUE)
if (class(datatest) == "try-error") {
  datatest <- lapply(l, function(x) na.omit(read.csv(text = x,  stringsAsFactors = FALSE, header = FALSE)))
}
datatest <- datatest[sapply(datatest, nrow) > 0]
d <- vector()
for (i in 1:length(datatest)) d[i] <- nchar(as.character(datatest[[i]]$X[1])) == 8
for (i in 1:length(datatest)) d[i] <- nchar(as.character(datatest[[i]][1,1])) == 8
m <- vector()
for (i in 1:length(datatest)) m[i] <- nchar(as.character(datatest[[i]]$X[1])) == 6
for (i in 1:length(datatest)) m[i] <- nchar(as.character(datatest[[i]][1,1])) == 6
a <- vector()
for (i in 1:length(datatest)) a[i] <- nchar(as.character(datatest[[i]]$X[1])) == 4
for (i in 1:length(datatest)) a[i] <- nchar(as.character(datatest[[i]][1,1])) == 4
annual  <- lapply(datatest[unlist(a)], function(x) xts::xts(as.data.frame(lapply(x[,-1,drop=FALSE],as.numeric)), order.by = as.yearmon(as.character(x[, 1]), format = "%Y")) )
monthly <- lapply(datatest[unlist(m)], function(x) xts::xts(as.data.frame(lapply(x[,-1,drop=FALSE],as.numeric)), order.by = as.yearmon(as.character(x[, 1]), format = "%Y%m")))
return(list(annual = annual, monthly = monthly))
}
```

download the four Fama-French factors including the risk-free rate.
```{r}
FFdownload(exclude_daily=TRUE,tempdir=tempdir(),download=TRUE,download_only=TRUE)
FF_worker(output_file = "FFdata.RData", tempdir=tempdir())
load(file = "FFdata.RData")

factors <- FF_worker$`x_F-F_Research_Data_Factors`$monthly$Temp2 %>% 
        tk_tbl(rename_index="date") %>% # make tibble
        mutate(date=as.Date(date, frac=1)) %>% # make proper month-end date format
        mutate(Mkt.RF=Mkt.RF/100, RF=RF/100, HML=HML/100, SMB=SMB/100)
        #gather(key=FFvar,value = price,-date) # gather into tidy format
k
```

From both datasets we select data for the last (available) 60 months, calculate returns (simple percentage) for the US-Stocks and eliminate those stocks that have NAs for this period.
```{r}

anyNA(sp100_daily_RI)
sp100_daily_RI_prices <- gather(sp100_daily_RI, key = symbol, value= prices, "AMAZON.COM":"CHARTER COMMS.CL.A")
anyNA(sp100_daily_RI_prices)
```

```{r}
sp100_returns_RI_60_long <- sp100_daily_RI_prices %>% mutate(prices = as.numeric(prices)) %>% group_by(symbol) %>%
 tq_transmute(select = prices,
              mutate_fun = periodReturn, 
              period="monthly", 
              type="arithmetic",
              col_rename = "Stock.returns") %>% ungroup() %>% mutate(date = as.yearmon(date))
anyNA(sp100_returns_RI_60_long)
sp100_returns_RI_60_long <- sp100_returns_RI_60_long[c(2,1,3)] %>% group_by(symbol)
fama_french <- factors %>%
   select(date, Mkt.RF, RF) %>% mutate(date = as.yearmon(date))
```

c) Now subtract the risk-free rate from all the stocks. Then estimate each stocks beta with the market: Regress all stock excess returns on the market excess return and save all betas (optimally use `mutate` and `map` in combination with `lm`). Estimate the mean-return for each stock and plot the return/beta-combinations. Create the security market line and include it in the plot! What do you find?

```{r}

joined_data <- left_join(sp100_returns_RI_60_long, fama_french, by= c("date"))
joined_data <- mutate(joined_data, 
      monthly_ret_rf = Stock.returns - RF)
require(xts)
regr_fun <- function(data_xts) {
   lm(monthly_ret_rf ~ Mkt.RF, data = as_data_frame(data_xts)) %>%
       coef()
}
beta_alpha <- joined_data %>% 
   tq_mutate(mutate_fun = rollapply,
             width      = 60,
             FUN        = regr_fun,
             by.column  = FALSE,
             col_rename = c("alpha", "beta"))
beta_alpha
```

```{r}
beta_alpha_filter <- filter(beta_alpha, !is.na(alpha))
symbol_beta_alpha <- beta_alpha_filter %>%
   select(symbol, alpha, beta)
symbol_beta_alpha 
```

```{r}
alpha <- beta_alpha %>%
   select(symbol, alpha)
```

```{r}
beta <- beta_alpha_filter %>%
   select(symbol, beta)
beta
```

```{r}

means_sp100_RI_60 <- joined_data %>%
   group_by(symbol) %>%
   summarize(mu = mean(monthly_ret_rf, na.rm=TRUE))
means_sp100_RI_60
```

```{r}
mu.hat <- mutate(beta_alpha, 
      mu_capm = beta * mean(Mkt.RF))
mu.hat <- filter(mu.hat, !is.na(alpha))
mu.hat <- mu.hat  %>%
   select(symbol, alpha, beta, mu_capm)
mu.hat <- left_join(mu.hat, means_sp100_RI_60, by = "symbol")
sml.fit <- lm(mu_capm~beta, mu.hat)
install.packages("plotly")
library(plotly)
p <- plot_ly(mu.hat, x = ~beta, y = ~mu_capm, type = 'scatter', mode = 'line', text = ~paste('symbol:', symbol)) %>%
   add_markers(x = ~beta, y = ~mu)
p
```

d) In a next step (following both documents), we sort the stocks according to their beta and build ten value-weighted portfolios (with more or less the same number of stocks). Repeat a) for the ten portfolios. What do you observe?

```{r}
sp100_monthly_MV <- read_excel("sp100 monthly MV.xlsx")
head(sp100_monthly_MV, n=10)
anyNA(sp100_monthly_MV)
sp100_monthly_MV <- gather(sp100_monthly_MV, key = symbol, value= value, "AMAZON.COM":"CHARTER COMMS.CL.A")
anyNA(sp100_daily_RI_prices)
```
mean value
```{r}
mean_sp100_MV <- sp100_monthly_MV %>% 
   group_by(symbol) %>%
   summarize(mean_value = mean(value, na.rm=TRUE))
```

```{r}
symbol_beta_alpha_value <- left_join(mean_sp100_MV, beta,  by= "symbol")
```

```{r}
symbol_beta_alpha_value <- arrange(symbol_beta_alpha_value, beta)
symbol_beta_alpha_value
```

create weights
```{r}
Portfolio1 <- symbol_beta_alpha_value[1:10,]
sum_weights1 <- sum(Portfolio1$mean_value)
weight_portfolio1 <- Portfolio1$mean_value/sum_weights1
Portfolio2 <- symbol_beta_alpha_value[11:20,]
sum_weights2 <- sum(Portfolio2$mean_value)
weight_portfolio2 <- Portfolio2$mean_value/sum_weights2
Portfolio3 <- symbol_beta_alpha_value[21:30,]
sum_weights3 <- sum(Portfolio3$mean_value)
weight_portfolio3 <- Portfolio3$mean_value/sum_weights3
Portfolio4 <- symbol_beta_alpha_value[31:40,]
sum_weights4 <- sum(Portfolio4$mean_value)
weight_portfolio4 <- Portfolio4$mean_value/sum_weights4
Portfolio5 <- symbol_beta_alpha_value[41:50,]
sum_weights5 <- sum(Portfolio5$mean_value)
weight_portfolio5 <- Portfolio5$mean_value/sum_weights5
Portfolio6 <- symbol_beta_alpha_value[51:60,]
sum_weights6 <- sum(Portfolio6$mean_value)
weight_portfolio6 <- Portfolio6$mean_value/sum_weights6
Portfolio7 <- symbol_beta_alpha_value[61:70,]
sum_weights7 <- sum(Portfolio7$mean_value)
weight_portfolio7 <- Portfolio7$mean_value/sum_weights7
Portfolio8 <- symbol_beta_alpha_value[71:80,]
sum_weights8 <- sum(Portfolio8$mean_value)
weight_portfolio8 <- Portfolio8$mean_value/sum_weights8
Portfolio9 <- symbol_beta_alpha_value[81:90,]
sum_weights9 <- sum(Portfolio9$mean_value)
weight_portfolio9 <- Portfolio9$mean_value/sum_weights9
Portfolio10 <- symbol_beta_alpha_value[91:nrow(symbol_beta_alpha_value),]
sum_weights10 <- sum(Portfolio10$mean_value)
weight_portfolio10 <- Portfolio10$mean_value/sum_weights10
```


returns Portfolio
```{r}
sp100_returns_RI_60_wide <- sp100_returns_RI_60_long %>% spread(symbol, Stock.returns)
portfolio1_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio1$symbol)]
portfolio1_returns_long <- gather(portfolio1_returns_wide, key = symbol, value= returns, c(Portfolio1$symbol))
portfolio1_returns<- portfolio1_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio1,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 1")
portfolio2_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio2$symbol)]
portfolio2_returns_long <- gather(portfolio2_returns_wide, key = symbol, value= returns, c(Portfolio2$symbol))
portfolio2_returns<- portfolio2_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio2,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 2")
portfolio3_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio3$symbol)]
portfolio3_returns_long <- gather(portfolio3_returns_wide, key = symbol, value= returns, c(Portfolio3$symbol))
portfolio3_returns<- portfolio3_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio3,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 3")
portfolio4_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio4$symbol)]
portfolio4_returns_long <- gather(portfolio4_returns_wide, key = symbol, value= returns, c(Portfolio4$symbol))
portfolio4_returns<- portfolio4_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio4,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 4")
portfolio5_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio5$symbol)]
portfolio5_returns_long <- gather(portfolio5_returns_wide, key = symbol, value= returns, c(Portfolio5$symbol))
portfolio5_returns<- portfolio5_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio5,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 5")
portfolio6_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio6$symbol)]
portfolio6_returns_long <- gather(portfolio6_returns_wide, key = symbol, value= returns, c(Portfolio6$symbol))
portfolio6_returns<- portfolio6_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio6,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 6")
portfolio7_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio7$symbol)]
portfolio7_returns_long <- gather(portfolio7_returns_wide, key = symbol, value= returns, c(Portfolio7$symbol))
portfolio7_returns<- portfolio7_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio7,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 7")
portfolio8_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio8$symbol)]
portfolio8_returns_long <- gather(portfolio8_returns_wide, key = symbol, value= returns, c(Portfolio8$symbol))
portfolio8_returns<- portfolio8_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio8,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 8")
portfolio9_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio9$symbol)]
portfolio9_returns_long <- gather(portfolio9_returns_wide, key = symbol, value= returns, c(Portfolio9$symbol))
portfolio9_returns<- portfolio9_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio9,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 9")
portfolio10_returns_wide <- sp100_returns_RI_60_wide[c("date", Portfolio10$symbol)]
portfolio10_returns_long <- gather(portfolio10_returns_wide, key = symbol, value= returns, c(Portfolio10$symbol))
portfolio10_returns<- portfolio10_returns_long %>%
 tq_portfolio(assets_col = symbol,
              returns_col = returns,
              weights = weight_portfolio10,
              col_rename = "Portfolio.returns") %>% mutate(Portfolio = "Portfolio 10")
allportfolio_returns <- rbind(portfolio1_returns, portfolio2_returns, portfolio3_returns, portfolio4_returns, portfolio5_returns, portfolio6_returns, portfolio7_returns, portfolio8_returns, portfolio9_returns, portfolio10_returns)
allportfolio_returns <- allportfolio_returns %>% group_by(Portfolio)
allportfolio_returns
```

```{r}
joined_data_portfolio <- left_join(allportfolio_returns, fama_french, by= c("date"))
joined_data_portfolio <- mutate(joined_data_portfolio, 
      monthly_ret_rf = Portfolio.returns - RF)
require(xts)
regr_fun_portfolio <- function(data_xts) {
   lm(monthly_ret_rf ~ Mkt.RF, data = as_data_frame(data_xts)) %>%
       coef()
}
beta_alpha_portfolio <- joined_data_portfolio %>% 
   tq_mutate(mutate_fun = rollapply,
             width      = 60,
             FUN        = regr_fun_portfolio,
             by.column  = FALSE,
             col_rename = c("alpha", "beta"))
beta_alpha_portfolio
```


```{r}
beta_alpha_portfolio_filter <- filter(beta_alpha_portfolio, !is.na(alpha))
symbol_beta_alpha_portfolio <- beta_alpha_portfolio_filter %>%
   select(Portfolio, alpha, beta)
symbol_beta_alpha_portfolio
```

```{r}
alpha_portfolio <- beta_alpha_portfolio %>%
   select(Portfolio, alpha)
```


```{r}
beta_portfolio <- beta_alpha_portfolio %>%
   select(Portfolio, beta)
```

```{r}
means_Portfolio <- joined_data_portfolio %>%
   group_by(Portfolio) %>%
   summarize(mu = mean(monthly_ret_rf, na.rm=TRUE))
means_Portfolio
```

```{r}
return_beta_portfolio <- left_join(means_Portfolio, beta_portfolio, by="Portfolio")
mu.hat_portfolio <- mutate(beta_alpha_portfolio, 
      mu_capm_portfolio = beta * mean(Mkt.RF))
mu.hat_portfolio <- filter(mu.hat_portfolio, !is.na(alpha))
mu.hat_portfolio <- mu.hat_portfolio  %>%
   select(Portfolio, alpha, beta, mu_capm_portfolio)
mu.hat_portfolio <- left_join(mu.hat_portfolio, means_Portfolio, by = "Portfolio")
sml.fit_portfolio <- lm(mu_capm_portfolio~beta, mu.hat_portfolio)
library(plotly)
p_portfolio <- plot_ly(mu.hat_portfolio, x = ~beta, y = ~mu_capm_portfolio, type = 'scatter', mode = 'line', text = ~paste('Portfolio:', Portfolio)) %>%
   add_markers(x = ~beta, y = ~mu)
p_portfolio
```

e) In the third step you follow page 6-8 of the second document and estimate the second-pass regression with the market and then market & idiosyncratic risk. What do you observe? Present all your results in a similar fashion as in the document.

```{r}
regr_fun_residuals <- function(data_xts) {
   data_xts <- lm(monthly_ret_rf ~ Mkt.RF, data = as_data_frame(data_xts))
R <- summary(data_xts)$sigma^2
return(R)
}
residuals <- joined_data %>% 
   tq_mutate(mutate_fun = rollapply,
             width      = 60,
             FUN        = regr_fun_residuals,
             by.column  = FALSE,
             col_rename = c("Residuals"))
residuals_only <- filter(residuals, !is.na(Residuals))
symbol_residuals <- residuals_only %>%
   select(symbol, Residuals)
mean_MKt.RF <- joined_data %>%
   group_by(symbol) %>%
   summarize(mu_MKt_RF = mean(Mkt.RF, na.rm=TRUE))
first <- left_join(symbol_beta_alpha, symbol_residuals, by = "symbol" )
second <- left_join(mean_MKt.RF, means_sp100_RI_60,  by = "symbol" )
all_inputs <- left_join(first, second,  by = "symbol" )
second_pass_regression <- lm(mu~ beta + Residuals, all_inputs)
summary(second_pass_regression)
```



## Exercise 3: Calculating and checking the CAPM cont.

As we have seen: the CAPM for small portfolios does not work very well, and so we start using portfolios that get rid of the idiosyncratic risk!
Go to Kenneth French's Homepage  again and download the following datasets: "Portfolios Formed on Market Beta" (where we will use 10 monthly value weighted portfolios formed on beta) and "25 Portfolios Formed on Size and Market Beta" (same thing) as well as the market factor and rf (as before). Now we are going to check the CAPM like famous researchers have done it!
We can use returns as they are in the files (simple returns)!

```{r}
#Download the Portfolios from Kenneth French's Homepage
portf_mkt_beta <- "https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/Portfolios_Formed_on_BETA_CSV.zip"
 portf_mkt_beta_csv <- "Portfolios_Formed_on_BETA.csv"
 temp <- tempfile()
download.file(portf_mkt_beta, temp, quiet = TRUE)
portf_mkt_beta <- read_csv(unz(temp, portf_mkt_beta_csv), skip = 15, quote = "\",") %>%
  dplyr::rename(date = "X1") %>%
  mutate_at(vars(-date), as.numeric) %>%
  mutate(date = rollback(ymd(parse_date_time(date, "%Y%m") + months(1))))%>%
  filter(date >= first('1964-01-01') & date <= '2019-12-31')

#Download the market factor and rf (Fama/French 3 Research Factors)
mkt_factors <- "https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_CSV.zip"
 mkt_factors_csv <- "F-F_Research_Data_Factors.CSV"
 temp <- tempfile()
download.file(mkt_factors, temp, quiet = TRUE)
mkt_factors <- read_csv(unz(temp, mkt_factors_csv), skip = 3, quote = "\",") %>%
  dplyr::rename(date = X1) %>%
  mutate_at(vars(-date), as.numeric) %>%
  mutate(date = rollback(ymd(parse_date_time(date, "%Y%m") + months(1)))) %>%
  filter(date >= first('1964-01-01') & date <= '2019-12-31')


```


a)	Subtract the risk-free rate from the first set of 10 portfolios (only sorted on beta) (Lo 10,., Hi 10) and estimate each stocks beta with the market. Estimate the mean-return for each stock and plot the return/beta-combinations. Create the security market line and include it in the plot! What do you find? (You can split the file in 2-3 different time blocks and see if something changes). * Now we are done with the first-pass regression.*


Subtract the risk-free rate from the first set of 10 portfolios (only sorted on beta) (Lo 10,., Hi 10) and estimate each stocks beta with the market.

```{r}
#join data
ten_portf <- portf_mkt_beta[1:672, -c(2:6)]
ten_portf_joined <- left_join(mkt_factors, ten_portf)

mkt_factors
ten_portf
ten_portf_joined

```
```{r, echo=FALSE}
ten_portf_joined <- ten_portf_joined <- ten_portf_joined%>% dplyr::rename("Lo10" = "Lo 10") %>% dplyr::rename("Dec2" = "Dec 2") %>% dplyr::rename("Dec3" = "Dec 3") %>% dplyr::rename("Dec4" = "Dec 4") %>% dplyr::rename("Dec5" = "Dec 5") %>% dplyr::rename("Dec6" = "Dec 6") %>% dplyr::rename("Dec7" = "Dec 7") %>% dplyr::rename("Dec8" = "Dec 8") %>% dplyr::rename("Dec9" = "Dec 9") %>% dplyr::rename("Hi10" = "Hi 10")

view(ten_portf_joined)
ten_portf_joined

```

```{r}
#substract Risk-Free-Rate
ten_portf_rf <- mutate(ten_portf_joined, Lo10rf = Lo10 - RF, Dec2rf = Dec2 - RF, Dec3rf = Dec3 - RF, Dec4rf = Dec4 - RF, Dec5rf = Dec5 -RF, Dec6rf = Dec6 - RF, Dec7rf = Dec7 - RF, Dec8rf = Dec8 - RF, De9rf = Dec9 - RF, Hi10rf = Hi10 - RF)
ten_portf_rf <- ten_portf_rf[-2:-15]

view(ten_portf_rf)
ten_portf_rf
```

```{r, echo=FALSE}
#Create XTS
mkt_factors_xts <- tk_xts(data = mkt_factors, date_var = date)
ten_portf_rf_xts <- ten_portf_rf %>%
  tk_xts(date_var = date, silent = TRUE)

```
```{r}
?lm()
#Calculate Betas for each portfolio
betas_ten_portf_lm <- lm(ten_portf_rf_xts ~ mkt_factors_xts[, 1])
betas_ten_portf_lm
betas_ten_portf <- CAPM.beta(Ra = ten_portf_rf_xts, Rb = mkt_factors_xts[, 1], Rf = 0)
betas_ten_portf
```
Estimate the mean-return for each stock and plot the return/beta-combinations.

```{r}
#Estimate Mean Return
mean_ten_portf_rf_xts <- as.data.frame(lapply(ten_portf_rf_xts, FUN=mean))
mean_ten_portf_rf_xts

#Plot the return/beta-combinations
plot.default(x = betas_ten_portf, xlim=c(0, 2),
             y = mean_ten_portf_rf_xts, ylim=c(0, 1),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations")
```
Create the security market line and include it in the plot! What do you find?

```{r}
mean_mkt <- as.data.frame(lapply(mkt_factors_xts[, 1], FUN=mean))
y_mkt <- mean_mkt[1, 1]
plot.default(x = betas_ten_portf, xlim=c(0, 2),
             y = mean_ten_portf_rf_xts, ylim=c(0, 1),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations",
             abline(0, y_mkt))
plot.default(x = betas_ten_portf, xlim=c(0, 2), 
             y = mean_ten_portf_rf_xts, ylim=c(0, 10), 
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations",
             abline(0, y_mkt))

#summary
summary_CAPM_ten_portf <- (table.CAPM(Ra = ten_portf_rf_xts, Rb = mkt_factors_xts[, 1], Rf = 0)[1:9, ])
```
(You can split the file in 2-3 different time blocks and see if something changes). * Now we are done with the first-pass regression.*

```{r}
#look for first 10 years
ten_portf_rf_10yrs_xts <- ten_portf_rf[1:120, ] %>%
  tk_xts(date_var = date, silent = TRUE)
betas_ten_portf_rf_10yrs <- CAPM.beta(Ra = ten_portf_rf_10yrs_xts, Rb = mkt_factors_xts[1:120, 1], Rf = 0)
mean_ten_portf_rf_10yrs_xts <- as.data.frame(lapply(ten_portf_rf_10yrs_xts, FUN=mean))
mean_mkt_10yrs <- as.data.frame(lapply(mkt_factors_xts[1:120, 1], FUN=mean))
y_mkt_10yrs <- mean_mkt_10yrs[1, 1]
plot.default(x = betas_ten_portf_rf_10yrs, xlim=c(0, 2),
             y = mean_ten_portf_rf_10yrs_xts, ylim=c(0, 1),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations 1964-1974",
             abline(0, y_mkt_10yrs))
summary_CAPM_ten_portf_10yrs <- (table.CAPM(Ra = ten_portf_rf_xts[1:120, ], Rb = mkt_factors_xts[1:120, 1], Rf = 0)[1:9, ])
summary_CAPM_ten_portf_10yrs
```
```{r, echo=FALSE}

#look for 2000-2019
ten_portf_rf_2000_xts <- ten_portf_rf[433:672, ] %>%
  tk_xts(date_var = date, silent = TRUE)
betas_ten_portf_rf_2000 <- CAPM.beta(Ra = ten_portf_rf_2000_xts, Rb = mkt_factors_xts[433:672, 1], Rf = 0)
mean_ten_portf_rf_2000_xts <- lapply(ten_portf_rf_2000_xts, FUN=mean)
mean_ten_portf_rf_2000_xts <- as.data.frame(mean_ten_portf_rf_2000_xts)
mean_mkt_2000 <- lapply(mkt_factors_xts[433:672, 1], FUN=mean)
mean_mkt_2000 <- as.data.frame(mean_mkt_2000)
y_mkt_2000 <- mean_mkt_2000[1, 1]
plot.default(x = betas_ten_portf_rf_2000, xlim=c(0, 2),
             y = mean_ten_portf_rf_2000_xts, ylim=c(0, 1),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations 2000-2019",
             abline(0, y_mkt_2000))
summary_CAPM_ten_portf_2000 <- (table.CAPM(Ra = ten_portf_rf_xts[433:672, ], Rb = mkt_factors_xts[433:672, 1], Rf = 0)[1:9, ])
summary_CAPM_ten_portf_2000

plot.default(x = betas_ten_portf, xlim=c(0, 2),
             y = mean_ten_portf_rf_xts, ylim=c(0, 1),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations 1964-2019",
             abline(0, y_mkt))
summary_CAPM_ten_portf


```


b)	In the second-pass regression we now regress the average stock returns on the betas estimated before. What do you find in the coefficients and does this contradict the CAPM? Try different time periods again and see what you find. (all of the interpretations are in BKM pp.416f). 

There are a number of reasons we expect might the CAPM to
fail:
1. Imperfect measures of the market portfolio
2. Beta is an incomplete measure of risk
3. Tax effects
4. Non - normality of returns
5. No riskless asset
6. Divergent borrowing and lending rates

c)	Now do the extended second pass regression (regress on betas and residual-sds that you can extract from the regression) and see what you find for different periods. Interpret according to concept check 13.2. One of the (many) problems of the CAPM can be the correlation between residual variances and betas. Calculate and interpret.

```{r}
#Look at a) -> We now do it with the mean return of every portfolio combined... 

#1964-2019
com_mean_ten_portf_rf <- sum(mean_ten_portf_rf_xts)/10
mean_betas_ten_portf <- sum(betas_ten_portf)/10
plot.default(x = mean_betas_ten_portf, xlim=c(0, 2),
             y = com_mean_ten_portf_rf, ylim=c(0, 2),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations 10 Portfolios 1964-2019",
             abline(0, y_mkt))


```

```{r, echo=FALSE}

#1964-1974
com_mean_ten_portf_rf_10yrs <- sum(mean_ten_portf_rf_10yrs_xts)/10
mean_betas_ten_portf_10yrs <- sum(betas_ten_portf_rf_10yrs)/10
plot.default(x = mean_betas_ten_portf_10yrs, xlim=c(0, 2),
             y = com_mean_ten_portf_rf_10yrs, ylim=c(0, 2),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations 10 Portfolios 1964-1974",
             abline(0, y_mkt_10yrs))

#2000-2019
com_mean_ten_portf_rf_2000 <- sum(mean_ten_portf_rf_2000_xts)/10
mean_betas_ten_portf_2000 <- sum(betas_ten_portf_rf_2000)/10
plot.default(x = mean_betas_ten_portf_2000, xlim=c(0, 2),
             y = com_mean_ten_portf_rf_2000, ylim=c(0, 2),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations 10 Portfolios 2000-2019",
             abline(0, y_mkt_2000))

```

```{r, echo=FALSE}

#SML-Function
calc_residual <- function(x) {y <- y_mkt*x}
calc_residual_10yrs <- function(x) {y <- y_mkt_10yrs*x}
calc_residual_2000 <- function(x) {y <- y_mkt_2000*x}
residual_1964_2019 <- as.data.frame((com_mean_ten_portf_rf - calc_residual(mean_betas_ten_portf))^2)
residual_1964_1974 <- as.data.frame((com_mean_ten_portf_rf_10yrs - calc_residual_10yrs(mean_betas_ten_portf_10yrs))^2)
residual_2000_2019 <- as.data.frame((com_mean_ten_portf_rf_2000 - calc_residual_2000(mean_betas_ten_portf_2000))^2)
joined_residuals <- merge(residual_1964_2018[1, 1], residual_1964_1974[1, 1])
joined_residuals <- merge(joined_residuals, residual_2000_2018)
Residuals_different_timeperiods <- joined_residuals %>% 
  dplyr::rename("Residual 2000-2019" = "(com_mean_ten_portf_rf_2000 - calc_residual_2000(mean_betas_ten_portf_2000))^2") %>% dplyr::rename("Residual 1964-2008" = "x") %>% dplyr::rename("Residual 1964-1974" = "y")
Residuals_different_timeperiods
```


d)	Try again with 25 portfolios sorted on size and beta. What do you find? Is that interesting? 

```{r}
#join data
twentyfive_portf <- portf_mkt_beta[1:672, -c(7:16)]
twentyfive_portf_joined <- left_join(mkt_factors, twentyfive_portf)
```

```{r, echo=FALSE}

twentyfive_portf_joined <- twentyfive_portf_joined <- twentyfive_portf_joined%>%
  dplyr::rename("Lo20" = "Lo 20") %>%
  dplyr::rename("Qnt2" = "Qnt 2") %>%
  dplyr::rename("Qnt3" = "Qnt 3") %>%
  dplyr::rename("Qnt4" = "Qnt 4") %>%
  dplyr::rename("Hi20" = "Hi 20")
````

```{r}
#substract Risk-Free-Rate
twentyfive_portf_rf <- mutate(twentyfive_portf_joined, Lo20rf = Lo20 - RF, Qnt2rf = Qnt2 - RF, Qnt3rf = Qnt3 - RF, Qnt4rf = Qnt4 - RF, Hi20rf = Hi20 - RF)
twentyfive_portf_rf <- twentyfive_portf_rf[-2:-10]

```

```{r, echo=FALSE}
#substract Risk-Free-Rate
twentyfive_portf_rf <- mutate(twentyfive_portf_joined, Lo20rf = Lo20 - RF, Qnt2rf = Qnt2 - RF, Qnt3rf = Qnt3 - RF, Qnt4rf = Qnt4 - RF, Hi20rf = Hi20 - RF)
twentyfive_portf_rf <- twentyfive_portf_rf[-2:-10]


#Create XTS
twentyfive_portf_rf_xts <- twentyfive_portf_rf %>%
  tk_xts(date_var = date, silent = TRUE)

#Calculate Betas for each portfolio
betas_twentyfive_portf <- CAPM.beta(Ra = twentyfive_portf_rf_xts, Rb = mkt_factors_xts[, 1], Rf = 0)

#Estimate Mean Return
mean_twentyfive_portf_rf_xts <- as.data.frame(lapply(twentyfive_portf_rf_xts, FUN=mean))

#Plot the return/beta-combinations
plot.default(x = betas_twentyfive_portf, xlim=c(0, 2),
             y = mean_twentyfive_portf_rf_xts, ylim=c(0, 1),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations 25",
             abline(0, y_mkt))

#We now do it with the mean return of every portfolio combined...
com_mean_twentyfive_portf_rf <- sum(mean_twentyfive_portf_rf_xts)/5
# and the beta
mean_betas_twentyfive_portf <- sum(betas_twentyfive_portf)/5

plot.default(x = mean_betas_ten_portf, xlim=c(0, 2),
             y = com_mean_ten_portf_rf, ylim=c(0, 1),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations Portfolio Summary 25",
             abline(0, y_mkt))
plot.default(x = mean_betas_ten_portf, xlim=c(0, 2),
             y = com_mean_ten_portf_rf, ylim=c(0, 1),
             xlab = "Beta", ylab = "Mean Return",
             main = "Return/Beta-combinations Portfolio Summary 10",
             abline(0, y_mkt))

```