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.
Newton Excel Bach, not (just) an Excel Blog 