XLL+ Class Library (7.0)

A function with a vector argument

The arguments we have used so far have been simple scalar values. Most add-in functions also need to be able to handle array values. In the following sections we are going to use the Function Wizard to create a function with a vector input.

Historical volatility function

We are going to add a function to calculate the historical volatility of a series of daily prices. The function will be implemented as a stand-alone pure C++ function, with no awareness of Excel.

Add the following code to Tutorial1.cpp:

// Calculates historical volatility of daily price data 
// May throw an exception (as text) 
double CalcHistVol(const std::vector<double>& values, double dDaysInYear)
    if (values.size() < 2)
        throw "too few points";

    // Calculate the returns 
    std::vector<double> returns(values.size() - 1);
    for (size_t i = 0; i < returns.size(); i++) {
        double dDen = values[i];
        if (dDen == 0.0)
            throw "zero value";
        double dRatio = values[i + 1] / dDen;
        if (dRatio < 0.0) 
            throw "negative value";
        returns[i] = log(dRatio);

    // Calculate the standard deviation of the returns 
    double dSum = 0.0, dSumSq = 0.0, dSD;
    for (size_t i = 0; i < returns.size(); i++) {
        double ret = returns[i];
        dSum += ret;
        dSumSq += ret * ret;
    dSD = sqrt((dSumSq - (dSum * dSum / (double)returns.size()))
             / ((double)returns.size()));

    // Calculate the historic volatility 
    if (dDaysInYear <= 0.0) 
        throw "DaysInYear must be positive";
    return dSD * sqrt(dDaysInYear);

The function returns the historical volatility of the series if it succeeds. Otherwise, it throws an exception.

Next: Creating a vector argument >>