Excel Add-in Development in C/C++ Applications in Finance phần 10 potx

Example Add-ins and Financial Applications 341

Prototype xloper * __stdcall compare_nchars(char *Atext,

char *Btext, short n_chars, xloper

*op_is_case_sensitive);

Type string "RCCIP"

Notes Any error in input is reflected with an Excel #VALUE! error. Return

type does not need to allow for reference xlopers. This function is

a wrapper for the C library functions strncmp() and

strincmp().

xloper * __stdcall compare_nchars(char *Atext, char *Btext,

short n_chars, xloper *op_is_case_sensitive)

{

static xloper ret_oper = {0, xltypeNum};

if(!Atext || !Btext || n_chars <= 0 || n_chars > 255)

return p_xlErrValue;

// Case-sensitive by default

bool case_sensitive = (op_is_case_sensitive->xltype == xltypeBool

&& op_is_case_sensitive->val._bool == 1);

if(!case_sensitive)

ret_oper.val.num = strnicmp(Atext, Btext, n_chars);

else

ret_oper.val.num = strncmp(Atext, Btext, n_chars);

return &ret_oper;

}

Function

name

concat (exported)

Concat (registered with Excel)

Description Concatenate the contents of the given range (row-by-row) using the

given separator (or comma by default). Returned string length limit is

255 characters by default, but can be set lower. Caller can specify the

number of decimal places to use when converting numbers.

Prototype xloper * __stdcall concat(xloper *inputs, xloper

*p_delim, xloper *p_max_len, xloper *p_num_decs);

Type string "RPPPP"

xloper * __stdcall concat(xloper *inputs, xloper *p_delim,

xloper *p_max_len, xloper *p_num_decs)

342 Excel Add-in Development in C/C++

{

cpp_xloper Inputs(inputs);

if(Inputs.IsType(xltypeMissing | xltypeNil))

return p_xlErrValue;

char delim = (p_delim->xltype == xltypeStr) ?

p_delim->val.str[1] : ',';

long max_len = (p_max_len->xltype == xltypeNum) ?

(long)p_max_len->val.num : 255l;

long num_decs = (p_num_decs->xltype == xltypeNum) ?

(long)p_num_decs->val.num : -1;

char *buffer = (char *)calloc(MAX_CONCAT_LENGTH, sizeof(char));

char *p;

cpp_xloper Rounding(num_decs);

long total_length = 0;

DWORD size;

Inputs.GetArraySize(size);

if(size > MAX_CONCAT_CELLS)

size = MAX_CONCAT_CELLS;

for(DWORD i = 0; i < size;)

{

if(num_decs >= 0 && num_decs < 16

&& Inputs.GetArrayElementType(i) == xltypeNum)

{

xloper *p_op = Inputs.GetArrayElement(i);

Excel4(xlfRound, p_op, 2, p_op, &Rounding);

}

Inputs.GetArrayElement(i, p);

if(p)

{

if((total_length += strlen(p)) < MAX_CONCAT_LENGTH)

strcat(buffer, p);

free(p);

}

if(++i < size)

buffer[total_length] = delim;

if(++total_length > max_len)

{

buffer[max_len] = 0;

break;

}

}

cpp_xloper RetVal(buffer);

free(buffer);

return RetVal.ExtractXloper(false);

}

Function name parse (exported)

ParseText (registered with Excel)

Example Add-ins and Financial Applications 343

Description Parse the input string using the given separator (or comma by

default) and return an array. Caller can request conversion of all

fields to numbers, or zero if no conversion possible. Caller can

specify a value to be assigned to empty fields (zero by default).

Prototype xloper * __stdcall parse(char *input, xloper

*p_delim, xloper *p_numeric, xloper *p_empty);

Type string "RCPP"

Notes Registered name avoids conflict with the XLM PARSE() function.

xloper * __stdcall parse(char *input, xloper *p_delim,

xloper *p_numeric, xloper *p_empty)

{

if(*input == 0)

return p_xlErrValue;

cpp_xloper Caller;

Excel4(xlfCaller, &Caller, 0);

Caller.SetExceltoFree();

if(!Caller.IsType(xltypeSRef | xltypeRef))

return NULL; // return NULL in case was not called by Excel

char delimiter =

(p_delim->xltype == xltypeStr && p_delim->val.str[0]) ?

p_delim->val.str[1] : ',';

char *p = input;

WORD count = 1;

for(;*p;)

if(*p++ == delimiter)

++count;

cpp_xloper RetVal;

RetVal.SetTypeMulti(1, count);

// Can't use strtok as it ignores empty fields

char *p_last = input;

WORD i = 0;

double d;

bool numeric = (p_numeric->xltype == xltypeBool

&& p_numeric->val._bool == 1);

bool empty_val = (p_empty->xltype != xltypeMissing);

while(i < count)

{

if((p = strchr(p_last, (int)delimiter)))

*p = 0;

if((!p && *p_last) || p > p_last)

{

344 Excel Add-in Development in C/C++

if(numeric)

{

d = atof(p_last);

RetVal.SetArrayElement(0, i, d);

}

else

RetVal.SetArrayElement(0, i, p_last);

}

else if(empty_val) // empty field value

{

RetVal.SetArrayElement(0, i, p_empty);

}

i++;

if(!p)

break;

p_last = p + 1;

}

return RetVal.ExtractXloper(false);

}

10.2 STATISTICAL FUNCTIONS

As a mathematics professor once told the author (his student), a statistician is someone

with their feet in the fridge, their head in the oven, who thinks on average they are quite

comfortable. This scurrilous remark does no justice at all to what is a vast, complex

and, of course, essential branch of numerical science. Excel provides many functions

that everyday statisticians, actuaries, and so on, will use frequently and be familiar with.

Finance professionals too are heavy users of these built-in capabilities.1 This section only

aims to provide a few examples of useful functions, or slight improvements on existing

ones, that also demonstrate some of the interface issues discussed in earlier chapters.

Financial markets option pricing relies heavily on the calculation of the cumulative

normal (Gaussian) distribution for a given value of the underlying variable (and its

inverse). Excel provides four built-in functions: NORMDIST(), NORMSDIST(), NORMINV() and

NORMSINV(). One small problem with Excel 2000 is that the inverse functions are not pre￾cise inverses. Another is that the range of probabilities for which NORMSINV() works is

not as great as you might wish – see example code below. (Both these problems are fixed

in Excel 2002.) This can lead to accumulated errors in some cases or complete failure.

The function NORMSDIST(X) is accurate to about ±7.3 × 10−8 and appears to be based on

the approximation given in Abramowitz and Stegun (1970), section 26.2.17, except that

for X > 6 it returns 1 and X < −8.3 it returns zero.2

There is no Excel function that returns a random sample from the normal distribution.

The compound NORMSINV(RAND()) will provide this, but is volatile and therefore may not

be desirable in all cases. In addition to its volatility, it is not the most efficient way to

calculate such samples.

1 See Jackson and Staunton (2001) for numerous examples of applications of these functions to finance. 2 Inaccuracies in these functions could cause problems when, say, evaluating probability distribution functions

from certain models.

Example Add-ins and Financial Applications 345

This section provides a consistent and more accurate alternative to the NORMSDIST() and

NORMSINV(), as well as functions (volatile and non-volatile) that return normal samples.

The normal distribution with mean zero and standard deviation of 1 is given by the

formula:

N (x) = 1

√2π

x

−∞

e−t 2/2 dt

From this the following Taylor series expansion and iterative scheme can be derived:

N (x) = 1

2 +

1

√2π

∞

n=0

tn

t0 = x

tn = tn−1.

x2(2n − 1)

2n(2n + 1)

Starting with this, it is straightforward to construct a function that evaluates this series

to the limits of machine accuracy, roughly speaking, subject to cumulative errors in the

terms of the summation. These cumulative errors mean that, for approximately |x| > 6,

a different scheme for the tails is needed.

The source code for all these functions in this section is in the module XllStats.cpp

in the example project on the CD ROM. They are registered with Excel under the category

Statistical.

Function name ndist_taylor (exported)

NdistTaylor (registered with Excel)

Description Returns a two-cell row vector containing (1) the value of N(x)

calculated using the above Taylor series expansion, and (2) a

count of the number of terms used in the summation. For

|x| < 6 this is accurate roughly to within 10−14.

Prototype xloper * __stdcall ndist_taylor(double d);

Type string "RB"

Notes Uses the expansion for |x| < 6 and the same approximation as

Excel (but not Excel’s implementation of it) for the tails. The

function called is a wrapper to a function that has no knowledge

of Excel data types.

xloper * __stdcall ndist_taylor(double d)

{

double retvals[2];

int iterations;

retvals[0] = cndist_taylor(d, iterations);

retvals[1] = iterations;

346 Excel Add-in Development in C/C++

cpp_xloper RetVal((WORD)1, (WORD)2, retvals);

return RetVal.ExtractXloper();

}

double cndist_taylor(double d, int &iterations)

{

if(fabs(d) > 6.0)

{

// Small difference between the cndist() approximation and the real

// thing in the tails, although this might upset some pdf functions,

// where kinks in the gradient create large jumps in the pdf

iterations = 0;

return cndist(d);

}

double d2 = d * d;

double last_sum = 0, sum = 1.0;

double factor = 1.0;

double k2;

for(int k = 1; k <= MAX_CNDIST_ITERS; k++)

{

k2 = k << 1;

sum += (factor *= d2 * (1.0 - k2) / k2 / (k2 + 1.0));

if(last_sum == sum)

break;

last_sum = sum;

}

iterations = k;

return 0.5 + sum * d / ROOT_2PI;

}

Function name norm_dist (exported)

Ndist (registered with Excel)

Description Returns the value of N(x) calculated using the same

approximation as Excel (but not Excel’s implementation of it).

Prototype xloper * __stdcall norm_dist(double d);

Type string "BB"

Notes NORMSDIST, in Excel 2000 and earlier, rounds down to zero

for x < −8.3 and up to 1 for x > 6.15. The function called is

a wrapper to a function that has no knowledge of Excel data

types.

double __stdcall norm_dist(double d)

{

return cndist(d);

}