The spreadsheet and associated Python packages have had significant edits since the first post in this series, so please download the latest file.
Details of the required pyxll package (including download, free trial, and full documentation) can be found at:pyxll
For those installing a new copy of pyxll, a 10% discount on the first year’s fees is available using the coupon code “NEWTONEXCELBACH10”.
The py_Integrate spreadsheet links to the Scipy quadrature functions. In addition, a Python version of the VBA Quad_Tanh_Sinh function by Graham Dennes has been added. See Tanh-Sinh Quadrature v. 5.03 for details and download of the VBA version.
Three functions allow for integration of functions with one variable:
The Tanh-Sinh version works with either a string on the spreadsheet or a Python function:
The Scipy and Tanh-Sinh functions return near identical resuts:
For typical functions the Tanh-Sinh function is a little slower than the Scipy functions, since it is written entirely in uncompiled code, but for functions requiring a large number of iterations it can be faster:
The spreadsheet includes examples with trigonometric functions, with results accurate to machine precision:
There are also functions linking to the Scipy functions for double, triple, and multiple integration, with examples of each:
The spreadsheet and associated Python packages have had significant edits since the first post in this series, so please download the latest file.
Details of the required pyxll package (including download, free trial, and full documentation) can be found at:pyxll
For those installing a new copy of pyxll, a 10% discount on the first year’s fees is available using the coupon code “NEWTONEXCELBACH10”.
The py_EvalU spreadsheet provides functions for the evaluation of functions entered as text, conversion and evaluation of units, and high precision maths functions. In addition to Numpy and Scipy, the following Python packages should be installed:
Sympy
Pint
MPMath
Matplotlib
Plotly
The Examples sheet has various functions for working with different units:
py_ConvertA converts column arrays between specified unit systems:
py_ToSIBase and py_FromSIBase convert between base SI units and a specified unit system:
The py_ConvertTab converts between unit systems in tabular format:
List_units lists all unit names in a specified system:
The functions below convert between metric and feet and inches in fraction format:
The py_EvalU function evaluates a text string, adjusting for the specified input and output units:
The Latex sheet displays the Plot_Math function, which converts a text string to Latex format, then uses Matplotlib to convert this to a graphic image. The original text string may be evaluated with the py_Eval or py_EvalU functions, or optionally Plot_Math may return the evaluated result (Cell C27 in the image below):
The Implied Units sheet shows two options for unit-aware evaluation of equations including factors with implied units:
The mpmath sheet shows examples of high precision calculations using the mp_Eval function. In the example below the sum of 3 cubes of 17 digit integers is correctly evaluated to 42, whereas floating point calculations return a value of 1.09785E+36.
Finally the Pint_Quant sheet shows the creation of Pint Quantity objects with specified units, and the creation of new units:
This is followed by a more useful example, looking at the elastic design of a reinforced concrete section. (click on any image for full-screen view):
This is an extension of the second example for the py_Brent function in the previous post. In that case the area of the tension reinforcement was specified, and the depth of the section neutral axis was found for a specified bending moment and axial load. In today’s example only the number of tension bars is specified and the required outputs are the diameter of the tension bars, the strain at the compression face, and the depth of the neutral axis, for a specified bending moment, axial load and tensile stress in the tension reinforcement.
Note that the same results can be generated using the Excel solver:
To solve this problem with the Excel solver the square of the deviation of each return value from the target values is calculated on the spreadsheet, then the sum of the squares is minimised, The Excel solver solution is identical to the Python results, but it has several disadvantages:
It is slower
It takes longer to set up
It is less flexible and provides less control over solution methods
The solver results need to be recalculated every time an input is changed
The final example is a more complex problem from the Scipy docs (see the docs for full details):
Details of the required pyxll package (including download, free trial, and full documentation) can be found at:pyxll
For those installing a new copy of pyxll, a 10% discount on the first year’s fees is available using the coupon code “NEWTONEXCELBACH10”.
This post will look at functions for solving equations with one variable, and the next one multi-variable equations.
The py_Brent function finds a root to a function of one variable within a specified range using Brent’s Method:
The function may either be entered as text on the spreadsheet using the Python lambda format, or may be the name of any accessible Python function. In the example above func2 and func3 are included in the pyScipy3 module:
The py_BrentA function allows two of the function arguments to be transferred as row and column arrays, with the result returned as a table:
In the first example the function has one unknown, T, two variable arguments, epsilon and alpha, and five fixed arguments, G_1 to G_5.
The 5 fixed arguments have been converted to numerical values using the py_Eval function so that the unknown and the two variable arguments are the only arguments required for the py_BrentA function:
The second example finds the depth of the Neutral Axis of a reinforced concrete section with elastic properties with two variable arguments (applied axial force and bending moment) and 7 fixed arguments
For the on-sheet lambda function the fixed arguments must be converted to numerical values, again using py_Eval, but for the Python function the values can be passed to the function as an array:
def FindDNA(x, Ax, Mom, addargs):
A = addargs[0]
B = addargs[1]
C = addargs[2]
D = addargs[3]
E = addargs[4]
F = addargs[5]
G = addargs[6]
ecc = Mom*1000/Ax
return A*x**3+(B-C*ecc)*x**2+(D-ecc*E)*x+Mom*1000/Ax*F-G
On the spreadsheet either approach may be used by entering 1 or 2 in the “Func Type” cell (Y74).
The py_MinimizeFS function has similar functionality to py_Brent, but uses the Python minimize_scalar function, that allows alternative solver methods:
The py_MinimizeF function provides unconstrained or constrained minimization of scalar functions of one or more variables using the Python minimize function, using 11 alternative solver methods.
The py_FindRoots function returns a root of a vector function.
Examples in the spreadsheet include the root finding example used for the py_Brent function, and more complex examples from the Scipy documentation:
Details of the required pyxll package (including download, free trial, and full documentation) can be found at: pyxll
For those installing a new copy of pyxll, a 10% discount on the first year’s fees is available using the coupon code “NEWTONEXCELBACH10”.
The py_Interpolate spreadsheet has 10 user defined functions linking to the Scipy spline interpolation functions. The Scipy help on each function is easily accessed from within Excel:
Open the “Insert Function” dialog, then click on “Help on this function” in the bottom left corner.
That takes you straight to the Scipy on-line help:
The examples in the spreadsheet plot the same data using each of the 9 functions. The screenshots below compare the output from the py_CubicSpline function, with “natural” end conditions, with the other nine functions:
Convenience function for polynomial interpolation.
Constructs a polynomial that passes through a given set of points, then evaluates the polynomial. For reasons of numerical stability, this function does not compute the coefficients of the polynomial.
This function uses a “barycentric interpolation” method that treats the problem as a special case of rational function interpolation. This algorithm is quite stable, numerically, but even in a world of exact computation, unless the x coordinates are chosen very carefully – Chebyshev zeros (e.g., cos(i*pi/n)) are a good choice – polynomial interpolation itself is a very ill-conditioned process due to the Runge phenomenon.
class scipy.interpolate.UnivariateSpline(x, y, w=None, bbox=[None, None], k=3, s=None, ext=0, check_finite=False)
1-D smoothing spline fit to a given set of data points.
Fits a spline y = spl(x) of degree k to the provided x, y data. s specifies the number of knots by specifying a smoothing condition.
py_Splev
scipy.interpolate.splev
scipy.interpolate.splev(x, tck, der=0, ext=0)
Evaluate a B-spline or its derivatives.
Given the knots and coefficients of a B-spline representation, evaluate the value of the smoothing polynomial and its derivatives. This is a wrapper around the FORTRAN routines splev and splder of FITPACK.
py_CubicSplineS
scipy.interpolate.CubicSpline
class scipy.interpolate.CubicSpline(x, y, axis=0, bc_type=’not-a-knot’, extrapolate=None)
Cubic spline data interpolator.
Interpolate data with a piecewise cubic polynomial which is twice continuously differentiable [1]. The result is represented as a PPoly instance with breakpoints matching the given data.
py_Interp1d(
scipy.interpolate.interp1d
class scipy.interpolate.interp1d(x, y, kind=’linear’, axis=-1, copy=True, bounds_error=None, fill_value=nan, assume_sorted=False)
Interpolate a 1-D function.
Legacy
This class is considered legacy and will no longer receive updates. This could also mean it will be removed in future SciPy versions.
py_Pchip
scipy.interpolate.PchipInterpolator
class scipy.interpolate.PchipInterpolator(x, y, axis=0, extrapolate=None)
PCHIP 1-D monotonic cubic interpolation.
x and y are arrays of values used to approximate some function f, with y = f(x). The interpolant uses monotonic cubic splines to find the value of new points. (PCHIP stands for Piecewise Cubic Hermite Interpolating Polynomial).
Newton Excel Bach, not (just) an Excel Blog 