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:
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;
Great overview. I’m using python and it has been very useful. Thanks for sharing.