The Alglib library provides many valuable numerical analysis routines, but the VBA version is no longer actively maintained. The latest versions are available in C++, C#, and Python, of which the Python version (in conjunction with xlwings) provides the easiest interface with Excel.
An Alglib/Python based spreadsheet has previously been presented here. I have now updated this to the latest versions of xlwings and Alglib, added additional solver and linear algebra functions, and updated the Python code to make better use of the xlwings auto-import functionality, which generates the necessary VBA code at the touch of a button. The new spreadsheet can be downloaded from:
xlAlglib.zip
As usual, the download files include full open source code.
To install the necessary files:
- Install Python, either version 2 or 3. The Anaconda Python package will also install xlwings, but see below.
- Install or update to the latest version of xlwings (0.11.04 at the time of writing). If the automatic install delivers an earlier version, see here.
- Install Alglib.
- Copy the contents of the xlAlglib zip file to any convenient location.
- Any problems, please let me know.
The screenshots below show the functions available in the new version. Future posts will look in more detail at specific examples, and procedures for modifying the Python code.
List of functions:
Spline Functions:
Parametric Splines:
Rational Interpolation Function:
2D and 3D Interpolation:
Fit a spline to scattered data:
Polynomial fitting:
Non-Linear Fitting:
Fit Polynomial to scattered data:
Radial Basis Function Interpolation:
Levenberg-Marquardt optimisation:
Integration of Cubic Splines:
Differentiation of Cubic Splines:
Matrix algebra:
Newton Excel Bach, not (just) an Excel Blog 
Pingback: #Excel Super Links #126 – shared by David Hager | Excel For You
Pingback: Evaluating text and calling Python functions from Excel with xlwings | Newton Excel Bach, not (just) an Excel Blog
Pingback: Using Alglib least-squares solvers | Newton Excel Bach, not (just) an Excel Blog
Don’t know why,but in excel file it shows syntax error everywhere. I have python 3.7 32bit with alglib library, office 16 with xlwing 0.16. what more should I do? thanks.
LikeLike
after a long sleepless night WS is working with python. but not able to get the Coefficients in power form using output =2.please help. is there any function to get the future prediction from the poly?
LikeLike
I’m glad you got it working. For the benefit of others, could you give some details of what the problem was, and the solution.
I’m concentrating on pyxll, rather than xlwings at the moment, but if you give more details of which function you are using, and the problem with the output, I may be able to give some suggestions.
LikeLike
1) in xlalglib.py line no 1335 there is a parenthesis missing.
2) xlalglib.py and NLFuncs.py file should be placed in site-packages directory
LikeLike
1) In my master copy of the code that function is commented out, presumably because it wasn’t working.
2) That’s surprising. I have always kept my code in the same directory as the Excel file, and haven’t had a problem with that. The Alglib code I have just installed in accordance with the supplied documentation.
LikeLike
I am using the xl_polyfit function with output format set to out=2 to get the the power based coefficient. but it shows a single value for any order of polynomial.
LikeLike
Unfortunately I can’t follow this up at the moment as I don’t have xlwings installed on my current computer. Is it possible the function is returning the array as a row rather than a column? If you press F2 then F9 the full array will display in the edit box. If you confirm it is returning a single value rather than an array, it might be worth raising it on the Alglib forum.
LikeLike
Sorry man for bothering you. Actually i just learn to use Shift+Ctrl+Enter instead of enter. thanks
LikeLike
No problem – glad you got it sorted.
By the way, it looks like the “dynamic arrays” feature is now released on Office 365, which means that UDFS that return arrays will return the full array when you press just Enter.
How well this will work with spreadsheets set up for the old way remains to be seen.
LikeLike