4
$\begingroup$

Hey I try to price options in VBA. To do this I need to define characteristic function and do some operations on complex numbers. For example I have this code:

Function cf(u, T, r, q, sigma)
i = WorksheetFunction.Complex(0, 1)
b = r - q - 0.5 * sigma ^ 2
cf = WorksheetFunction.ImExp(T * (i * u * b - 0.5 * u ^ 2 * sigma ^ 2)
End Function

But it doesnt work. I know that WorksheetFunction.Complex(0, 1) returns string and I wonder how to do this to make this code work. Maybe there are some better libraries?

Improve this question
$\endgroup$

1 Answer 1

Reset to default
8
$\begingroup$

Ahhh complex numbers in Excel VBA, the pinnacle of cubicle programming. Been there, done that!

The functions that you quote are indeed returning string types. But you should be able to compose those functions, e.g.

Option Explicit
Public Function cf(u As Double, ttm As Double, rf As Double, q As Double, s As Double) As String
    Dim b As Double
    b = rf - q - 0.5 * s ^ 2
    If u = 0 Then
        cf = "1.0"
    Else
        With Application.WorksheetFunction
            cf = .ImExp(.Complex(-0.5 * u ^ 2 * s ^ 2 * ttm, ttm * u * b))
        End With
    End If
End Function

This function will now return a string type, but Excel can nevertheless consume it:

Sub ATest()
    Dim z1 As String, z2 As String, z3 As String
    Dim x1 As Double, x2 As Double
   ' some parameters
    Const ttm As Double = 1#
    Const rf As Double = 0.05
    Const q As Double = 0#
    Const sigma As Double = 0.2
    Const eps As Double = 0.0001
    
    z1 = cf(-eps, ttm, rf, q, sigma)
    z2 = cf(0, ttm, rf, q, sigma)
    z3 = cf(eps, ttm, rf, q, sigma)
    With Application.WorksheetFunction
        x1 = .ImReal(.ImDiv( _
                    .ImSub(z3, z1), _
                    .Complex(0, 2 * eps))) ' first moment estimator
        x2 = .ImReal(.ImDiv( _
                        .ImSum(.ImSub(z1, z2), .ImSub(z3, z2)), _
                        .Complex(-eps * eps, 0))) ' second moment estimator
    End With
    Debug.Print "drift:", Round(CDbl(x1), 6), (rf - 0.5 * sigma ^ 2) * ttm
    Debug.Print "variance:", Round(CDbl(x2) - CDbl(x1) ^ 2, 6), sigma ^ 2 * ttm
End Sub

will result in

drift:         0,03          0,03 
variance:      0,04          0,04

i.e. the return expectation.

If you want another implementation, there is one available at pfadintegral (I have never checked it). Another path, of course, would be to compose your own library in VBA (as I said, been there, done that!), but I think it may not be worthwile as there are many cornercases to think about, e.g. branch cutting, numerical underflow etc. ...

HTH?

Improve this answer
$\endgroup$
5
  • $\begingroup$ I remember implementing a code that prints out a mandelbrot set on the screen: the way I did that was to simply define a complex number as $c=a+b$, where both $a$ and $b$ are regular floats and $b$ represents the imaginary part. Then, one can work out operations such as $c^2$ using the logic: "$Real = a^2-b^2$", "$Complex = 2ab$". I think it's possible to work with complex numbers without having to call excel "complex" functions: instead, one can just code up logic that works out the real and imaginary parts of various operations.... $\endgroup$
    – Jan Stuller
    Dec 2, 2020 at 16:05
  • 1
    $\begingroup$ Hi @JanStuller yes that works to some extent. But at some point you want to introduce complex log, exp, etc... that’s where the fun begins. I ended up with a class for all these operations, and „my“ complex integral in VBA is still a bit off due to numerical underflows in some angle calculations :-D $\endgroup$
    – Kermittfrog
    Dec 2, 2020 at 16:25
  • $\begingroup$ Out of curiosity, do you / have you ever used Complex numbers for anything in finance? $\endgroup$
    – Jan Stuller
    Dec 2, 2020 at 16:31
  • 2
    $\begingroup$ Yes! The whole FFT Based option pricing stuff based on Duffie/Pan/Singleton 2000! $\endgroup$
    – Kermittfrog
    Dec 2, 2020 at 16:38
  • $\begingroup$ Oh wow! Ok, I gotta look into that! Haven't come across it so far, sounds interesting! $\endgroup$
    – Jan Stuller
    Dec 2, 2020 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.