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:

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

Column input and output and polyshort function

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)

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

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.

py_PolyfromRoots function with complex roots.

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.