Books in black and white
 Books Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

# 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
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
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);
}
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
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
}