Python in Excel: How to do rolling correlations

As a financial analyst, you often want to trace how relationships between asset returns evolve over time. Maybe you’re looking to understand diversification benefits, detect changing market dynamics, or flag periods of abnormal correlation between two securities. That’s where rolling correlations come in. In this walkthrough, we’ll use Python in Excel to track how eBay (EBAY) and ExxonMobil (XOM) stock returns move together across time. Using Python makes this analysis a bit simpler and more dynamic than otherwise in Excel.

Download the exercise file below to follow along:

You’ll find the following code blocks already set up in the downloadable workbook. They’re shown below as Gists for easier reading and reference.

In these first steps, we’re loading the historical price data from the Excel table, setting the Date column as the index so we can work with time series properly, and then calculating daily percentage changes to get the returns for each stock.

We drop the first row of missing values since you can’t calculate a return without a previous day’s price. This gives us a clean DataFrame of daily returns that we’ll use for correlation analysis.

Next, we’ll set up the rolling correlation calculation. Instead of hardcoding the window size (like 30 days), we’ll pulling it dynamically from cell F1 in Excel. This lets the user adjust the rolling window directly in the sheet, much like a parameter in Power BI or Tableau, so they can explore how different time spans affect the correlation.

Next, we’ll create a line chart to visualize how the rolling correlation between EBAY and XOM changes over time. We start by setting the figure size for a cleaner layout, then plot the rolling_corr Series. The title automatically reflects the window size selected in cell F1, and we add axis labels to make the chart easier to interpret. This lets you quickly spot periods when the two stocks moved more or less in sync.

This rolling correlation plot shows how the relationship between EBAY and XOM stock returns changes over time. Instead of calculating a single correlation value for the entire dataset, we’re calculating it over a moving window so we can see how their connection strengthens or weakens during different periods.

When the line is closer to +1, it means the two stocks tended to move in the same direction during that window. A value near 0 means there wasn’t much of a consistent relationship—sometimes they moved together, sometimes not. When the line dips toward -1, it suggests the stocks were moving in opposite directions.

This is useful because market conditions change, and relationships between assets can shift. A static correlation won’t show that. With a rolling view, you can identify periods where diversification may have helped (when correlation is low or negative) or when it didn’t (when correlation is high). And since the window size comes from a cell in Excel, you can easily adjust it to explore how different timeframes affect the trends you see.

A natural next question after looking at a rolling correlation plot is: what window size should I use? The window determines how many days of data are used to calculate each point on the chart. Smaller windows pick up changes more quickly, while larger windows smooth out the noise and give you a broader trend.

There’s no one-size-fits-all answer. Like with most things as an analyst, it depends on your goals.

If you’re trying to spot quick shifts in behavior, a shorter window may be useful. But if you want a more stable picture that avoids overreacting to day-to-day fluctuations, a longer window might be better. Here’s a quick reference table to help you decide:

In addition to plotting the rolling correlation over time, a scatterplot comparing all return points is a good idea too because it gives you a quick visual sense of the overall relationship between the two assets. While the rolling chart shows how correlation changes, the scatterplot reveals the general pattern of how often and how strongly the returns move together across the whole dataset. We can derive that with this code block:

In this case, the code creates a scatterplot of EBAY vs. XOM daily returns, where each dot is a trading day. The alpha=0.6 parameter sets the dots to be semi-transparent, making it easier to see where the points are most concentrated. If the stocks moved in lockstep, you’d see a clear diagonal line—either upward or downward. Here, the points are more loosely spread, suggesting a weak positive correlation.

You’ll also notice some outliers—points that sit far away from the center cluster. These could reflect unusual events like earnings surprises or broader market shocks. While they may be interesting, they can also distort the correlation, so it’s worth considering how much weight to give them depending on your analysis goals.

In this demo, we calculated and visualized rolling correlations using Python in Excel. By working with returns instead of raw prices, we uncovered how their relationship changes over time… something a single correlation number can’t show.

Rolling correlations are great for spotting patterns and shifts in asset behavior, but they depend on window size and assume a linear relationship. Use them as one piece of your broader analysis. If you have questions or want help bringing this kind of insight into your workflow, drop a comment below or get in touch.