Several
files for numerical or financial Math, free
for download and with no warranty.
|
Contents:
|
More stuff:
|
|
|
feedback is welcome
& if you
want to drop me any
comment
...  |
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.
