I have recently been experimenting with Pyxll, which after a few initial hiccoughs is proving to be an easy and efficient way to connect Excel to Python based scientific and maths applications, such as linear algebra routines and ODE solvers. An overview of what the program will do is given in the Youtube link below:
The main thing to watch out for when installing is to make sure that all the software packages required are compatible with your version of Excel, and with each other. Note that to run Pyxll the version is controlled by the version of Excel (32 bit or 64 bit), regardless of the number of bits of your Windows version.
Finding the version information used to be easy; it was always under Help-About, but Microsoft has apparently decided that this information might frighten the uninformed, so they have decided to hide it:
- In Excel 2010 it is under File-Help
- In Excel 2013 it is under File-Account-About Excel
The second choice is the version of Python to install, either the latest 3.x or 2.7. After a few false starts I ended up installing 2.7, which seems to give a better chance of compatibility with the various add-on packages.
Finally there are numerous different options for the source of the maths and science routines. I ended up installing Anaconda (one of the packages recommended at the SciPy site); since it seemed the best option for including everything I needed, without being excessively complex.
Having downloaded and installed the Windows 32 bit, Release 2.7 version of Anaconda and Pyxll the next steps were:
- Add the PyXll path to the system search path; using (in Windows 7) Control Panel- System and Security- System- Advanced system settings- Environment Variables- System variables, then (finally) find and Edit the “Path” variable.
- Install pyxll as an Excel add-in using File- Options- Add-ins, select Excel Add-ins in the Manage drop-down box, click Go…, then Browse and find the PyXll add-in file
Having done that you should get a PyXll message when you start Excel. I then suggest looking at the Examples.xls file in the PyXll/Examples folder. Have a look at the introduction video linked above, and the Pyxll documentation.
Newton Excel Bach, not (just) an Excel Blog 
Does this allow access to any features of Excel 2007 and later? If you can point me at how to download the source for this I could figure that out. Is this an open source project, or do you just have to trust Bojan?
C++ seems anathema to you, but you are missing out on some the coolest things the smartest guys in the software world have been producing, See http://xllblog.wordpress.com for what I was up to today.
LikeLike
It doesn’t seem to be open source. I’m not sure what access to 2007+ features it provides, that’s not what I’m wanting it for, but I imagine it would be covered in the documentation.
I’m not sure why you think I don’t like C++; if you search this blog for C++ you’ll find 19 posts that refer to it, none of which say not to use it.
LikeLike
Christoph Gohlke has created a very comprehensive collection of WIN-binaries of PY-extensions directed at science and engineering. So you might want to check out this page http://www.lfd.uci.edu/~gohlke/pythonlibs/
LikeLiked by 1 person
There’s certainly a lot of it!
I don’t know if/when I’ll have time to explore it, but I’ll certainly try.
Thanks for the link.
LikeLike
Reblogged this on Sutoprise Avenue, A SutoCom Source.
LikeLike
Pingback: Python matrix functions in Excel, using Pyxll | Newton Excel Bach, not (just) an Excel Blog
Pingback: Python for VBA users – 1 | Newton Excel Bach, not (just) an Excel Blog
Pingback: Python for VBA users – 5; Using built in numpy functions | Newton Excel Bach, not (just) an Excel Blog
Pingback: Converting from global to local coordinates (and vice versa) | Newton Excel Bach, not (just) an Excel Blog
Pingback: The angle between two vectors, Python version | Newton Excel Bach, not (just) an Excel Blog
Pingback: Global to Local for plates (and three node beams) | Newton Excel Bach, not (just) an Excel Blog
Pingback: Dynamic sorting with Excel, VBA, and Python | Newton Excel Bach, not (just) an Excel Blog
I have some experience with using pyxll, and I agree to what has been mentioned in the article.
With PyXLL you can do everything in Excel using Python insteadof VBA.
If however all you want is creating Excel UDFs in Python, the much easier way is XLLoop!
(1) The impact on your Python source is the same as with PyXLL (namely very minor)
(2) XLLoop is free and open source
(3) XLLoop also supports a bunch of other languages (Java, R, Erlang, …)
(4) There is no annoying screen popup when Excel starts (as with the PyXLL unregistered version)
(5) Once you know what to do, XLLoop is simpler to install and to use than PyXLL
(6) If you want you can bundle your Python UDFs into EXE files and send them around to computers with no Python installation: everything the target computer needs is the XLLoop xll-file and your UDF exe-file.
LikeLike
Thanks for the suggestion Jorj.
I hadn’t heard of XLLoop, but it sounds similar to ExcelPython, which I have been concentrating on recently, and is also free and open source.
LikeLike
Hi DOUGAJ4,
I had a rather superficial look at ExcelPython, too.
What turned me off a bit was the (relative …) complexity of transforming stuff between Excel and Python formats – though of course it also allows for more direct control of what really is happening.
In contrast, XLLoop makes that very simple: it seems to have only two formats for single cells:
(1) if numeric, then it is a float (always)
(2) if not numeric, then it is a string.
For exchanging cell ranges with Excel, Python lists are being used:
(1) receiving / sending something like [a, b, c, …] means column / vertical data
(2) receiving / sending something like [[a, b, c, …]] means row / horizontal data
That’s it more or less.
If you e.g. get [[1,2,3],[4,5,6]] in Python, then you know it was a matrix with 2 rows and 3 columns (same meaning when sending from Python to Excel).
In your UDF you are also free to use whatever Python package – be it NumPy, SciPy and so on,
because it runs as a server in a process separate from Excel, having its own Python interpreter environment.
LikeLike