Alglib/Python spline functions update

Posted on 02/08/2014 by dougaj4

The Excel Spline-Matrix spreadsheet has now been updated with several new spline interpolation functions.  See Alglib/Python linear and non-linear fitting functions for links to download and install the necessary Excel-Python and Alglib files.  Also required is Python version 2.7 or 2.6.

The spline functions included in the new version are:
xl_Spline3-01

Of which, the new ones are:

xl_PSpline: Parametric spline interpolation
xl_Spline3-02

The parametric spline function generates a smooth curve through a series of points, which need not be in order of increasing X.  Output may be specified to be periodic, in which case the curve is extended to the starting point, with slopes adjusted to provide a smooth curve throughout:
xl_Spline3-03

xl_Ratinterp provides rational interpolation without poles.  See the Alglib User Guide for background information.
xl_Spline3-04

xl_RatInterp output compared with a cubic spline
xl_Spline3-05

xl_Spline3D provides 3D Cubic spline interpolation.  The screenshot below shows a very simple example, taken from the Alglib manual.
xl_Spline3-06

xl_RBF and xl_RBFGrid provide Radial Basis Function interpolation.  Again see the Alglib User Guide for background information.
xl_Spline3-07

In this example the xl_RBF function is used to interpolate concrete shrinkage data, as used in the xl_NonLinFit example:
xl_Spline3-08

The xl_RBFGrid function provides the same results, but with more convenient data input, when 2D data is arranged in grid format.
xl_Spline3-09

Posted in AlgLib, Arrays, Excel, Link to Python, Maths, Newton, UDFs, VBA | Tagged , , , , , , , , , , | 2 Comments

The Deserter

Posted on 26/07/2014 by dougaj4

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

Posted on 25/07/2014 by dougaj4

Continuing from the previous post, the new xl_Spline-Matrix.zip 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.

xl_Spline2-2
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:
xl_Spline2-3

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

xl_Spline2-5

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

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

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

xl_Spline2-9

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:
xl_Spline2-10

Alternative spline fits:
xl_Spline2-11

xl_Spline2-12

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:
xl_Spline2-13

xl_Spline2-14

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

Alglib/Python linear and non-linear fitting functions

Posted on 23/07/2014 by dougaj4

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)
Excel-Python
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 xl_Spline-Matrix.zip, 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:

xl_Spline1
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:
xl_Spline2

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.
xl_Spline3
xl_Spline4

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.
xl_Spline5

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

xl_Spline7

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 NLFuncs.py.

xl_Spline8

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

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.
xl_Spline10

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.
xl_Spline11

xl_Spline12

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

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:
xl_Spline14

xl_Spline15

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

Evaluating text and Integration with Python and ALGLIB

Posted on 07/07/2014 by dougaj4

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 Eval-PyInt.zip.

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:
PyInt1
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:
PyInt3
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:
PyInt4
The three ALGLIB based functions work in the same way:
PyInt5
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:
PyInt8
For this function the Python functions failed to provide an accurate result:
PyInt6
whereas  the ALGLIB function  continued to subdivide the function until an accurate result was achieved;
PyInt7

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