XLL+ Class Library

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
// Returns 1 for success, 0 for failure
int CalcHistVol(double* adValues, int cPoints, double dDaysInYear, double* pdVol)
{
    double *adReturns = 0;
    double *pdOut, *pdIn;
    double dSum = 0.0, dSumSq = 0.0, dSD, dRatio;
    int i;
    int rc = 1;
    #define DAYS_IN_YEAR 250

    if (cPoints < 2)
        return 0;

    // Calculate the returns
    adReturns = (double*)malloc(sizeof(double) * (cPoints - 1)); 
    for (i = 0, pdIn = adValues, pdOut = adReturns; i < cPoints - 1; i++, pdIn++, pdOut++) {
        if (*pdIn == 0.0) {
            rc = 0;
            goto cleanup;
        }
        dRatio = pdIn[1] / pdIn[0];
        if (dRatio < 0.0) {
            rc = 0;
            goto cleanup;
        }
        *pdOut = log(dRatio);
    }

    // Calculate the standard deviation of the returns
    for (i = 0, pdIn = adReturns; i < cPoints - 1; i++, pdIn++) {
        dSum += *pdIn;
        dSumSq += *pdIn * *pdIn;
    }
    dSD = sqrt((dSumSq - (dSum * dSum / (double)(cPoints - 1)))
             / ((double)(cPoints - 1)));

    // Calculate the historic volatility
    if (dDaysInYear <= 0.0) {
        rc = 0;
        goto cleanup;
    }
    *pdVol = dSD * sqrt(dDaysInYear);

cleanup:
    free(adReturns);
    return rc;
}

The function returns 0 if it fails and 1 if it succeeds. If it succeeds, the value of *pdVol is set to the historical volatility of the series.

Next: Creating a vector argument >>