Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Create a Stock Chart with openpyxl

Draw an open-high-low-close candlestick chart in Excel from Python: the column order Excel requires, hiding the series lines, high-low lines, up and down bars in your own colours, a value axis that does not start at zero, trading days without weekend gaps, and a volume chart underneath.
Charts & imagesopenpyxl

A stock chart — Excel's name for the open-high-low-close (OHLC) chart, drawn as candlesticks — shows each day's range and direction at a glance: a vertical line from the low to the high, and a box between the opening and closing price, coloured by whether the price rose or fell. It is not only for share prices: daily min/max/first/last readings of anything — energy prices, sensor values, queue lengths — fit the same shape. openpyxl can build the chart natively, but Excel's stock chart type is strict about how the data is arranged and needs a few properties the defaults leave off. This page, part of Creating Charts in Excel with openpyxl, goes through them.

Prerequisites

Bash
pip install openpyxl pandas

prices.xlsx has twenty trading days of prices for one instrument — date, open, high, low, close and volume — on a sheet called Prices. The dates are business days only: there are no rows for weekends.

The column order a stock chart needs A worksheet with Date, Open, High, Low, Close and Volume columns. The four price columns must be in exactly the order Open, High, Low, Close for Excel's open-high-low-close chart. prices.xlsx A B C D E F 1 Date Open High Low Close Volume 2 01 Sep 100.00 100.02 98.44 99.68 19326 3 02 Sep 103.06 103.21 101.82 102.25 11580 4 03 Sep 99.23 102.55 99.04 102.33 11052 Open, High, Low, Close exactly this order four adjacent columns

Step 1 — Arrange the data in the order Excel expects

Excel's stock charts identify the series by position, not by name: an open-high-low-close chart needs exactly four series in the order Open, High, Low, Close. A high-low-close chart needs three, in that order. If an export has the columns in another order, reorder them before writing:

Python
import pandas as pd

prices = pd.read_excel("prices.xlsx", sheet_name="Prices")
prices = prices[["Date", "Open", "High", "Low", "Close", "Volume"]]
print(prices.head(3).to_string(index=False))
print(len(prices), "trading days")

With the columns in the wrong order, Excel draws the chart without complaint — but the boxes span the wrong prices, so a day that fell can appear to have risen. The column selection above makes the order explicit in code, where a later change to the export cannot silently break it.

Step 2 — Build the candlestick chart

Add the four price columns as series, hide their lines, and turn on the two features that make it a candlestick: high-low lines and up/down bars:

Python
from openpyxl import load_workbook
from openpyxl.chart import StockChart, Reference
from openpyxl.chart.axis import ChartLines
from openpyxl.chart.shapes import GraphicalProperties
from openpyxl.chart.updown_bars import UpDownBars

wb = load_workbook("prices.xlsx")
ws = wb["Prices"]
last = ws.max_row
for cell in ws["A"][1:]:
    cell.number_format = "dd mmm"

chart = StockChart()
chart.add_data(Reference(ws, min_col=2, max_col=5, min_row=1, max_row=last), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=last))
for series in chart.series:
    series.graphicalProperties.line.noFill = True
chart.hiLowLines = ChartLines()
chart.upDownBars = UpDownBars(
    gapWidth=60,
    upBars=ChartLines(spPr=GraphicalProperties(solidFill="0F9488")),
    downBars=ChartLines(spPr=GraphicalProperties(solidFill="BE185D")),
)
print([s.tx.strRef.f for s in chart.series])

Without line.noFill, Excel joins each series' points with a line, and the chart becomes four zigzags across the candles. hiLowLines draws the vertical line from the day's high to its low. upDownBars draws the box between the first and last series — open and close — and colours it by direction: here teal for days that closed higher than they opened and pink for days that closed lower, instead of Excel's default white and black. gapWidth sets the space between the boxes as a percentage of their width; lower values give wider candles.

Step 3 — Axes: no zero, no weekends

A price chart starting at zero squeezes all the movement into a thin band at the top. Start the value axis just below the lowest low, and make sure both axes are displayed:

Python
low = prices["Low"].min()
chart.y_axis.scaling.min = int(low) - 1
chart.y_axis.title = "Price"
chart.y_axis.delete = False
chart.x_axis.delete = False
chart.x_axis.number_format = "dd mmm"
chart.legend = None
chart.title = "ACME daily prices"
chart.width, chart.height = 16, 9
ws.add_chart(chart, "H2")
print("y axis from", chart.y_axis.scaling.min)

The category axis here is a text axis: one slot per row, so the twenty trading days sit next to each other with no gaps for weekends. A date axis would space the points by calendar date and leave empty slots for every Saturday and Sunday — right for irregular events, wrong for trading days. openpyxl's stock chart uses a text axis by default, so the only thing to do is not to change it. Use a Date Axis in an Excel Chart with openpyxl covers the other case. The legend is removed because four entries named Open, High, Low and Close explain nothing about a candlestick chart. The delete = False settings make Excel show the axes, which openpyxl otherwise writes as hidden.

The pieces of one candle Three layers explaining a candle. The high-low line spans the day's range, the up-down bar spans open to close, and its colour shows whether the day closed higher or lower than it opened. High-low line hiLowLines — from the day's low to its high Up/down bar upDownBars — the box between open and close Colour teal when close > open, pink when close < open

Step 4 — Add a volume chart underneath

Excel can combine volume bars with the candles on a secondary axis, but the combination is fragile to build from code and cramped to read. A separate column chart of volume, the same width and directly below the candles with the same categories, reads more clearly:

Python
from openpyxl.chart import BarChart

volume = BarChart()
volume.add_data(Reference(ws, min_col=6, min_row=1, max_row=last), titles_from_data=True)
volume.set_categories(Reference(ws, min_col=1, min_row=2, max_row=last))
volume.series[0].graphicalProperties.solidFill = "94A3B8"
volume.series[0].graphicalProperties.line.noFill = True
volume.gapWidth = 60
volume.legend = None
volume.title = "Volume"
volume.y_axis.delete = False
volume.x_axis.delete = False
volume.x_axis.number_format = "dd mmm"
volume.width, volume.height = 16, 5
ws.add_chart(volume, "H20")

ws.page_setup.orientation = "landscape"
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_setup.fitToWidth, ws.page_setup.fitToHeight = 1, 1
wb.save("prices_chart.xlsx")
print("saved")

Matching width and gapWidth lines the volume bars up under the candles, day for day — closely rather than exactly, because the two charts' value axes have labels of different widths (prices like 104 against volumes like 25000), which shifts each plot area slightly. A neutral grey keeps volume visually secondary to the price.

Step 5 — Render and check

Convert to PDF with LibreOffice and look at the result before sending it anywhere. The landscape, fit-to-one-page setup at the end of Step 4 keeps the charts to the right of the data on the same printed page — set it in the same session that adds the charts, because reopening a workbook with openpyxl drops its charts:

Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf prices_chart.xlsx > /dev/null 2>&1
pdftoppm -png -r 50 -f 1 -l 1 prices_chart.pdf preview && ls preview*

The render shows teal and pink candles with their wicks, pink days clustering in the second half of the month, a value axis from just below the lowest price, dates without weekend gaps, and the volume bars aligned underneath.

Which stock chart layout Two panels. Open-high-low-close needs four columns and draws candles with up and down bars. High-low-close needs three columns and draws a range line with a marker for the close. Open-High-Low-Close 4 series in that order hiLowLines + upDownBars shows direction per day High-Low-Close 3 series in that order hiLowLines + close marker when there is no open price

A high-low-close chart when there is no open price

Some sources record only a daily high, low and closing value — sensor logs and many price feeds do. The same chart type works with three series in the order High, Low, Close: keep the high-low lines, leave out the up/down bars (there is no open to compare with), and show the close as a marker:

Python
wb = load_workbook("prices.xlsx")
ws = wb["Prices"]
for cell in ws["A"][1:]:
    cell.number_format = "dd mmm"
hlc = StockChart()
hlc.add_data(Reference(ws, min_col=3, max_col=5, min_row=1, max_row=ws.max_row), titles_from_data=True)
hlc.set_categories(Reference(ws, min_col=1, min_row=2, max_row=ws.max_row))
for series in hlc.series:
    series.graphicalProperties.line.noFill = True
close = hlc.series[2]
close.marker.symbol = "dash"
close.marker.size = 8
close.marker.graphicalProperties.solidFill = "4338CA"
hlc.hiLowLines = ChartLines()
hlc.y_axis.scaling.min = int(low) - 1
hlc.x_axis.delete = hlc.y_axis.delete = False
hlc.x_axis.number_format = "dd mmm"
hlc.legend = None
hlc.width, hlc.height = 16, 8
ws.add_chart(hlc, "A24")
wb.save("prices_hlc.xlsx")
print(len(hlc.series), "series:", [s.tx.strRef.f for s in hlc.series])

A short horizontal dash on each high-low line marks where the day closed. It is the chart to use for any daily range with a representative end value, not only for prices.

Common pitfalls

  • Series in the wrong order. Excel assigns roles by position; select the columns explicitly as Open, High, Low, Close.
  • Zigzag lines across the candles. Set line.noFill = True on every series.
  • Hidden axes in Excel. Set delete = False on both axes.
  • Weekend gaps. Keep the default text axis for trading days; a date axis adds empty slots.
  • Re-saving with openpyxl. Loading a workbook drops its charts; add charts in the final save.

Conclusion

Put the price columns in the order Open, High, Low, Close, add them to a StockChart with titles_from_data=True, hide every series line, and turn on hiLowLines and upDownBars with your own up and down colours. Start the value axis just below the lowest low, keep the text axis so trading days sit together, set delete = False on the axes and drop the legend. Put volume in a matching column chart underneath, and render with LibreOffice to check.

Frequently asked questions

How do I create a candlestick chart in Excel with Python? Use openpyxl's StockChart with four series in the order Open, High, Low, Close, set each series' line to noFill, and set chart.hiLowLines = ChartLines() and chart.upDownBars = UpDownBars(). Add the dates as categories.

Why does my stock chart show lines instead of candles? The series lines are still drawn. Set line.noFill = True on each series' graphicalProperties; the candles come from the high-low lines and up-down bars.

How do I change the colours of rising and falling days? Pass upBars and downBars to UpDownBars, each a ChartLines whose spPr is a GraphicalProperties with the fill colour you want, such as GraphicalProperties(solidFill="0F9488").

How do I stop weekends showing as gaps? Use a text category axis, which is the stock chart's default in openpyxl: it gives one slot per row, so only the dates in the data appear. A date axis spaces points by calendar date and leaves weekend gaps.

More on charts & images

More on charts & images →