library(readxl)
library(quantmod)## Loading required package: xts## Loading required package: zoo##
## Attaching package: 'zoo'## The following objects are masked from 'package:base':
##
## as.Date, as.Date.numeric## Loading required package: TTR## Registered S3 method overwritten by 'quantmod':
## method from
## as.zoo.data.frame zoolibrary(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:xts':
##
## first, last## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(PerformanceAnalytics)##
## Attaching package: 'PerformanceAnalytics'## The following object is masked from 'package:graphics':
##
## legendlibrary(IntroCompFinR)In this workshop you will write a program that automates data collection, stock selection and portfolio optimization. We will use an Excel file as input with detailed parameters, so our program can run with any change in the Excel file.
In this workshop you will write a program to do the following:
Bring data according to input specifications of an Excel file
Select stocks based on the CAPM alpha and/or beta,
Apply portfolio optimization, and
Evaluate the portfolio strategy calculating the holding period return (portfolio backtesting)
The input parameters are a list of tickers, frequency, start and end dates, market ticker, risk-free ticker, and backtest dates. These parameters are specified in the templateW9.xlsx Excel file.
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.
parameters <- read_excel("templateW9.xlsx",sheet="parameters")
dataset<- 1if (dataset== 1) {
datasheet="sometickers"
}else if (dataset==2) {
datasheet="tick1"
}else if (dataset==3) {
datasheet="tick2"
}else {
cat("This ticker dont exist ")
datasheet="sometickers"
}
tickers <- read_excel("templateW9.xlsx",sheet= parameters$stocksheet[dataset])firstdate <- parameters$from[dataset]
lastdate <- parameters$to[dataset]
freq <- parameters$freq[dataset]
source <- parameters$source[dataset]mticker <- parameters$market[dataset]
tickerlist<- c(mticker, tickers$ticker)for(t in tickerlist){
try(getSymbols(Symbols=t,from=firstdate,to=lastdate,periodicity=freq,src=source))
}## Warning: BLL download failed; trying again.## Error in getSymbols.yahoo(Symbols = "BLL", env = <environment>, verbose = FALSE, :
## Unable to import "BLL".
## BLL download failed after two attempts. Error message:
## HTTP error 404.## Warning: MSBF download failed; trying again.## Error in getSymbols.yahoo(Symbols = "MSBF", env = <environment>, verbose = FALSE, :
## Unable to import "MSBF".
## MSBF download failed after two attempts. Error message:
## HTTP error 404.## Warning: PBBI download failed; trying again.## Error in getSymbols.yahoo(Symbols = "PBBI", env = <environment>, verbose = FALSE, :
## Unable to import "PBBI".
## PBBI download failed after two attempts. Error message:
## HTTP error 404.mticker1<-substr(parameters$market[dataset],2,nchar(parameters$market[dataset]))
list<-c(mticker1)for(i in ls()) {if (i %in% tickerlist){ list <- c(list , i)}}
tickerlist<-list
objectlist <- lapply(tickerlist, get)
prices <- do.call(merge, objectlist)
tickerlist1<-as.list(tickerlist)
do.call(rm,tickerlist1)
returns <-as.data.frame(diff(log(Ad(prices))))
tickerslist1<-sub(".MX","",tickerlist1,fixed=TRUE)
colnames(returns)<-tickerlistYou 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. (Optional) Send the matrix of capm results to your Excel template in the “CAPM_RESULTS” sheet. In your Excel file now you will have the capm results for each ticker. Now do the following processes: Keep only the stocks with significant and positive alpha (b0) and sort the remaining stocks from the highest to the lowest alpha. Select the top N stocks. The number of stocks to be selected is indicated in the “parameter” sheet.
With these stocks estimate the optimal portfolio (use a risk-free rate=0).
To test this strategy, imagine you are at the beginning of the backtest date. With the optimal portfolio run a backtest algorithm to calculate the holding-period return that your portfolio would have made if you had invested in this portfolio in the backtest date range specified in the “parameter” sheet.
CAPM <- function(stock,mkt,rfree){
stockpremreturn <- stock - rfree
marketpremreturn <- mkt - rfree
regression <- lm(stockpremreturn ~ marketpremreturn, na.action=na.omit)
summary <- summary(regression)
b0min <- summary$coefficients[1,1] - (2*summary$coefficients[1,2])
b0max <- summary$coefficients[1,1] + (2*summary$coefficients[1,2])
b1min <- summary$coefficients[2,1] - (2* summary$coefficients[2,2] )
b1max <- summary$coefficients[2,1] + (2* summary$coefficients[2,2] )
coefficients <- c(summary$coefficients[1,c(1,2)],b0min,b0max,summary$coefficients[2,c(1,2)], b1min, b1max)
coefficients <- c(coefficients,regression$df.residual+2)
names(coefficients) <- c("b0","se(b0)","min(b0)","max(b0)", "b1","se(b1)", "min(b1)","max(b1)","# Non-missing")
return(coefficients)
}riskfree <- parameters$riskfree[dataset]
getSymbols(Symbols=riskfree,from=firstdate,to=lastdate,periodicity=freq,src="FRED")## [1] "TB3MS"RF <- get(riskfree)
RF <- RF/12/100
# WE START WITH AN EMPTY VECTOR FOR RESULTS:
results <- c()
# WE DEFINE FOR FUNCTION TO START FROM THE SECOND COLUMN OF MY RETURNS
#data frame:
for (i in 2:ncol(returns)){
# FOR MY FUNCTION WE WRITE[,i] IN ORDER TO TAKE EVERY COLUMN AND RETURNS [,1] JUST FOR THE IPC:
model <- CAPM(returns[,i],returns[,1],RF[,1])
results <- rbind(model,results)
}## Warning in `-.default`(stock, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(mkt, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(stock, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(mkt, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(stock, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(mkt, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(stock, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(mkt, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(stock, rfree): longer object length is not a multiple of
## shorter object length## Warning in `-.default`(mkt, rfree): longer object length is not a multiple of
## shorter object lengthcolnames(results) <- c("b0","se(b0)","min(b0)","max(b0)", "b1","se(b1)", "min(b1)","max(b1)","# Non-missing")
results <- as.data.frame(results)
rownames(results) <- tickerlist[2:length(tickerlist)]
results## b0 se(b0) min(b0) max(b0) b1 se(b1)
## BURL 0.012273444 0.001390205 0.009493035 0.015053854 1.2136378 0.04449261
## HEAR 0.004364088 0.001030365 0.002303358 0.006424818 0.0581799 0.03297617
## KSS 0.001848974 0.002634666 -0.003420358 0.007118306 0.8521374 0.08432080
## MGYR -0.032919900 0.007678450 -0.048276801 -0.017562999 0.9005493 0.24574387
## MSFT 0.027858630 0.002211745 0.023435139 0.032282120 0.1887242 0.07078549
## min(b1) max(b1) # Non-missing
## BURL 1.124652584 1.3026230 1048
## HEAR -0.007772442 0.1241322 1048
## KSS 0.683495841 1.0207790 1048
## MGYR 0.409061545 1.3920370 1048
## MSFT 0.047153199 0.3302951 1048Did your portfolio strategy performed better than the market index? Report your results in program as comments.
WE HAVE TWO VARIABLES, BETA 0 AND BETA 1. ON AVERAGE, OUR PORTOLIO INDICATES WITH BETA 0 THAT ON AVERAGE WE PERFORMED .239% BETTER THAN THE MARKET. ON THE OTHER HAND, BETA 1 MEASURING RISK PRESENTED AN AVERAGE OF.622, OR 62% RISKIER THAN THE MARKET INDEX.