PYTHON and xlWings
Code the Black-Scholes option model as a Python based UDF
0. Quick guide
In this module:
- 1. A Python based Excel UDF - requirements: a valid installation of Python, import libraries, and xlWings module. This means that section 2 of this module has been completed.
- 1.1 Code for the Black-Scholes option pricing model as a Python based user defined function (UDF) in Excel
- 1.2 Comparison of a Python version to the VBA version
- 2. Python installation - packages
- 2.1 Python or 2.2 Anaconda
- 2.3 xlWings
- 3. Writing - the xlWings UDF
- 3.1 Python editor - NotePad++
- 3.2 A Python UDF - the .py module, VBA code, and function argument descriptions
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
- Line 2: Import the xlwings module and rename it as xw. This abbreviation makes the code more concise
- Line 3 and 4: Import more modules using the same logic as line 2
- Line 5: Is a python comment identified by the hash symbol
- Line 6: @xw. is an xlWings decorator for a UDF (@xw.func). It identifies the UDF and creates the VBA wrapper in code 1.3 lines 53 to 65. It increases the Python functions functionality, in other words it decorates the Python function, by making it available in Excel.
- Line 7: Define a Python function with the def keyword . The backslash character is a Python statement line break . The function ends in line 23 with the last return keyword. Return exits the function and returns a value.
- Line 12: The OptType parameter is optional with default value set to True
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
- Python version 3.7.2 (release date 24 Dec 2018). Select python-3.7.2-amd64-webinstall.exe for 64-bit web-based installer
- Python folder:
- Size on disk: 116 MB
- Contains: 9,023 files, 582 folders
- PIP location: Python\Scripts\pip.exe
- Packages: Python\Lib\site-packages\
- Install packages: D:\Programs2\Python\Scripts\pip numpy scipy matplotlib ipython jupyter pandas sympy xlwings
2.2 Anaconda
- Anaconda3 version 2018.12 (release date 24 Dec 2018). Select Anaconda3-2018-12-Windows-x86_64.exe for 64-bit web-based installer
- Anaconds3 folder:
- Size on disk: 4.81 GB
- Contains: 114,855 files, 13,510 folders
- PIP location: Anaconda3\Scripts\pip.exe
Conda info(Conda info :: click to view)
- Packages: Anaconda3\pkgs\, and Anaconda3\Lib\site-packages\
Conda list (of packages)(Conda list :: click to view, then Magnify [263 lines])
2.3 xlWings
Add xlWings as an Excel add-in (location: XLSTART folder; file name: xlwings.xlam [51KB]; password: xlwings)
- xlwings v0.15.2 can be installed with pip. D:\Programs2\Python\Scripts\pip install xlwings
- Three images:
- Trust center - XLSTART location
- Windows explorer XLSTART folder
- Excel ribbon with xlwings tab
- Python
- User Defined Functions (UDFs)
- Advanced
(1. xlstart location, 2. xlwings in windows explorer, and 3. xlwings ribbon tab :: click to view)
- Add the xlwings.xlsm file to the XLSTART folder. D:\Programs2\Python\Scripts\xlwings addin install
(
VBA references :: xlwings)
3. Writing the xlWings UDF.
3.1 Python editor
A useful editor is NotePad++ which includes Python syntax.
- NotePad++ version 7.6.3 (release date 27 January 2019)
For more advanced work in the Python environment consider the Jupyter Notebook.
- Jupyter Notebook install via pip version 1.0.0 on the conda package list
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
- Display the Run command window with the key sequence Windows + R. Note: this is equivalent to in point 2.3 above
- Then run D:\Programs2\Python\Scripts\xlwings quickstart xlfbsudf , depending on the location of your Windows xlwings folder
- This creates a Folder named xlfbsudf containing two files
-
xlfbsudf.py, (code 1.1) and
- xlfbsudf.xlsm, including VBA module, and WS named _xlwings.conf
-
- In this example the Python py file and Excel xlsm have the same base file name (xlfbsudf) and are located in the same folder (..\Python\Scripts)
- To add descriptions to the arguments in the WS Functions Arguments dialog box, use the DescribeBSOptionPYFunction procedure in code 3.2
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 '
- Note: the FuncDesc = "Returns the theoretical price of a stock option using the Black-Scholes model" statement (code 3.2, line 114) could be replace by the xlWings statement """Returns the theoretical price of a stock option using the Black-Scholes model""" inserted in code 1.1 at line 14
References
- Anaconda distribution, Python/R Data Science Platform, accessed 20 January 2019
- Python, download Windows version, accessed 20 January 2019
- SciPy.org, Statistical functions (scipy.stats), accessed 20 January 2019
- Zoomer Analytics, xlwings - Python for Excel, accessed 3 February 2019
- Download the Excel file for this module: xlfbsudf.xlsm [111 KB]
- Development platform: Excel 2019 (64 bit) Office 365 ProPlus
- Published : 30 January 2019
- Revised: Friday 24th of February 2023 - 03:12 PM, Pacific Time (PT)