Scipy Functions with Excel and pyxll 9 – Special, distance, space and constants functions and FFT

Posted on 08/01/2024 by dougaj4

In the final post in this series I will look at linking Excel to the Scipy special, distance, space and constants functions and also the Fast-Fourier Transform (FFT) functions.

The py_Special-Dist and py_FFT spreadsheet, with associated Python code in PythonSpaceFuncs3.py and pyScipy3.py, are included in the download file. There have been significant changes since the previous post, so download the latest version at:

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 coded Scipy documentation can be displayed in Excel, using the get_docs function:

The Scipy special functions are called with the py_CallfuncS function. The spreadsheet lists the 220 available functions with a description, and is set up to generate simple examples:

The Distance worksheet shows alternative methods to call the distance functions:

The Space worksheet has examples of the py_Delaunay, py_ConvexHull and py_Voronoi functions:

The Scipy constants return a range of physical constants, including definition of units, accessed with py_GetConst:

The NIST CODATA constants can be accessed with the py_GetCodata function:

The py_FFT spreadsheet has an example from the Scipy documentation, using the functions py_fft, py_fftfreq, and py_ifft:

The FFT results are also plotted using Matplotlib with the py_plot function:

More information on using Matplotlib with Excel can be found at:

Posted in AlgLib, Excel, Link to Python, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , , , , , , , , , | Leave a comment

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