In finance, Microsoft Excel is used as a handy tool for bond traders and is useful in banking operations, as well as task automations using Visual Basic for Applications (VBA). Excel supports the use of Component Object Model (COM) to extend the functionality for custom tasks. This is achieved with the use of COM add-ins as an in-process COM server. With VBA, a wrapper can be created for the COM add-in function so that the COM component can be integrated as a worksheet cell formula function. COM allows the reuse of objects across different software and hardware environments to interface with each other, without the knowledge of its internal implementation. It allows an object to be created in several languages, such as C, C++, Visual Basic, Delphi, or Python.
In this chapter, we will learn how to build a COM server in Python. We will then create a COM client in Microsoft Excel and interface with the COM server to perform numerical pricing on the call and put options. We will use the Black-Scholes model, the binomial tree model, and the trinomial lattice model from the earlier chapters covered in this book for the COM server implementation. By linking to the cell values in Excel, or a market data source subscription within the worksheet cells, we can compute the theoretical option prices on the fly.
In this chapter, we will cover the following topics:
- Overview of the Component Object Model (COM)
- Understanding Excel for finance and COM
- Prerequisites for building a COM server
- Building the Black-Scholes model COM pricing server
- Registering and unregistering the COM server
- Building the Cox-Ross-Rubinstein binomial tree COM server
- Building the trinomial lattice model COM server
- Setting up VBA functions to build a COM client in Excel
- Setting up parameters in Excel to invoke the COM client-server interface
- Computing the theoretical option prices on the fly in Excel
COM allows the reuse of objects across different software and hardware environments to interface with each other, without the knowledge of its internal implementation. COM is a proprietary standard and is commonly associated with Microsoft's COM. COM forms the basis for Microsoft's other technologies, including ActiveX, COM+, and Document Component Object Model (DCOM).
COM allows an object to be created in several languages, such as C, C++, Visual Basic, Delphi, or Python. Using COM-aware components, COM classes are built as binary standards. Each COM component has its own class identifier (CLSID), which are globally unique identifiers (GUIDs), used for identification when used on a runtime framework. To locate a COM library, the Microsoft Windows registry is used to list all the available class and interface libraries as GUIDs.