Open the templateW9.xlsx in Excel. This file has the specifications of the inputs for your program.
The “parameters” Sheet has the specification of 3 different datasets. For each dataset specification, you have the following inputs:
Start and end data to collect historical prices of stocks and the market
The frequency of the data to be brought
The Excel sheet of the ticker list to be brought from the web, and also the source.
The market ticker to be used to run the CAPM models for the stocks. Also, the risk-free rate ticker (from the FED) to be used to generate the premium returns for the CAPM.
The minimum and maximum weight to allow for each stock for the portfolio optimization
The number of stocks to be selected for the portfolio
The initial and end date to do the backtest of the portfolio. The backtest of the portfolio is the holding return of the optimized portfolio in the periods specified.
Define a variable called dataset and assign 1. You have to bring the corresponding data for dataset 1 secified in the input. You have to bring the stock continuously compounded returns, the market index returns, and the risk-free returns. Do any data processing you need to generate this data. Hint: start testing your program with the dataset 1, which has a short list of tickers (like 10 tickers). Make sure your program works if you change the dataset to 2 or 3.
library(readxl)
library(quantmod)
library(IntroCompFinR)parameters <- read_excel("templateW9.xlsx", sheet = "parameters")I define the dataset to 2
dataset = 2I need to get the list of tickers for the dataset=1. That is stored in the sheet “sometickers”:
sheet = parameters$stocksheet[dataset]
tickers.df = read_excel("templateW9.xlsx", sheet = sheet)from = parameters$from[dataset]
to = parameters$to[dataset]
freq = parameters$freq[dataset]
src = parameters$source[dataset]
price <- parameters$price[dataset]Symbols <- read_excel("templateW9.xlsx", sheet= parameters$stocksheet[dataset])
Market <- parameters$market[dataset]
Riskfree <- parameters$riskfree[dataset]
mktprice <- getSymbols(Symbol=Market,from = from,to=to, periodicity = freq, src = src)## 'getSymbols' currently uses auto.assign=TRUE by default, but will
## use auto.assign=FALSE in 0.5-0. You will still be able to use
## 'loadSymbols' to automatically load data. getOption("getSymbols.env")
## and getOption("getSymbols.auto.assign") will still be checked for
## alternate defaults.
##
## This message is shown once per session and may be disabled by setting
## options("getSymbols.warning4.0"=FALSE). See ?getSymbols for details.tickers.list <- c(mktprice, Symbols$ticker)
for (i in tickers.list) {
try(getSymbols(i,
from = from, to = to,
periodicity = freq,
src = src) )
}## Warning: ANCB_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "ANCB_old", env = <environment>, verbose = FALSE, :
## Unable to import "ANCB_old".
## el argumento "conn" está ausente, sin valor por omisión## Warning: ARRS_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "ARRS_old", env = <environment>, verbose = FALSE, :
## Unable to import "ARRS_old".
## el argumento "conn" está ausente, sin valor por omisión## Warning: DNR download failed; trying again.## Error in getSymbols.yahoo(Symbols = "DNR", env = <environment>, verbose = FALSE, :
## Unable to import "DNR".
## el argumento "conn" está ausente, sin valor por omisión## Warning: FRSH_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "FRSH_old", env = <environment>, verbose = FALSE, :
## Unable to import "FRSH_old".
## el argumento "conn" está ausente, sin valor por omisión## Warning: HBK_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "HBK_old", env = <environment>, verbose = FALSE, :
## Unable to import "HBK_old".
## el argumento "conn" está ausente, sin valor por omisión## Warning: HFBC_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "HFBC_old", env = <environment>, verbose = FALSE, :
## Unable to import "HFBC_old".
## el argumento "conn" está ausente, sin valor por omisión## Warning: HMSY download failed; trying again.## Error in getSymbols.yahoo(Symbols = "HMSY", env = <environment>, verbose = FALSE, :
## Unable to import "HMSY".
## el argumento "conn" está ausente, sin valor por omisión## Warning: MSBF download failed; trying again.## Error in getSymbols.yahoo(Symbols = "MSBF", env = <environment>, verbose = FALSE, :
## Unable to import "MSBF".
## el argumento "conn" está ausente, sin valor por omisión## Warning: PBBI download failed; trying again.## Error in getSymbols.yahoo(Symbols = "PBBI", env = <environment>, verbose = FALSE, :
## Unable to import "PBBI".
## el argumento "conn" está ausente, sin valor por omisión## Warning: RHT_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "RHT_old", env = <environment>, verbose = FALSE, :
## Unable to import "RHT_old".
## el argumento "conn" está ausente, sin valor por omisión## Warning: TRK_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "TRK_old", env = <environment>, verbose = FALSE, :
## Unable to import "TRK_old".
## el argumento "conn" está ausente, sin valor por omisión## Warning: TYPE_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "TYPE_old", env = <environment>, verbose = FALSE, :
## Unable to import "TYPE_old".
## el argumento "conn" está ausente, sin valor por omisión## Warning: UWN_old download failed; trying again.## Error in getSymbols.yahoo(Symbols = "UWN_old", env = <environment>, verbose = FALSE, :
## Unable to import "UWN_old".
## el argumento "conn" está ausente, sin valor por omisiónrfrate <- getSymbols(Symbols = Riskfree, periodicity = freq, src = "FRED")
rfrate <- get(rfrate)
rfrate <- rfrate$TB3MS/100/12
range_date <- paste(from, "/", to, sep = "")
rfrate <- rfrate[range_date,]Market2 <-substr(Market,2,nchar(Market))
t.list<-c(Market2)
# I create a list with the downloaded tickers
for(i in ls()) {
if (i %in% tickers.list){
t.list <- c(t.list , i)
}
}
tickers.list <- t.list
obj.list <- lapply(tickers.list, get)
prices.zoo <- do.call(merge, obj.list)
returns <-as.data.frame(diff(log(Ad(prices.zoo))))
colnames(returns) <- tickers.listYou have to do the same you did in previous workshop in terms of selecting stocks according to specific beta coefficients, and the estimate an optimal portfolio. However, you have to use the input parameters you read to process the right information. Then, make sure that you have the correct program for the previous workshop. Here are more details for this part:
For each stock return you brought, run a CAPM model. You have to use a function to run one CAPM model. For each CAPM model you have to save: 1) the alpha coefficient (b0), 2) the beta coefficient (b1), 3) standard error of alpha, and 4) the standard error of beta. Once you save these values, store them in a matrix. You have to endup with a matrix of CAPM coefficients, standard errors and their corresponding 95% confidence intervals. Each row of the matrix will have information for each stock. Each row will have the beta coefficients, 95% C.I. for each beta, and standard errors for the beta. HINT: Use the program you did in previous Workshop where you program a CAPM function.
capm <- function(stockr, marketr, rfr) {
stockpremium <- stockr - rfr
marketpremium <- marketr - rfr
model1 <- lm(stockpremium ~ marketpremium, na.action = na.omit)
s <- summary(model1)
beta0 <- s$coefficients [1,1]
Se0 <- s$coefficients [1,2]
Minb0 <- s$coefficients [1,1]-(2*s$coefficients[1,2])
Maxb0 <- s$coefficientes [1,1]+(2*s$coefficients[1,2])
beta1 <- s$coefficients [2,1]
Se1 <- s$coefficients [2,2]
Minb1 <-s$coefficients [2,1]-(2*s$coefficients[2,2])
Maxb1 <- s$coefficients [2,1]+(2*s$coefficients[2,2])
nm <- df.residual (model1)+2
pval0 <- s$coefficients [1,4]
pval1 <- s$coefficients [2,4]
results <- c(beta0, Se0,Minb0 ,Maxb0,beta1 ,Se1 ,Minb1 ,Maxb1 , pval0, pval1, nm)
names(results) <- c("beta0", "Se0", "Minb0", "Maxb0", "beta1", "Se1", "Minb1", "Maxb1", "pvalB0", "pvalB1" , "nm")
return(results)
}