Scipy Functions with Excel and pyxll 8 – Statistics

Posted on 05/01/2024 by dougaj4

This post looks at linking Excel to the Scipy and Pandas statistics functions using pyxll.

The py_Stats spreadsheet, with associated Python code in PythonStatsFuncs3.py and pyScipy3.py, are included in the download file:

py_SciPy.zip

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_Stats spreadsheet is still under development, but links to most of the Scipy statistics functions. As shown below, the functions may either be called with specific Excel UDF’s, or using the general purpose py_Stats function:

For background information on passing “callable” arguments from Excel to Python see:

Python callable arguments from Excel

Links to the Scipy on-line documentation have not yet been implemented for the stats functions, but a text version of the documentation for any function can be displayed in the spreadsheet:

The “Index” input links to a full list of the available Scipy functions, using the get_funcs() function:

A search string can be added to narrow down the list of functions:

The specific Excel functions available are also listed:

There is also a list of functions not yet implemented in:

Finally the Pandas statistics functions are also available:

Posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, Python Pandas, PyXLL, UDFs | Tagged , , , , , , , , | 1 Comment

Scipy Functions with Excel and pyxll 7 – Linear Algebra

Posted on 01/01/2024 by dougaj4

This post looks at linking Excel to the Scipy linear algebra functions using pyxll, and also using the PyPardiso solver. Previous posts on this topic include:

The pyLinAlgfuncs3 spreadsheet, with associated Python code in pyLinalgfuncs3.py and pyScipy3.py, are included in the download file:

py_SciPy.zip

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 pyLinAlgfuncs3 spreadsheet includes links to many different solver functions and associated utility functions, but for most purposes the py_Solve (for dense matrices) or py_SpSolve (for sparse matrices) will be most efficient. Note that the py_SpSolve function calls the fast PyPardiso sparse solver if it is installed, or the Scipy spsolve function if not.

Links to the Scipy on-line help are available for most of the functions by opening the function dialog box, and clicking on “Help on this function” in the bottom left corner:

This links to the Scipy help for the associated Scipy fumction:

Input of the matrix to be solved may be in alternative formats:

  • A complete square matrix on the spreadsheet.
  • A linked list in COO format on the spreadsheet
  • A pyxll cache object. Note that this option allows links to Python generated arrays that would be too big to transfer to a spreadsheet, and will also be much faster for very large matrices.

The SpSolveit sheets has functions linking to 10 alternative sparse iterative solvers. 

See Speed of Scipy Linear Algebra Solvers for more information on the relative speed of the solvers for typical structural engineering applications.

The LuSolve sheet has functions for factorisation and solving using LU factorisation.

ChoSolve has Cholesky and banded solvers:

Python_LU has functions demonstrating background to the LU solver approach. See LU decomposition with python and scipy for more details.

Finally the Misc sheet has examples of various matrix related functions:

Posted in Arrays, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Maths, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , , , , , | 1 Comment

Scipy Functions with Excel and pyxll 6 – Integration

Posted on 29/12/2023 by dougaj4

The py_Integrate spreadsheet, with associated Python code in pyScipy3.py, are included in the download file:

py_SciPy.zip

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:

Posted in Excel, Link to Python, Maths, Newton, Numerical integration, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , , , , | Leave a comment

Scipy Functions with Excel and pyxll 5 – Evaluate with Units

Posted on 27/12/2023 by dougaj4

The Scipy series continues with the py_EvalU spreadsheet included in the download file:

py_SciPy.zip

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:

Posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , , , , , , , | 2 Comments

Scipy Functions with Excel and pyxll 4 – Solvers 2

Posted on 19/12/2023 by dougaj4

Following the previous post I will continue to look at the py_Solvers spreadsheet included in the download file:

py_SciPy.zip

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 the py_SolveFS function for solving multi-variable equations using the Python root function.

Documentation is given on the spreadsheet:

The first example is a simple problem from the Scipy documentation:

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

Posted in Concrete, Excel, Link to Python, Maths, Newton, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , , , | 1 Comment