VBA - Python best option for Microsoft Word maths and science functions

Yesterday I was programming a little piece of code that drew observations from the Normal (Gaussian) distribution because I wanted to mimic a the stochastic nature of a financial security. I wrote the code in Excel VBA and naturally drew upon the WorksheetFunction class off the Application class

?Application.WorksheetFunction.NormSInv(0.95)
 1.64485362695147 

Then I wanted for the purposes of a demo to run the code in WinWord (Microsoft Word) and then of course I was completely stuck. Without the Excel worksheet functions there is no standard VBA library for maths and science functions. I did Google for some VBA implementation of NormSInv but found nothing usable.

But Python can be called from VBA so long as you code the Python class with some COM registration so that it can be called from VBA. I've given many examples of this design pattern on this blog but I never get tired of advocating it. So here is the Python code which will expose the equivalent to Application.WorksheetFunction.NormSInv()

from scipy.stats import norm

class PythonNormsInv(object):
    _reg_clsid_ = "{CA2C3923-E70B-498F-BE0C-0E9953BF69B8}"
    _reg_progid_ = 'SciPyInVBA.PythonNormsInv'
    _public_methods_ = ['PyNormStdInv', 'PyNormInv']

    def PyNormStdInv(self, x):
        # https://stackoverflow.com/questions/20626994/how-to-calculate-the-inverse-of-the-normal-cumulative-distribution-function-in-p/20627638#answer-20627638
        return norm.ppf(x)

    def PyNormInv(self, x, loc=60,scale=40):
        return norm.ppf(x,loc,scale)

if __name__ == '__main__':
    print("Registering COM server...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(PythonNormsInv)

Now there is a startup/initialisation penalty so this can be mitigated by cacheing an instance of the class in a module level variable, so here is the client VBA code

Option Explicit

Private moPythonNormsInv As Object

Public Property Get PythonNormsInv2()
    If moPythonNormsInv Is Nothing Then
        Set moPythonNormsInv = VBA.CreateObject("SciPyInVBA.PythonNormsInv")
    End If
    Set PythonNormsInv2 = moPythonNormsInv

End Property

Sub Test_VBAPyNormStdInv()
    Debug.Print PythonNormsInv2.PyNormStdInv(0.95)
End Sub

So now one can you use Python in WinWord (Microsoft Word) and any VBA environment.

And another thing ... Python is a programming language for PostgreSQL

Not related to Word Processors but I discovered Python can be used in the open source relational database in PostgreSQL. In PostgreSQL, triggers and database functions can be written in Python. Python is everywhere it seems.

Code to mimic a financial security

So the code below detects if it is running in Word and uses the above Python class. Also if running in Excel VBA it uses the built-in Excel worksheet function.

Option Explicit

Private vExcelCoinPrice As Variant

Private moPythonNormsInv As Object
Private moRedisSocket As Object

Public Property Get RedisSocket() As Object
    If moRedisSocket Is Nothing Then
        
        '* see next blog post for codeo this server
        Set moRedisSocket = VBA.CreateObject("RedisRTDServerLib.RedisSocket")
        moRedisSocket.Initialize
    End If
    Set RedisSocket = moRedisSocket
End Property

Public Sub DisposeRedisSocket()
    If Not moRedisSocket Is Nothing Then
        moRedisSocket.Dispose
        Set moRedisSocket = Nothing
    
    End If
End Sub


Public Property Get PythonNormsInv2() As Object
    If moPythonNormsInv Is Nothing Then
        Set moPythonNormsInv = VBA.CreateObject("SciPyInVBA.PythonNormsInv")
    End If
    Set PythonNormsInv2 = moPythonNormsInv
End Property

Sub Test_VBAPyNormStdInv()
    Debug.Print PythonNormsInv2.PyNormStdInv(0.95)
End Sub

Sub ResetCoin()
    vExcelCoinPrice = 1.2
End Sub

Sub TestOnTime()
    
    '*
    '* initialize price of ExcelCoin
    '*
    If IsEmpty(vExcelCoinPrice) Then
        vExcelCoinPrice = 1.2
    End If

    Const dDrift As Double = 1.0001
    
    Dim dRectangular As Double
    dRectangular = Rnd(1)
    
    Dim dNormal2 As Double
    If WinWordVBA() Then
        dNormal2 = PythonNormsInv2.PyNormInv(dRectangular, 0, 0.001)
    ElseIf ExcelVBA() Then
        '=NORM.INV(0.95,0,0.001)
        Dim objApp As Object
        Set objApp = Application
        dNormal2 = objApp.WorksheetFunction.NormInv(dRectangular, 0, 0.001)
    
    End If
    
    Dim dLogNormal As Double
    dLogNormal = Exp(dNormal2)
    
    vExcelCoinPrice = vExcelCoinPrice * dLogNormal * dDrift
    Debug.Print vExcelCoinPrice

    '*
    '* call Redis to update price of Excel Coin, this is just a number (double)
    '*
    Dim oRedisSocket As Object
    Set oRedisSocket = RedisSocket
    Dim sResponse As String
    sResponse = oRedisSocket.SendAndReadReponse("SET USD/ExcelCoin " & CStr(vExcelCoinPrice) & vbCrLf)
    Dim vParsed As Variant
    vParsed = oRedisSocket.Parse(sResponse)
    
    '*
    '* also write the fuller meta document that carries timestamps, source URI etc.
    '*
    Dim sJsonDoc As String
    sJsonDoc = "{ ""timestamp2"": " & CDbl(Now()) & ", ""redisId"": ""USD/ExcelCoin/meta"" ,  ""point"": " & vExcelCoinPrice & " }"
    sJsonDoc = VBA.Replace(sJsonDoc, """", "\""")
    sResponse = oRedisSocket.SendAndReadReponse("SET USD/ExcelCoin/meta """ & sJsonDoc & """" & vbCrLf)
    vParsed = oRedisSocket.Parse(sResponse)
    
    '*
    '* set up next call
    '*
    DoEvents
    Call Application.OnTime(Format(Now() + CDate("00:00:02"), "dd/mmm/yyyy hh:mm:ss"), "TestOnTime")
End Sub

Function ExcelVBA() As Boolean
    On Error GoTo QuickExit
    Dim objApp As Object
    Set objApp = Application
    
    Dim objWbs As Object
    Set objWbs = objApp.Workbooks
    
    ExcelVBA = True
QuickExit:
End Function

Function WinWordVBA() As Boolean
    On Error GoTo QuickExit
    Dim objApp As Object
    Set objApp = Application
    
    Dim objActiveDocument As Object
    Set objActiveDocument = objApp.ActiveDocument
    
    WinWordVBA = True
QuickExit:
End Function