The Deserter

The Deserter is a traditional song featured on Fairport Convention’s 1968 album Liege and Lief; it is said to be the favourite track on the album of Sandy Denny, and the producer, Joe Boyd.  The song has a long history, and many versions.  Dave Swarbrick commented:

Although we learned this song from a faded Victorian broadside, it is much older in origin. It was the habit of broadside printers to bring older songs up to date—in this case by including the name of Prince Albert.

A detailed list of performances can be found at Mainly Norfolk.  It seems from some of the comments that some performers totally missed the irony in the closing verse.

Searching on “Ratcliff Highway” led me to this beautiful instrumental version:

and a live recording of a totally different traditional song, by The Dubliners:

Posted in Bach | Tagged , , , , | Leave a comment

Alglib/Python spline functions

Continuing from the previous post, the new spreadsheet includes functions for 1D and 2D linear and cubic splines.

xl_Spline2-1The basic 1D spline allows  for output of linear splines, or 5 different types of cubic spline, as listed above.

Output may be spline values at listed points, spline parameters for each spline segment, or spline integration or slope.

Additional data allows input of end slopes or curvatures for cubic splines, slope data for hermite splines, or end type and tension for Catmull-Rom splines:

The screenshots below show input and results for all six spline types, passing through 5 points:


Output of spline segment data, integration, and slope and curvature values is shown below:

Input and output for the 2D spline function is shown below.

The screenshots below show 2D splines fitted to tabular data for air density under varying pressure and temperature:


The xl_Spline1DFit function finds the least squares fit of a cubic spline with a specified number of segments, with optional weighting and/or constraints:

Alternative spline fits:


The xl_SplineInt and xl_SplineDiff functions provide integration and differentiation data for any of the spline types. They are equivalent to output options 3 or 4 with the xl_Spline1d function:


Posted in AlgLib, Excel, Link to Python, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , , , , , , , | 1 Comment

Alglib/Python linear and non-linear fitting functions

I have updated the Alglib spline-matrix spreadsheet to use the latest Alglib release, using the Python version, in conjunction with the Excel-Python add-in. To use the spreadsheet requires:

Python ver 2.7 (or 2.6)
Alglib with Python interface

In addition to using the latest Alglib library, the new spreadsheet updates the functions, providing added functionality in applying weights and constraints to fitted functions, and additional examples. The new spreadsheet may be downloaded from, including full open-source VBA and Python code linking to the Alglib library. The Alglib code may be downloaded from the link above.

The functions included in the spreadsheet are listed in the screenshot below:

This post will look at the fitting functions.  The spline interpolation functions and matrix functions will be described in a later post.

The xl_LinFit function is equivalent to the Excel LinFit function, but also allows weights and constraints to be applied to the fitted data:

The example below shows a quadratic curve fitted to scattered data with various options and constraints.  Results from the Excel LinEst function are also included, which are the same as the Alglib function without weights and constraints.

The xl_LinFit function can be used to fit polynomial curves, as seen above, but this is more conveniently done with the xl_PolyFit function, which also gives much better results for high order polynomials.

The screenshots below show a 15th order polynomial fitted to a data generated from a cyclic function:


Two functions are provided for non-linear fitting. xl_NLFitFunc fits any named Python function to the input data. The function code must be in the file


xl_NLFitText works in the same way except the function is entered as text on the spreadsheet, rather than as a Python function.

The example below shows output from both xl_NLFitFunc and xl_NLFitText applied to concrete shrinkage data. Note that both functions allow the use of weights and constraints.

The two screenshots below show the results of two alternative functions applied to the creep data. It can be seen that the second function gives a far better fit to the data.


The xl_NLFitFunc function also allows the use of gradient and hessian functions. See the alglib documentation for more details.

Finally the last two screenshots illustrate the use of the xl_NLFitFunc and xl_NLFitText functions to fit a fourth degree polynomial to scattered data:


Posted in AlgLib, Excel, Link to Python, Newton, UDFs, VBA | Tagged , , , , , , | 3 Comments

Evaluating text and Integration with Python and ALGLIB

Following the previous post on evaluating mathematical functions entered as text on the spreadsheet, I have now written a spreadsheet with similar functionality using Python functions, and the Excel-Python add-in.  Also included are integration functions using the SciPy Python library, and the ALGLIB library, with the Python interface.  The new spreadsheet, including full open source VBA and Python code, can be downloaded from

All the functions require the installation of  ExcelPython, which is free and open-source, and Python, including the Scipy library.

The ALGLIB based integration functions also require the installation of the ALGLIB library, which comes in a free version, and a higher performance commercial version.

Use of the xl_Eval function is shown in the screenshot below, together with the VBA version:
The main benefit of the Python based functions is that they allow direct access to Python, Numpy and Scipy functions, without coding: PyInt2
The new xl_EvalR function allows the evaluation of Python functions requiring array input:
In the examples above, the first applies the Python max() function, which works in the same way as the Excel equivalent.  In the second, the array is converted to a Numpy array, which is converted to absolute values, then the .max() method is applied, returning the absolute maximum value.  In the third example the argmax() method is applied to the absolute values of the array, returning the offset of the maximum absolute value, which is then applied to the original array, to return the signed value of the maximum absolute value.

Three Numpy based integration functions are provided, which will integrate a function entered as text on the spreadsheet (xl_IntString), integrate a specified Python function (xl_IntCallFunc), or integrate a hard coded Python function:
The three ALGLIB based functions work in the same way:
The ALGLIB functions are significantly slower than the Python based equivalents, however they were found to perform better with very difficult functions, such as that shown below:
For this function the Python functions failed to provide an accurate result:
whereas  the ALGLIB function  continued to subdivide the function until an accurate result was achieved;

Posted in AlgLib, Arrays, Excel, Link to Python, Maths, Newton, Numerical integration, NumPy and SciPy, UDFs, VBA | Tagged , , , , , , | 1 Comment

The Dome of Santa Maria del Fiore in Florence; new links

Earlier this year the on-line edition of National Geographic posted an article on:

Mystery of Florence’s Cathedral Dome May Be Solved

The wording of the article owes more to journalistic hyperbole than anything else, but it includes a link to an interactive 360 degree image of the cathedral interior of quite amazing detail.  The screenshot below shows a detail from the dome, but visit the link (about half way down) to view any other part of the cathedral interior in similar detail.


Other links from this article include:

Redefining the Dome

An Interactive History of the Dome

The Secrets of the Florentine Dome

As we will see in this article, this great building site, challenging all of human knowledge at that epoch, was the locomotive provoking a scientific and technological revolution. Raising the great dome drew new horizons, which far beyond the construction of a building sent ripples and shockwaves of philosophical optimism to the great benefit of future generations.



Posted in Arch structures, Bach, Dome Structures, Newton | Tagged , , , | Leave a comment