Matplotlib has several backends which enables it to be used with different UI toolkits. This could just as easily be data retrieved from a database or the output from another calculation. The unsmoothed data is generated with the Excel formula =SIN(B9)+SIN(B9*10)/3+SIN(B9*100)/7. However, Excel won’t respond to any user input until after the window is closed as the plt.show() call blocks until the window is closed. To add this code to Excel save it to a Python file and add it to the pyxll.cfg file (see for details).Ĭalling this function from Excel brings up a matplotlib window with the expected plot. To see how to use pandas datatypes with PyXLL see the pandas examples on github. Note that our function could take a pandas dataframe or series quite easily, but just to keep things as simple as possible I’ll stick to plain numpy arrays. To start off we’ll write a simple function that takes two columns of data (our x and y values), calculates the exponentially weighted moving average (EWMA) of the y values, and then plot them together as a line plot. There are examples of hundreds of other types of plots on the matplotlib website that can all be used in exactly the same way as this example in Excel. Matplotlib can plot just about anything you can imagine! For this blog I’ll be using only a very simple plot to illustrate how it can be done in Excel. This blog shows how to plot data from Excel using matplotlib and PyXLL so that Excel can continue to be used while a plot window is active, and so that same window can be updated whenever the data in Excel is updated. The difficulty is that when showing a plot the call to matplotlib blocks and so control isn’t returned to Excel until the user closes the window. As matplotlib is just a Python package like any other it can be imported and used in the same way as from any Python script. Something that has been asked a couple of times is how to use matplotlib within Excel using PyXLL. When doing the same in an IPython console when a plot is shown control returns to the IPython prompt immediately, which is useful for interactive development.
Users already familiar with matplotlib will be aware that when showing a plot as part of a Python script the script stops while a plot is shown and continues once the user has closed it. While Excel is able to produce various types of plots, sometimes it’s either not quite good enough or it’s just preferable to use matplotlib. Python has a broad range of tools for data analysis and visualisation. This post can be used for earlier versions of PyXLL, but we recommend updating the PyXLL 5 and using that instead. NOTE: PyXLL 5 includes matplotlib integration.
0 kommentar(er)
