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

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

Posted on 01/07/2014 by dougaj4

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.

Duomo-1

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

Evaluating text – update

Posted on 28/06/2014 by dougaj4

One disadvantage of the spreadsheet style interface is that even simple formulae are difficult to check.  For instance, the deflection of a cantilever loaded at the end is given by:
F*L^3/(3*E*I)
which is much easier to read than the Excel version:
=B31*B32^3/(3*B33*B34)
It is possible to allocate names to the spreadsheet cells, but this rapidly becomes cumbersome if there are more than a few formulae, or if a formula is repeated with different data.

The spreadsheet Eval2.xlsb overcomes these problems by allowing the evaluation of formulae entered as text:

Eval2-2-1

In the example above, the text formula in Cell A29 is evaluated using the values listed in the range A31:B34.  The Eval function also has the option to return a text string with the variable names replaced with values, as seen in Cell D31.

The original version of this function had the disadvantage that short variable names might corrupt longer names, or function names, that included the same characters.  For instance a variable “a” would result in “ab” or “tan” having their a characters replaced by the value of a.  I have now re-written the function, using the VBA scripting dictionary, so that all variable names only apply to a string of the same length.  The new function, including full open source code, may be downloaded from the link below.  Links are also provided to other spreadsheets using the Eval function.

The screenshot below shows an example of the use of the new version with a variable name that would previously have caused an error.  Note also that the range specifying the variable names and values may now be entered as a single range of two adjacent columns, or two separate ranges.

Eval2-2-2

The Evala function returns an array of values with evaluation of a single formula.  In the example below the formula for K2 is evaluated for a range of values of t and th:

Eval2-2-3

The Eval-Integration spreadsheet (included in the Eval2.zip download) includes a number of functions for numerical integration of any function of a single variable, including the Tanh-Sinh method (based on a function provided by Graeme Dennes), and Gauss-Kronrod Quadrature, using the Alglib library:

Eval2-2-4

The new version has also been included in the Units4Excel spreadsheet, allowing unit aware evaluation of any formula, using a wide range different units:

Eval2-2-5

 

Posted in AlgLib, Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , , , | 5 Comments

Reinforced Concrete – ULS capacity under combined axial load and biaxial bending

Posted on 19/06/2014 by dougaj4

I have modified the ULS Design Functions spreadsheet, last presented here, to analyse sections subject to bi-axial bending, and non-symmetrical sections.  The new version makes use of the routines for splitting any section defined by XY coordinates into trapezoidal layers, described here.  The new version (ULS Design Functions-biax.xlsb) has been added to the zip file along with the previous version, and can be downloaded from ULS Design Functions.zip, including full open source code.

Input and results for a wide rectangular section, subject to bi-axial bending and axial load, are shown in the screenshot below:

Biax-1

The direction of the applied moment is defined by MX and MY, then the Neutral Axis angle is adjusted so that the reaction force and moments are in equilibrium with the applied loads, by clicking the “Adjust NA Angle” button.

The concrete section is defined by XY coordinates of each corner, listed in a clockwise direction, and the reinforcement is defined in layers, by entering the coordinates of the start and end of each layer:  For each layer the number of bars and bar diameter are defined, together with the steel properties for the first layer, and any subsequent layer with different properties.  It is also possible to specify a prestress force for any layer.

Biax-2

The hexagonal section shown below demonstrates that for a symmetrical section the angle of the resultant moment axis is parallel to the Neutral Axis angle, as would be expected:
Biax-3

It is possible to define any complex shape, such as the precast Super-T bridge girder shown below:
Biax-T1

It is also possible to define shapes with internal voids, as shown below, by listing the corners of the void in the anti-clockwise direction.  In this case the line must be continuous from start to end, and the connection between the outer line and the void must be made with two separate lines, with a very small separation, so that separate lines do not overlap or cross at any point.  See the example in the download file for more details.
Biax-T2

More detailed output data is provided on the “UMom Out” sheet, in a similar format to the earlier version.
Biax-6

The analysis is carried out by two user defined functions (UDFs), UMom and UMomA.  UMom provides the detailed output shown above for a single set of applied loads.  UMomA returns any one of the available output values for a range of applied axial loads, and a single value or range of Neutral Axis directions.  Both functions return an array of values, and must be entered as an array function, as described at Using Array Functions and UDFs.

Use of the UMomA function allows the rapid generation of interaction diagrams, as shown in the screenshots below:
Biax-7

 

Biax-8

Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , , | 13 Comments