Financial Modelling Final Assessment: Semi-Variance/Covariance Matrix

1.   Introduction to the Financial Modelling Problem

In this final assessment, students will be required to extend and improve the variance-covariance matrix VBA code provided in lectures so that it can be used to estimate a semi- variance covariance matrix. Students will be required to use both their own knowledge of  VBA programming, meta-skills, and also Generative Artificial Intelligence (GAI) to complete this final assessment.

2.   Goals of Assessment

GAI has revolutionized computer programming. Once a programmer has mastered the rudiments of programming, GAI dramatically increases their productivity. The focus of this final assessment is for you to appreciate how ChatGPT can enhance your VBA programming productivity. This assessment requires that you use ChatGPT to program VBA. The goal is for you to understand and appreciate the power of GAI for computer programming.

The assessment is to be completed in two stages. The first stage (first week) requires you to take VBA code you have seen in lectures (see Figure 1) that is deliberately poorly named, structured, formatted, documented and and has (syntactic and semantic) errors and improve it without GAI. You will also be required to extend the VBA code so that it estimates a semi-variance/covariance matrix.

You will take the VBA code provided in the first stage of this assessment and improve it using your knowledge of meta-skills: (1) debugging, (2) code planning, (3) code formatting, and (4) algorithms. The goal of this first stage of the assessment is to demonstrate how you   have mastered all the programming meta-skills we have studied in this subject.

Next, you will be given new VBA code for the second stage (second week), upload it to

ChatGPT, and then use ChatGPT to improve the code. You will be required to compare and contrast your stage one solution (obtained without ChatGPT) with your stage two solution (obtained with ChatGPT) in your second week of your final assessment.

3.   Extending the VBA Covariance matrix estimate using Semivariance

For stage one of this final assessment, students are required to extend the VBA code provided in Figure 1 (below) to incorporate semivariance matrix estimates.

Semivariance is a measurement of data that can be used to estimate the potential downside risk of an investment portfolio. Semivariance is calculated by measuring the dispersion of all observations that fall below the stock’s average return.

The semi-variance/covariance matrix you will be required to estimate is straightforward and sets all excess returns above zero to zero. The semivariance/covariance matrix is similar to the variance/covariance matrix, but it only includes returns that are below the mean return for a stock.

Semivariance is a useful tool in portfolio analysis because it provides a measure of downside risk. While standard deviation and variance provide measures of volatility, semivariance only looks at the negative fluctuations of an asset. Thus, semivariance can be used to calculate the average loss that a portfolio could incur because it neutralizes all values above the mean return.

The excess matrix,A, is found by calculating the excess return for each stock in each period:

To find the semivariance/covariance matrix students must set all positive excess returns to   zero in the excess matrix,A, and estimate the semivariance matrix using a similar approach to the variance/covariance matrix estimation we have considered in lectures. Ifrnm>rn the   rnm is set to zero and the covariance matrix is calculated:

4.   Structure & Submissions of the semivariance/covariance matrix VBA assessment

The semivariance/covariance matrix VBA assessment will be given in two stages:

(1) The first stage will require students to use the VBA code provided in the stage one

workbook “Stage One Semivariance Matrix.xlsx”. Students are required to change the VBA code so it generates the correct output as per the marking criterion provided below (see Figure 2). ChatGPT must *not* be used in stage one. Students will report on their stage one work in the Q&A (Week 11 Tutorial).

(2) The second will require students to take the VBA code provided in the stage two

workbook “Stage Two Semivariance Matrix.xlsx” . This workbook will only be released after the first stage submission is complete. The VBA code is the stage two workbook will be similar to the stage one workbook. However, there will be important differences between the VBA code of the two workbooks. Students are required to change the VBA code so it generates the correct output as per the marking criterion released to students (after stage one is submitted, see Figure 3). ChatGPT must be used in stage two. Students will report on their stage one work in the Presentation week (Week 12 Tutorial).

The “Stage One Semivariance Matrix.xlsx” provides VBA code (see Figure 1) and requires worksheet output (see Figure 2). Students are to prototype their VBA semivariance code using covariance and semivariance in Excel first. In Figure 2, the blue areas (above) represent outputs using the (a) covariance matrix and (b) semivariance matrix both to be completed using Excel and not VBA. The green areas represent outputs using the (c) semivariance matrix VBA code.

Figure 1: VBA code provided for Stage One. This code is based on the covariance matrix code provided in lectures. It must be extended to accommodate semivariance matrix estimation.Also, the VBA code has errors, is poorly structured, has no program plan or decision tree, has poor variable names and has no commenting. Students are expected to improve this code and will be marked using the stage one assessment criteria provided in Figure 3.

Figure 2 The “Stage One Semivariance Matrix.xlsx” workbook required to complete stage one of the final assessment. Students are to prototype their VBA semivariance code using covariance and semivariance in Excel first. The blue areas (above) represent outputs using the (a) covariance matrix (b) semivariance matrix both to be completed using Excel and not VBA. The green areas represent outputs using the (c) semivariance matrix VBA code. Students must populate the blue and green areas and submit the “Stage One Semivariance Matrix.xlsx” on Moodle at the end of the first week.