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.
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.