VBA for smarties: Cholesky decomposition

Cholesky decomposition

Cholesky decomposition

VBA calculation

Python calculation

In certain cases you may need a Cholesky decomposition of a matrix.
For elaborate information see Cholesky decomposition Wiki
In several programming languages the Cholesky decomposition is available.
In this page we will show how you can use VBA to do these calculations.
We will also show you how you can use the already available Cholesky calculation in Python's library NumPy.
The description matches the accompanying file.
The VBA approach is written in the macromodule of the sheet "Cholesky".
The VBA UDF approach is written in the macromodule "M_UDF".
The Python approach is stored in the macromodule of the sheet "Python".

Cholesky calculation in VBA

The basic data in the Cholesky calculation is a symmetrical Matrix/Array
It means the matrix has the same amount of rows and columns.
It also means that the data are being mirrored around the diagonal (1,1) to (n,n).

The basic VBA-approach was found in a YouTube channel, created by Gerard Verschuuren. Cholesky decomposition
That code has been modified by G. Vilensky.
In cooperation with G. Vilensky snb adapted the code to its present status.
A Cholesky decomposition can be run in a macro, using an available matrix in a worksheet and writing the resulting (demi) matrix into the same worksheet.
The Cholesky decomposition can also be performed in a Function or as a User Defined Function (UDF) in Excel.

The code originates from G. Vilensky, who introduced me to mr. Cholesky.
It has been adapted by snb.

It is assumed that Python has been installed on your system.
The worksheet in the Excel Workbook contains a symmetrical Matrix, starting in cell A1.

The VBA code

- changes the active directory to the same directory that contains the Excel workbook.
- copies the array/matrix in sheet "Cholesky"
- saves the copied array/matrix as a CSV-file: corr.csv with comma as field separator
- runs the Python file 'cholesky.py' that resides in the same directory as the Excel Workbook
- the Python file writes the calculation result into the CSV-file 'cholesky.csv'
- refreshes the querytable in the worksheet "Python", that is linked to the 'cholesky.csv' file in the active directory.
- you can use the CommandButton in sheet 'Python' to run the Python code.

Private Sub CB_002_Click()

ChDir ThisWorkbook.Path

ThisWorkbook.Sheets("Cholesky").Cells(1).CurrentRegion.Copy
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

.GetFromClipboard
CreateObject("scripting.filesystemobject").createtextfile(ThisWorkbook.Path &"\corr.csv").write Replace(Replace(.gettext, ",", "."), vbTab, ",")

End With
Application.CutCopyMode = 0

CreateObject("wscript.shell").Run "cholesky.py corr.csv", 0, -1

ThisWorkbook.Sheets("Python").QueryTables(1).Refresh 0

End Sub

NB. If the Array is big, the clipboard approach might be too slow.
In that case you could use

Private Sub CB_002_Click()

sn= ThisWorkbook.Sheets("Cholesky").Cells(1).CurrentRegion
for j=1 to ubound(sn)

c00=c00 & vbCrLf & join(application.index(sn,j),",")

next

CreateObject("scripting.filesystemobject").createtextfile(ThisWorkbook.Path & "\corr.csv").write mid(c00,3)

CreateObject("wscript.shell").Run "cholesky.py corr.csv", 0, -1

ThisWorkbook.Sheets("Python").QueryTables(1).Refresh 0

End Sub

The Python File

The Python code
- loads the function savetxt from the Python library NumPy
- loads the function genfromtxt from the Python Library NumPy
- loads the function argv from the Python library Sys
- loads the library linalg from the Python library SciPy
- reads the file 'corr.csv' that has been passed as argument ( ar[1] ) when calling the Python file
- the file will be read as comma separated text: genformtxt(.., delimiter=",")
- the text will be 'fed' to the function 'cholesky' in the linear algebra function linalg.cholesky
- the calculation result will be written into the csv-file 'cholesky.csv' with a comma as separator: st('cholesky.csv', ... , delimiter=",")

from numpy import savetxt as st, genfromtxt as gt
from sys import argv as ar
from scipy import linalg as al

st('cholesky.csv', al.cholesky(gt(ar[1], delimiter = ','), lower=True),delimiter = ',')

Create the Python file

You can use the CommandButton in sheet "Python" to create the Python file.

Private Sub CB_001_Click()

ReDim sn(4)
sn(0) = "from numpy import savetxt as st, genfromtxt as gt"
sn(1) = "from sys import argv as ar"
sn(2) = "from scipy import linalg as al"
sn(4) = "st('cholesky.csv', al.cholesky(gt(ar[1], delimiter = ','), lower=True),delimiter = ',')"

CreateObject("scripting.filesystemobject").createtextfile(ThisWorkbook.Path & "\cholesky.py").write Join(sn, vbCrLf)

End Sub

Cholesky decomposition Macro

Cholesky decomposition Function

Cholesky decomposition UDF

The VBA code

The Python File

Create the Python file