# Stock Charts

Data that is arranged in columns or rows in a specific order on a worksheet can be plotted in a stock chart. As its name implies, a stock chart is most often used to illustrate the fluctuation of stock prices. However, this chart may also be used for scientific data. For example, you could use a stock chart to indicate the fluctuation of daily or annual temperatures. You must organize your data in the correct order to create stock charts.

The way stock chart data is organized in the worksheet is very important. For example, to create a simple high-low-close stock chart, you should arrange your data with High, Low, and Close entered as column headings, in that order.

Although stock charts are a distinct type, the various types are just shortcuts for particular formatting options:

> - high-low-close is essentially a line chart with no lines and the marker set to XYZ. It also sets hiLoLines to True

> - open-high-low-close is the same as a high-low-close chart with the marker for each data point set to XZZ and upDownLines.

Volume can be added by combining the stock chart with a bar chart for the volume.

    from datetime import date

    from openpyxl import Workbook

    from openpyxl.chart import (
        BarChart,
        StockChart,
        Reference,
        Series,
    )
    from openpyxl.chart.axis import DateAxis, ChartLines
    from openpyxl.chart.updown_bars import UpDownBars

    wb = Workbook()
    ws = wb.active

    rows = [
       ['Date',      'Volume','Open', 'High', 'Low', 'Close'],
       ['2015-01-01', 20000,    26.2, 27.20, 23.49, 25.45,  ],
       ['2015-01-02', 10000,    25.45, 25.03, 19.55, 23.05, ],
       ['2015-01-03', 15000,    23.05, 24.46, 20.03, 22.42, ],
       ['2015-01-04', 2000,     22.42, 23.97, 20.07, 21.90, ],
       ['2015-01-05', 12000,    21.9, 23.65, 19.50, 21.51,  ],
    ]

    for row in rows:
        ws.append(row)

    # High-low-close
    c1 = StockChart()
    labels = Reference(ws, min_col=1, min_row=2, max_row=6)
    data = Reference(ws, min_col=4, max_col=6, min_row=1, max_row=6)
    c1.add_data(data, titles_from_data=True)
    c1.set_categories(labels)
    for s in c1.series:
        s.graphicalProperties.line.noFill = True
    # marker for close
    s.marker.symbol = "dot"
    s.marker.size = 5
    c1.title = "High-low-close"
    c1.hiLowLines = ChartLines()

    # Excel is broken and needs a cache of values in order to display hiLoLines :-/
    from openpyxl.chart.data_source import NumData, NumVal
    pts = [NumVal(idx=i) for i in range(len(data) - 1)]
    cache = NumData(pt=pts)
    c1.series[-1].val.numRef.numCache = cache

    ws.add_chart(c1, "A10")

    # Open-high-low-close
    c2 = StockChart()
    data = Reference(ws, min_col=3, max_col=6, min_row=1, max_row=6)
    c2.add_data(data, titles_from_data=True)
    c2.set_categories(labels)
    for s in c2.series:
        s.graphicalProperties.line.noFill = True
    c2.hiLowLines = ChartLines()
    c2.upDownBars = UpDownBars()
    c2.title = "Open-high-low-close"

    # add dummy cache
    c2.series[-1].val.numRef.numCache = cache

    ws.add_chart(c2, "G10")

    # Create bar chart for volume

    bar = BarChart()
    data =  Reference(ws, min_col=2, min_row=1, max_row=6)
    bar.add_data(data, titles_from_data=True)
    bar.set_categories(labels)

    from copy import deepcopy

    # Volume-high-low-close
    b1 = deepcopy(bar)
    c3 = deepcopy(c1)
    c3.y_axis.majorGridlines = None
    c3.y_axis.title = "Price"
    b1.y_axis.axId = 20
    b1.z_axis = c3.y_axis
    b1.y_axis.crosses = "max"
    b1 += c3

    c3.title = "High low close volume"

    ws.add_chart(b1, "A27")

    ## Volume-open-high-low-close
    b2 = deepcopy(bar)
    c4 = deepcopy(c2)
    c4.y_axis.majorGridlines = None
    c4.y_axis.title = "Price"
    b2.y_axis.axId = 20
    b2.z_axis = c4.y_axis
    b2.y_axis.crosses = "max"
    b2 += c4

    ws.add_chart(b2, "G27")

    wb.save("stock.xlsx")

Warning

Due to a bug in Excel high-low lines will only be shown if at least one of the data series has some dummy values. This can be done with the following hack:

    from openpyxl.chart.data_source import NumData, NumVal
    pts = [NumVal(idx=i) for i in range(len(data) - 1)]
    cache = NumData(pt=pts)
    c1.series[-1].val.numRef.numCache = cache