Bokeh¶

To plot a bokeh figure in Excel you first create the figure in exactly the same way you would in any Python script using bokeh, and then use PyXLL’s plot function to show it in the Excel workbook.

Bokeh supports interactive charts and these are displayed in Excel using an interactive web control, where available [1].

The code below shows an Excel worksheet function that generates a bokeh figure and displays it in Excel.

# Download the bokeh sample data first
import bokeh
bokeh.sampledata.download()

from math import pi
import pandas as pd
from bokeh.plotting import figure, output_file, show
from bokeh.sampledata.stocks import MSFT

@xl_func
def bokeh_plot():
    # Get some sample data to plot
    df = pd.DataFrame(MSFT)[:50]
    df["date"] = pd.to_datetime(df["date"])

    # Select dates based on open <> close
    inc = df.close > df.open
    dec = df.open > df.close
    w = 12*60*60*1000 # half day in ms

    # Set up the figure
    p = figure(x_axis_type="datetime", plot_width=1000, title="MSFT Candlestick")
    p.xaxis.major_label_orientation = pi/4
    p.grid.grid_line_alpha = 0.3

    # Plot lines for high/low and vbars for open/close
    p.segment(df.date, df.high, df.date, df.low, color="black")
    p.vbar(df.date[inc], w, df.open[inc], df.close[inc], fill_color="#D5E1DD", line_color="black")
    p.vbar(df.date[dec], w, df.open[dec], df.close[dec], fill_color="#F2583E", line_color="black")

    # Show the plot in Excel using pyxll.plot
    plot(p)

When this function is run in Excel the plot is shown just below the calling cell.

If the interactive web control is not available, the figure will instead be exported as a static image. This is done using Selenium and so that must be installed before Bokeh can be used with PyXLL.

The easiest way to install Selenium is to use Anaconda and install it using either of the following commands:

conda install selenium geckodriver firefox -c conda-forge

or

conda install selenium python-chromedriver-binary -c conda-forge

If you are not using Anaconda you can use pip install selenium but you will also need to install a suitable web browser backend. See https://pypi.org/project/selenium/ for additional details about how to install Selenium.

If you have any problems with exporting plots as html or using the interactive web control, you can tell PyXLL to use a static image format instead by passing allow_html=False to plot.

When exporting as an image and not html, an SVG image may be used. If you version of Excel does not support SVG images (Excel 2016 or earlier) or you are having problems with the SVG image not displaying correctly you can tell PyXLL to use the PNG format instead by passing allow_svg=False to plot.

Footnotes