# VBA Programming 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:

- Your application should
**at least**allow the user to:

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

x Calculate the Sharpe Ratio risk-adjusted performance measures for each portfolio; x Fill a pre-created graph with data in order to show the efficient frontier; x Calculate the weight of each asset in the minimum variance portfolio.

- 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).
- Allow users to select increments of weights for the portfolio formation.
- 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

- The input data of your UDF’s
**must be adjusted close prices**. It means that the**returns**of each asset must be calculated in arrays**within your macros**. - 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.
- You must make a robust error checking (check input from user for correctness), both for the parameters of your UDF’s and for the user input in the userforms.
- 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).
- 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.

- 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**’. - 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. - Enhancements can include but are not limited to:

x Implementation of alternative risk-adjusted performance measure (implying the creation of another UDF). For instance, Jensen’s 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 Jensen’s alpha is just represented by the estimate of the constant :

The signature of this function could be:

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

x Creative userform design;

x 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;

x Create a new chart from the GUI for the efficiency frontier (instead of filling a pre-created one); x Allow stock prices to be automatically imported from a text file;

x 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); x Let your creative juices flow for further enhancements… but make enhancements pertinent to the tasks at hand!

- 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.
- 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 won’t be read).
- 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. - 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**. - Submitting your work:

x You will see 2 assignments on Blackboard – one for each teacher: use the right one, depending on the teacher you had in class!

x **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”. **

x **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**.