Workshop 5, Computational Finance
Montserrat Guzmán
Sepetember 08, 2021
1 Introduction
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, unionsetwd("D:/Tec/7mo sem/Computational Finance")We open the excel file
input.df<-read_excel("InputsW5.xlsx",sheet = "ticklist1")
tickers.list<-input.df$ticker
tickers.list## [1] "AMD" "WMT" "MSFT" "TSLA"#The first column is the list of tickers and the other 4 columns are portfolio weights for 4 different portfolios.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.5We get the monthlydata 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")## '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.## [1] "AMD" "WMT" "MSFT" "TSLA"We need to merge the xts.zoo files the getSymbols function gave us
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))
#The Ad function extracts only the adjusted prices (columns) of an xts-zoo object created by the getSymbols function.
# 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))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))2 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(apply(returns.zoo, 2, mean, na.rm=TRUE)) - 1
ER## AMD.Adjusted WMT.Adjusted MSFT.Adjusted TSLA.Adjusted
## 0.047289405 0.008285829 0.025796294 0.038527212In this case the function was applied to returns.zoo, and the mean was calculated by column, after these, the exponential function is applied.The substraction of 1 is to get the geometric mean returns, which is the expected return for each stock.
ER2<- exp(colMeans(na.omit(returns.zoo)))-1
ER2## AMD.Adjusted WMT.Adjusted MSFT.Adjusted TSLA.Adjusted
## 0.047289405 0.008285829 0.025796294 0.038527212na.rm=TRUE omit the NA values column by column. The na.omit function don’t use a whole row if there’s a NA for one 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 TSLA.Adjusted
## AMD.Adjusted 0.026313875 0.0010517769 0.0029716785 0.003926305
## WMT.Adjusted 0.001051777 0.0028164789 0.0005085894 0.001363370
## MSFT.Adjusted 0.002971679 0.0005085894 0.0032432427 0.002937118
## TSLA.Adjusted 0.003926305 0.0013633700 0.0029371178 0.025831064We can also calculate the Correlation Matrix to better understand the relationships of all different pairs of stock returns:
#CORRELATION MATRIX
CORR<-cor(na.omit(returns.zoo))
CORR## AMD.Adjusted WMT.Adjusted MSFT.Adjusted TSLA.Adjusted
## AMD.Adjusted 1.0000000 0.1221738 0.3216768 0.1505984
## WMT.Adjusted 0.1221738 1.0000000 0.1682768 0.1598414
## MSFT.Adjusted 0.3216768 0.1682768 1.0000000 0.3208932
## TSLA.Adjusted 0.1505984 0.1598414 0.3208932 1.0000000We can also run the cov2cor function to get the correlation matrix:
CORR2<- cov2cor(COV)
CORR2## AMD.Adjusted WMT.Adjusted MSFT.Adjusted TSLA.Adjusted
## AMD.Adjusted 1.0000000 0.1221738 0.3216768 0.1505984
## WMT.Adjusted 0.1221738 1.0000000 0.1682768 0.1598414
## MSFT.Adjusted 0.3216768 0.1682768 1.0000000 0.3208932
## TSLA.Adjusted 0.1505984 0.1598414 0.3208932 1.0000000#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.02997469
## [2,] 0.03900795
## [3,] 0.01704106
## [4,] 0.04290831The 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.07233703 0.11174593 0.04206216 0.12247199With this matrix multiplication we are applying Markowitz theory to estimate the expected risk of 4 portfolios at the same time.
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.
rm(list = ls())3 Challenges
At the beginning of the challenge you need to WRITE YOUR GENERAL APPROACH to solve the challenge. You have to use CAPITAL LETTERS. Your general approach is the logic you will follow to solve the problem (at least 1 paragraph for the general approach). In addition, you must include a SEQUENCE OF STEPS needed to get the input(s) and end up with the correct output(s).
Write your R code in R chunks, and use the # character in the chunk to briefly document as comments what your lines of code do
You have to show/print the value(s) of the result of each challenge
3.1 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:
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.
3.2.1 INPUT
I need to get stock monthly prices for Microsoft and Wal-Mart from 2015 to date.
3.2.2 DATA MANAGEMENT PROCESS
I visualize the data management as:
1.Download monthly stock prices from Yahoo Finance using the getSymbols function
2.Calculate historical continuously compounded returns for both stocks using adjusted prices
3.Merge the data in one dataset
3.2.3 FUNCTIONAL ALGORITHM
1.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
2.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.
3.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.
4.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.
3.2.4 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="2017-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.02855944 0.01535236# 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.0022259248 0.0008030786
## rWMT 0.0008030786 0.0026418622# 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)3.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?
#instead of using the formulas I need to use the matrix algebra
weights<- rbind(wm,ww)#I get the expected return
ExpRet<- t(weights)%*%MR#The expected variance
VAR<- diag(t(weights)%*%COV%*%weights)#Finally I get the expected risk
Risk<- sqrt(VAR)# I plot the risk and the expected returns
plot(Risk, ExpRet, xlab="Portfolio volatility",
ylab="Portfolio Expected Return",
main="Portfolios of 2 Assets: MSFT & WMT")
3.4 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.
3.4.1 General Approach
This would be very similar to challenge 1, the main difference is that the weights are different
3.4.2 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="2017-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:
prices2<-merge(MSFT,WMT)
# I calculate continuously compounded returns to all columns of the price object:
rets2<-diff(log(prices2))
# 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:
rets2 <- as.data.frame(rets2) %>%
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(rets2)<-c("rMSFT", "rWMT")
# I display the first rows of the data frame:
head(rets2)# I calculate the expected returns of each stock. I calculate the geometric
# average of the columns of the rets object:
MR2=exp(colMeans(rets2))-1
# MR will be a vector with the 2 geometric means, which I will use as the expected
# returns of the stocks:
MR2## rMSFT rWMT
## 0.02855944 0.01535236# Now I calculate the Variance-Covariance matrix using the rets data frame:
COV2<-cov(rets2)
# 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:
COV2## rMSFT rWMT
## rMSFT 0.0022259248 0.0008030786
## rWMT 0.0008030786 0.0026418622# 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:
wm2<-seq(from=-0.40, to=1.40, by=0.10)
# I can easily create the vector for Wal-Mart as a complement of the Microsoft vector:
ww2<- 1 - wm2
# I create a data frame with these vectors:
portfolio2 <- as.data.frame(cbind(wm2,ww2))
# The cbind function "binds" or put together vectors into one matrix. It binds by column
portfolio2# 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:
portfolio2$ERP<-portfolio2$wm2*MR2[1] + portfolio2$ww2*MR2[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
portfolio2# I create a column for the Variances of the 11 portfolios:
portfolio2$VARP <- portfolio2$wm2^2*COV2[1,1] + portfolio2$ww2^2*COV2[2,2] + 2*portfolio2$wm2*portfolio2$ww2*COV2[1,2]
# I create a column for the expected risk of the 11 portfolios:
portfolio2$SDP=sqrt(portfolio2$VARP)
portfolio2#Finally I do a plot to illustrate the relationship between expected risk and return
# of the 11 portfolios:
plot(portfolio2$SDP,portfolio2$ERP,col="blue", xlab="Portfolio volatility",
ylab="Portfolio Expected Return",
main="Portfolios of 2 Assets: MSFT & WMT")
rm(list = ls())3.5 Challenge 3 - Finding the efficient frontier of portfolios with 4 assets
This challenge is OPTIONAL, but very recommended to be ready for Exam 1!
When you have more than 2 assets, then it is not straight forward to find the efficient frontier. In the case of 2 assets the frontier you generated is the efficient frontier. When you have 3 or more assets, if you create different possible combinations of assets you will get most of the portfolios that are not efficient. An efficient portfolio is a portfolio that offers the minimum expected risk for a specific level of expected return.
For this challenge you have to do the following:
Download monthly prices from Microsoft (MSFT), Wal-Mart (WMT), Amazon(AMZN) and General Electric (GE). Get data from 2015 to date.
Following portfolio theory and using the function portfolio.optim() from the tseries package estimate an efficient frontier
This is a challenging exercise!
Hint: you can use a loop to estimate minimum variance portfolios for different expected returns
3.5.1 General Approach
3.5.2 Code
#get the data we need, in this case is the monthly information of Walmart,Microsoft, Amazon and General Electric since 2015
#1-Download price data of the 4 stocks
#2-Apply (map) the adjusted function to all xts-zoo datasets in order to get the adjusted stock prices
#3-Do the merge of all the xts-zoo objects into one object
#4-Finally rename the columns of the integrated object, and return the object as prices.
symbols <- c("MSFT","WMT", "AMZN", "GE")
prices <- getSymbols(symbols,src = 'yahoo',
periodicity = "monthly",
from = "2015-01-01")
adjprices<-merge(Ad(MSFT),Ad(WMT),Ad(AMZN),Ad(GE))#I also get the continuously compounded return
returns<-na.omit(diff(log(adjprices)))
returns<-returns[c(-1),]#I calculate the expected returns by the geometric average
ER <- exp(apply(returns, 2, mean, na.rm=TRUE)) - 1
ER## MSFT.Adjusted WMT.Adjusted AMZN.Adjusted GE.Adjusted
## 0.025920481 0.008729219 0.027952763 -0.006882713#To start with the loop I need to get the max and the min return expected
min_ER<- min(ER)
max_ER <- max(ER)#I would do 50 portfolios, so I need to substract the min return to the max, and divide this by 50
x<- (max_ER - min_ER)/50
XR<-min_ER#I generate my loop
#while (XR< max_ER){
#}