Financial Modelling in Python

P1: JYS

c12 JWBK378-Fletcher May 28, 2009 10:59 Printer: Yet to come

168 Financial Modelling in Python

Figure 12.3 Invoking the PPF Black–Scholes function from Microsoft Excel.

Vol As Double,

T As Double,

CallPut As Double) As Variant

On Error Resume Next

Dim Pricer As Object

Set Pricer = CreateObject("ppf.black

scholes")

PPF

BlackScholes = Pricer.OptionPrice(

Spot, Strike, Rate, Vol, T, CallPut)

If Err.Number <> 0 Then

PPF

BlackScholes = "#err: " & Err.Description

Err.Clear

End If

Set Pricer = Nothing

End Function

12.2 NUMERICAL PRICING WITH PPF IN EXCEL

Armed with the understanding of the basic principles of publishing Python COM components

from the preceding section, we now consider how we can assemble such components into a

broader example to provide numerical pricing capabilities using PPF in Excel. Specifically,

our goal will be to enable Bermudan swaption pricing on a Hull–White lattice.

12.2.1 Common Utilities

Before going on, we will find, in implementing the COM servers for this example, some

code that is common among all the servers that it is helpful to factor out (to aid clarity and

reduce unnecessary code repetition). The simple utilities presented here all reside in the the

ppf.com.utils module.

Date Conversion

The to

ppf date function converts a Win32 COM date into a ppf.date time.date

representation. It is frequently used at the boundary between the COM servers and ‘pure’ ppf

Python:

def to ppf