As a Python enthusiast for a few years and an Excel aficionado for even longer, it’s been exciting to see Python become officially supported in Excel. Yet, I still receive a lot of questions from Excel users about who this tool is right for and when they should use it.
It’s a fair question and one that’s hard to always have an answer for, especially with Excel offering so much great native functionality these days! However, one discipline that particularly stands to benefit from Python is finance. It has truly become a tool of the trade for finance professionals, to the extent that the CFA exams now even assume proficiency in Python.
This post will demonstrate how to create a particular kind of financial markets analysis plot using Python, directly within Excel. You can follow along with the link below:
What is an OHLC plot?
An OHLC plot is a financial chart showing Open, High, Low, and Close prices for a set period—day, week, or month. It visually summarizes price movements, offering market behavior insights. Traders, investors, and analysts use it to gauge market trends, make decisions, and spot trading opportunities from historical data.
OHLC plots with Python in Excel
Summarizing the data
The first task on our agenda is to summarize the data. Currently, we have hourly prices, and to facilitate plotting, I aim to extract the open, high, low, and close prices for each date. This task shouldn’t be too daunting as pandas offers convenient tools for such operations which I can employ in our Excel-based Python environment.
To kick things off, I’ll convert the stock prices from the exercise workbook’s stocks table into a DataFrame. From there, I’ll leverage pandas to determine the daily open, high, low, and close prices—a process that involves transforming the DataFrame index into dates. By employing a combination of the resample() and ohlc() methods, I can reformat the data into a structure conducive for Python-based plotting and user comprehension.
I have pasted the code in a snippet below for ease of visibility and use:
Building the OHLC plot
The next step is to actually build the plot. Although there are specific Python packages like mplfinance that can easily generate financially-related plots in Python such as this one, we do not have access to them in the Python in Excel environment. Therefore, we will need to be a little creative by using matplotlib.pyplot to build the chart mostly from our own code.
Since this code is somewhat challenging to understand, and it’s difficult to fit everything into one screenshot, I will provide it below in a snippet with some comments. Then, I will display the results inside Excel. Please note that when you receive a plot from Python in Excel, it will appear in a single cell, so you will want to resize it appropriately.
Your Python in Excel plot should look something like this:
Interpretation
Let’s take a moment to interpret what we’ve created. The horizontal lines to the left and right show the opening and closing prices for each day, respectively. A green bar means the stock closed higher than it opened, which is seen as positive, while a red bar means it closed lower, seen as negative.
In the chart you’ve shown, the stock had two days where it ended higher than it started, and three days where it ended lower. The lengths of the vertical lines show how much the price fluctuated during the day—the longer the line, the more the price moved.
Conclusion
I hope this blog post has helped you consider what types of tools can assist you in leveraging Python within Excel. Perhaps you’ve discovered a new finance plot style, or at the very least, gained insights into how to create one. What questions do you have, or what ideas are you considering for utilizing Python in Excel? Feel free to share your thoughts in the comments. I also offer corporate training services on various topics in Python, Excel, finance and data analytics.
Leave a Reply