PYTHON and xlWings
Code the Black-Scholes option model as a Python based UDF


0. Quick guide


In this module:


1. A WS UDF written in Python


This section includes three code examples. The key Python material is code 1 a: the Python module file, and code 1 color: #000;


1.1 Python BSOptionPY


The Python code (Code 1a) is a module file named xlfbsudf.py .


Python BSOptionPY UDF
Code 1.1: Function in file named xlfbsudf.py [972 bytes]
#
import xlwings as xw
import numpy as np
import scipy.stats as st
# ===========================
@xw.func
def BSOptionPY(Stock,\
               Exercise,\
               Rate,\
               Sigma,\
               Time,\
               OptType = True):
    #OptType = True (default) for Call, or False for Put

    d1 = (np.log(Stock / Exercise) + (Rate + 0.5 * Sigma ** 2) * Time) / (Sigma * np.sqrt(Time))
    d2 = (np.log(Stock / Exercise) + (Rate - 0.5 * Sigma ** 2) * Time) / (Sigma * np.sqrt(Time))
    BSCall = (Stock * st.norm.cdf(d1, 0.0, 1.0) - Exercise * np.exp(-Rate * Time) * st.norm.cdf(d2, 0.0, 1.0))
    BSPut = (Exercise * np.exp(-Rate * Time) * st.norm.cdf(-d2, 0.0, 1.0) - Stock * st.norm.cdf(-d1, 0.0, 1.0))

    if OptType:
        return BSCall
    else:
        return BSPut
 #

About code 1a


1.2 VBA BSOption comparison



VBA BSOption UDF [scroll to view]
Code 1.2: Function BSOption combines the BSCall procedure and the BSPut procedure and adds an Optional argument OptType
'
Function BSOption(Stock As Double, _
                  Exercise As Double, _
                  Rate As Double, _
                  Sigma As Double, _
                  Time As Double, _
                  Optional OptType As Variant) As Variant
                  ' OptType TRUE (default) for Call, FALSE for Put

Dim d1 As Double, d2 As Double
Dim BSCall As Double, BSPut As Double

    If IsMissing(OptType) Then OptType = True
    ' Check that Variant has sub type Boolean
    If VBA.TypeName(OptType) <> "Boolean" Then GoTo ErrHandler
    On Error GoTo ErrHandler

    With Application
        d1 = (.Ln(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time))
        d2 = (.Ln(Stock / Exercise) + (Rate - (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time))
        BSCall = Stock * .Norm_S_Dist(d1, True) - Exercise * Exp(-Rate * Time) * .Norm_S_Dist(d2, True)
        BSPut = Exercise * Exp(-Rate * Time) * .Norm_S_Dist(-d2, True) - Stock * .Norm_S_Dist(-d1, True)
    End With

    If OptType Then
        BSOption = BSCall
    Else
        BSOption = BSPut
    End If

Exit Function
ErrHandler:
End Function
'

1.3 VBA BSOption comparison



VBA BSOption UDF
Code 1.3: Function BSOption combines the BSCall procedure and the BSPut procedure and adds an Optional argument OptType
'Autogenerated code by xlwings - changes will be lost with next import!
#Const App = "Microsoft Excel" 'Adjust when using outside of Excel

Function BSOptionPY(Stock, Exercise, Rate, Sigma, Time, Optional OptType)
    If (Not Application.CommandBars("Standard").Controls(1).Enabled) Then Exit Function
    #If App = "Microsoft Excel" Then
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        BSOptionPY = Py.CallUDF("xlfbsudf", "BSOptionPY", Array(Stock, Exercise, Rate, Sigma, Time, OptType), ThisWorkbook, Application.Caller)
        Exit Function
    #Else
        BSOptionPY = Py.CallUDF("xlfbsudf", "BSOptionPY", Array(Stock, Exercise, Rate, Sigma, Time, OptType))
        Exit Function
    #End If
failed:
    BSOptionPY = Err.Description
End Function


2. Download Python and packages


Choose a package source, Python or Anaconda.


2.1 Python


2.2 Anaconda


2.3 xlWings


Add xlWings as an Excel add-in (location: XLSTART folder; file name: xlwings.xlam [51KB]; password: xlwings)


3. Writing the xlWings UDF.


3.1 Python editor

A useful editor is NotePad++ which includes Python syntax.

For more advanced work in the Python environment consider the Jupyter Notebook.


3.2 A Python UDF

Write a User Defined Function (UDF) with xlwings and Python. If you are new to xlWings then follow these steps


VBA BSOption UDF [scroll to view]
Code 3.2: Sub procedure DescribeBSOptionPYFunction. Normally located in the ThisWorkbook module
'
Sub DescribeBSOptionPYFunction()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 6) As String

   FuncName = "BSOptionPY"
   FuncDesc = "Returns the theoretical price of a stock option using the Black-Scholes model"
   ArgDesc(1) = "Price of the underlying stock"
   ArgDesc(2) = "Exercise price of the option"
   ArgDesc(3) = "Risk free rate of interest"
   ArgDesc(4) = "Standard deviation of the stock price"

   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub
'


References