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:

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

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:

Works great. Very useful and easy to use. Thanks for sharing.

LikeLike

Hi Doug,

Great post, it’s nice to see how many possibilities are opened up by mixing Python with Excel.

I just wanted to point out that I have released ExcelPython version 2, which is a major rewrite of version 1 and has a slightly different syntax. So if your readers want to use your sample worksheet they should make sure they download version 1.x.x.

The reason for the rewrite was to take Python out-of-process. This means that v2 should not suffer from the DLL issues many people experienced with v1, and also allows mixing 32 and 64 bit Excel and Python and is compatible with any version of Python. However if v1 works well for you there is no reason to stop using it!

Regards

Eric

LikeLike

Thanks Eric. I’ll have a look at the new version. The added flexibility with Excel and Python versions looks like a worthwhile advantage.

LikeLike

Pingback: Alglib/Python spline functions update | Newton Excel Bach, not (just) an Excel Blog