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.
Newton Excel Bach, not (just) an Excel Blog 
it seems nice and extensive, unfortunately the zip seems to miss a file PythonSpaceFuncs.py which is required to run
LikeLike
Thanks for letting me know.
I’m still working on the Space functions, but it’s not ready for release yet. I have uploaded a new version of xlSciPy.py that has the import PythonSpaceFuncs line commented out.
You can download that, or just comment out the import line yourself in your current download.
LikeLike
Pingback: xlSciPy 1.01 | Newton Excel Bach, not (just) an Excel Blog
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!
If interested, let me know how to provide the Python (or R) example.
LikeLike
Thanks for the suggestion Maurizio. I’m certainly interested at having a look (although finding the time will be a problem). You can e-mail me at gmail (dougaj4).
LikeLike
Hi Doug,
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).
1) start the server with the provided batch file, wait till the message “Starting Function Server on port 5454…”
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).
Best regards, maurizio P.S.: dougaj4@gmail.com not existing
Date: Sun, 27 Dec 2015 11:16:47 +0000 To: maurizionardo@hotmail.it
LikeLike
Pingback: xlSciPy – Python SciPy for Excel; Update with new functions | Newton Excel Bach, not (just) an Excel Blog
Anybody tried PyXLL or xlwings?
LikeLike
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.
LikeLike
Hello !!! I get following error in all formulae in xlScipy.xlsb file :-
“TabError: inconsistent use of tabs and spaces in indentation
^
A[2,i] = abs(y[i])*2/rows
File “”…\xlScipy\xlScipy.py””, line 219
File “”””, line 1, in
exec(“”import “” + module + “” as the_module””, vars)
File “”…\xlScipy\xlpython\xlpyserver.py””, line 107, in Module
return func(*args)
File “”…\AppData\Local\Programs\Python\Python35-32\lib\site-packages\win32com\server\policy.py””, line 586, in _invokeex_
return S_OK, -1, self._invokeex_(dispid, lcid, wFlags, args, None, None)
File “”…\AppData\Local\Programs\Python\Python35-32\lib\site-packages\win32com\server\policy.py””, line 283, in _invoke_
return self._invoke_(dispid, lcid, wFlags, args)
File “”…\AppData\Local\Programs\Python\Python35-32\lib\site-packages\win32com\server\policy.py””, line 278, in _Invoke_”
Please help.
LikeLike
Thanks for letting me know.
This seems to be a “feature” of Python and/or the Notepad++ editor. Why it does not cause a problem for me. I have no idea.
Anyway, I have gone through all the .py files called by xlSciPy, and replaced all tabs with four spaces.
The new files can be downloaded from:
http://interactiveds.com.au/software/xlScipy.zip
Please let me know if the new version works or not
LikeLike