代写FIN6102 Spreadsheet and VBA Modelling in Finance Homework 3调试R语言

FIN6102 Spreadsheet and VBA Modelling in Finance

Homework 3

Homework: VBA Exercises

Exercise 1

Please write a VBA sub to sum A1:A5 conditioning on B1:B5. More specifically, add cells in A  column if the corresponding cell in column B equals 1, and ignore the value if the corresponding cell in column B equals 0. Send an error message if column B contains other values. You should output your result to A6.

For example:

A1:A5 = [1,2,3,4,5]

B1:B5 = [1,0,0,1,0]

Your output to A6 should be 5 (adding 1 and 4, ignoring other) A1:A5 = [1,2,3,4,5]

B1:B5 = [1,0,2,1,0]

Your output to A6 should be #N/A and an error message box should pop out.

Exercise 2

Write a VBA code that will copy all the cells on the current worksheet and paste the values to the same worksheet, so that all the cells that originally contain formulas will now become values only. (Hint: try record macro if you don’t know how to do copy & paste value)

Exercise 3

Write a program for option pricing that allows user to freely set all parameters about the option and underlying stock. In addition, the program should allow user to choose: 1) Option Type: Put or Call, 2) Option Style. European or American and 3) Pricing method: Black Scholes Formula  (for European Style. Only), Monte Carlo Simulation (for European Style. Only), and Binomial Tree.

Print out both your results and your simulation paths (for Monte Carlo and Binomial Tree)

Hint: Write Black Scholes formula, Monte Carlo Simulation and Binomial Tree separately. Then use a main program to call each Sub based on user’s choice of pricing method.

Here is what the interface should look like.