MSCI516 – Spreadsheet Modelling for Quantitative FinanceIndividual Project 2022/23
Module Co-ordinator
You have two tasks, each worth 50%. For the first one, you have three questions, covering
different topics of the module, and you need to select only one of them. You will not get
additional marks if you do two or more questions in that task. For the second task, you have
a case study, which you need to automate using VBA. You are expected to do both tasks and
prepare a short report, explaining what you did, why you did that and what the answers to
the questions are.
This is an individual work. Although no one can forbid you from communicating with each
other and discussing the problem, it is expected that your models and report would be
different. You should not give your spreadsheet model or report to anyone and you should
not use files of anyone else. If we notice that some files have resemblance, this will be
treated as plagiarism.
Some of the elements of the work might resemble the things discussed in the class. This
does not mean that the code can be copied and pasted (this might also be treated as
plagiarism). You are expected to either develop the code from scratch or to modify the
existing code, so that it becomes apparent that you have created a new program. Failing to
do so might flag your work as plagiarism.
The file "MSCI516CourseworkData.xlsx" contains your specific data for task 2. Use your
library card number in order to find out what the initial conditions for the tasks are for you.
“MSCI516CourseworkSharesData.zip” file contains the share prices, which you will need for
portfolio optimisation.
Task 1 (50%)
Choose only one of the following questions, do it in Excel and prepare a short report (1000
to 1500 words), explaining what you did, why and answering the questions.
1. Energy consumption analysis
The file “EnergyConsumption.csv” contains half-hourly energy consumption in a region. The
company is interested in finding answers to several questions:
a) How many seasonal cycles are there in the data and what type of seasonality the
company is dealing with?
b) Is the average demand on energy increasing?
c) Are there any unexpected observations in the data that cannot be explained by the
standard decomposition? When did they happen?
2. Drivers Casualties
The file “DriversCasualties.csv” contains data on the number of deaths of drivers in car
accidents with the following variables:
DriversKilled – number of drivers killed per month.
front – front-seat passengers killed or seriously injured.
rear – rear-seat passengers killed or seriously injured.
kms – distance driven on average.
PetrolPrice – average petrol price per month.
law – binary variable, showing when the law for wearing seatbelts was in effect.
January – December – dummy variables for specific month of year.
You need to construct a meaningful regression model, do basic diagnostics of it and answer
the following questions based on the model:
a) In what month historically the highest number of deaths happen?
b) Was the seatbelts law effective in decreasing the number of drivers deaths?
c) What would be the number of deaths in January 1985 if the law was still in effect,
the distance driven was 20,000, petrol price was 0.11 and the number of front and
rear passengers killed (or injured) was 700 and 450 respectively?
3. Bitcoin price dynamics
The file “BTC-data.csv” contains Open, High, Low, Close prices of Bitcoin in USD together
with the Volume sold each day. Use Monte-Carlo simulations to generate a 1000 potential
price trajectories of the Bitcoin for the next two weeks. Assume a GBM process with Laplace
distribution (you might need to implement a quantile function for it in VBA, see Wikipedia
for the formula) with zero mean and a fixed scale instead of the Weiner process. After
generating the data, calculate the statistics for the Close prices necessary to answer the
following questions:
a) If we invest a 1000 USD today, what is the expected gain/loss in two weeks?
b) What is the Value-at-Risk of bitcoin on 1% level and how would you interpret it?
c) Calculate the Expected Shortfall on the 2% level and interpret it.
Monte-Carlo simulation of trajectories of price and calculation of VaR and ES. Assume GBM
for price.
Task 2 (50%)
An investment company Rebel Inc is creating a portfolio, they have an initial capital and
they want to buy some shares (see csv-files in "MSCI516CourseworkSharesData.zip" archive;
use “Close” prices). However, they also have some restrictions, because their CEO has strong
feelings about some types of shares (see "MSCI516CourseworkData.xlsx" file). They want to
increase the profitability of their portfolio, while decreasing the risks. However, they are not
100% sure about the objective function, so they also want to have an option of simply
increasing profitability and simply decreasing risks. Create a VBA script that would allow the
company analysing different scenarios and selecting a portfolio for their needs. Write a brief
report with the analysis of the results (1000 to 1500 words).
You can use the following as guidelines to the successful completion of the task:
1. Formulate the task in terms of linear programming.
2. Implement the model and solve the problem.
3. Analyse the results. Do the proportions of shares in the portfolio make sense?
4. What if the risk attitude was different?
5. Generate efficient frontier and analyse it.
6. Use VBA forms to collect the preferences of user.
Make the macro as flexible as possible, allowing changes in constraints, the cost function
and the option of changing the original data.
Assessment criteria:
Each of the tasks is assessed separately. After that the weighted average is calculated and is
used as a final mark. The project carries 100% of the module assessment.
The work will be assessed on the following criteria:
1. The correctness of the implementation (is the model formulated correctly?);
2. Ease of understanding of the MS Excel model (is it clear what should be changed
and what is fixed on a sheet, is it easy to read?);
3. The VBA code (efficiency, the absence of errors, the level of development etc);
4. The flexibility of the VBA model (how many elements are hard coded);
5. User interface (user forms, the readability of the model),
6. VBA best practices;
7. Analysis of the results (quality of the report).
If the number of words in the report is exceeded, the mark is decreased by 10%.
Submission
You need to submit:
The report (two sections) written in Microsoft Word or PDF file:
o the title of the report should contain the id of student,
o the report should not contain the name of student.
Excel files with implemented models;
MS Word or PDF file with the code of your macros (you can copy and paste it from
VBA);
All the other files needed for running your program (if there are any).
The files must be submitted on Moodle as they are (ZIP archives are not accepted) latest by
Monday (16 January 2023) 10am.
Note that the Moodle submission system is very strict in terms of deadline. Do not wait until
the last minute to submit your work.
Do not include your name in your submission to allow blind marking – your details can
always be found on Moodle after the marking.
Plagiarism
Software to detect plagiarism will be used in marking. Standard university procedure for
plagiarism applies for any detected plagiarism attempt. It is important to cite all the sources
used in the report (such as: url, a paper, a textbook). The code is also checked for plagiarism,
so make sure that you did not “borrow” it from anywhere else.
Late submission policy
Standard departmental penalties will apply for late submission unless you have been given
an extension (by departmental coordinators) for exceptional reasons.