Suggestions Example File Cholesky decomposition

Cholesky decomposition

VBA calculation

Python calculation  Cholesky decomposition

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.

#### Cholesky decomposition Macro

- read the array in sheet1, starting in A1 and store it in the Variant variable sn
- read half i.e. the right upper part of the Array variable sn
- perform the Cholesky operation
- store the calculation results into the left down part of Array variable sn
- write Array variable sn into worksheet sheet1

Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion

For j = 2 To UBound(sn)
For jj = j To UBound(sn)
y = sn(j, jj)
For k = 1 To j - 1
y = y - sn(j, k) * sn(jj, k)
Next

sn(j, jj) = ""
sn(1, jj) = ""
If j = jj Then sn(j, j) = Sqr(y)
If j <> jj Then sn(jj, j) = y / sn(j, j)
Next
Next

Sheet1.Cells(40, 1).CurrentRegion.ClearContents
Sheet1.Cells(40, 1).Resize(UBound(sn), UBound(sn)) = sn
End Sub

#### Cholesky decomposition Function

- read half i.e. the right upper part of the passed Array variable sn
- perform the Cholesky operation
- store the calculation results into the left down part of Array sn
- assign Array sn to the function F_snb
- use the resulting Array F_snb wherever you need to

Sub M_snb()
With Sheet1.Cells(1).CurrentRegion
Sheet1.Cells(40, 1).Resize(.Rows.Count, .Columns.Count) = F_snb(.Value)
End With
End Sub
Function F_snb(sn)
For j = 2 To UBound(sn)
For jj = j To UBound(sn)
y = sn(j, jj)
For k = 1 To j - 1
y = y - sn(j, k) * sn(jj, k)
Next

sn(j, jj) = ""
sn(1, jj) = ""
If j = jj Then sn(j, j) = Sqr(y)
If j <> jj Then sn(jj, j) = y / sn(j, j)
Next
Next

F_snb = sn
End Function

#### Cholesky decomposition UDF

- store the UDF is a separate macro module
- call the UDF using the Array Formula = F_snb(A1:AC29)

- convert the passed Range into a Variant variable
- read half i.e. the right upper part of Array variable sn
- perform the Cholesky operation
- store the calculation results into the left down part of Array variable sn
- empty the corresponding right upper 'cell' in the array
- assign Array variable sn to the function F_snb

Function F_snb(sn)
sn = sn

For j = 2 To UBound(sn)
For jj = j To UBound(sn)
y = sn(j, jj)
For k = 1 To j - 1
y = y - sn(j, k) * sn(jj, k)
Next

sn(j, jj) = ""
sn(1, jj) = ""
If j = jj Then sn(j, j) = Sqr(y)
If j <> jj Then sn(jj, j) = y / sn(j, j)
Next
Next

F_snb = sn
End Function

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 ) 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, 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, delimiter = ','), lower=True),delimiter = ',')"

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