代写MSCI516、代写Java/Python编程

日期：2023-01-01 12:53

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.