CALCULATE PI USING MONTECARLO SIMULATION Phase IV – Excel VBA & Python via PYXLL

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.

  1. pyxll
  2. 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 :

Yes, my environments were a total mess too.

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

Iterations505005000500005000005000000
calc_pi_numba0.00022740.0002450.0004370.0020.01648070.1567803
calc_pi_numba Jupyter0.0000130.000020.0001610.0015880.0158830.152588
CalcPiMonteCarloVBA0.00014330.0003250.0026270.0250180.24452122.4704114
N/AN/AN/AN/AN/AN/AN/A
multi_calc_pi2.15119512.2541022.2633842.2548212.47322452.9926874
multi_calc_pi Jupyter2.8211772.8119432.7145792.7103182.9168163.315011
calc_pi_quick0.00047330.0007430.005730.048880.46228825.3454645
calc_pi_quick Jupyter0.0000710.0005040.0051170.0512970.4672264.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)