Thursday, January 14, 2021

ALGLIB: Numerical analysis and data processing library in VB6 (by Dr. Sergey Bochkanov)

Download from ME
Download from VBForums

102 modules containing several hundred advanced mathematical functions written by Dr. Sergey Bochkanov. Some of the functions, include:
  • Decision forest classifier (regression model)
  • K-means++ clustering
  • Linear discriminant analysis
  • Linear models
  • Logit models
  • Basic neural network operations
  • Neural network ensemble models
  • Neural network training
  • Principal component analysis
  • Ordinary differential equation solver
  • Fast real/complex convolution
  • Fast real/complex cross-correlation
  • Real/complex FFT
  • Real Fast Hartley Transform
  • Adaptive 1-dimensional integration
  • Gauss-Kronrod quadrature generator
  • Gaussian quadrature generator
  • Inverse distance weighting: interpolation/fitting
  • Linear and nonlinear least-squares solvers
  • Polynomial interpolation/fitting
  • Parametric spline interpolation
  • Rational interpolation/fitting
  • 1D spline interpolation/fitting
  • 2D spline interpolation
  • Level 2 and Level 3 BLAS operations
  • Bidiagonal SVD
  • Eigensolvers
  • Sherman-Morrison update of the inverse matrix
  • LDLT decomposition
  • Determinant calculation
  • Random matrix generation
  • Matrix inverse
  • Real/complex QR
  • LQ
  • bi(tri)diagonal
  • Hessenberg decompositions
  • Condition number estimate
  • Schur decomposition
  • Determinant of a symmetric matrix
  • Symmetric inversion
  • Generalized symmetric eigensolver
  • Condition number estimate for symmetric matrices
  • Singular value decomposition
  • LU and Cholesky decompositions
  • ASA bound constrained optimizer
  • Conjugate gradient optimizer
  • Limited memory BFGS optimizer
  • Improved Levenberg-Marquardt optimizer
  • Nearest neighbor search: approximate and exact
  • Dense linear system solver
  • Symmetric dense linear system solver
  • Airy functions
  • Bessel functions
  • Beta function
  • Chebyshev polynomials
  • Dawson integral
  • Elliptic integrals
  • Exponential integrals
  • Fresnel integrals
  • Gamma function
  • Hermite polynomials
  • Incomplete beta function
  • Incomplete gamma function
  • Jacobian elliptic functions
  • Laguerre polynomials
  • Legendre polynomials
  • Psi function
  • Trigonometric integrals
  • Binomial distribution
  • Chi-Square distribution
  • Pearson/Spearman correlation coefficients
  • Hypothesis testing: correlation tests
  • Descriptive statistics: mean
  • variance, etc.
  • F-distribution
  • High quality random numbers generator
  • Hypothesis testing: Jarque-Bera test
  • Hypothesis testing: Mann-Whitney-U test
  • Normal distribution
  • Poisson distribution
  • Hypothesis testing: sign test
  • Student's t-distribution
  • Hypothesis testing: Student's t-test
  • Hypothesis testing: F-test and one-sample variance test
  • Hypothesis testing: Wilcoxon signed rank test.

Original content from Dr. Sergey Bochkanov:

Contents

Introduction
Getting started with ALGLIB
FAQ
AP library description
ALGLIB reference manual

Introduction

Sections

  • ALGLIB license
  • Documentation license
  • Reference Manual and User Guide
  • Acknowledgements

ALGLIB license

ALGLIB is a free software which is distributed under a GPL license - version 2 or (at your option) any later version. A copy of the GNU General Public License is available at http://www.fsf.org/licensing/licenses

Documentation license

This reference manual is licensed under BSD-like documentation license:
Copyright 1994-2009 Sergey Bochkanov, ALGLIB Project. All rights reserved.
Redistribution and use of this document (ALGLIB Reference Manual) with or without modification, are permitted provided that such redistributions will retain the above copyright notice, this condition and the following disclaimer as the first (or last) lines of this file.
THIS DOCUMENTATION IS PROVIDED BY THE ALGLIB PROJECT "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE ALGLIB PROJECT BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS DOCUMENTATION, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Reference Manual and User Guide

ALGLIB Project provides two sources of information: ALGLIB Reference Manual (this document) and ALGLIB User Guide.
ALGLIB Reference Manual contains full description of all publicly accessible ALGLIB units accompanied with examples. Reference Manual is focused on the source code: it documents units, functions, structures and so on. If you want to know what unit YYY can do or what subroutines unit ZZZ contains Reference Manual is a place to go. Free software needs free documentation - that's why ALGLIB Reference Manual is licensed under BSD-like documentation license.
Additionally to the Reference Manual we provide you User Guide. User Guide is focused on more general questions: how fast ALGLIB is? how reliable it is? what are the strong and weak sides of the algorithms used? We aim to make ALGLIB User Guide an important source of information both about ALGLIB and numerical analysis algorithms in general. We want it to be a book about algorithms, not just software documentation. And we want it to be unique - that's why ALGLIB User Guide is distributed under less-permissive personal-use-only license.

Acknowledgements

ALGLIB was not possible without the contribution of next open source projects:
  • LAPACK
  • Cephes
  • GNU MP
  • MPFR

Getting started with ALGLIB

Sections

    FAQ

    Sections

    • What version of Visual Basic are the algorithms translated into?
    • Why is the goto operator used in some programs?
    • What is the AP library?
    • Why do some algorithms (for instance, optimization methods) use reverse communication instead of function pointers, delegates and other means of my programming language?
    • What is ALGLIB aimed at?
    • What is the difference between ALGLIB and other similar projects?
    • What is AlgoPascal?

    What version of Visual Basic are the algorithms translated into?

    The algorithms are translated into VBA, but in general are compatible with VB6.

    Why is the goto operator used in some programs?

    In many programming languages there is control operator continue, but it is absent in VB. In AlgoPascal, this operator appears from time to time. The goto operator is used to replace it and go to the next iteration of the cycle.

    What is the AP library?

    AP library is a generic name for a set of libraries in several programming languages performing low-level tasks depending on specific programming languages. The AP library carries out tasks such as working with dynamic one- and multidimensional arrays in languages which do not support this data type, contains implementation of basic linear algebra algorithms, etc. The library is distributed as source codes under GPL 2+ license (GPL 2 or later). The library is attached to the ALGLIB package.

    Why do some algorithms (for instance, optimization methods) use reverse communication instead of function pointers, delegates and other means of my programming language?

    Optimization, integration and other similar methods are united by one common trait. They need to have a way of calculating the meaning of a function defined by the user at a point defined by the method.
    The most convenient way of solving this problem is transferring a function pointer into the module. However bear in mind that ALGLIB package is written using pseudocode that is automatically translated into different programming languages. While each language has its own function pointer analog that is often different from other languages. When the ALGLIB pseudocode was developed, at some point is became clear that adding function pointers in it will be very complex as this feature is implemented differently in every language. This is why reverse communication was chosen as a different kind of solution.

    What is ALGLIB aimed at?

    It is aimed at creating a convenient and efficient multilingual scientific software library.

    What is the difference between ALGLIB and other similar projects?

    The ALGLIB package:
    • is a multilingual project. The main feature of the project is that each algorithm is represented by programs in several languages and the language list is the same for every algorithm. This is the main advantage of the site before other similar collections - one algorithm, several languages, identical functionality in each language.
    • is focused on numerical analysis. There are some other directions in the project but numerical analysis is a priority.
    • is easy to use. To use the ALGLIB package you don't need to learn an unknown programming language, attach additional external libraries or work with an inconvenient interface to a code written in another programming language.

    What is AlgoPascal?

    AlgoPascal is a programming language, designed particularly for this project. The programs, written in this language, are processed by an automatic translator and translated into other programming languages. Almost all ALGLIB source is produced by the AlgoPascal translator.

    AP library description

    Sections

    • Introduction
    • Compatibility
    • Constants
    • Functions
    • Complex numbers operations

    Introduction

    The document describes a VBA version of the AP library. The AP library for VBA contains a basic set of mathematical functions needed to compile ALGLIB package. The library includes the only module ap.bas.

    Compatibility

    This library is developed for VBA only.

    Constants

    MachineEpsilon
    The constant represents the accuracy of machine operations times some small number r>1.
    MaxRealNumberThe constant represents the highest value of the positive real number, which could be represented on this machine. The constant may be taken "oversized", that is real boundary can be even higher.
    MinRealNumber
    The constant represents the lowest value of positive real number, which could be represented on this machine. The constant may be taken "oversized", that is real boundary can be even lower.

    Functions

    Public Function MaxReal(ByVal M1 As Double, ByVal M2 As Double) As Double
    Returns the maximum of two real numbers.
    Public Function MinReal(ByVal M1 As Double, ByVal M2 As Double) As Double
    Returns the minimum of two real numbers.
    Public Function MaxInt(ByVal M1 As Long, ByVal M2 As Long) As Long
    Returns the maximum of two integers.
    Public Function MinInt(ByVal M1 As Long, ByVal M2 As Long) As Long
    Returns the minimum of two integers.
    Public Function ArcSin(ByVal X As Double) As Double
    Returns arcsine (in radians).
    Public Function ArcCos(ByVal X As Double) As Double
    Returns arccosine (in radians).
    Public Function SinH(ByVal X As Double) As Double
    Returns hyperbolic sine.
    Public Function CosH(ByVal X As Double) As Double
    Returns hyperbolic cosine.
    Public Function TanH(ByVal X As Double) As Double
    Returns hyperbolic tangent.
    Public Function Pi() As Double
    Returns the value of π.
    Public Function Power(ByVal Base As Double, ByVal Exponent As Double) As Double
    Returns Base raised to a power of Exponent (introduced for compatibility).
    Public Function Square(ByVal X As Double) As Double
    Returns x2.
    Public Function Log10(ByVal X As Double) As Double
    Returns common logarithm from X.
    Public Function Ceil(ByVal X As Double) As Double
    Returns the smallest integer bigger or equal to X.
    Public Function RandomInteger(ByVal X As Long) As Long
    Returns a random integer between 0 and I-1.
    Public Function Atn2(ByVal Y As Double, ByVal X As Double) As Double
    Returns an argument of complex number X + iY. From interval from -π to π.

    Complex numbers operations

    As there is no operator overloading in Visual Basic 6.0, operations with complex numbers could not be implemented as easy as with built-in data type. Therefore Complex data type is defined in a library. It is a record with two real number fields x and y, and all the operations are performed with the use of special functions implementing addition, multiplication, subtraction and division. An input can be complex or real, and output is complex. These functions are listed below.
    Public Function C_Add(Z1 As Complex Z2 As Complex):Complex
    Public Function C_AddR(Z1 As Complex R As Double):Complex

    Calculate Z1+Z2 or Z1+R.
    Public Function C_Sub(Z1 As Complex Z2 As Complex):Complex
    Public Function C_SubR(Z1 As Complex R As Double):Complex
    Public Function C_RSub(R As Double, Z1 As Complex):Complex

    Calculate Z1-Z2Z1-R or R-Z1.
    Public Function C_Mul(Z1 As Complex Z2 As Complex):Complex
    Public Function C_MulR(Z1 As Complex R As Double):Complex

    Calculate Z1*Z2 or Z1*R.
    Public Function C_Div(Z1 As Complex Z2 As Complex):Complex
    Public Function C_DivR(Z1 As Complex R As Double):Complex
    Public Function C_RDiv(R As Double, Z2 As Complex):Complex

    Calculate Z1/Z2Z1/R or R/Z2. Modulus calculation is performed using so called "safe" algorithm, that could never cause overflow when calculating intermediate results.
    Public Function C_Equal(Z1 As Complex Z2 As Complex):Boolean
    Public Function C_EqualR(Z1 As Complex R As Double):Boolean
    Public Function C_NotEqual(Z1 As Complex Z2 As Complex):Boolean
    Public Function C_NotEqualR(Z1 As Complex R As Double):Boolean

    Compare Z1 and Z2 or Z1 and R.
    Public Function C_Complex(X As Double):Complex
    Converts a real number into equal complex number.
    Public Function C_Opposite(Z As Complex):Complex
    Returns -Z.
    Public Function AbsComplex(Z As Complex):Double
    Returns the modulus of complex number z. Modulus calculation is performed using so called "safe" algorithm, that could never cause overflow when calculating intermediate results.
    Public Function Conj(Z As Complex):Complex
    Returns complex conjugate to z.
    Public Function CSqr(Z As Complex):Complex
    Returns the square of z.

    ALGLIB reference manual

    Packages and units


    DataAnalysis package
    dforest Decision forest classifier (regression model)
    kmeans K-means++ clustering
    lda Linear discriminant analysis
    linreg Linear models
    logit Logit models
    mlpbase Basic neural network operations
    mlpe Neural network ensemble models
    mlptrain Neural network training
    pca Principal component analysis
     
    DiffEquations package
    odesolver Ordinary differential equation solver
     
    FastTransforms package
    conv Fast real/complex convolution
    corr Fast real/complex cross-correlation
    fft Real/complex FFT
    fht Real Fast Hartley Transform
     
    Integration package
    autogk Adaptive 1-dimensional integration
    gkq Gauss-Kronrod quadrature generator
    gq Gaussian quadrature generator
     
    Interpolation package
    idwint Inverse distance weighting: interpolation/fitting
    lsfit Linear and nonlinear least-squares solvers
    polint Polynomial interpolation/fitting
    pspline Parametric spline interpolation
    ratint Rational interpolation/fitting
    spline1d 1D spline interpolation/fitting
    spline2d 2D spline interpolation
     
    LinAlg package
    ablas Level 2 and Level 3 BLAS operations
    bdsvd Bidiagonal SVD
    evd Eigensolvers
    inverseupdate Sherman-Morrison update of the inverse matrix
    ldlt LDLT decomposition
    matdet Determinant calculation
    matgen Random matrix generation
    matinv Matrix inverse
    ortfac Real/complex QR, LQ, bi(tri)diagonal, Hessenberg decompositions
    rcond Condition number estimate
    schur Schur decomposition
    sdet Determinant of a symmetric matrix
    sinverse Symmetric inversion
    spdgevd Generalized symmetric eigensolver
    srcond Condition number estimate for symmetric matrices
    svd Singular value decomposition
    trfac LU and Cholesky decompositions
     
    Optimization package
    minasa ASA bound constrained optimizer
    mincg Conjugate gradient optimizer
    minlbfgs Limited memory BFGS optimizer
    minlm Improved Levenberg-Marquardt optimizer
     
    Other package
    nearestneighbor Nearest neighbor search: approximate and exact
     
    Solvers package
    densesolver Dense linear system solver
    ssolve Symmetric dense linear system solver
     
    SpecialFunctions package
    airyf Airy functions
    bessel Bessel functions
    betaf Beta function
    chebyshev Chebyshev polynomials
    dawson Dawson integral
    elliptic Elliptic integrals
    expintegrals Exponential integrals
    fresnel Fresnel integrals
    gammafunc Gamma function
    hermite Hermite polynomials
    ibetaf Incomplete beta function
    igammaf Incomplete gamma function
    jacobianelliptic Jacobian elliptic functions
    laguerre Laguerre polynomials
    legendre Legendre polynomials
    psif Psi function
    trigintegrals Trigonometric integrals
     
    Statistics package
    binomialdistr Binomial distribution
    chisquaredistr Chi-Square distribution
    correlation Pearson/Spearman correlation coefficients
    correlationtests Hypothesis testing: correlation tests
    descriptivestatistics Descriptive statistics: mean, variance, etc.
    fdistr F-distribution
    hqrnd High quality random numbers generator
    jarquebera Hypothesis testing: Jarque-Bera test
    mannwhitneyu Hypothesis testing: Mann-Whitney-U test
    normaldistr Normal distribution
    poissondistr Poisson distribution
    stest Hypothesis testing: sign test
    studenttdistr Student's t-distribution
    studentttests Hypothesis testing: Student's t-test
    variancetests Hypothesis testing: F-test and one-sample variance test
    wsr Hypothesis testing: Wilcoxon signed rank test



    Sources:
    1. https://www.alglib.net/
    2. https://sites.google.com/site/chandanprogrammingdocs/platforms-frameworks/alglib
    3. https://newtonexcelbach.com/2010/05/20/installing-alglib-with-excel-vba/


    Here you can download GPL-licensed version of ALGLIB. Commercial users may use GPL-licensed code as unlimited trial version. But if you want to distribute something that includes GPL-ed code, you have to either distribute it under GPL too or buy commercial license.
    3.x branch
    Change Log
    alglib-3.1.0.cpp        zip tgz        C++ version
    alglib-3.1.0.csharp        zip tgz        C# version (100% managed code)
     
    pre-3.x releases
    Pre-3.x releases are not compatible with 3.x branch;
    however, they will be there for languages which were not ported to 3.x yet
    alglib-2.6.0.mpfr.zip Multiple precision version (MPFR)
    alglib-2.6.0.freepascal.zip FreePascal version
    alglib-2.6.0.delphi.zip Delphi version
    alglib-2.6.0.vb6.zip VBA version

    Tuesday, January 12, 2021

    Numerical or financial Math: VBA / EXCEL / VB6

    Download from ME
    Download from SOURCE

    Classical binomial model

     CRR_optimized.zip
        Simplifying algebraic terms in the binomial model of Cox-Ross-Rubinstein for american options the
        speed against the usual solution (cf Haug's book for a code) is improved by a factor of 40 - 50.
        That file contains the Excel code (with inline comments as user docu) and examples for testing.
        But it does not heal the combinatorical curse of double looping for American options ...

     LeisenReimer_NP.zip
        A binomial Leisen-Reimer tree avoids the oscillations of the usual binomial trees through a proper
        choice of the tree parameters. Besides that the geometry is the same as for CRR the speed
        is improved by a factor better than 40. The Excel file contains code and examples for testing.

     LeisenReimer_properties.zip
        The code for the above has been translated into a DLL (C source code included) for better speed to
        play with the properties of the model within Excel (like extrapolation or what may happen through
        numerical differentiation). I am somewhat too lazy to comment the various results.
        Speed is about 880 prices per second for a 257 step tree and a 4-point Richardson extrapolation
        gives an exactness of 6 - 7 digits for the european case (starting with 65 steps), while extrapolation
        is not really helpfull in the case of early exercise.

    Pricing

     BS&Vol_CodyMiller.xls.zip 
        This uses an improved version of the former through a better cumulative normal distribution (due to Miller and Cody) and gives almost IEEE correctness in Excel (relativ error ~ 2 DBL_EPSILON). On my Office PC it needs ~ 1 sec for 100000 prices, computing volatility needs 10 times more. It also works for non-practical situations of data (strike=1.5*spot, time=some days, vol ~ 0.25%).

     BS&Vol.xls.zip, BS&Vol_increased.xls.zip
        Excel sheets to compute Black-Scholes prices and retrieve volatility. Yes, there are many such files. The point is: these here are robust (working with the option premium and switch to 'normed' situations) and the volatility is computed in the spirit of a fairly good initial guess (similar to Jaeckel). The 2nd Excel file shows how one can increase the usual solution for vol and still gets given prices. This (partially) solves the problem, that vol numerical is not well-defined as the inverse of a price. It even works in extreme situations (like vol ~ 10% and small time or very far off the money). A more sound solution has to use C code (or similar), but it is just a stripped down version of that. Of course that depends on the quality of the pricing function and to judge it one can not use Excel.

      perf_tst.zip
        Performance test for Black-Scholes prices for pure VBA vs a C DLL (docu)

     pdf_pricing.zip
        BS pricing through integrating the pay off against the risk neutral density, both over spots and
        logarithmic moneyness (i.e. Breeden-Litzenberger). That Excel sheet uses the DLL of the
        integrator in integratorXL. A Maple sheet (as pdf) is included explaining the manipulations and
        gives estimations, where to cut off to restrict to integration to finite intervalls

     CarrMadan_Fourier.pdf
        Simple example for Madan & Carr's Fourier method on option pricing: the case of constant volatility (which means: Fourier method for Black-Scholes) using integration (instead of FFT).

    Stochastic volatility / Heston

     Heston93-Check.pdf
        Heston's model using characteristic functions (Maple)

     Heston93_opt.zip
        Optimized Excel solution for Heston's model using Gauss integration (undiscounted option values), including reference values (from Maple) and graphics for the integrands (VolVol = 0 is missing), short documentation.

     Heston93_pdf.zip
        Smiles and probability function (RND) for Heston's model with Excel; if strikes are extreme that may fail for the smiles

     Heston93_withDLL.zip
        DLL version for Heston in Excel: one fast and one exact solution, gives back prices and/or volatility, VolVol=0 is still missing (as I found to allow reaching it my solution become instable due to oscillation)

     Heston-MC.pdf
        A Monte-Carlo simulation for the Heston market model in Maple, somewhat slow ...
        and I should have added 'reflecting/absorbing barrier' to be chosen ...

     Heston_MC_hf_10.pdf
        A speed-improved version of the above in Maple 10, almost 100 times faster


    Stochastic volatility / other models

     NIG_tiny_withDLL.zip
        Normal Inverse Gauss option pricer (with Esscher transform correction), Excel + DLL, and
        a Maple worksheet with short explanations, cf Schoutens book "Levy Proccess in Finance"

     VG_Pricer_short(Maple).pdf
        A 'brute' option pricer for the Variance Gamma model (Madan, Carr, Chang 1998) in Maple

     VG_small.zip
        Variance Gamma model in Excel + DLL; it uses a gamma distribution pdfGamma(a,x)
        which accepts large numerical arguments, short docu

    Correction for VG (both Maple and Excel+DLL, 02. Jan 2014)
        The paper has a typo. To get the correct values one has to use  - theta instead of + theta.
        With that change of sign on input level the values are correct (without modifying the code),
        see the discussion here.


    Volatility smile

     GatheralSmileExample.zip
        Example for Gatheral's parsimonious arbitrage-free implied volatility parametrization, in Maple as pdf (24 Oct 2004: corrected some errors in that sheet)

     GatheralSmile_Vola.zip
        Fitting Gatheral's model to a given, empirical volatility smile. The estimates for initial parameters are computed from data only. This is a pure Excel solution with least square fitting likewise either through Excel's solver or a Levenberg-Marquardt method included as VBA project, short docu

     GatheralSmile_Vola_DLL.zip
        This is the same as above, but uses a DLL for fitting to speed things up.

     PatSmile.zip
        Continous family of smiles produced by the SABR model of Pat Hagan et al

     SABR.pdf
        Code in C and Visual Basic SABR_Code_VB_and_C.txt  and some graphs for the SABR model various

     European_Dividend_Alan_Example.pdf
        Numerical example for european options and discontinous dividends, valuation method due to Alan Lewis

     ExtremeSmiles.htm
        Example using actual historical data for 'extreme' smiles and vol term structures after crashes

     VolaTermDAX.zip
        Example using actual historical data for 'visible volatility ATM term structure', if front month expires

     Div1Year.gif
        Dividend strip for the Swiss market regarding tax (both tax variants) in money and SMI points

     Exane.gif
        Example for sticky strike vs sticky delta from exane.com

    Risk neutral density

     a_brute_way_to_get_a_RND_from_option_prices.pdf
        Using polynomial approximations and normal distribution for tails one can find a RND (over log
        space), which is good enough to recover option prices and to get reasonable statistical results.

     From_a_brute_RND_to_a_NIG_estimation.pdf
        Having descriptive statistics for a RND one can fit a normal inverse Gauss model against option prices

     RND_statistics_example.zip
        This Excel sheet (with pure VBA code) shows, how one can estimate the descriptive statistics for
        a RND directly from option prices using an approach similar to the VIX construction (where I use
        a somewhat different discretization), no interpolation of volatility or prices is needed.

    Here is a sketchy explanation for the method: Explaining the method in RND_statistics_example.pdf.


    Numerics / Excel

     Testing Excel 2010.pdf
        This is a test report about Excel 2010 (beta), which seems to be a good improvemet over older versions. For testing essentially taking an input in decimal number it is converted to the nearest IEEE 754 double, then it is feed to Maple to be evaluated with higher precision, which then is rounded to the nearest IEEE again to have a correct result (as far as it can be correct).  Only then it makes sense to compare against some floating point result given by Excel. For that a work around for the limitation of 15 decimal places in Excel is needed and provided as well.


    Numerics / Excel / various financial stuff

     simpleGarch11.zip
        A simple GARCH(1,1) in Excel (using optimizer for the maximum likelihood and the statistics
        for the time series) to estimate DAX spot volatility

     Hist_Vol.zip
        How to compute historical volatility in Excel with a variable time frame


    Numerics / Excel / fitting

     LMfit_logistic.zip
        Example for Levenberg-Marquardt in Excel (pure VBA), which shows the essential algorithm
        (ie: the linear algebra and the numerics), short documentation

     LMfit3_with_weights.zip
        It contains the complete usual Levenberg-Marquardt in Excel (pure VBA, dim = 1) and a version,
        which allows weightings of data points

     LeastSquareFitting.zip
        An Excel interface to a DLL (containing a Levenberg-Marquardt method) for fitting curves against
        data and estimating the parameters of the curve. The objective function is given within VBA and
        can be chosen freely, short docu. As example Gatheral's SVI volatility smile is treated.

     LMfit_logistic.zip
        Example for Levenberg-Marquardt in Excel (pure VBA), which shows the essential algorithm
        (ie: the linear algebra and the numerics), short documentation

     LMfit3_with_weights.zip
        It contains the complete usual Levenberg-Marquardt in Excel (pure VBA, dim = 1) and a version,
        which allows weightings of data points

     LeastSquareFitting.zip
        An Excel interface to a DLL (containing a Levenberg-Marquardt method) for fitting curves against
        data and estimating the parameters of the curve. The objective function is given within VBA and
        can be chosen freely, short docu. As example Gatheral's SVI volatility smile is treated.


    Numerics / Excel / cumulative normal distribution

     cdfN2010_June.zip
        This is my best cdf Normal in pure VBA. Absolute errors are fine, of course. The relative errors
        are below 2 DBL_EPSILON or 3 ULPs over the full range (to be seen for negative inputs only)
        as far as I am aware of it (i.e. I have no 'proof' for that, just tests, see the graphical test results).
        Testing was done as sketched in the report "Testing Excel 2010.pdf" (so: precisely at IEEE level).
        This is even much better than Excel 2010 (as of today), though I used my good old Excel 2000,
        which I prefer (and yes, tiny relative errors at the left tail may be a little bit a matter of taste ...).
        A short test docu sketches, how explicit test values and results can be achieved using Maple.
     xmasNormDist.zip cdf Normal (following George Marsaglia) for Excel, pure VBA with 19 digits (using data type CDec),short documentation

     cdfN_GMsimple_Test.zip
        cdf Normal (following George Marsaglia) for Excel (pure VBA), simplified version, precise test
        data calculated using Maple

     bivariateNormal_Series.zip
        This Excel sheet contains fast codes for the cumulative normal distribution in dimensions
        1 and 2 through series developments up to machine precision in Excel, short documentation

     cdfN_trivariate_mini.zip
        This Excel sheet (with DLL for integration) compares implementations for the cumulative normal
        distribution up to dimension 3 (references are given in the code and the short documentation), so
        it is a kind of study (but not meant to be a complete overview). For high precision one would have
        to switch to other environments of course, for example one can use LCC.

    Numerics / Excel / random number generation

     RNG_normal.xls.zip
        This is an Excel solution with DLL for 3 very fast and good pseudo-random generators for normal
        distributed numbers (Ziggurat [Marsaglia], ZIGNOR [Doornik], FastNorm3 [Wallace]). Speed is
        about 1 sec for 10 Mio numbers.


    Numerics / Excel / more ...

     Brent_netlib.zip
        Excel / VBA code for Brent's method to find Zeros or Minima in dimension 1. That are ports from
        the Netlib C library. The original C sources have reasonable inline comments and serve as docu,
        they are included.

     integratorXL.zip
        Numerical quadrature for Excel using a DLL which takes function names as arguments, short docu
        applications: pricing by the risk neutral density (see above) and bi- and tri-variate normal densities
        (to be done).

     integratorXL_doubleIntegral.zip
        The above integrator can be used to compute double integrals in Excel and as an example this is
       shown for the cumulative bivariate normal distribution starting from a Gauss kernel only, short docu.

     integrator_GaussKronrod.zip
        An adaptive Gauss-Kronrod integrator, purely in VBA.

     FFT_xl.zip
        Fast Fourier Transform in Excel with VBA, that does not use Excel's slow and ugly built-in solution.The docu explains conventions used, handling is shown by examples through a workbook.


    Numerics / Excel / functional

     wrapGSL.zip
        Wrapper to use GSL from Excel: files
        There is an Excel sheet enclosed how to work with function names as arguments (as Excel/VBA
        does not have function pointers) for special functions and complex functions. One needs the free
        GNU GSL lib to be installed and for a reasonable handling one should consult the documentation
        for namings, arguments etc. That are the binary GSL files (DLLs) needed:  gslWIN32_1.3.zip
        Documentation has to be done ...

     Function_as_Arguments_in_Excel.zip
        Several ways how to live with functions as arguments in Excel, VBA does not have this. Usually I do not work with classes, but here it is seems to be one way out. That sheet grew from a discussion on a forum, the main example is integration by Gauss-Legendre.

     Working_with_Array_Functions_and_DLLs_in_Excel_VBA.pdf
        This is a tutorial how to work with numerical arrays using Excel and DLLs: reading and writing from VBA to DLL and vice versa (so it covers the old question "how to pass array data?"), using functions having array arguments or array outputs. It does not use SDK and all the overhead. And it is only through commented examples in C and VBA, so it is a bit technical, but practical (and not thought as an introduction to DLL & VBA). Here are the sources (Excel sheet, C code and DLL).

      Reading_and_Writing_Arrays_across_Excel_and_DLLs.pdf
        That tutorial is a short variant of "Working with Array Functions" having just implementation in mind. Here are the sources (Excel sheet, C code and DLL).

      Reading_and_writing_strings_between_VBA_and_DLLs.txt
        This is for working with C-strings between VBA and a DLL, quite similar to the numerical case above. Here are the sources (Excel sheet, C code and DLL).

    All the VBA projects are unprotected while the source code for DLLs usually is not provided.
    Many are just pure VBA code, but if a sheet uses a DLL then set the correct pathes within the
    project. Open Excel's debug window to watch results being printed out.

    22 Feb 2015:  uploaded BS&Vol_CodyMiller.zip

    23 Dec 2010:  uploaded Reading_and_writing_strings_between_VBA_and_DLLs.zip

    24 Jun 2010: uploaded cdfN2010_June

    31 Mar 2010: uploaded  Testing Excel 2010.pdf

    31 Oct 2009: uploaded BS&Vol

    17 Aug 2006: uploaded Integrator_GaussKronrod

    11 May 2006: uploaded some files around the risk neutral density from option prices

    18 Jan 2006: uploaded high precision for cumulative normal in dim <= 3 to the LCC subdirectory

    18 Dec 2005: uploaded cdfN_trivariate_mini.zip

    17 Dec 2005: reorganized to give a better overview

    12 Dec 2005: linked to my LCC files - giving 100 digits precision

    12 Nov 2005: uploaded Reading_and_Writing_Arrays_across_Excel_and_DLLs.pdf

    01 Nov 2005: uploaded Working_with_Array_Functions_and_DLLs_in_Excel_VBA.pdf

    31 Oct 2005: uploaded LeastSquareFitting

    30 Oct 2005: uploaded Function_as_Arguments_in_Excel

    23 Oct 2005: uploaded gslWIN32_1.3.zip (the DLLs for GSL)

    06 Oct 2005: uploaded GatheralSmile_Vola_DLL, a DLL version

    06 Oct 2005: uploaded GatheralSmile_Vola

    06 Aug 2005: uploaded LeisenReimer_properties

    09 Jul 2005: uploaded LeisenReimer_NP

    08 Jul 2005: uploaded CRR_optimized

    07 Jul 2005: uploaded Heston_MC_hf_10.pdf (speed in Maple 10 improved)

    28 May 2005: uploaded Levenberg-Marquardt with weights

    27 May 2005: uploaded RNG_normal

    02 Apr 2005: uploaded Brent_netlib

    24 Mar 2005: uploaded IntegratorXL_doubleIntegral

    19 Feb 2005: uploaded FFT_xl

    30 Jan 2005: uploaded bivariateNormal_Series (contains the deleted cdfN_Marsaglia_Taylor.txt)

    28 Dec 2004: uploaded pdf_pricing.zip

    29 Nov 2004: uploaded cdfN_Marsaglia_Taylor.txt

    31 Oct 2004: uploaded integratorXL and ExtremeSmiles.htm

    24 Oct 2004: corrected some errors in GatheralSmileExample.zip

    22 Oct 2004: uploaded simpleGarch11.zip

    June 2004: Yahoo killed my web space, so I use this one from now on ...


    This software is provided "as is" and any express or implied warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose are disclaimed. In no event shall the author be liable for any direct, indirect, incidental, special, exemplary, or consequential damages (including, but not limited to, procurement of substitute goods or services; loss of use, data, or profits; or business interruption) however caused and on any theory of liability, whether in contract, strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this software, even if advised of the possibility of such damage.

    Source: