VBA with Applications for Finance - IT Computer Science Assignment Help

Assignment Task

CASE STUDY

You have just received a positive response from Merrill Lynch on a recent graduate ‘Financial Analyst’ role you had applied for and you are invited to a job interview. The job specification includes, among other things, knowledge of VBA core features, but with the prospect of further training within the organisation. If successful you will work in a recently re-organised asset pricing department and if you were to be successful, you’d work as part of a team dealing with financial analysis and asset pricing that also involves computational work. Your prospective employer has prepared a trial test for you as part of the interview, entirely based on introductory VBA programming.

You’d be expected to be tested on programming skills in VBA, where a specific task is given that must be coded within a limited amount of time to a problem specification. You are expected to code the solution with user-defined functions, some elements of object orientation, and relevant graphical user interface(GUI) capabilities at some capacity. Make use of good VBA-style comments, variable declaration, initialization, data processing, control flow statements, function cohesion, and coupling. You are also expected to demonstrate that you understand both the syntax, function design, and have good and timely programming skills in VBA.

PROBLEM SPECIFICATION

Consider a scenario where investor can select a number of investments to be part of a financial portfolio. Your VBA program should be able to process that information and create the initial Excel layout with either default values or values you enter through prompts:

VBA program should be able to compute the V (variance covariance) matrix values:

VBA program should compute the capital weights for an n-security portfolio inferred by the following Lagrange system:

VBA program should use a range of target portfolio returns from 0% to 100% and the Lagrange system to compute the capital weight solutions. Portfolio risk should be computed using the Markowitz formula given above.

You should also code a choice to increase the number of assets in portfolio by any number of assets. Your VBA program should have the capabilities to insert the relevant rows and columns in the macro-enabled excel file to accommodate for the data of the new assets.

Your program should be organised in such a way that some of it is done by macros (subs) and the rest in dedicated user-defined functions of your choice, but not less than 4 user defined functions. Make good use of the VBA’s object model as well as cohesion and coupling programming principles. The finished product must contain at least four well designed user-defined functions, several macros (subs), at least one class, and at least one graphical user interface. There is only one program file you are expected to submit within a macro-enabled excel workbook file.

The program should output the capital weight values within 4 decimal points and in a field with a reasonable character width, as well as the portfolio expected return, volatility, utility, and sharp ratio within a reasonable character width and 4 decimal places. The values should be sorted from the lowest portfolio return to the highest. It should print out the maximum utility and sharp ratio, clearly indicating the corresponding portfolio with its return and volatility attributes. The input must be robustly validated with error handling and the output should be properly formatted.

You must produce a running program saved in a Microsoft Excel Macro-Enabled Worksheet (.xlsm) file named “studentID.xlsm”. You may make use of your own recorded macros to plot the portfolio mean-variance efficient frontier, CML, and identify the market portfolio, based on the program output data in the Macro enabled excel file. This would allow you to check with literature that the shape of the graph is as described in literature. You may wish to note the maximum utility and sharp ratio in the graph. You do not need to produce any written work on the theorized financial portfolio elements. Focus is on the programming skills.

You have flexibility on how you organise the code, and the layout of data in Excel; both input and output should be in the same macro enabled excel workbook file. However pay attention to details and programming principles applied.

The program must run in order to be marked.

This IT-Computer Science Assignment has been solved by our IT-Computer Science Experts at My Uni Papers. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing Style. Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered.

You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turn tin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.