1 General directions for this Workshop

You will work in RStudio. Create an R Notebook document to write whatever is asked in this workshop.

You have to solve CHALLENGE exercises.

It is STRONGLY RECOMMENDED that you write your OWN NOTES as if this were your notebook. Your own workshop/notebook will be very helpful for your further study.

Keep saving your .Rmd file, and ONLY SUBMIT the .html version of your .Rmd file.

2 Introduction

In this workshop you will write a program to do the following:

  1. Bring data according to input specifications of an Excel file

  2. Select stocks based on the CAPM alpha and/or beta,

  3. Apply portfolio optimization, and

  4. 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.

3 Specifications of the input 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:

  1. Start and end data to collect historical prices of stocks and the market

  2. The frequency of the data to be brought

  3. The Excel sheet of the ticker list to be brought from the web, and also the source.

  4. 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.

  5. The minimum and maximum weight to allow for each stock for the portfolio optimization

  6. The number of stocks to be selected for the portfolio

  7. 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.

4 Challenge 1 - Data collection

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.

5 Challenge 2 - Security selection, optimization and backtesting

You 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:

  1. 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.
  1. (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.
  1. Now do the following processes:

  1. 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.

  2. With these stocks estimate the optimal portfolio (use a risk-free rate=0).

  1. 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.

(Optional) Save the portfolio HPR in the “parameter” sheet (yellow cell)

  1. Now calculate the holding-period return of the corresponding market index in the same backtest date range to have a benchmark to compare your portfolio strategy.

(Optional) Save the market return in the “parameter” sheet (yellow cell)

Did your portfolio strategy performed better than the market index? Report your results in program as comments.