Evaluating text and calling Python functions from Excel with xlwings

The Alglib spreadsheet presented here includes functions to evaluate formulas entered as text on the spreadsheet, or to call specified Python functions.  I have now added a user defined function (UDF) that will perform either of these operations (xl_CallFuncD), and added a number of examples of use in different situations.  The updated spreadsheet (including full open-source code) can be downloaded from:

xlAlglib.zip

As before the following software is required:

  • Python, including Numpy and Scipy
  • xlwings ver 0.11.04 or later
  • Alglib Python version 3.11 or later

The Anaconda Python package includes Numpy, Scipy and xlwings (now updated to ver 0.11.04).  Alglib must be installed separately, and comes in free and commercial versions.

To deal with the need to pass any number of arguments, the function arguments are listed in a two (or three) column range; column 1 lists argument names, and column 2 either single values, or range addresses, entered as a text string.  This data is read into a VBA variant array, then converted to a Python dictionary.  Examples of the usage of xl_CallFuncD are shown in the screen-shots below:

A formula entered as text on the spreadsheet, with any number of listed variables.  Note that exponents may be entered using either VBA notation (^) or Python/Fortran notation (**):

Function results may be used as input values for another formula:

On-sheet formulas may include any built-in Python function, or functions from the imported Numpy, Scipy or Alglib modules:

Both on-sheet formulas and external function calls may have array arguments.  In this case the argument range must have three columns, with the third column specifying the number of dimensions for the array (1 or 2).  The range address may be entered as text, but to ensure that the address is updated if the range is moved the UDF RngAddress should be used:

xlCallFunc will call user defined Python functions from the xlAlgLib module without any dedicated VBA interface function:

… or user defined functions from another module:

… or functions from other Python libraries.  If the called function returns 2 or more array results the Out argument must be used to select the array to be returned.  The Python dictionary of arguments passes arrays as tuples, which will cause problems with some functions.  In this case enter True in the Convert argument, which will convert all passed tuples to lists, or lists of lists.

Note that argument names must be entered exactly, including correct case.  To list all arguments for any function, use the get_argnames UDF:

 

Advertisements
This entry was posted in AlgLib, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings and tagged , , , , , , , , , . Bookmark the permalink.

One Response to Evaluating text and calling Python functions from Excel with xlwings

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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s