Assignment 2 (Part 1)
Due: January 16, 2024 (Tuesday), 11:59pm
MSBA7017 Financial Engineering
Dr. Wang, Liao lwang98@hku.hk
ˆ Any part with a single asterisk (*) is for extra credit; any part with a double asterisk
(**) will not be graded and only serves as food for thought.
ˆ For all questions involving coding, you are free to use existing codes and spreadsheet
models for the numerical examples in notes/slides; you can choose either VBA or
Python.
ˆ For all questions involving coding, hand in the spreadsheet together with the VBA
codes or Python codes. You may print the spreadsheet and the VBA codes, or Python
codes, as hard copies and hand in it with solutions/answers for other problems. You
can also include the spreadsheet with VBA codes in a single .xlsm file (if you choose
VBA), or putting all Python codes in a zipped file (if you choose Python), and email
it to the Teaching Assistant (remember to include your name in the email).
ˆ Students are allowed to discuss and collaborate for the problems, but each student is
required to independently prepare the codes and the answers.
1 Coding: Build Your Own Markowitz Portfolio (40%)
Download the file stockData.xlsx from Moodle. Below is a description of data:
ˆ The data contains (i) stock prices of the component firms of S&P500 index; and (ii)
prices of a fund that tracks S&P500, and the ticker is “IVV” (“ticker” means the
symbol for that instrument); IVV is a proxy for market portfolio.
ˆ There are two spreadsheets in the dataset. On each sheet, the first column is the row
number; the second column is the date on which the prices are observed (the dates are
1
already sorted from the oldest to the newest); the last column contains the prices of
IVV. All columns in between are the prices for the individual stocks.
ˆ Each row contains prices observed for all tickers. For example, on the first sheet
“allStock daily”, the second row means: on 1/3/2012, “ORCL” (this is for Oracle
Corporation) has price USD25.865, “MSFT” (this is for Microsoft Corporation) has
price USD26.765, and so on; and the price for one tradable unit of IVV is USD128.02.
ˆ The first sheet, “allStock daily”, contains daily prices; and the second sheet, “all-
Stock monthly” contains monthly prices.
Complete the following tasks:
(a). Write a function, getMarkowitzWeights Stocks, which implements Markowitz Model
1. That is, the inputs are µ (the mean vector of returns for risky assets), Σ (the co-
variance matrix of returns for risky assets), and µ (the investor’s required return). The
output is a portfolio weight vector that achieves an expected return at µ. Note the
output portfolio does not include the risk-free asset.
(b). Pick at least two tickers. You may randomly select the tickers; you may also select
the tickers based on your own selection criteria, and if you choose to do so, state your
criteria. You may choose to or not to involve the market portfolio (i.e. IVV); but it
is recommended that you include IVV since you need this ticker for Black-Litterman
Model in part (c) below. Also, you cannot pick exactly the same set of tickers as those
in the numerical examples in class.
* You will receive extra credit if you impose some reasonable stock picking criteria.
If you want to look into the data to help with stock picking, use data over 2012-2015
only. The cut-off points for this range are highlighted on both spreadsheets.
(If you choose not to, that is, if you choose to randomly select tickers, you will not be
penalized in grading.)
(c). On the tickers you pick, specify in total at least one view under Black-Litterman Model.
You may express the view(s) randomly; or express views based on some reasoning
deemed reasonable by you, and if you choose to do so, state your reasoning.
2
* You will receive extra credit if your view(s) are well-motivated. If you want to look
into data to generate views, use data over 2012-2015 only. The cut-off points for this
range are highlighted on both spreadsheets.
(If you choose not to, that is, if you choose to randomly express the view(s), you will
not be penalized in grading.)
(d). Implement Black-Litterman Model based on (b) and (c). That is, the assets include
all the tickers you pick in (b), and the views are those expressed in (c). To do this,
you need to estimate Σ, specify pi, specify P and ν. If you do not have an idea on
how to set τ , use τ = 1/48; likewise, if you do not have an idea on how to set c, set
c = 1. You may impose other values on τ and c deemed reasonable by you.
To do this part, use data over 2012-2015 only. The cut-off points for this range are
highlighted on both spreadsheets. The investment period is one month, and the one-
month risk-free rate is µf = 0.004%.
(e). Combine the outputs from (d) with Markowitz Model 2 for a one-month investment
period. That is, pick an investment need µ, use getMarkowitzWeights to generate
the optimal portfolio weights.
(f). Perform portfolio analytics on monthly data over 2016 for your portfolio in (e). You
can do it in the same format as Slide 61 of Topic2a.
Note: some tickers have experienced share splits during the past years. For example, one
share of $100 becomes two shares of $50. This does not affect the value of the stock holding,
but the dataset is not adjusted for that. For example, the price in the dataset before the
split is $100, and after the split becomes $50, but this does not imply a return of −50%.
IVV did not experience the splits. Only if you want to use machine learning techniques for
extra credit parts, you may want to exclude the outliers in the return realizations.