Financial Modelling in Python

P1: JYS

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

12

Python Excel Integration

The use of Microsoft Excel in financial institutions practising Quantitative Analysis is simply

too widespread to be overlooked. Python, or more specifically, the Win32 Python extensions,

make it easy to access Python functionality from Microsoft Excel. To do so, we dip into the

world of COM.

1

We won’t attempt to teach COM here, it is too broad a subject for this book,

but will instead focus on how we can write simple COM servers in Python and COM clients

in Microsoft Excel VBA.

2

For richer details regarding Python/COM integration beyond those

presented here, readers are strongly recommended to consult Hammond & Robinson’s Python

Programming on Win32 [19]. For those readers who are interested in acquiring a deeper

understanding of COM beyond the specific field of Python/COM integration we recommend

Don Box’s Essential COM [3].

12.1 BLACK–SCHOLES COM SERVER

To illustrate the basic ideas of publishing COM servers in Python we’ll jump straight in with a

COM component exposing the functionality of the ppf.core.black

scholes module.

The code presented below can be found in the ppf.com.black

scholes module.

class BlackScholes:

public methods = ["OptionPrice"]

reg clsid = "{14B40B3E-DC9A-4E07-A512-F65DA07BDC09}"

reg progid = "ppf.black scholes"

def OptionPrice(self, spot, strike, rate, vol, time, call

put):

from win32com.server.exception ...