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:

No comments:

Post a Comment