Calc2Code Tips of the Day

Day 3 Part 2

This tip is about VBA. If you haven't ever used VBA, that's OK. You don't need VBA to get a lot of value out of Calc2Code. on the other hand, you don't need to learn and use very much VBA to get a lot more value out of Calc2Code. if you're new to VBA, hopefully you'll be willing to at least skim this tip to see how easy it is to take advantage of VBA.

Although we're programmers by trade here at Savvysoft, we appreciate that a lot of problems get solved more quickly in spreadsheets. But one thing spreadsheets don't handle well is looping. This is the notion of repeating something a certain number of times. Yes, you can create a spreadsheet formula and then copy it down some number of rows or across some number of columns, but the number of times the formula gets calculated is fixed based on the number of rows or columns it was copied to. A loop, on the other hand, can be run a different number of times, depending on the situation. So while Excel can recalaculate formulas based on a changing set of input cell values, it can't easily change the number of rows that contain a formula, or the number of cells included in a sum, based on the inputs.

A loop in VBA could take the following form:

For i = 1 to N

do some stuff here

Next i

where N can be one of the inputs, or a function of the inputs.

Calc2Code can convert this type of code to C++. Let's create an example where the "do some stuff here" is built using a spreadsheet, and then used inside a VBA function.

The spreadsheet stockoptionvalue.xls looks at a random path of a stock price over a year, and then calculates the value of a call option on that stock. It's similar to the example in the tutorial, but it shows how to work with an arbitrary simulation size:

For those who aren't familiar with options, a call option is the right to buy something at a prespecified strike price on (or before) a given date. In our example, that date is one year in the future.

We have 12 monthly points, and we say that the stock either jumps up or down every month. The starting stock price is given in A8, and the price at the end of the month depends on whether a random number is above or below .5. The size of the jump is based on the volatility. The stock also has an expectational rise equal to the risk free rate over one month. So if the stock goes up we have:

new stock price = old stock price * vol * (1+rate)^(1/12)

because (1+rate)^(1/12) is the monthly interest rate.

Given the random stock price at the end of the year, we calculate the option value as max(price strike,0), and then we discount that amount back to the present by dividing by 1+r.

Here's the configuration screen:

It's important to keep the return type as Pascal since we will be using this inside VBA. Also, we made the function type Volatile to make sure it recalculates each time F9 is hit (this makes the Rand() calls work each time).

This is the value given one simulation path. The full Monte Carlo would calculate the option value many times, and then average the results. How many times? It depends. So we'll use a loop that will let us try different numbers of times.

Here's the VBA to do it:

Private Declare Function stockoptionvalue Lib "stockoptionvalue" Alias "_stockoptionvalue@32" (ByVal p0 As Double,

ByVal p1 As Double, ByVal p2 As Double, ByVal p3 As Double) As Double

Function stockoptionsimulation(stockprice As Double, vol As Double, strike As Double, _
rate As Double, numsim As Long) As Double

Dim i As Long
Dim s As Double

s = 0

For i = 1 To numsim
s = s + stockoptionvalue(stockprice, vol, strike, rate)
Next i

stockoptionsimulation = s / numsim

End Function

This is found in the spreadsheet stockoptionsimulation.xls.

The Private Declare Function line tells VBA we're going to call into the function that was just created with Calc2Code. If your turboexcel.ini file has the line GenerateIncludes=Y in it, then this will be created for you in file in a subfolder of your function called includes\vb6\functionname.bas (this line is towards the bottom of the example file).

The function stockoptionsimulation(... line tells VBA the name of the function we're creating, and what the parameters are. This is similar to what gets done in the Calc2Code configuration screen.

The Dim lines tell VBA what variables we have. Then, we initialize s to zero. S will be used to keep a running sum of options values.

And then comes the loop. One of the inputs to this function is numsim, and the loop is based on this input. Inside the loop it merely calls into the previous function, and keeps a running tally of the option values. and after leaving the loop, it turns the running sum into an average.

That's it.

Telling Calc2Code to convert a VBA function is even easier than working with a spreadsheet function. You don't need to deal with input and result cells, just enter the name and click the VBA button:

After running the Calc2Code builder, we need to close this workbook and open a new one to test the function (otherwise having both the VBA version and the XLL open with the same name will confuse Excel). We can then enter =stockoptionsimulation(100,1.1,100,.05,1000) to get the result of 1000 simulations.