Finance Programming - Workshop 5

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. # Excel interface - Data gathering from an Excel file

You have to install and load the readxl package. Use the RStudio interface (Package tab in the right hand side window pane). We will also use the quantmod package.

The readxl reads Excel files

Instead of typing the tickers and weights in our R code, we will read the tickers of a portfolio along with specific weights for each ticker from an Excel file.

We load the libraries to the current R session.

library(readxl)
library(quantmod)

Now we set the working directory where you downloaded the Excel file. You can set the working directory using the RStudio Menu: Session/Set working directory/Choose Directory, and then you can copy and paste the R code from the console to your R chunk.

We read a list of tickers from an Excel file. The Excel file has one Sheet with a list of tickers along with 4 different portfolios with their respective weights. You can download the Excel file “InputsW5.xlsx” from the course site. Save this file in any folder you want.

input.df<-read_excel("InputsW5.xlsx",sheet = "ticklist1")
tickers.list<-input.df$ticker
tickers.list

[1] "AMD"  "WMT"  "MSFT" "TSLA"

Now we have the input of the program in the data frame input.df. The first column is the list of tickers and the other 4 columns are portfolio weights for 4 different portfolios.

We create a matrix with the weight columns of input.df:

W<-matrix(c(input.df$W1, input.df$W2, input.df$W3, input.df$W4),nrow=4,ncol=4)
W

     [,1] [,2] [,3] [,4]
[1,] 0.25  0.4  0.0  0.5
[2,] 0.25  0.1  0.5  0.0
[3,] 0.25  0.0  0.5  0.0
[4,] 0.25  0.5  0.0  0.5

With the matrix function we specify the number of rows and columns of the matrix, otherwise it would create a long vertical vector.

We can get monthly data of the stocks from Yahoo from 2015 to date:

getSymbols(Symbols=tickers.list, from="2015-01-01",
          periodicity="monthly", to="2021-03-10", src="yahoo")

[1] "AMD"  "WMT"  "MSFT" "TSLA"

Since the getSymbols function creates an xts - zoo object for each asset, it is convenient to merge those objects into one single object using the merge function.

Up to now we do not have a way to make a reference to the zoo object. We only have the name of the objects as a character list, which is stored in ticker.list. We can use the function get to create a reference to those objects.

Note that the merge function works for xts and zoo objects only, but not for plain dataframes.

p1<-get(tickers.list[1])
p2<-get(tickers.list[2])
p3<-get(tickers.list[3])
p4<-get(tickers.list[4])
prices.zoo <- Ad(merge(p1, p2, p3, p4))

# I can do the merge with the names of R objects, but this code
#  will NOT work if I change the tickers in my Excel file
#prices.zoo<-Ad(merge(AMZN,MSFT,TSLA,ALFAA.MX))

The Ad function extracts only the adjusted prices (columns) of an xts-zoo object created by the getSymbols function.

We can now remove the objects we created since all the data is in the prices.zoo object.

rm(p1, p2, p3, p4) 

Now we calculate the compounded returns. We do this as the difference in the log prices for asset i.

rit=Δ(log(priceit)) We use the diff and log functions:

returns.zoo <- diff(log(prices.zoo))

Monthly expected return and risk of several portfolios

Now that we have monthly returns in a data frame we can calculate the expected return of each stock and create the variance-covariance matrix, which is needed to compute the expected risk of a portfolio.

To estimate expected stock returns we can calculate the geometric average of returns for all stocks located in returns.zoo. We can estimate the vector of mean returns or expected returns (Mr) as:

ER=exp[Mr]=exp⎡⎣⎢⎢⎢⎢⎢⎢⎢⎢r1¯r2¯…rN¯⎤⎦⎥⎥⎥⎥⎥⎥⎥⎥−1

I calculate the vector Mr of expected simple monthly returns using the exponential and the apply function at the same time:

ER <- exp(apply(returns.zoo, 2, mean, na.rm=TRUE)) - 1
ER

 AMD.Adjusted  WMT.Adjusted MSFT.Adjusted TSLA.Adjusted 
  0.047289405   0.008285827   0.025796291   0.038527212 

In this case I applied the function mean to returns.zoo, and the mean was calculated by column. With the second parameter equal to 2 I indicated to calculate the means by column (1 is to apply the mean by row). After the means are calculated, then the exponential function is applied to these means. The parameter na.rm=TRUE is used to avoid using null values (NA’s) to calculate the mean. Finally I subtract 1 to get the geometric mean return, which is the expected return for each stock.

I can also use the function colMeans to do the same we did above:

ER2 <- exp(colMeans(na.omit(returns.zoo))) - 1
ER2

 AMD.Adjusted  WMT.Adjusted MSFT.Adjusted TSLA.Adjusted 
  0.047289405   0.008285827   0.025796291   0.038527212 

There is a slight difference between the calculation of ER and ER2. With the apply function and the parameter na.rm=TRUE the NA values are omited column by column. In the case of the na.omit function, if there is an NA for one stock then the whole raw is not used to compute the mean. The differences are very minor, but in the real world you must apply your criteria and use the more accurate. I will use ER since it is more accurate because it is considering all available history of returns for each stock.

Now we compute the Var-Covariance matrix. The var function receives a matrix of returns as parameter. The returns of stock 1 are in column 1, returns of stock 2 are in column 2, and so on. This function calculates the Variance-Covariance Matrix of stock returns.

Remember that the Variance-Covariance matrix contains Variances of stock returns in the diagonal, and it contains the covariances of pairs of stock returns in the non-diagonals. Also, this matrix is symmetric, since Cov(Reti,Retj) = Cov(Retj,Reti).

For example, the variance-covariance matrix of 2 variables X1 and X2 contains the variance of each variable in its diagonal and covariances between the variables in its non-diagonal terms. In other words, the elements in the upright part of the matrix are repeated in the down-left part of the matrix. More formally:

∑cov=[Var(X1)Cov(X2,X1)Cov(X1,X2)Var(X2)] We can easily calculate the variance-covariance matrix in R as follows:

COV <- var(returns.zoo, na.rm = TRUE)
COV

              AMD.Adjusted WMT.Adjusted MSFT.Adjusted
AMD.Adjusted   0.026313875  0.001051776   0.002971675
WMT.Adjusted   0.001051776  0.002816480   0.000508591
MSFT.Adjusted  0.002971675  0.000508591   0.003243242
TSLA.Adjusted  0.003926307  0.001363373   0.002937124
              TSLA.Adjusted
AMD.Adjusted    0.003926307
WMT.Adjusted    0.001363373
MSFT.Adjusted   0.002937124
TSLA.Adjusted   0.025831067

We can also calculate the Correlation Matrix to better understand the relationships of all different pairs of stock returns:

CORR<-cor(na.omit(returns.zoo))
CORR

              AMD.Adjusted WMT.Adjusted MSFT.Adjusted
AMD.Adjusted     1.0000000    0.1221736     0.3216764
WMT.Adjusted     0.1221736    1.0000000     0.1682773
MSFT.Adjusted    0.3216764    0.1682773     1.0000000
TSLA.Adjusted    0.1505985    0.1598418     0.3208938
              TSLA.Adjusted
AMD.Adjusted      0.1505985
WMT.Adjusted      0.1598418
MSFT.Adjusted     0.3208938
TSLA.Adjusted     1.0000000

We can also run the cov2cor function to get the correlation matrix:

CORR2 <- cov2cor(COV)

Remember that the correlation between 2 stock returns can be any value between -1 and 1. When the correlation is close to 0, it means that both stocks have no relationship. If the correlation is close to 1 it means that both stock returns move in a very similar way in the same direction.

We can estimate the expected return and expected risk of the 4 portfolios using the weights that we read from the Excel file.

Now we are ready to start estimating the expected return of the 4 portfolios using Matrix Algebra:

ERP1<-t(W)%*%ER
ERP1

           [,1]
[1,] 0.02997468
[2,] 0.03900795
[3,] 0.01704106
[4,] 0.04290831

The vector ERP1 will have the monthly expected returns of the 4 portfolios with the specific weights stated in the Excel file.

The t function is the transpose function of a vector or matrix. The result of multiplying the vector transposed times the vector of expected returns of the stocks is the same as calculating a weighted average. This is true since we use matrix multiplication to efficiently compute SUM of PRODUCTS.

Now I can estimate the expected risk of the 4 portfolios using Matrix Albegra:

EVARPORT=t(W)%*%COV%*%W
ERISK=sqrt(diag(EVARPORT))
ERISK

[1] 0.07233704 0.11174593 0.04206217 0.12247199

With this matrix multiplication we are applying Markowitz theory to estimate the expected risk of 4 portfolios at the same time.

You can review why this is the case in my Notes about Portfolio Theory. Make sure you understand why we do this matrix multiplication.

The expected variance of the 4 portfolios will be in the diagonal of the matrix multiplication W′∗COV∗W. Finally, we get the squared root of the diagonal to get the expected risk of the 4 portfolios.

Challenge 1 - Create a frontier with portfolios of combinations of 2 assets

Download monthly stock prices from Yahoo Finance for 2 stocks of your preference. Bring data from 2017 to date. With this data you have to do the following:

Generate 11 portfolios of these 2 assets. The first portfolio will have 0% of Microsoft and 100% of Wal-Mart; the 2nd portfolio will have 10% of Microsoft and 90% of Wal-Mart; etc.. until you endup with the 11th portfolio that has 100% of Microsoft and 0% of Wal-Mart.

For each portfolio estimate the expected return and risk of the portfolio according to Portfolio Theory

Do a plot of expected risk and expected return of these 11 portfolios. Put the expected risk in the X axis and expected return in the Y axis. This will represent a frontier of possible portfolios.

3.2 Challenge 1 Solution - Approach 1

Any programming problem receives input data that needs to be processed to arrive to specific result(s).

The input data can be specifications or actual raw data. In this case we have the specification that we have to use stock data for Microsoft and Wal-Mart. The processing of the data can be divided in a) data management, and b) functional algorithm(s) needed to process the data and achieve the specific results.

In order to write programming code for data management and the functional algorithm(s) you need to UNDERSTAND THE PROBLEM very well (100%), and also you need to know very well the concepts, methods and/or theories related to the problem. For this problem, you need to understand the basics of Portfolio Theory.

The data management process is related to data collection, data cleanning, and data merging.

The functional algorithm(s) are basically the specific sequential steps the computer has to process in order to get the result.

As a good practice for programming, before you start writing programming code, I strongly recommend you to write down WITH YOUR WORDS your own conception of the problem. Write the specific INPUTS, and write the specific steps you need to program for data processing and for the functional algorithm(s). I will try to follow this recommendation for this problem.

A simple illustration of the inputs, process and output can be:

INPUT: MICROSOFT and WAL-MART stock historical monthly prices

DATA MANAGEMENT: Download prices and merge them into one object

FUNCTIONAL ALGORITHM: It has to do the calculations to get the expected risk and return of 11 portfolios, each portfolio will have different weight combination

OUTPUT: 11 expected return and 11 expected risk of the 11 portfolios; a graph to illustrate the 11 portfolios in terms of risk vs return

I will detail this in the following sections.

INPUT

I need to get stock monthly prices for Microsoft and Wal-Mart from 2015 to date.

DATA MANAGEMENT PROCESS

I visualize the data management as:

Download monthly stock prices from Yahoo Finance using the getSymbols function

Calculate historical continuously compounded returns for both stocks using adjusted prices

Merge the data in one dataset

FUNCTIONAL ALGORITHM

Estimate the expected return of both stocks, which will be used to estimate the expected return of different portfolios. Calculate geometric return as expected stock returns. I can save these expected returns in a vector

Estimate the variance-covariance matrix using the continuously returns of Microsoft and Wal-Mart. Save this in a matrix. I need this to later estimate the expected risk of the different portfolios.

Calculate the expected return and expected risk of the 11 portfolios. The first portfolio will have 0% of Microsot stock and 100% of Wal-Mart, the 2nd portfolio will have 10% of Microsoft and 90% of Wal-Mart, and so on until the last portfolio that will have 100% Microsoft and 0% Wal-Mart.

3.1. I will do this by creating a data frame that will have the following columns: Microsoft weight (wm), Wal-Mart weight (ww), Expected Portfolio Return (ERP) and Expected Portfolio Risk (ExpRisk). I visualize this dataframe something like:

portfolio wm ww ERP VARP ExpRisk 1 0 1 ? ? ? 2 0.10 0.90 ? ? ? 3 0.20 0.80 ? ? ? .. .. .. .. .. .. 11 1 0 ? ? ? I can fill out the weights manually or using the seq function.

3.2. Estimate the expected return following Markowitz Portfolio Theory. Portfolio return can be estimated with a simple weighted average according to each specific asset weight:

ERP=E[P]=wm∗E[Rm]+ww∗E[Rw] where E[Rm] is Expected return for Microsoft and E[Rw] is the expected return of Wal-Mart. I can do this using R vectorization with the data frame, so the column for ERP will be fill-out in one operation.

3.3. Estimate the expected variance and expected risk following Markowitz Portfolio Theory. Since I only have 2 assets I can follow the analytical formula for portfolio variance, which is:

VAR(P)=wm2∗VAR(rm)+ww2∗VAR(rw)+2∗wm∗ww∗COV(rm,rw) I can simply get the expected risk of each portfolio by taking the square root of the expected variance:

ExpRisk(P)=VAR(P)−−−−−−−√

I can also to this with R vectorization, so that I can calculate the columns VARP and ExpRisk using a mathematical expression following the previous formulas.

Do a plot of the 11 portfolio return and risk. Use Expected Portfolio Return as the Y-axis and Expected Portfolio Risk as the X-axis.

Programming Code

# I load the required R packages for the program:
# To download data from Yahoo I use quantmod
library(quantmod)
# To manage columns of data frames I use dplyr:
library(dplyr)

# DATA MANAGEMENT PROCESSES
# I first get stock prices from Yahoo Finance:
getSymbols(Symbols=c("MSFT","WMT"), from="2015-01-01", 
           periodicity="monthly", src="yahoo")

[1] "MSFT" "WMT" 

# getSymbols create zoo object for each dataset brought from the Web. 
# The merge function works only with zoo or xts objects. 

# I merge the 2 zoo objects into one zoo dataset called prices:
prices<-merge(MSFT,WMT)
# I calculate continuously compounded returns to all columns of the price object:
rets<-diff(log(prices))
# I use the dplyr package to remove NA values (since the first month is not possible to 
#   calculate returns) and select only Adjusted columns
# The dplyr only works with data frame objects, so I need to convert the zoo object into
#   data frame first, and then drop rows with NA, and then select only Adjusted columns:
rets <- as.data.frame(rets) %>%   
  na.omit() %>% # remove the NAs
  select(contains("Adjusted"))
# Now rets is a data frame with only continuously compounded returns calculated with
#   adjusted prices. This data frame has only 2 columns, one for each stock
# I change the names of the columns:
colnames(rets)<-c("rMSFT", "rWMT")
# I display the first rows of the data frame:
head(rets)

# I calculate the expected returns of each stock. I calculate the geometric
#   average of the columns of the rets object:
MR=exp(colMeans(rets))-1
# MR will be a vector with the 2 geometric means, which I will use as the expected
#   returns of the stocks:
MR

      rMSFT        rWMT 
0.021871358 0.006966573 

# Now I calculate the Variance-Covariance matrix using the rets data frame:
COV<-cov(rets)
# The function cov generates a matrix where the diagonal will have the variances of
#   each column, in this case, the variances of Microsoft and Wal-Mart returns.
#   In the non-diagonal the matrix has the paired covariances between the returns

#I display the variance-covariance matrix:
COV

             rMSFT         rWMT
rMSFT 0.0035480398 0.0008355751
rWMT  0.0008355751 0.0029902722

# Now I will create a data frame with the weights and expected return, variance and
#  risk of each of the 11 portfolios:
# I start creating a vector for the weights of Microsoft:
wm<-seq(from=0, to=1, by=0.10)
# I can easily create the vector for Wal-Mart as a complement of the Microsoft vector:
ww<- 1 - wm

# I create a data frame with these vectors:
portfolios <- as.data.frame(cbind(wm,ww))
# The cbind function "binds" or put together vectors into one matrix. It binds by column
portfolios

# I calculate the expected returns of each of the portfolios using the
#   geometric mean returns and the weight vectors.
# I add a new column in the portflios data frame:
portfolios$ERP<-portfolios$wm*MR[1] + portfolios$ww*MR[2]
# The ERP column is created and will be filled out according to the expression
# R performs this statement to all rows of the data frame, 
#  so we do not need to do a loop to fill out all expected returns for the 11 portfolios.
# This is also called R vectorization since R performs all operations to the elements 
#  of vectors or other R object like data frame
portfolios

# I create a column for the Variances of the 11 portfolios:
portfolios$VARP <- portfolios$wm^2*COV[1,1] + portfolios$ww^2*COV[2,2] +   2*portfolios$wm*portfolios$ww*COV[1,2]

# I create a column for the expected risk of the 11 portfolios:
portfolios$SDP=sqrt(portfolios$VARP)
portfolios

#Finally I do a plot to illustrate the relationship between expected risk and return
#  of the 11 portfolios:

plot(portfolios$SDP,portfolios$ERP, xlab="Portfolio volatility", 
     ylab="Portfolio Expected Return",
     main="Portfolios of 2 Assets: MSFT & WMT")
# I add labels of the weights
text(portfolios$SDP,portfolios$ERP,labels=wm, cex=0.7, pos=3)

Q Challenge 1 - Approach 2

Modify approach 1 by applying matrix algebra to calculate expected return and expected variance and risk of the 11 portfolios. Which changes you need to do?

Q Challenge 2 - Create a frontier with portfolios of 2 assets allowing for short sales

In portfolio formation you can have 2 type of positions: long and short positions. Long positions is when you buy certain number of shares of an asset (that represets a specific weigth with respect to your total money you will use for the portfolio). A short position is when you receive money for a certan number of shares of an asset, so that you increase your money to invest in other stocks. Shorting is a way to leverage your portfolio by borrowing money from a stock, and then invest more money in other stocks. In this challenge you have to create portfolios of 2 assets with short and long positions. Here are the specific directions:

With the same stocks now create 19 portfolios instead of 11. The first portfolio will have -40% in Microsoft and 140% in Wal-Mart; the 2nd portfolio will have -30% in Microsoft and 130% in Wal-Mart; … ; the last portfolio will have 140% in Microsoft and -40% in Wal-Mart.

For each portfolio estimate the expected return and risk of the portfolio according to Portfolio Theory

Do a plot of expected risk and expected return of these 11 portfolios. Put the expected risk in the X axis and expected return in the Y axis.

Compare your plot in the case of shorting vs the plot you did in the previous challenge. What do you see? What happen when you allow for short positions?