Using Excel-DNA with Math.NET Numerics library - Example from Wilmott magazine July 2018

[spursfan]

I write a regular column for Wilmott quant finance magazine and for issue no 96 (July 2018) thought I would illustrate how easy it is to combine Excel-DNA with a math library

My code needs the .NET framework (comes with a typical Windows install) plus the two attached files plus a single file from the Excel-DNA download (renaming either ExcelDna.xll for the 32-bit version or ExcelDna64.xll for the 64-bit version to Wilmott96.xll)

The MathNet.Numerics.dll file comes from MathNet.Numerics version 4.4.0 (https://numerics.mathdotnet.com/Packages.html) 

From the web page reference in the previous line, click on the text near the beginning that says release archive highlighted in blue, choose Zip format and then download the latest version – finally extract the MathNet.Numerics.dll file

You will also have to create your own Wilmott96.vb  text file with the following text

Imports System

Imports System.Math

Imports MathNet.Numerics.Distributions

Imports MathNet.Numerics.Random

Public Module Wilmott96

<ExcelFunction(Description:="Heston approximation by Leif Andersen")>

  Function vbEuroCallSVMC(

  <ExcelArgument(Description:="share price")> S As Double,

  <ExcelArgument(Description:="strike")> K As Double,

  <ExcelArgument(Description:="rf")> r As Double,

  <ExcelArgument(Description:="div yield")> q As Double,  

  <ExcelArgument(Description:="maturity")> Tyr As Double,

  <ExcelArgument(Description:="volofvol")> volofvol As Double,

  <ExcelArgument(Description:="kappa")> kappa As Double,

  <ExcelArgument(Description:="rho")> rho As Double,  

  <ExcelArgument(Description:="div yield")> theta0 As Double,  

  <ExcelArgument(Description:="maturity")> theta As Double,

  <ExcelArgument(Description:="volofvol")> dTyr As Double,

  <ExcelArgument(Description:="kappa")> nSim As Int32) As Double

  

  Dim psiC As Double, gamma1 As Double, gamma2 As Double, ekd As Double

  Dim v1 As Double, v2 As Double, K1 As Double, K2 As Double

  Dim K3 As Double, K4 As Double, capA As Double, ve As Double

  Dim Vt As Double, lnXt As Double, M As Double, V As Double

  Dim psi As Double, uV As Double, b2 As Double, a As Double

  Dim zV As Double, Vtnext As Double, p As Double, beta As Double

  Dim zX As Double, K0star As Double, lnXtnext As Double, veadd As Double

  Dim i As Int32, n As Int32, ndT As Int32

  

  Dim uRnd As New MersenneTwister()

     

  psiC = 1.5

  gamma1 = 0.5

  gamma2 = 0.5

  

  ndT = CInt(Tyr / dTyr)

  ekd = Exp(-kappa * dTyr)

  v1 = volofvol * volofvol * ekd * (1 - ekd) / kappa

  v2 = 0.5 * theta * volofvol * volofvol * (1 - ekd) * (1 - ekd) / kappa

  

  K1 = gamma1 * dTyr * (kappa * rho / volofvol - 0.5) - rho / volofvol

  K2 = gamma2 * dTyr * (kappa * rho / volofvol - 0.5) + rho / volofvol

  K3 = gamma1 * dTyr * (1 - rho * rho)

  K4 = gamma2 * dTyr * (1 - rho * rho)

  capA = K2 + 0.5 * K4

  

  ve = 0

  

  For n = 1 To nSim

    Vt = theta0

    lnXt = Log(S)

    

    For i = 1 To ndT

      M = theta + (Vt - theta) * ekd

      V = Vt * v1 + v2

      psi = V / (M * M)

      uV = uRnd.NextDouble()

      If psi <= psiC Then

        b2 = 2 / psi - 1 + Sqrt(2 / psi) * Sqrt(2 / psi - 1)

        If b2 < 0 Then b2 = 0

        a = M / (1 + b2)

        zV = Normal.InvCDF(0, 1, uV)

        Vtnext = a * (Sqrt(b2) + zV) * (Sqrt(b2) + zV)

      Else

        p = (psi - 1) / (psi + 1)

        beta = (1 - p) / M

        If uV <= p Then

          Vtnext = 0

        Else

           Vtnext = Log((1 - p) / (1 - uV)) / beta

        End If

      End If

      

      zX = Normal.InvCDF(0, 1, uRnd.NextDouble())

      If psi <= psiC Then

        K0star = -capA * b2 * a / (1 - 2 * capA * a) + 0.5 * Log(1 - 2 * capA * a)

      Else

        K0star = -Log(p + beta * (1 - p) / (beta - capA))

      End If

      K0star = K0star - (K1 + 0.5 * K3) * Vt

      lnXtnext = lnXt + K0star + K1 * Vt + K2 * Vtnext + Sqrt(K3 * Vt + K4 * Vtnext) * zX

      

      Vt = Vtnext

      lnXt = lnXtnext

    Next i

    

    veadd = Exp(lnXtnext) - K

    If veadd < 0 Then veadd = 0

    ve = ve + veadd

  Next n

  

  vbEuroCallSVMC = ve / nSim

End Function

End Module

So you then need to put the five files in the same directory: Wilmott96.xls, Wilmott96.dna, Wilmott96.vb, Wilmott96.xll and MathNet.Numerics.dll. 

From Excel, open Wilmott96.xls, then open the Wilmott96.xll add-in and you should then see my wonderful vbEuroCallSVMC in the function wizard under the Wilmott96 category, with the even-more-wonderful IntelliSense labels that Excel-DNA can now handle.

 

I use the following parameter values: S=100, K=60, r=0.0; q=0.0 and Tyr=1.0; for Heston, volofvol=0.39; kappa=1.15; rho=-0.64; theta0=0.04 and theta=0.04. For the simulation, I use dTyr=1/32 with nsim at least 40,000 – though I have run it up to 960,000.

Since the example uses Monte Carlo, the option value that you get will be different on each run - though it will typically be around 40

Any problems, please let me know at msta...@london.edu

[Markus Kantor]

Greetings!

I have had problems packaging MathNet.Numerics.dll within the xll file so that the xll can be redistributed and used by users of the application. Did you have any problems with this perspective using MathNet.Numerics.dll with Excel-DNA.

Thanks in advance of your experise,

Markus Kantor

[Caio Proiete]

Hi Markus,

Could you create a reproducible example and put on GitHub or somewhere else we can download for testing this behavior?

Thanks,

Caio Proiete

https://github.com/caioproiete

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.