Three recently uploaded You Tube recordings of live performances by guitarists Bert Jansch and John Renbourn. As further evidence that the quality of a You Tube video is inversely proportional to the number of views, none of the three have over 1000 views, and the first has just 6.
Bett’s Dance; Bert Jansch live at the 12 bar:
Bert Jansch and John Renbourn; un-named instrumental, live from Massachusetts, 1990
From the same concert, John Renbourn plays So Early in the Spring:
Yesterday I saw “Scotland the Brave” at the Sydney Opera House, featuring the Willoughby Symphony Orchestra and Choir, and fiddler extraordinaire Marcus Holden:
whose playing of Scottish fiddle music reminded me of Robin Williamson and the Incredible String Band:
and lead to the discovery that Marcus Holden also plays in a string band, The Jugalug String Band:
The Alglib spreadsheet presented here includes functions to evaluate formulas entered as text on the spreadsheet, or to call specified Python functions. I have now added a user defined function (UDF) that will perform either of these operations (xl_CallFuncD), and added a number of examples of use in different situations. The updated spreadsheet (including full open-source code) can be downloaded from:
As before the following software is required:
The Anaconda Python package includes Numpy, Scipy and xlwings (now updated to ver 0.11.04). Alglib must be installed separately, and comes in free and commercial versions.
To deal with the need to pass any number of arguments, the function arguments are listed in a two (or three) column range; column 1 lists argument names, and column 2 either single values, or range addresses, entered as a text string. This data is read into a VBA variant array, then converted to a Python dictionary. Examples of the usage of xl_CallFuncD are shown in the screen-shots below:
A formula entered as text on the spreadsheet, with any number of listed variables. Note that exponents may be entered using either VBA notation (^) or Python/Fortran notation (**):
Function results may be used as input values for another formula:
On-sheet formulas may include any built-in Python function, or functions from the imported Numpy, Scipy or Alglib modules:
Both on-sheet formulas and external function calls may have array arguments. In this case the argument range must have three columns, with the third column specifying the number of dimensions for the array (1 or 2). The range address may be entered as text, but to ensure that the address is updated if the range is moved the UDF RngAddress should be used:
xlCallFunc will call user defined Python functions from the xlAlgLib module without any dedicated VBA interface function:
… or user defined functions from another module:
… or functions from other Python libraries. If the called function returns 2 or more array results the Out argument must be used to select the array to be returned. The Python dictionary of arguments passes arrays as tuples, which will cause problems with some functions. In this case enter True in the Convert argument, which will convert all passed tuples to lists, or lists of lists.
Note that argument names must be entered exactly, including correct case. To list all arguments for any function, use the get_argnames UDF:
Excel has a built-in function (FormulaText) to display a formula as cell references, rather than the result value. It would be useful to be able to display the value of each cell reference as well, but there is no built-in function to do that.
It turns out it’s not easy to do with VBA either. I have added a macro to do the job to the Eval2 spreadsheet:
Sub Addr2Val()
Dim PrecCells As Variant, CellRng As Range, NumP As Long, i As Long, Form As String
Dim CellAddr() As String, CellVal() As Double, xCell As Range, OutCols As Long
Set CellRng = Application.ActiveCell
Form = CellRng.Formula
Form = Replace(Form, "$", "")
Set PrecCells = CellRng.Precedents
NumP = PrecCells.Count
ReDim CellAddr(1 To NumP, 1 To 1)
ReDim CellVal(1 To NumP, 1 To 1)
i = 1
For Each xCell In PrecCells
CellAddr(i, 1) = xCell.Address
CellAddr(i, 1) = Replace(CellAddr(i, 1), "$", "")
CellVal(i, 1) = xCell.Value
i = i + 1
Next
Form = Eval(Form, CellAddr, CellVal, 0)
OutCols = Selection.Columns.Count
If OutCols > 1 Then OutCols = OutCols - 1
CellRng.Offset(0, OutCols).Value = " " & Form
Set CellRng = Nothing
Set PrecCells = Nothing
End Sub
The macro uses the Range.Precedents method to return the address and value of each cell reference in the formula, then calls the Eval user defined function (UDF) to convert the cell addresses in the formula to their numerical values. It would be convenient to put this in a function, which could be called from the spreadsheet, but when entered in a UDF the .Precedents method does not return information on the cell precedents, it returns the information for the cell itself. If anyone has any suggestions for converting the macro to a working UDF, please leave a comment.
The macro has been set up to return the results in the cell to the right of the selected cell, or if three or more columns are selected, in the top right cell of the selected range. Note that the macro will write over any contents in the results cell.
An example of the macro in use (with instructions) is shown in the screen-shot below:
The updated spreadsheet, including full open-source code and the example above, can be downloaded from:
The Alglib library provides many valuable numerical analysis routines, but the VBA version is no longer actively maintained. The latest versions are available in C++, C#, and Python, of which the Python version (in conjunction with xlwings) provides the easiest interface with Excel.
An Alglib/Python based spreadsheet has previously been presented here. I have now updated this to the latest versions of xlwings and Alglib, added additional solver and linear algebra functions, and updated the Python code to make better use of the xlwings auto-import functionality, which generates the necessary VBA code at the touch of a button. The new spreadsheet can be downloaded from:
As usual, the download files include full open source code.
To install the necessary files:
The screenshots below show the functions available in the new version. Future posts will look in more detail at specific examples, and procedures for modifying the Python code.
List of functions:
Spline Functions:
Parametric Splines:
Rational Interpolation Function:
2D and 3D Interpolation:
Fit a spline to scattered data:
Polynomial fitting:
Non-Linear Fitting:
Fit Polynomial to scattered data:
Radial Basis Function Interpolation:
Levenberg-Marquardt optimisation:
Integration of Cubic Splines:
Differentiation of Cubic Splines:
Matrix algebra:
