Displays the current exchange rates

Project

Note: Make 2 Separate Workbooks For Each Part

For Part 2 : Please refer the Black Scholes Option Pricing Doc where simulation and Option Pricing Formula is described.

Part 1

The website http://www.x-rates.com/d/CAD/table.html displays the current exchange rates between the Canadian Dollar and several other currencies. Set up an appropriate userform that possesses a button that will import this data from the website and place it onto a worksheet. In addition, place a second button on the userform that will allow the user to refresh the query at any time (Hint #1: assuming that the imported data begins in cell A3 – the VBA code to refresh the query would be Range("A3").QueryTable.Refresh BackgroundQuery:=False. Hint #2: only unload the userform when the cancel button is selected). Each time the query is refreshed, calculate which country has the “strongest” currency (defined for this question as the currency which 1 CAD buys the least of) and place this result into a textbox on the userform (Hint #3: You might want to look at how to use the Match function in Excel and then incorporate this function into your VBA code using Application.WorksheetFunction – up to you, but it requires only a single line of code).

Part 2

Consider the (Tech Stock Option Pricing file Attached) Antech stock option pricing file attached. Suppose now that a share of AnTech currently sells in the market for $34. The stock has an annual standard deviation of 17% (year = 0.17). The stock price has tended to increase at a rate of 12% per year (year = 0.12). The risk-free rate is 5% per year. An option is available for this stock with an expiration date of 6 months and an exercise price of $32.50. Rather than using data tables we would want to now price the option via simulation entirely in VBA code. Develop an application that consists of a userform containing the above, key data displayed in text boxes as the default values. However, the user will be allowed to change any of these values should they so wish. Also include a textbox which permits the user to input a value for the number of iterations of the simulation – be sure to allow for the input of “large” integer values (i.e. 100,000 or even 1,000,000 runs). When the user presses the OK button, have your application calculate the option values in a simulation analysis for both a Put and a Call on this stock using the data values selected. Once calculated, have these two option prices displayed on the userform. Do not unload the userform, since the user may wish to enter new values onto the form and resimulate the pricing routine. Only unload the userform on the selection of the Cancel button. Hint: For any probability values required, you can make use of the Application.WorksheetFunction object together with appropriate Excel functions. Note that in VBA, the Rnd or Rnd() function generates random numbers between 0 and 1 – this is different from the Rand() function in Excel.