ASB2522 Investment & Portfolio Management
PRACTICAL CLASS
During this computer class, you will download financial data, compute an optimal portfolio, evaluate the performance of your portfolio and evaluate securities.
You will employ Yahoo Finance or Investing.com to obtain financial data and Microsoft Excel to make your computations.
Objective: you want to build a security portfolio that outperforms the commonly used S&P500. You are free to do what you want, but your department cannot do short selling.
To build your optimal portfolio, you think the most appropriate start is to use 4 tradable indexes: the MSCI Japan Index; the DAX; the JKSE; the S&P500.
You first need financial data.
1) Get the daily prices of the 4 indexes from Yahoo Finance, over the last 5 years.
Hint: go on the website and choose historical data. Download it on Excel.
Now, you need the annualised mean returns, standard deviations and the covariance matrix.
2) Compute the returns of each index.
Be cautious: you have to remove the missing values in prices and the returns equal to zero due to non-traded days.
3) Compute the annualized mean returns and annualized standard deviation.
Hint: use coefficients of returns instead of percentage change.
4) Compute the covariance and the correlation matrices between the indices.
Hint: naming the cells and matrices and using the functions INDEX and MATCH can turn to be very helpful.
Now, you have to create portfolios.
5) Compute the portfolio return and standard deviation if you invest equally in each index.
6) Compute the weight of the portfolio with minimal variance (Global minimum variance portfolio)
Hint: you should use excel solver and minimize the cell containing the variance of the portfolio, under the constraint that the weights equal to 1 and are non-negative.
Advices in the use of Excel:
Excel is widely used in the financial industry and you should be proficient in its basic functions. The following functions can be very useful in this class:
- Calculations: AVERAGE, GEOMEAN, COVAR, VAR.S, CORREL.
- Data management: INDEX, MATCH.
You should also understand how to use the solver. If your Excel does not have solver, follow the tutorial in this link to add Solver to Excel. https://support.office.com/en-gb/article/Load-the-Solver-Add-in-612926fc-d53b-46b4-872c-e24772f078ca