MCom in Risk Management
Risk Management Computing Skills 2025
Assignment - 2025
Due Date - 08 May 2025, 17h00
Instructions
Before you begin the assignment, read the following instructions carefully:
• This assignment is to be completed in pairs.
• Marks will be allocated for good programming techniques, so ensure that your code is efficient and readable.
• There are five parts to this assignment - upload your solutions on Amathuba (only one member needs to submit).
• All group members need to complete the Peer Review form which can be found here.
Problem
Following on from Tutorials 7 and 10, you should now be familiar with AIFMRM’s quarterly equity risk service.
One of AIFMRM’s clients, who is primarily interested in major JSE-listed indices, has requested a decomposition
and attribution of an index’s weight composition, beta, systematic volatility and specific variance statistics across a
standardised list of industries, defined by the FTSE’s Industry Classification Benchmark (ICB).
For information on the FTSE’s ICB, see: [Link]
In order to satisfy this client’s request, you will make use of three pieces of information in the form of CSV files:
(i) FTSE [Link] - Industry, Super Sector, Sector and Sub-Sector codes and definitions from the FTSE’s ICB.
(ii) FTSEJSE [Link] - Quarterly JSE-listed share and index constituent information, identical to Tutorial 7.
(iii) AIFMRM [Link] - Quarterly risk statistics on JSE-listed shares and indices, identical to Tutorial 7.
You will follow five steps, which will involve SQL, MATLAB and VBA, to eventually develop a basic Excel application
that will provide the client a visual depiction of the required decomposition through time. Have a look at the Excel
workbook named “Index [Link]” which shows an example of the final result for a bespoke index.
#1 Create the RMCS Database
The first step involves the design and creation of a database named RMCS. Below, you will find a section
that describes the general structure of the required database tables, however the specification of data types,
constraints, primary and foreign key relations are left for you to specify. For this step, the requirement is a
single SQL script file named [Link] that creates the RMCS database, including tables.
#2 Export Data from MATLAB into the RMCS Database
This step requires the creation of a MATLAB script file named ExportDataRMCS.m that uses functions generated
by MATLAB’s Import Tool to import all three of the CSV files mentioned above, along with any other functions
that are used to insert data the required data into RMCS’s tables. While populating the tbl FTSE ICB table
should be straightforward, there are a few aspects to consider for the other tables:
• the tbl JSE SS table requires a unique list of shares and their associated sub-sector codes;
• the tbl AIFMRM MV table requires the J203 total volatility through time only;
• the tbl AIFMRM ERS table requires data pertaining to shares only, i.e. do not include the index data - you
may distinguish between shares and indices by the length of their instrument codes; and
• the client is interested in the ALSI, FLED, LRGC, MIDC, SMLC, TOPI, DTOP, RESI, FINI, INDI, PCAP, SAPY
and ALTI indices, therefore the tbl FTSEJSE ICW table requires information for all of these. The function
created in Tutorial 7, GetICsAndWeights, will be useful here. However this function did not include the
first five indices. Information for these may be found in a similar fashion using the “ALSI New” and “Index
New” columns of the FTSEJSE [Link] file.
This step will provide you with a better understanding of the underlying data, which will be useful when
thinking about data types, constraints, primary and foreign key relations for the RMCS databse.
1
#3 Design the Decomposition and Attribution SQL Query
At this step you have the necessary data and database structure to solve the client’s request. Write a single
SQL script named [Link] that contains a query that produces the following result,
given one of the indexes, say for example the TOPI:
Quarter Industry Weight Beta SysVol SpecVar
2017-Q3 I(1, 1) w(1, 1) β(1, 1) m(1, 1) s2 (1, 1)
2017-Q3 I(2, 1) w(2, 1) β(2, 1) m(2, 1) s2 (2, 1)
.. .. .. .. .. ..
. . . . . .
2017-Q3 I(n1 , 1) w(n1 , 1) β(n1 , 1) m(n1 , 1) s2 (n1 , 1)
2017-Q4 I(1, 2) w(1, 2) β(1, 2) m(1, 2) s2 (1, 2)
2017-Q4 I(2, 2) w(2, 2) β(2, 2) m(2, 2) s2 (2, 2)
.. .. .. .. .. ..
. . . . . .
2017-Q4 I(n2 , 2) w(n2 , 2) β(n2 , 2) m(n2 , 2) s2 (n2 , 2)
.. .. .. .. .. ..
. . . . . .
2022-Q1 I(1, 19) w(1, 19) β(1, 19) m(1, 19) s2 (1, 19)
2022-Q1 I(2, 19) w(2, 19) β(2, 19) m(2, 19) s2 (2, 19)
.. .. .. .. .. ..
. . . . . .
2022-Q1 I(n19 , 19) w(n19 , 19) β(n19 , 19) m(n19 , 19) s2 (n19 , 19)
where I(i, j) denotes the i-th industry in the j-th quarter, and w(i, j), β(i, j), m(i, j) and s2 (i, j) denotes the
weight, beta, systematic volatility and specific variance contributions of this industry to the given index in this
quarter, respectively. There are only nineteen quarters of data, hence j ∈ {1, 2, . . . , 19} while there are only
eleven FTSE ICB industries, hence i ∈ {1, 2, . . . , 11}. Moreover, due to changes in index constituents through
time the number of industries an index has exposure to will fluctuate through time - this is represented by the
whole numbers {n1 , n2 , . . . , n19 } each of which will also lie between 1 and 10. For a given index, importantly,
the following must hold for each quarter j:
nj nj
X X
w(i, j) = 1 , β(i, j) = β(j) ,
i=1 i=1
nj nj
X X
m(i, j) = m(j) , s2 (i, j) = s2 (j) ,
i=1 i=1
where β(j), m(j) and s2 (j) are the index’s beta, systematic volatility and specific variance in quarter j. In
designing this query, you may use the TOPI as the index under consideration. As a hint, you have to combine
all five tables while also using the SELECT clause, aggregation and arithmetic operations to produce this result.
#4 Write a MATLAB Function to Create the Decomposition Tables
Now that you have designed the query for the TOPI index, the same query can be used for all of the other
indices that are of interest to the client. This may be achieved by creating a MATLAB function that makes use
of the Database Toolbox and your query from step 3. At the same time, this offers us an opportunity to reformat
the data and improve visualisation of the data.
Write a MATLAB function named CreateDecompTables that takes as input a string object named indexCode
and outputs four table objects tw, tb, tv and ts all having the common format:
2017-Q3 2017-Q4 ··· 2022-Q1
Basic Materials
Consumer Discretionary
Consumer Staples
Energy
Financials
Health Care
Industrials
Real Estate
Technology
Telecommumications
Utilities
2
with tw, tb, tv and ts containing the decomposed weights, betas, systematic volatilities and specific variances
for the index specified by the string variable indexCode, respectively.
#5 Write a VBA Subroutine to Import the Decomposition Tables into Excel
Finally, with the MATLAB function from the previous step, it is possible to make the decomposition tables even
more accessible and visual for the client by creating a basic Excel application. The provided Excel workbook
named “Index [Link]” has only one worksheet with a predefined drop-down list containing all of the
indices in cell C4 and a command button that is linked to a VBA subroutine template named GetDecompTables.
In this subroutine you are required to make use of the index specified in cell C4 and MATLAB’s Spreadsheet
Link functionality to execute your CreateDecompTables and return:
• table tw to the range which starts at cell L8;
• table tb to the range which starts at cell L24;
• table tv to the range which starts at cell L40; and
• table ts to the range which starts at cell L56.
If done correctly, changing the index in cell C4 and clicking the command button will execute your MATLAB function,
import the data from MATLAB into the Excel worksheet and update the predefined stacked column graphs.
Table Description for the RMCS Database
The RMCS database consists of five tables:
1. [Link] FTSE ICB - this table contains all of the information that defines the FTSE’s ICB and will be populated
with data from the FTSE [Link] file;
2. [Link] JSE SS - this table contains the JSE alpha codes for all shares that have been listed on the JSE, along
with their ICB sub-sector codes, and will be populated with data from the FTSEJSE [Link] file;
3. [Link] AIFMRM MV - this table contains total volatility of the J203 index as estimated by AIFMRM at a quarterly
frequency and will be populated with data from the AIFMRM [Link] file;
4. [Link] AIFMRM ERS - this table contains beta, total and specific volatilities of all JSE-listed shares as estimated
by AIFMRM at a quarterly frequency and will be populated with data from the AIFMRM [Link] file; and
5. [Link] FTSEJSE ICW - this table contains the constituent information, including market capitalisation weights,
for the relevant indices at a quarterly frequency and will be populated with data from the FTSEJSE [Link] file.
Below is a general description of each of the aforementioned tables.
Table 2 - [dbo].[tbl JSE SS]
Table 1 - [dbo].[tbl FTSE ICB]
Column Category Description
Column Category Description
JSECode String Standard alpha JSE code.
SubSecCode String ICB sub-sector code.
SubSecCode String Associated sub-sector code.
SubSector String ICB sub-sector name.
SecCode String ICB sector code.
Sector String ICB sector name.
Table 3 - [dbo].[tbl AIFMRM MV]
SupSecCode String ICB super sector code.
SupSector String ICB super sector name.
Column Category Description
IndCode String ICB industry code.
Quarter String Formatted as ’yyyy-QQ’.
Industry String ICB industry name.
MktVol Numeric J203 index total volatility.
Table 4 - [dbo].[tbl AIFMRM ERS]
Table 5 - [dbo].[tbl FTSEJSE ICW]
Column Category Description
Column Category Description
Quarter String Formatted as ’yyyy-QQ’.
Quarter String Formatted as ’yyyy-QQ’.
JSECode String Standard alpha JSE code.
IndexCode String Standard index code.
Beta Numeric AIFMRM’s beta.
JSECode String Standard alpha JSE code.
Vol Numeric AIFMRM’s total volatility.
Weight Numeric Weight of share in index.
SpecVol Numeric AIFMRM’s specific volatility.
3