Evaluating text and Integration with Python and ALGLIB

Following the previous post on evaluating mathematical functions entered as text on the spreadsheet, I have now written a spreadsheet with similar functionality using Python functions, and the Excel-Python add-in.  Also included are integration functions using the SciPy Python library, and the ALGLIB library, with the Python interface.  The new spreadsheet, including full open source VBA and Python code, can be downloaded from Eval-PyInt.zip.

All the functions require the installation of  ExcelPython, which is free and open-source, and Python, including the Scipy library.

The ALGLIB based integration functions also require the installation of the ALGLIB library, which comes in a free version, and a higher performance commercial version.

Use of the xl_Eval function is shown in the screenshot below, together with the VBA version:
The main benefit of the Python based functions is that they allow direct access to Python, Numpy and Scipy functions, without coding: PyInt2
The new xl_EvalR function allows the evaluation of Python functions requiring array input:
In the examples above, the first applies the Python max() function, which works in the same way as the Excel equivalent.  In the second, the array is converted to a Numpy array, which is converted to absolute values, then the .max() method is applied, returning the absolute maximum value.  In the third example the argmax() method is applied to the absolute values of the array, returning the offset of the maximum absolute value, which is then applied to the original array, to return the signed value of the maximum absolute value.

Three Numpy based integration functions are provided, which will integrate a function entered as text on the spreadsheet (xl_IntString), integrate a specified Python function (xl_IntCallFunc), or integrate a hard coded Python function:
The three ALGLIB based functions work in the same way:
The ALGLIB functions are significantly slower than the Python based equivalents, however they were found to perform better with very difficult functions, such as that shown below:
For this function the Python functions failed to provide an accurate result:
whereas  the ALGLIB function  continued to subdivide the function until an accurate result was achieved;

This entry was posted in AlgLib, Arrays, Excel, Link to Python, Maths, Newton, Numerical integration, NumPy and SciPy, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

One Response to Evaluating text and Integration with Python and ALGLIB

  1. Great overview. I’m using python and it has been very useful. Thanks for sharing.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.