[SOLVED] gui Excel finance Financial Economics Track

Financial Economics Track

2016-2017

Course: Introduction to Excel and VBA PROGRAMMING

FINAL EXAM

DATE : POSTED FRIDAY FEB. 26, DUE MONDAY MARCH 7

TH
BEFORE MIDNIGHT

Surname: Student number:
First name:
Group:

If it is a group assignment, thank you for specifying the name, student number and Group of the other working
members ( + signature)

1-
2-

EDHEC Business School expects honesty from students in presenting all of their academic work. Students are
responsible for knowing and observing accepted principles of scholarly research and writing in all academic work.
Academic dishonesty or cheating includes acts of plagiarism, forgery, fabrication or misrepresentation such as the
following:

Using someone elses ideas or words without appropriate acknowledgement,
Submitting your own work in more than one course without the permission of the instructor,
Making up sources or facts,
Obtaining or providing unauthorized assistance on any assignment (this includes collaborating with others on

assignments that are supposed to be completed individually).
All suspected cases of academic dishonesty will be investigated by the procedure outlined in the School Rules.
If you have any questions about what is, or not, permitted in the course, please do not hesitate to contact the course
instructor.

I do hereby acknowledge that I have carefully read this, and fully understand, agree with, and accept
the terms.

PART FILLED BY THE PROFESSOR.

Grade:

Comment Field:

VBA Programming Take-Home Exam
Portfolio Analysis

Instructions:
Your objective is to build an application to perform the analysis of a two-asset portfolio.

The input data consists in two tables containing the dates and adjusted close prices of two different assets. There is a

third table with the same data for a risk-free asset: you will use it to calculate the Sharpe ratio.

The most commonly used risk-adjusted performance measure in finance is the Sharpe Ratio. It relies on volatility as

risk measure and considering historical returns. The Sharpe Ratio is usually computed as:

Where
denotes the sample average of realised returns,the constant return over the risk-free asset and

the

sample volatility of portfolio returns.

Minimum requirements of your application:

1. Your application should at least allow the user to:

Fill a table with weight of asset #1 in the portfolio (ranging from 0-100%), expected portfolio returns,
portfolio standard deviations and portfolio variances;

Calculate the Sharpe Ratio risk-adjusted performance measures for each portfolio;

Fill a pre-created graph with data in order to show the efficient frontier;

Calculate the weight of each asset in the minimum variance portfolio.

2. Everything can be done from a Graphical User Interface (GUI), with no need to execute macros manually (you

must use Excel Userforms to build the GUI).

3. Allow users to select increments of weights for the portfolio formation.

4. The portfolios table must contain formulas (and not just values) everywhere it is possible to use one. To this

aim, your Excel file must contain the following User-Defined Functions (UDF) (you must respect EXACLTY

the following syntax):

portfolio_mean_return(asset1_prices As Range, asset2_prices As Range, proportionAsset1 As Double) As Variant

portfolio_variance(asset1_prices As Range, asset2_prices As Range, proportionAsset1 As Double) As Variant

sharpe_ratio(asset1_prices As Range, asset2_prices As Range, proportionAsset1 As Double, risk_free_prices As Range) As Variant

minimum_variance_portfolio(rangeA As Range, rangeB As Range) As Variant

5. The input data of your UDFs must be adjusted close prices. It means that the returns of each asset must be

calculated in arrays within your macros.

6. The return value of the function minimum_variance_portfolio is the proportion of the asset represented by the

first parameter giving the minimum variance portfolio.

7. You must make a robust error checking (check input from user for correctness), both for the parameters of

your UDFs and for the user input in the userforms.

8. Your workbook must contain sample data of your choice (typically, monthly close prices of 2 assets of your

choice over 5 or 6 years, and the corresponding price of a risk-free asset of your choice).

9. Your application must be generic and easy to reuse on different data sets. To put it clearly, your VBA

application will be tested on a third sample of data.

General recommendations
Read these carefully as they impact your final grade.

1. You must use the template Excel file available on Black Board. The data and the different outputs of your
work must be on the worksheet portfolio analysis.

2. The requirements above are a minimum. Some points will be allocated for improvements, if they make the

application better. As a consequence, an application meeting only the basic requirements cannot expect the

maximum grade. Original improvements will be specially rewarded.

3. Enhancements can include but are not limited to:

Implementation of alternative risk-adjusted performance measure (implying the creation of another

UDF). For instance, Jensens alpha is given by the outperformance (or underperformance) of the

portfolio with respect to the CAPM-implied expected return. Once a CAPM regression is run for the

portfolio under analysis, the Jensens alpha is just represented by the estimate of the constant:

The signature of this function could be:

Creative userform design;

Users can navigate through several userforms easily, one userform per task (efficiency frontier,

building the portfolios table, etc.), and only one userform is visible at the same time;

Create a new chart from the GUI for the efficiency frontier (instead of filling a pre-created one);

Allow stock prices to be automatically imported from a text file;

Harder: Allow users to give ticker symbol of stocks, a start date and an end date (and possibly the

choice between weekly, monthly, annual data) and then use a macro to import data from a website

(only try this if all the basics work well);

Let your creative juices flow for further enhancements but make enhancements pertinent to the

tasks at hand!

4. The result is not all right or all wrong: some point will be allocated for the quality of the code and the quality

of the user experience! Make a user-friendly application. Respect the best practices seen in class.

5. Explain everything the users need to know to use your application, with a special focus on the improvements,

in the worksheet user manual (but not in a separate file, as it wont be read).

6. Make sure that your application works on a PC running Windows / Excel 2007, 2010 or 2013. Do not use

ActiveX controls or other non-standard extensions, as you cannot be sure that these controls will be available

on the computer used to grade your application.

7. You can work in pairs of two for this exam. No groups of more than two are accepted. If you choose to work

in pairs, only upload the exam file ONCE.

8. Submitting your work:

You will see 2 assignments on Blackboard one for each teacher: use the right one, depending on the

teacher you had in class!

Upload a single Excel file containing all the work, with the names and groups of both partners

in the file name. Example: Teller-Pierre-A1_Lespagnol-Vivien-B2.xlsm.

You must also upload the consent form with both names and signatures indicating that you are

aware of penalties for non-adherence to the Academic Honesty policy. Any pieces of work judged

to be too similar will be investigated and heavily sanctioned.

9. DEADLINE: All files must be uploaded before midnight on Sunday March 19, 2017. NO EXCEPTIONS

will be made, late submissions will be penalized: -2 points each day, starting the first minute after the

deadline.

Jensen_alpha(asset1 As Range, asset2 As Range, proportionAsset1 As Double, risk_free As Range, benchmark As Range) As Variant