Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Excel add in development in C++ Aplications in finance - Dalton S.

Dalton S. Excel add in development in C++ Aplications in finance - Wiley publishing , 2005. - 425 p.
ISBN 0-470-02469-0
Download (direct link): exceladdindevelopmentincand2005.pdf
Previous << 1 .. 136 137 138 139 140 141 < 142 > 143 144 145 146 147 148 .. 168 >> Next

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);
}
Example Add-ins and Financial Applications
347
#define B1 0.31938153
#define B2 -0.356563782
#define B3 1.781477937
#define B4 -1.821255978
#define B5 1.330274429
#define PP 0.2316419
#define ROOT_2PI 2.506628274631
double cndist(double d)
{
if(d == 0.0) return 0.5;
double t = 1.0 / (1.0+ PP* fabs(d));
double e = exp(-0.5 * d * d) / ROOT_2PI;
double n = ((((B5 * t + B4) * t + B3) * t + B2) * t + B1) * t;
return (d > 0.0) ? 1.0 - e * n : e * n;
}
Function name norm_dist_inv (exported) Ndistlnv (registered with Excel)
Description Returns the inverse of N(x) consistent with the norm dist ( ).
Prototype xloper * stdcall norm_dist_inv(double d);
Type string "BB"
Notes Returns the inverse of norm_dist ( ). Uses a simple solver to return, as far as possible, the exact corresponding value and for this reason may be slower than certain other functions. Code could be easily modified to return the inverse of NORMSDIST() if required.
#define NDINV_ITER_LIMIT 50
#define NDINV_EPSILON 1e-12 // How precise do we want to be
#define NDINV_FIRST_NUDGE 1e-7
// How much change in answer from one iteration to the next #define NDINV_DELTA 1e-10
// Approximate working limits of Excel 2000's NORMSINV() function #define NORMSINV_LOWER_LIMIT 3.024e-7 #define NORMSINV_UPPER_LIMIT 0.999999
xloper * stdcall norm_dist_inv(double prob)
{
if(prob <= 0.0 || prob >= 1.0) return p_xlErrNum;
// Get a (pretty) good first approximation using Excel's NORMSINV() // worksheet function. First check that prob is within NORMSINV's // working limits
static xloper op_ret_val;
348
Excel Add-in Development in C/C++
double v1, v2, p1, p2, pdiff, temp;
op_ret_val.xltype = xltypeNum;
if(prob < NORMSINV_LOWER_LIMIT)
{
v2 = (v1 = -5.0) - NDINV_FIRST_NUDGE;
}
else if(prob > NORMSINV_UPPER_LIMIT)
{
v2 = (v1 = 5.0) + NDINV_FIRST_NUDGE;
}
else
{
op_ret_val.val.num = prob;
Excel4(xlfNormsinv, &op_ret_val, 1, &op_ret_val);
if(op_ret_val.xltype != xltypeNum)
return p_xlErrNum; // shouldn't need this here
v2 = op_ret_val.val.num; v1 = v2 - NDINV_FIRST_NUDGE;
}
// Use a secant method to make the result consistent with the // cndist() function
p2 = cndist(v2) - prob;
if(fabs(p2) <= NDINV_EPSILON)
{
op_ret_val.val.num = v2;
return &op_ret_val; // already close enough
}
p1 = cndist(v1) - prob;
for(short i = NDINV_ITER_LIMIT; --i;)
{
if(fabs(p1) <= NDINV_EPSILON || (pdiff = p2 - p1) == 0.0)
{
// Result is close enough, or need to avoid divide by zero op_ret_val.val.num = v1; return &op_ret_val;
}
temp = v1;
v1 = (v1 * p2 - v2 * p1) / pdiff;
if(fabs(v1 - temp) <= NDINV_DELTA) // not much improvement {
op_ret_val.val.num = v1; return &op_ret_val;
}
v2 = temp; p2 = p1;
p1 = cndist(v1) - prob;
}
return p_xlErrValue; // Didn't converge
}
Example Add-ins and Financial Applications
349
Table 10.1 shows a comparison of Excel and the above functions from which it can be seen that Excel 2002 has greatly improved accuracy over 2000.
Table 10.1 Excel’s normal distribution accuracy
Excel 2000 Excel 2002
Cumulative distribution 4 4
NORMSDISTO NORMSINVO Error (absolute) 0.999968314 4.000030458 3.0458E-05 0.999968329 4 —3.26814E-11
Ndist() Ndistlnv() Error (absolute) 0.999968314 3.999999998 -1.76691E-09 0.999968314 4 —5.40723E-12
Both the norm_dist() and norm_dist_inv() functions could easily be made to return results based on any of the algorithms and methods discussed above, including Excel’s own worksheet functions, with the addition of an extra method parameter. Both functions could even be accommodated in a single function interface.
Previous << 1 .. 136 137 138 139 140 141 < 142 > 143 144 145 146 147 148 .. 168 >> Next