[SOLVED] Excel finance VBA Programming Take-Home Exam

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.