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.