# Complex numbers in VBA

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?

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?

• 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.... Dec 2, 2020 at 16:05
• 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 Dec 2, 2020 at 16:25
• Out of curiosity, do you / have you ever used Complex numbers for anything in finance? Dec 2, 2020 at 16:31
• Yes! The whole FFT Based option pricing stuff based on Duffie/Pan/Singleton 2000! Dec 2, 2020 at 16:38
• Oh wow! Ok, I gotta look into that! Haven't come across it so far, sounds interesting! Dec 2, 2020 at 17:55