Newton Excel Bach, not (just) an Excel Blog

Xlwings is another free and open source package allowing communication between Excel and Python.  It now incorporates ExcelPython, and is included in the Anaconda Python package, so will support my ExcelPython based spreadsheets after installation of xlwings using:
conda install xlwings

See http://docs.xlwings.org/installation.html for more details.

In this post I will look at using Matplotlib to plot graphs in Excel.  A free download of a spreadsheet with all examples and all VBA and Python code can be found at:

xlMatPlot.zip

The first example comes from the xlwings sample files, mpl.xlsm and mpl.py :

The VBA code couldn’t be simpler:

Sub Streamplot()
    RunPython ("import xlMatPlot; xlMatPlot.main()")
End Sub

The python code (that generates the graph) is also fairly straightforward (note: code updated for xlwings 0.9 and later, 7 May 2017):

import numpy as np
import matplotlib.pyplot as plt
import xlwings as xw
try:
    import seaborn
except ImportError:
    pass

def get_figure(const):
    # Based on: http://matplotlib.org/users/screenshots.html#streamplot
    Y, X = np.mgrid[-3:3:100j, -3:3:100j]
    U = -1 + const * X**2 + Y
    V = 1 - const * X - Y**2

    fig, ax = plt.subplots(figsize=(6, 4))
    strm = ax.streamplot(X, Y, U, V, color=U, linewidth=2, cmap=plt.cm.autumn)
    fig.colorbar(strm.lines)
    return fig


def main():
    # Create a reference to the calling Excel Workbook
    wb = xw.Workbook.caller()

    # Get the constant from Excel
    const = xw.Range('B1').value

    # Get the figure and show it in Excel
    fig = get_figure(const)
    sht = xw.Book.caller().sheets[0]
    sht.pictures.add(fig, name='MyStreamplot', update = True) 

The get_figure function generates a matplotlib graph, using the const value passed from the main function. The main function creates a reference to the calling Excel Workbook, then assigns the graph to the named Excel graphic object ‘MyStreamplot’. If this already exists, it will be updated to display the newly created graphic. If not, it will be created.

I have modified the xlwings code to display a range of different graphs, based on examples taken from http://kestrel.nmt.edu/~raymond/software/python_notes/paper004.html

The VBA code has been modified to update the graph whenever there is a change to the “Samples” worksheet:

The main function in the xlMatPlot Python module now reads the required figure number from the Excel named range ‘fig_no’, and the constant (for Figure 1) from the range ‘n’.  One of 10 functions are then called, depending on the value in ‘fig_no’.

Fig_no 2 generates a plot of sine and cos functions, together with some simple formatting and addition of text:

...
def get_figure2():
    x =     linspace(0., 10., 200)
    y =     sin(x) 
    y2 =     cos(x)
    fig = plt.figure()   # Make a new figure
    
    line1=plt.plot(x, y)
    line2=plt.plot(x, y2, 'b')
    plt.setp(line1, color='r', linewidth=3.0)
    plt.setp(line2, color='b', linewidth=2.)
    plt.axis([0,10,-1.5,1.2])
    xl = plt.xlabel('horizontal axis')
    yl = plt.ylabel('vertical axis')
    ttl = plt.title('sine function')
    txt = plt.text(0,1.3,'here is some text')
    ann = plt.annotate('a point on curve',xy=(4.7,-1),xytext=(3,-1.3), arrowprops=dict(arrowstyle='->'))

    plt.grid(True)
    return fig 
...

Fig_no 3 plots four functions, assigning a different line style to each:

...
def get_figure3():
    fig = plt.figure()
    x = arange(0.,10,0.1)
    a = cos(x)
    b = sin(x)
    c = exp(x/10)
    d = exp(-x/10)
    la = plt.plot(x,a,'b-',label='cosine')
    lb = plt.plot(x,b,'r--',label='sine')
    lc = plt.plot(x,c,'gx',label='exp(+x)')
    ld = plt.plot(x,d,'y-', linewidth = 5,label='exp(-x)')
    ll = plt.legend(loc='upper left')
    lx = plt.xlabel('xaxis')
    ly = plt.ylabel('yaxis')
    return fig 
...

In Fig_no 4 there are four sub-plots generated in the one image:

...
def get_figure4():
    fg = plt.figure(figsize=(10,8))
    adj = plt.subplots_adjust(hspace=0.4,wspace=0.4)

    sp = plt.subplot(2,2,1)
    x = linspace(0,10,101)
    y = exp(x)
    l1 = plt.semilogy(x,y,color='m',linewidth=2)
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")
    tl = plt.title("y = exp(x)")

    sp = plt.subplot(2,2,2)
    y = x**-1.67
    l1 = plt.loglog(x,y)
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")
    tl = plt.title("y = x$^{-5/3}$")

    sp = plt.subplot(2,2,3)
    x = arange(1001)
    y = mod(x,2.87)
    l1 = plt.hist(y,color='r',rwidth = 0.8)
    lx = plt.xlabel("y")
    ly = plt.ylabel("num(y)")
    tl = plt.title("y = mod(arange(1001),2.87)")

    sp = plt.subplot(2,2,4)
    l1 = plt.hist(y,bins=25,normed=True,cumulative=True,orientation='horizontal')
    lx = plt.xlabel("num(y)")
    ly = plt.ylabel("y")
    tl = plt.title("cumulative normed y")
    return fg ...

Figures 5 to 8 illustrate variations on contour plots, not available directly from Excel. The basic graph is generated in Figure 5:

...
def get_figure5():
    fig = plt.figure()
    x = linspace(0,10,51)
    y = linspace(0,8,41)
    (X,Y) = meshgrid(x,y)
    a = exp(-((X-2.5)**2 + (Y-4)**2)/4) - exp(-((X-7.5)**2 + (Y-4)**2)/4)
    c = plt.contour(x,y,a)
    l = plt.clabel(c)
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")

    return fig 
...

Figures 6 to 8 illustrate various display options:

...
def get_figure6():
    fig = plt.figure()
    x = linspace(0,10,51)
    y = linspace(0,8,41)
    (X,Y) = meshgrid(x,y)
    a = exp(-((X-2.5)**2 + (Y-4)**2)/4) - exp(-((X-7.5)**2 + (Y-4)**2)/4)

    c = plt.contour(x,y,a,linspace(-1,1,11),colors='r',linewidths=4, linestyles='dotted')
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")
    return fig ...

...
def get_figure7():
    fig = plt.figure()
    x = linspace(0,10,51)
    y = linspace(0,8,41)
    (X,Y) = meshgrid(x,y)
    a = exp(-((X-2.5)**2 + (Y-4)**2)/4) - exp(-((X-7.5)**2 + (Y-4)**2)/4)

    c = plt.contourf(x,y,a,linspace(-1,1,11))
    b = plt.colorbar(c, orientation='vertical')
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")
    ax = plt.axis([0,10,0,8])
    return fig 
...

...
def get_figure8():
    fig = plt.figure()
    x = linspace(0,10,51)
    y = linspace(0,8,41)
    (X,Y) = meshgrid(x,y)
    a = exp(-((X-2.5)**2 + (Y-4)**2)/4) - exp(-((X-7.5)**2 + (Y-4)**2)/4)
    ac = 0.25*(a[:-1,:-1] + a[:-1,1:] + a[1:,:-1] + a[1:,1:])
    c = plt.pcolor(x,y,ac)
    d = plt.colorbar(c,orientation='horizontal')
    q = plt.winter()
    e = plt.contour(x,y,a,linspace(-1,1,11),colors='k')
    lx = plt.xlabel("x")
    ly = plt.xlabel("y")
    return fig 
...

Figure 9 illustrates a vector plot, also not available directly from Excel:

...
def get_figure9():
    fig = plt.figure()
    x = linspace(0,10,11)
    y = linspace(0,15,16)
    (X,Y) = meshgrid(x,y)
    u = 5*X
    v = 5*Y
    q = plt.quiver(X,Y,u,v,angles='xy',scale=1000,color='r')
    p = plt.quiverkey(q,1,16.5,50,"50 m/s",coordinates='data',color='r')
    xl = plt.xlabel("x (km)")
    yl = plt.ylabel("y (km)")
    return fig 
...

The final example from http://kestrel.nmt.edu/~raymond/software/python_notes/paper004.html is a combination plot. See the link for additional background information and useful links.

...
def get_figure10():
    x = arange(0.,10.1,0.2)
    a = sin(x)
    b = cos(x)
    fig1 = plt.figure(figsize = (8,8))
    plt.subplots_adjust(hspace=0.4)
    p1 = plt.subplot(2,1,1)
    l1 = plt.plot(x,a)
    lx = plt.xlabel("x (km)")
    ly = plt.ylabel("y (m)")
    ttl = plt.title("sine")
    p2 = plt.subplot(2,1,2)
    l2 = plt.plot(x,b)
    lx = plt.xlabel("x (km)")
    ly = plt.ylabel("y (m)")
    ttl = plt.title("cosine")
    sttl = plt.suptitle("two plots")
    return fig1 ...

The remaining examples show how Matplotlib can be used to plot a function entered as text on the spreadsheet, without having to generate a table of values in the spreadsheet. This can also be done directly from Excel (although the procedure is not obvious, see Charting a function), but using Matplotlib also provides access to all the additional functionality of this program.

Here is the Python code:

def plotfunc():
# Get data from the spreadsheet
    wb = xw.Workbook.caller()
    func = xw.Range('PlotFunction', 'func').value
    var = xw.Range('PlotFunction', 'var').value
    if var == None: var = 'x'
    xrange = xw.Range('PlotFunction', 'x_range').value
    xmin = xrange[0]
    xmax = xrange[1]
    xnum = int(xrange[2])
    yrange = xw.Range('PlotFunction', 'y_range').value
    params = xw.Range('PlotFunction', 'params').value
    vals = xw.Range('PlotFunction', 'vals').value
# Convert params from strings to variables with the value given in vals
    for param, val in zip(params, vals):
        if param != None:
            globals()[param] = val
        else:
            break
# Create array of x values            
    x = linspace(xmin, xmax, xnum)
# Convert func to a lambda function and evaluate it for x
    lfunc = eval('lambda ' + var + ': ' + func)
    y = lfunc(x)
# Make a new figure and plot the results
    fig = plt.figure()   
    line1=plt.plot(x, y)
    plt.setp(line1, color='r', linewidth=1.0)
    if yrange[0] == None:
        ymin = amin(y)
    else:
        ymin = yrange[0]
    if yrange[1] == None:
        ymax = amax(y)
    else:
        ymax = yrange[1]
    xrng = xmax-xmin
    xmin = xmin - xrng * .05
    xmax = xmax + xrng * .05
    yrng = ymax - ymin
    if yrange[0] == None: ymin = ymin - yrng * .05
    if yrange[1] == None: ymax = ymax + yrng * .05
    plt.axis([xmin,xmax, ymin, ymax])
    xl = plt.xlabel('X')
    yl = plt.ylabel('Y')
    ttl = plt.title(func)
    plt.grid(True)
    
    sht = xw.Book.caller().sheets[1]
    sht.pictures.add(fig, name='MyFuncplot', update = True)

The VBA code simply calls the plotfunc function from the xlMatPlot Python code:

Private Sub Worksheet_Change(ByVal Target As Range)
    RunPython ("import xlMatPlot; xlMatPlot.plotfunc()")
End Sub

This code is located in the PlotFunction Worksheet code and will run whenever a value on the PlotFunction worksheet changes.

The first example shows a plot of a fourth order polynomial:

The remaining examples plot an oscillating function that was used as an example in recent presentations of integration functions:
exp(x) * sin(a*x**b)

With a = 2 and b = 3, plotting 1000 points with an x range of 1 to 3 produces a nice smooth result:

Increasing the maximum x to 10 greatly increases the number and range of the oscillations, with the result that 1000 points are clearly not enough to produce an accurate result.

Increasing the number of points to 100,000 gives a much better result, providing a visual illustration of the reason why it is difficult to get an accurate result when performing a numerical integration of this function.  Note that the first time any Python code is called from a newly opened worksheet there is a noticeable delay while the code is imported and compiled, but thereafter the graph with 100,000 points should recalculate and re-plot in well under one second.

 xlwins