The ExcelPython add-in, offering easy access to Python code from Excel VBA, has had a major update to Release 2. The new version provides new features and simpler installation, but has some incompatibilities with code written for the previous version, so the Excel Spline-Matrix spreadsheet has been updated to work with the new release.
Those wishing to generate their own ExcelPython applications can download the latest release from the ExcelPython github site. If you just want to use the spline-matrix spreadsheet all the necessary ExcelPython files are included in the download; see below for installation details.
As well as the included ExcelPython files the spreadsheet requires:
- Python version 2.6 or later, or any version 3, with PYWin32 installed. The Alglib code does not require Numpy, but the Python interface code does, so this must be installed. I can recommend the Anaconda package, which is comprehensive with simple installation.
- The Alglib Python package, which can be downloaded here, with installation instructions here. The Python for Windows package comes with full compiled code, and has very simple installation (but install Python first).
- After installing Python then Alglib, simply copy the contents of the Spline-Matrix zip file to any convenient folder.
Following this procedure the Spline-Matrix spreadsheet should work with 32 bit Excel and Python with no changes of settings, other than enabling macros at start-up, if your security settings require this. The 64 bit version of Excel Python is still under development, and I have not checked the spreadsheet with 64 bit Excel. Any problems, please let me know. Some examples of the included functions are shown below. For a full list of the available functions, see the index sheet.
1. You need to add PtrSafe in front of your Declare statements for it to work on 64-bit machines
2. Despite your comment, numpy is required and it’s obvious just by looking at your imports and your usage of ‘np’
3. You didn’t include the NLFuncs.py file
4. Also had to delete AL_StatsFuncs and AL_SpecialFuncs for it to work
Might be more but I can’t remember now.. but I do great appreciate the work you’ve done, it’s just a little frustrating getting everything to work.
Thanks John, I really appreciate you taking the time to work through this and provide the feedback.
I’m on holiday at the moment, but some brief comments:
On PtrSafe, I note that Eric Reynolds, who is responsible for the ExcelPython code has recently posted an update addressing this issue.
On the need for Numpy: yes you are quite right. The Alglib code does not require numpy, but I have used it in my linking code, so you do need it for the spreadsheet to work.
On AL_StatsFuncs and AL_SpecialFuncs: these import statements were copied across from another module; yes they should be deleted.
I’ll do a re-write as soon as I can.
John – I have now updated the download files to fix your points 3 and 4; they should run on any machine with 32 bit Excel, Python (including Numpy and Scipy) and Alglib installed.
There is a new release of ExcelPython (2.06) on the github site, but it is marked “pre-release” so I have stuck with 2.05 at the moment.
I will update the wording of the post in the next few days.
Thanks again for the feedback.
Pingback: ExcelPython2, Alglib and Spline-Matrix Rel 2.03 | Newton Excel Bach, not (just) an Excel Blog