Python - VBA - SciPy.CubicSpline - Curve Building

So, it's Python month on the Excel Development Blog where we look at elements of the Python ecosystem and see what they can do for the Excel (VBA) developer. This post I look curve building, that is the ability to take data and draw a curve of best fit crops up in finance rather a lot but specifically two use cases come to mind.

Yield Curve Fitting

The first use case concerns yield curves which are terms structures of interest rates. A yield curve draws a line of best fit through an array of points which are (typically sovereign) bond yields. The curve is then interpolated when a user wants an interest rate which does not coincide when a sovereign bond's due date. Yield curves are very useful they signal the market's view of the macro-economic trajectory; future interest rates, i.e. predictions about interest rates built into the curve can also be extracted.

Eliminating Jaggedness (Stochasticity) in Stock Data

The second use case I have seen is for technical analysis, the study of stock price movements. Stock charts are jagged and this jaggedness does not go away if one drills into a greater level of detail. This is because stock prices are stochastic and actually there is a branch of calculus called stochastic calculus which deals with the jaggedness but typically these techniques are the sole preserve of people with PHDs in Physics.

Don't worry, for technical analysis a cheat is available and that is to use curve building to smooth jagged stock data into a continuous and differentiable function; then local minima and maxima can be found.

The code

First we need to install Numpy and Scipy, so from an admin rights command window...

C:\WINDOWS\system32>pip install numpy
C:\WINDOWS\system32>pip install scipy

The following code is based on answer given to a Stack Overflow question - How to perform cubic spline interpolation in python?. The code quite simply calls into the CubicSpline class to be found in SciPy.

So here is a Python script which uses a COM Gateway class. (Go below for some VBA client code.) Of note, one can see the underlying CubicSpline object being stored as an instance variable by appending to self.

import numpy as np
from scipy.interpolate import CubicSpline

class PythonCubicSpline(object):
    
    _reg_clsid_ = "{F48006B8-42B5-4D89-8D3C-C3C3E5E24C8D}"
    _reg_progid_= 'SciPyInVBA.PythonCubicSpline'
    _public_methods_ = ['Initialize','Interpolate']

    def Initialize(self,x1,y1):
        # calculate natural cubic spline polynomials
        x = np.array(x1)
        y = np.array(y1)
        
        self.baseCubicSpline = CubicSpline(x,y,bc_type='natural')
        return str(self.baseCubicSpline)

    def Interpolate(self,x):
        return self.baseCubicSpline(x).tolist()

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

<UPDATE>You can make Python COM Gateway classes New-able and capable of early binding in this latest post</UPDATE>

Here is VBA client code which creates the object late bound, passes some bi-variate data into the Initialize method, the work to spline the curve is done there. Subsequent calls show the spline being interrogated; usefully one can supply a single value for interpolation or a list of values for interpolation by passing in an Array().

Option Explicit

Sub TestPythonCubicSpline()

    Dim cubSpline As Object
    Set cubSpline = CreateObject("SciPyInVBA.PythonCubicSpline")
    
    Call cubSpline.Initialize(Array(0, 1, 2, 3, 4, 5), Array(12, 14, 22, 39, 58, 77))
    
    Debug.Print cubSpline.Interpolate(1.25) '* passing single,  outputs 15.203125 
    
    Debug.Print Join(cubSpline.Interpolate(Array(1.25, 1.5)), ";") '# passing an array, outputs 15.203125;16.875

End Sub

Final Thoughts

As a VBA developer you maybe asked to write code to spline a yield curve and you maybe tempted to write VBA code to do this. I suggest you don't, I suggest you use a Python library instead using the gateway class method above. Splining yield curves is a major branch of quantitative analysis and I could not hope to introduce the topic here thoroughly but rest assured there is very probably some free Python code out there that will do what you want so no real need to write your own VBA.

UPDATE

You can make Python COM Gateway classes New-able and capable of early binding in this latest post

Links

• Stack Overflow - How to perform cubic spline interpolation in python?
• SciPy.org - scipy.interpolate.CubicSpline
• U.S. DEPARTMENT OF THE TREASURY - Daily Treasury Yield Curve Rates
• [Bank of Canada, Ron] A Practical Guide to Swap Curve Construction
• SciPy.org - scipy.interpolate.PiecewisePolynomial
• Treasury Direct: United States Treasuries - Today's prices
• Treasury Direct: United States Treasuries - Archived page of prices
• ActiveState: Alexander Baker - Zero curve bootstrapping and forward curve generation (python recipe)
• Simon Gunnarsson: Curve Building and Swap Pricing in the Presence of Collateral and Basis Spreads