Using Excel VBA Complex Strings for Option Characteristic Functions
Summary
The document addresses implementing a characteristic function for option pricing in Excel VBA, where Excel's complex-number worksheet functions represent complex values as strings. The answer shows that these functions can be composed by passing their string outputs between operations, and that a VBA function can return the resulting complex string for Excel to consume. It also handles the zero input separately, where the characteristic function equals one.
A demonstration estimates the first and second moments from characteristic-function values around zero, then compares those estimates with the model's known drift and variance expressions. The displayed output illustrates agreement for the chosen parameter setup. This is a narrow implementation example rather than a general options-pricing tutorial. The response cautions that a custom complex-number library must account for edge cases such as branch cuts and numerical underflow, and mentions an alternative implementation without evaluating it.
Key ideas
- Excel's complex worksheet functions return string representations that can be composed in VBA.
- A VBA characteristic-function routine can return a complex string for Excel to use.
- The example estimates moments using values of the characteristic function near zero.
- A custom complex arithmetic library must handle numerical and branch-cut edge cases.
Tags
Full text
# Complex numbers in VBA
# 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?
## Answer by Kermittfrog (score 8, accepted)
https://quant.stackexchange.com/a/59706
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?Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.