The Python Numpy and Scipy libraries contain a huge number of maths and science related functions. Most of these are not directly available from Excel, but using the free ExcelPython package it is fairly easy to make the link, vastly increasing the functionality of Excel for maths, science and engineering applications.

I have collected interface functions to many of the (to me) most useful Scipy and Numpy functions in a Python module called xlScipy. These may be accessed from a free and open source spreadsheet: xlSciPy.zip.

The spreadsheet requires a copy of Python to be installed (including Numpy and Scipy), all other files, including ExcelPython, are included in the download package. Just unzip to a convenient folder, enable macros when you start up, and it should work. For those who have downloaded earlier versions of ExcelPython, this spreadsheet uses version 2.08, which is not compatible with earlier versions, so install in a different folder to the earlier versions.

The functions included in xlSciPy are listed on the first sheet (index):

There are ten functions for interpolation and fitting:
17 linear algebra functions:
12 functions for solving, creating and evaluating polynomials of any order:
All of the Scipy special functions (219 in all):
and 26 statistical functions:

Additional functions will be added as time permits.

The spreadsheet includes examples and brief documentation of each function; more detailed documentation can be found at Numpy Reference and Scipy Reference.

I think it is worth to have a look also to XLLOOP as a different way to interface Excel with (not only) Python.
The project was on stand-by for a couple of years, and it was limited to 32-bit only (currently I’m using 64 bits Excel), but recently I found on the web also a 64 bit implementation. Basically you have only one .xll file (that is the 64 bit which was missing) which acts as a client that understands basic xloper types, in and out.

On the other side you must have a server, written in the language you want, that responds to client’s requests. I have tried and elaborated the Python and R server examples, and both they work very well: you can define your functions, and make custom categories in Excel, including help for functions. The overall feeling is like to have your own xll, but of course with the access to the underlying system (e.g., in Python, numpy/scipy).

I’ve tried the client/server on the same machine, but in principle the server can be on a different one, so that the Python (or R) environment is not needed on the client: the only file to deploy is the xll.Very convenient!

i’ve attached a zip with xlloop and a (rough) python excel sample. Please note that the “xlloop.xll” file is the 64 bit version, if you have 32-bit Excel you have to rename as such the other provided file “xlloop-0.3.2.xll” (or write some code in the Excel’s auto_open, to register the appropriate version).

2) double-click the excel sample file. On the sheets there are some sample functions, which are mostly define in the (user) file xl.py. Practically there is no need of vba code, the auto_open macro is just in order to load automatically the xll .

As I said, the same can be done in R, which looks very interesting using the Revolutionary R Open (ROO) multithreaded version (now owned by Microsoft).

There is quite a lot on PyXll here. I switched to ExcelPython at the end of 2014 because it is free and open source, and it works well for UDF applications, which suits the way I work. PyXll is free for evaluation and non-commercial work, but for commercial applications each user needs a licenced copy. It has some advantages over ExcelPython; mainly:
– Data transfer time for large arrays is much better
– It runs entirely outside VBA
– Being a commercial package, documentation and support should be better (although support from the ExcelPython guy (Eric Reynolds) has also been very good).

I have only had a quick look at XLWings, but I understand that it now incorporates ExcelPython.