Introduction
Everywhere I have ever worked (I am mainly talking about the standard sell side Wholesale / Investment banks) use Excel & VBA for their prototypes and any desk based development. While lots of time, money and effort is being spent on moving FO and MO staff onto Python there will still be quite a lot of legacy applications coupled with traders who don’t want to skill up to Python and a myriad of other reasons
Excel VBA Prototype
So lets see what we can do in native VBA to simulate a Front Office / Strat developed legacy solution we are looking to provide a strategic solution for.
Public Function CalcPiMonteCarloVBA(ByVal Simulations As Long, _
Optional ByRef TimeTaken As Double) As Double
Dim Inside As Long
Dim i As Long
Dim x As Double
Dim y As Double
Dim StartTime As Double
10 On Error GoTo ErrHandler
20 StartTime = Timer
30 Inside = 0
40 For i = 1 To Simulations
50 x = Rnd: y = Rnd
60 If x ^ 2 + y ^ 2 <= 1 Then
70 Inside = Inside + 1
80 End If
90 Next
100 CalcPiMonteCarloVBA = 4 * (Inside / Simulations)
110 TimeTaken = Timer - StartTime
120 Exit Function
ErrHandler:
130 Err.Raise 4480, Erl, "Error in CalcPiMonteCarloVBA: " & Err.Description
End Function
Excel Front End
We can take advantage of the advantages of an Excel VBA based solution and create a little GUI to test out some basic assumptions.
Eventhandling Code
Sub CalcPiMonteCarlo_Click()
Dim TimeTaken As Double
10 On Error GoTo ErrHandler
20 Me.Range("CalculatedPi").Value2 = CalcPiMonteCarloVBA(Me.Range("Simulations").Value2, TimeTaken)
30 Me.Range("TimeTaken").Value2 = TimeTaken
40 Exit Sub
ErrHandler:
50 MsgBox Err.Description & " occured on " & Erl, vbCritical, "Error in CalcPiMonteCarlo_Click"
End Sub
As can be seen in the Photo compared to Python itself the performance is none too shabby. About twice the speed of Native python without any tweaks. However we know from Phase III that numba will massively speed things up.
What would be interesting is to re-use this Python code, in its existing form and try to call this from VBA and see if we still get the performance benefit.
Calling Python from VBA.
I have been spoilt by the banks I work for doing all of this within their existing Quant Library. Mainly routing through the COM layer and into C++ and with the calling of Python tacked onto the side of that. However from homebrewing solutions there seem to be two main contenders I have found that I would like to try out.
- pyxll
- xlwings
PyXLL
Setup & Install
Microsoft Office issues
Well I don’t use it much at home and it turned out I have 32 Bit office installed on my machine. I use 64bit Python so I have to uninstall and re-install Office to not mix bit-ness between them.
I am glad I waited to finish off this blog piece, as I found the inital set up of this a nightmare. However this really wasn’t PyXLL’s fault. I had many many python installs across different versions, both Pip and Anaconda and 32bit 3.7 and 64 bit 2.7 etc. It was awful. Reminds me of this :
So I decided that as I had elected to use Anaconda for Jupyter Notebooks, I would wipe ALL of my python installs off the map and start afresh. I am super glad I did as after that all the installs and setup was miles easier and I now have a conda pyxll venv set up to play in.
Initial Python file changes
The documentation for PyXLL is very good and I was able to adapt my calc_pi code pretty easily
from random import random
from numba import njit, jit
from pyxll import xl_menu, xl_app, xlcAlert, xl_func
@xl_func
@jit
def calc_pi_numba(num_attempts: int) -> float:
inside = 0
for _ in range(num_attempts):
x = random()
y = random()
if x ** 2 + y ** 2 <= 1:
inside += 1
return 4 * inside / num_attempts
@xl_func
def calc_pi_quick(num_attempts: int) -> float:
from random import random
inside = 0
for _ in range(num_attempts):
x = random()
y = random()
if x ** 2 + y ** 2 <= 1:
inside += 1
return 4 * inside / num_attempts
@xl_func
def multi_calc_pi(num_attempts: int, verbose=False) -> float:
from multiprocessing import Pool
from random import random
import os
from statistics import mean
# lets leave one spare for OS related things so everything doesn't freeze up
num_cpus = os.cpu_count() - 1
# print('Num of CPUS: {}'.format(num_cpus))
try:
attempts_to_try_in_process = int(num_attempts / num_cpus)
pool = Pool(num_cpus)
# what I am trying to do here is get the calc happening over n-1 Cpus each with an even
# split of the num_attempts. i.e. 150 attempts over 15 CPU will lead to 10 each then the mean avg
# of the returned list being returned.
data_outputs = pool.map(calc_pi_quick, [attempts_to_try_in_process] * num_cpus)
return mean(data_outputs)
finally: # To make sure processes are closed in the end, even if errors happen
pool.close()
pool.join()
I did have to do some debugging which I hacked up from the examples bundled with PyXLL. Some of it was quite cool and worth noting here.
from pyxll import xl_menu, xl_app, xlcAlert, xl_func
from win32com.client import constants
import win32clipboard
import math
import time
import pydevd_pycharm
@xl_menu("Connect to PyCharm", menu="Profiling Tools")
def connect_to_pycharm():
pydevd_pycharm.settrace('localhost',
port=5000,
suspend=False,
stdoutToServer=True,
stderrToServer=True)
@xl_menu("Time to Calculate", menu="Profiling Tools")
def time_calculation():
"""Recalculates the selected range and times how long it takes"""
xl = xl_app()
# switch Excel to manual calculation and disable screen updating
orig_calc_mode = xl.Calculation
try:
xl.Calculation = constants.xlManual
xl.ScreenUpdating = False
# get the current selection
selection = xl.Selection
# Start the timer and calculate the range
start_time = time.clock()
selection.Calculate()
end_time = time.clock()
duration = end_time - start_time
finally:
# restore the original calculation mode and enable screen updating
xl.ScreenUpdating = True
xl.Calculation = orig_calc_mode
win32clipboard.OpenClipboard()
win32clipboard.EmptyClipboard()
win32clipboard.SetClipboardText(str(duration))
win32clipboard.CloseClipboard()
# report the results
xlcAlert('time taken : {}'.format(duration))
return duration
VBA based PERFORMANCE measuring
I tried to log the performance by passing in a cell to a Python macro function in PyXLL but as PXLL support informed me if that is called from another cell, then the calculation tree is already going and it wont work. Hence I had to use a menu Item.
Then I realised that if I could get a good enough timer in VBA by hooking into some of the underlying Windows functionality I could get soe decent timing by wrapping each of the calls in a VBA call.
modTimer VBA
Type UINT64
LowPart As Long
HighPart As Long
End Type
Private Const BSHIFT_32 = 4294967296# ' 2 ^ 32
Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As UINT64) As Long
Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As UINT64) As Long
Global u64Start As UINT64
Global u64End As UINT64
Global u64Fqy As UINT64
Public Function U64Dbl(U64 As UINT64) As Double
Dim lDbl As Double, hDbl As Double
lDbl = U64.LowPart
hDbl = U64.HighPart
If lDbl < 0 Then lDbl = lDbl + BSHIFT_32
If hDbl < 0 Then hDbl = hDbl + BSHIFT_32
U64Dbl = lDbl + BSHIFT_32 * hDbl
End Function
Public Sub StartTimer(ByRef u64Fqy As UINT64, ByRef u64Start As UINT64)
' Get the counter frequency
QueryPerformanceFrequency u64Fqy
' Get the counter before
QueryPerformanceCounter u64Start
End Sub
Public Function EndTimer() As UINT64
' Get the counter before
QueryPerformanceCounter u64End
EndTimer = u64End
End Function
Public Function CalcTimeTaken(ByRef u64Fqy As UINT64, ByRef u64Start As UINT64) As Double
' User Defined Type cannot be passed ByVal and also not copying it should reduce the error seen in
' tioming for very small increments as we expect to see say timing something like Numba loops vs VBA for
' small iteations (< 1000)
CalcTimeTaken = (U64Dbl(EndTimer) - U64Dbl(u64Start)) / U64Dbl(u64Fqy)
End Function
Wrapper VBA Function
Returns a 2 cell variant with the estimated Pi value and then the time taken
Public Function CalcPi(ByVal NumAttempts As Long, _
Optional ByVal CalcMethod As String = "CALCPIMONTECARLOVBA") As Variant
Dim x(1) As Variant
Dim StartTime As Double
Dim EndTime As Double
Dim TimeTaken As Double
Dim Pi As Double
On Error GoTo ErrHandler
Pi = 0
modTimer.StartTimer modTimer.u64Fqy, modTimer.u64Start
Pi = Run(CalcMethod, NumAttempts)
TimeTaken = CalcTimeTaken(modTimer.u64Fqy, modTimer.u64Start)
x(0) = Pi
x(1) = TimeTaken
CalcPi = Application.Transpose(x)
Exit Function
ErrHandler:
Err.Raise 4444, Erl, "Error in CalcPi: " & Err.Description
End Function
Results
Performance of existing VBA – considerations
So it is interesting to note that if you have lots of small number crunching tasks to complete, moving all these to native Python might not actually save your bacon if looking for performance improvements.
CalcPiMonteCarloVBA takes almost half the time ( 24s ) vs calc_pi_quick taking 46 seconds to complete for 50,000,000 iterations.
Comparison to Jupyter Notebook
| Iterations | 50 | 500 | 5000 | 50000 | 500000 | 5000000 |
| calc_pi_numba | 0.0002274 | 0.000245 | 0.000437 | 0.002 | 0.0164807 | 0.1567803 |
| calc_pi_numba Jupyter | 0.000013 | 0.00002 | 0.000161 | 0.001588 | 0.015883 | 0.152588 |
| CalcPiMonteCarloVBA | 0.0001433 | 0.000325 | 0.002627 | 0.025018 | 0.2445212 | 2.4704114 |
| N/A | N/A | N/A | N/A | N/A | N/A | N/A |
| multi_calc_pi | 2.1511951 | 2.254102 | 2.263384 | 2.254821 | 2.4732245 | 2.9926874 |
| multi_calc_pi Jupyter | 2.821177 | 2.811943 | 2.714579 | 2.710318 | 2.916816 | 3.315011 |
| calc_pi_quick | 0.0004733 | 0.000743 | 0.00573 | 0.04888 | 0.4622882 | 5.3454645 |
| calc_pi_quick Jupyter | 0.000071 | 0.000504 | 0.005117 | 0.051297 | 0.467226 | 4.660022 |
THe above table is a little data dense and not that informational but it seems that calling from PyXLL is even perhaps slightly faster than from a Notebook which is awesome news!
So computationally with Python diligence and some CompSci though we can move swathes of calculation intensive code out of VBA and into Python and get the panacea of
- greater code control – we can assume the python code can live in a repository and have CI/CD “DevOps” practices around its delivery to the user.
- a great library of open source functionality (pandas etc)
- increased speed from Cython
- code re-use (harder to do outside of XLAs)
Rapid Development for Finance 