Fast and accurate numerical integration routines based on the Tanh-Sinh method are available as Excel VBA based functions in the Tanh_Sinh Quadrature spreadsheet by Graeme Dennes. Graeme’s VBA code is based on open source Fortran code by Takuya Ooura, which I thought would form the basis of a useful exercise in the use of F2PY to link the compiled Fortran code to Python, and hence to Excel, via Excel-Python.

The procedure turned out not to be as straightforward as I had hoped, but is now complete, and the Quad_TS function has been added to the xlScipy spreadsheet, including full open source code:

## Download xlScipy.zip

The spreadsheet requires Excel plus an installed copy of Python, including Numpy and Scipy. All other required files are included in the download. Unzip all the contents of the download zip file to any convenient folder, and it should work.

This post will look at use of the new function, and the next will look at the detailed changes required to the Fortran code to compile successfully with F2PY.

The screenshots below shows use of the function with four different “function type” options (click on any image for a full-size view):

Option -1 allows the integration of a function entered as text on the spreadsheet, either a Python lambda function, or a named Python function, including the module name. Option 0 evaluates a hard-coded function (in this case tsc2.f1), and Option 1 (default) evaluates any named function in the tsc2 module.

Option -2 evaluates a text string entered in a worksheet cell, without the Python lambda notation. The default variable symbol is “x”, but any other symbol may be used as an optional argument, as illustrated above. The formula may include the Python math functions: log, exp, sin, cos, tan, or sqrt. Any other function must be preceded by the Python module name, as in Python code.

The integration process involves the generation of an array of weights, which defaults to a length of 11150 items. This is sufficient for the accurate integration of most functions, but for the function shown above, which oscillates rapidly about the x axis, a longer table is required. This may be specified as the optional fourth argument, allowing the accurate evaluation of the integral.

The function output shows the number of function evaluations, the execution time of the compiled code, and the total execution time, including overhead associated with the call of the function from Excel. For a single evaluation of a single integral the overhead swamps any performance gain, and the compiled code provides little or no benefit over the VBA function. With repeated calls from a Python routine however the saving would become significant, and even a single evaluation of an oscillating function can also show a huge performance improvement.

The screen shot below shows the evaluation of the F11 function ( exp(x) * sin( 2*x**3)) using the SciPy integration function. The compiled Quad_TS function has given a 58 times improvement in the evaluation time and a 20 times improvement in the overall execution time for a single evaluation.