The xlSciPy spreadsheet, previously described here, has been updated with new functions for integration, finding equation roots and maxima and minima, solving systems of non-linear equations, and evaluation of equations entered as text. The new version (including full open-source VBA and Python code) may be downloaded from:
As before, the spreadsheet requires Python, SciPy, and Numpy to be installed, but all other files (including the required ExcelPython files) are included in the download zip file. Unzip the download file to its own folder, and enable macros when Excel is started, and it should work.
All the available functions (about 350) are listed on the Index tab. The new functions introduced in this versions are illustrated in the screenshots below:
The integration functions allow the numerical integration of a function entered as text, a named Python function, or a Python function hard coded in the “xl_IntFunc1” function:
The first function below oscillates rapidly about the x axis, and requires an increased number of iterations to converge to an accurate result. The second has converged to machine precision in a much shorter time.
The xl_Brent function finds a root of any function of one variable (between specified limits), using Brent’s method.
The examples below show the evaluation of the integral of a function entered as text, the same function with additional fixed parameters, and Python versions of the two functions.
The xl_MinimizeFS function calls the SciPy minimize_scalar function, which will find the minimum of a Python function of one variable, or an equivalent lambda function entered as text.
The xl_MinimizeF function calls the Python minimize function to find the unconstrained or constrained minimum of a scalar function of one or more variables, using one of 11 alternative methods.
The examples below show the same root finding problem as in previous examples, followed by an example from the tutorial in the SciPy Manual, finding the minimum of the Rosenbrock Function, using different methods.
For constrained minimization, as shown below, the constraints may either be specified with Python functions or text Lambda functions. Change the “Constraint Type” to 2 on the spreadsheet to see the lambda function constraint input.
The xl_SolveF function solves a system of non-linear equations with an equal number of unknowns and objective values, using one of 10 alternative methods.
The example below shows a system of non-linear equations, from the SciPy Manual reference section.
The example below analyses a reinforced concrete section to find depth of neutral axis (DNA), compression face strain (epsc), and tension bar diameter for specified axial force, bending moment, and tensile steel stress values. A VBA routine to solve the same problem was presented in: https://newtonexcelbach.wordpress.com/2015/10/20/solving-non-linear-equations-with-two-or-more-unknowns-5/
The example below, from the SciPy Manual tutorial, illustrates the use of Krylov’s Method (Method 9), with and without the use of pre-conditioners.
The results shown in the SciPy manual show faster solution time with the use of a preconditioner, but my results for this example were slightly slower with the preconditioner.