Python for VBA users – 5; Using built in numpy functions

Previous Python Post

In previous posts in this series I have looked at translating VBA functions to solve quadratic and cubic equations, but the Python numpy library has a polyroots function that will solve polynomials of any degree, and will also handle polynomials with complex coefficients.

Full open source code for all the functions described in this post, as well as the py_Quadratic and py_Cubic functions, may be downloaded from py_polynomial.zip. The download file also includes the spreadsheet py_Polynomial.xlsb, including the examples illustrated below, and VBA based polynomial functions. Note that once the py_polynomial.py module has been installed the functions may be called from any Excel worksheet, including .xlsx files that have VBA disabled.

To install the Python functions:

  • Install Python with the Numpy add-in.
  • Install PyXll
  • Add: polynomial.py to the “modules =” section of the PyXll pyxll.cfg file (example included in the download file)

See: Installing Python, Scipy and Pyxll for more details.  Details of the Python code for the functions are given below, but all this is included in the polynomial.py module, and once that has been installed no further coding is required.  All the functions will be available in the same way as the built-in Excel functions.

To call the polyroots (and other polynomial functions) from any Python function the following line must be added to the Python code module:

import numpy.polynomial.polynomial as poly

The polyroots function can then be called with the following one-liner (two-liner, including the Excel decorator):

@xl_func("numpy_column CoeffA: numpy_column")
def py_Polyshort(CoeffA): return poly.polyroots(CoeffA)

The functionality can be considerably improved with a little more work however:

  • Add “Inc_power” and “Row_out” options, so that coefficients may be listed in either ascending or descending powers of x, and output arrays may be in either row or column format.
  • Specify “numpy_array” rather than “numpy_column” as the input and output data types, so that the data may be arranged in row or column format, and complex numbers may be input and output as pairs of values in adjacent cells.
  • Add “doc strings” that will appear in the function dialogue box, and “category” and “help_topic” items.
@xl_func("numpy_array CoeffA, bool Inc_power, bool Row_out: numpy_array", category="py-Maths", help_topic="http://docs.scipy.org/doc/numpy/reference/routines.polynomials.polynomial.html!0")
def py_Polyroots(CoeffA, Inc_power, Row_out):
    """
    Find the real and complex roots of a polynomial equation: a x^n + b x^(n-1) ... + y x + z = 0
    CoeffA: Row or column of function coefficients
    Inc_power: Optional, default False = coefficents listed in order of descending powers of x
    Row_out: Optional, default False = roots output in two columns (real and imaginary parts of each root)
    """

The screen shot below shows the “Insert Function” Dialogue for the py_Polyroots function, showing the function description, and help for each function argument, as defined in the Python doc string:
polyroots0

To deal with the Inc_power and Row_out options, and to deal with output of complex numbers as a pair of floats, the following operations are then required:

  • Check the orientation of the input array of coefficients (CoeffA), and transpose to column format if necessary.
  • Create an output array, with two columns x (number of roots + 1)
  • The numpy polyroots function requires a 1D array with coefficients listed in ascending powers of x.  Extract the first column of CoeffA, and if Inc_power is False reverse the order of the coefficients.  Note that this operation, including reversing the order of the coefficients, can be accomplished with a single Python “list comprehension”:
    CoeffA[::-1,0]
    or without the reversal of the order:
    CoeffA[:,0]
  • Convert complex results to a pair of floats, and count the number of complex roots.
  • Write the number of real and complex roots to the end of the output array.
  • Return the results as a row or column, depending on the value of Row_out.

The final code is shown below, followed by example output for a fifth order polynomial, with different arrangements of input and output.

@xl_func("numpy_array CoeffA, bool Inc_power, bool Row_out: numpy_array", category="py-Maths", help_topic="http://docs.scipy.org/doc/numpy/reference/routines.polynomials.polynomial.html!0")
def py_Polyroots(CoeffA, Inc_power, Row_out):
    """
    Find the real and complex roots of a polynomial equation: a x^n + b x^(n-1) ... + y x + z = 0
    CoeffA: Row or column of function coefficients
    Inc_power: Optional, default False = coefficents listed in order of descending powers of x
    Row_out: Optional, default False = roots output in two columns (real and imaginary parts of each root)
    """
# Transpose CoeffA to column format if necessary
    if CoeffA.shape[0] == 1: CoeffA = transpose(CoeffA)
# Create output array; two columns x (number of roots + 1)
    nroots = CoeffA.shape[0]-1
    resa = zeros((nroots+1,2))
# polyroots requires a 1D array with coefficients listed in ascending powers of x
# Extract the first column of CoeffA, and if Inc_power is False reverse the order of the coefficients
    if Inc_power == False:
        res = poly.polyroots(CoeffA[::-1,0])
    else:
        res = poly.polyroots(CoeffA[:,0])
# Convert complex results to a pair of floats, and count the number of complex roots
    numi = 0
    for i in range(0,nroots):
        resa[i,0] = res[i].real
        resa[i,1] = res[i].imag
        if resa[i,1] != 0: numi = numi+1
# Write the number of real and complex roots to the end of resa
    i = i+1
    resa[i,0] = nroots-numi
    resa[i,1] = numi
# Return the results as a row or column, depending on the value of Row_out
    if Row_out == False :
        return resa
    return transpose(resa)

Row input and column output

polyroots1

Column input and output and polyshort function

polyroots2a

Results for a 60th order polynomial.  The results in columns C and D are from the VBA rpolyjt() function.  The results from the two functions are sorted in different orders, but are in good agreement (see the spreadsheet for full results list)

polyroots3

The py_PolyrootsC function will accept complex coefficients of x.  The function converts each pair of values to a Python complex number, then calls the py_Polyroots function

polyroots4

The py_PolyfromRoots function generates a monic polynomial from the input roots, which may be real or complex.  The example illustrated shows the use of the Inc_power and Row_out options to generate output with ascending powers of x in row format.

polyroots5

py_PolyfromRoots function with complex roots.

polyroots6

The results generated by py_Polyroots and py_PolyrootsC have been checked using the py_Polyval function. This evaluates a polynomial defined by a series of coefficients for a specified value of x. X may be a real value defined by a single cell, or a complex value defined by two adjacent cells. As for the other functions the coefficients may be listed in descending powers of x (default), or ascending order.

polyroots7

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

5 Responses to Python for VBA users – 5; Using built in numpy functions

  1. Pingback: Using Pysparse with Excel | Newton Excel Bach, not (just) an Excel Blog

  2. kalx says:

    I went to a Meetup at DataNitro tonight. They have a commercial product for plugging python into Excel. It seems to be quite well done, but it costs money. Their latest product hooks up Excel to big data.
    If “data” is a vague term, “big data” is even vaguer. How can one or two words even describe such a complicated issue?

    Like

    • dougaj4 says:

      Yes, I’ve noticed DataNitro. The main site says it is commercial with a 30 day trial, but a reply on their forum says you can request a free licence for non-commercial use, which would make it similar to Pyxll. From a quick look it seems very similar to Pyxll in features, but I haven’t compared them in any detail. There is a comment on the Pyxll forum that Pyxll is more comprehensive, but that my just be user bias.

      There are a couple of free open-source options available, which I’ll try and have a look at, if I get time.

      Like

  3. Pingback: Download update – Python downloads | Newton Excel Bach, not (just) an Excel Blog

  4. Pingback: Solving Quadratic, Cubic, Quartic and higher order equations; examples | 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