Excel to Alglib via xlwings

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:

  1. Install Python, either version 2 or 3.  The Anaconda Python package will also install xlwings, but see below.
  2. 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.
  3. Install Alglib.
  4. Copy the contents of the xlAlglib zip file to any convenient location.
  5. 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:

This entry was posted in AlgLib, Curve fitting, Excel, Link to Python, Maths, Newton, Numerical integration, UDFs, VBA, xlwings and tagged , , , , , , , , , . Bookmark the permalink.

12 Responses to Excel to Alglib via xlwings

  1. Pingback: #Excel Super Links #126 – shared by David Hager | Excel For You

  2. Pingback: Evaluating text and calling Python functions from Excel with xlwings | Newton Excel Bach, not (just) an Excel Blog

  3. Pingback: Using Alglib least-squares solvers | Newton Excel Bach, not (just) an Excel Blog

  4. Roulior Halder says:

    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.

    Like

  5. Roulior Halder says:

    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?

    Like

    • dougaj4 says:

      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.

      Like

  6. Roulior Halder says:

    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

    Like

    • dougaj4 says:

      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.

      Like

  7. Roulior Halder says:

    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.

    Like

    • dougaj4 says:

      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.

      Like

      • Roulior Halder says:

        Sorry man for bothering you. Actually i just learn to use Shift+Ctrl+Enter instead of enter. thanks

        Like

        • dougaj4 says:

          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.

          Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.