A typical calculator add-in, which values vanilla options

Overview

This simple add-in contains a calculator for calculating the value and derivatives of a vanilla option.

It is typical of financial calculator add-ins, and offers a useful model for an XLL+ add-in which makes use of existing pure C libraries.

Features

The sample displays the following features:

• The CfRevalCashflows add-in function accepts two vector arguments, of dates and cashflows. The Function Wizard generated all the code that deals with extracting and validating the vectors' values from the cell arrays passed by Excel.
• The library includes a function which returns a range of values (BsCalc) and a set of functions (BsValue, BsDelta etc) which return only one value, extracted from the range returned by BsCalc.
• The Div argument to the Black-Scholes functions is flexible. It can contain either a single number (representing dividend yield) or two columns containing the dates and values of predicted dividends.

Implementation

The following steps were significant in creating and completing the SimpOpt application.

1. Pre-cooked header and source files were added to the project, containing code for the business functions.
2. The Calendar functions (CalDiffYears, CalDiffDays, CalDaysInYear and CalDayCountText) were each implemented as a thin shell around existing business functions, with the majority of the code being generated by the XLL+ Function Wizard. This is typical of most real-world add-in functions.
3. Each of Black-Scholes functions has the same arguments. Therefore only one was entered into the XLL+ Function Wizard. All the rest were created as clones of the first function, using the Copy signature from... field of the New Function dialog.
4. Each of the single value Black-Scholes functions (BsValue, BsDelta etc) calls the array function (BsCalc), via CXlOper::CallAddin. The value of interest is then extracted from the array and returned to Excel.
5. The BsCalc function, which returns an array, allocates the array with CXlOper::AllocArray and populates it using CXlOper::Cell.
6. The Div argument to the Black-Scholes functions is specified as a CXlOper value type, meaning that it can contain any or no data. Code was written (in BsCalc_Impl) to examine the contents of the Div argument, and handle it differently depending on its shape.
7. The Excel date functions (XlLSerialToDate and XlDateToLSerial) were used for all date conversions. This immunizes the code from the problem of the user setting an unusual zero date in Excel's options.

Porting from XLL+ 5

This project was upgraded from XLL+ 5.0 using the Upgrade Wizard. You can find the old version in the Backup directory underneath the project directory.

One (repeated) line of code was incompatible, and had to be changed by hand.

The calls to CXlOper::CallAddin() originally looked like this:

// Call the array version of the function
CXlOper xloCalc;
xloCalc.CallAddin(BsCalc(Spot, Put, Div, Borrow, ImpVol,
    Strike, ValueDate, Maturity, RiskFree, DayCount));

The implementation function was renamed to BsCalc_Impl, and takes an extra first argument, xloResult, into which the result is put. The new version of the code therefore lookes like:

// Call the array version of the function
CXlOper xloCalc;
xloCalc.CallAddin(BsCalc_Impl(xloCalc, Spot, Put, Div, Borrow, ImpVol,
    Strike, ValueDate, Maturity, RiskFree, DayCount));

See Breaking changes in the User Guide for more information.

Classes and functions used

CXlOper::IsDouble | CXlOper::ToDouble | CXlOper::IsArray | CXlOper::GetWidth | CXlOper::ReadVector | CXlOper::AllocArray | CXlOper::IsArray | CXlOper::Cell | CXlOper::CallAddin | CXlOper::RetString | CXlOper::Ret | ::XllGetTypedApp | CXlOperCache::Clear | ::XlLSerialToDate | ::XlDateToLSerial

Sample project

Each sample project is located in a sub-directory of the Samples directory of the XLL+ installation. To use the sample project, open the solution file SimpOpt.sln or the project file SimpOpt.vcproj.

You can enable debugging under Excel by using the Setup Debugging command in the XLL+ ToolWindow.

When delivered, the help files are excluded from the build. You can enable the help build by selecting the files SimpOpt.help.xml and SimpOpt.chm in the Solution Explorer, and using the right-click menu to view Properties. Select the page "Configuration Properties/General" and set the "Excluded from build" property to "No". See Generating help in the User Guide for more information.

See Also

List of Sample Projects