The Python Scipy library currently has 84 statistics functions. I have now updated the xlwSciPy3 spreadsheet to access all of them directly from Excel. The new version can be downloaded from:
As usual, the download includes full open-source code. As well as Excel, the spreadsheet requires Python, Scipy, Numpy and xlwings to be installed. The Anaconda Python installation includes all the required files.
The Scipy statistics functions have a variable number of required and optional arguments. To allow all the functions to be called from a single interface function the following procedure is used:
- Both required and optional arguments are passed to VBA using the ParamArray argument, which will accept any number of separate arguments. Optional arguments are passed as a pair of separate arguments; the name followed by the value, which may be a single cell or a range.
- The VBA xl_Stats function reads the number of required arguments, and converts the required number to a single variant array. The remaining pairs of arguments are converted to another variant array, and the two arrays are passed, together with the function name, to Python, via the VBA function “xl_callfuncSt0”.
- In Python the optional argument array is converted to a dictionary, and together with the required argument array passed to the required stats function.
For the functions to work correctly from Excel it is essential that all the required arguments are provided, and that any optional arguments are passed as a name/value pair. To help identifying the correct input two VBA functions provide help:
The Get_Args function lists all argument names, together with default values for optional arguments:
These arguments can then be used in the xl_Stats function. The examples below call the binned_statistic function, using the function name in cell K11. The first example passes only the two required arguments: x (K19:O19) and values(K20:O20). In the second the optional “bins” argument is set to 2, and in the third both optional arguments are provided:
The full help documentation can be called from Excel using the Get_Doc function, as shown below. The output range for this function can be re-sized by selecting the top-left corner (cell U7) and pressing Ctrl-Alt-S:
Finally all 84 statistics functions are listed on the spreadsheet, with a brief description of the function output:
The xlw_SciPy3 spreadsheet also links to a wide range of other Scipy functions, as listed at: xlwSciPy update for Python 3.