Scatter Plots for Financial Correlation Analysis

Hello! Welcome back to our course on financial data visualization.

In our last session, we explored how to use area charts to visualize portfolio composition and asset allocation over time. You learned to distinguish between stacked and 100% stacked area charts to show either absolute growth or proportional changes.

Today, we shift from looking at composition within an asset to analyzing the relationship between different financial variables. Our focus will be on learning how to design scatter plots to analyze correlations between financial variables like asset returns. This is a fundamental technique in finance used for everything from risk management to understanding how a specific stock moves in relation to the overall market.

1. What is a Scatter Plot and What Does It Tell Us?

Before we jump into Excel, let's establish what a scatter plot is and why it's so powerful. A scatter plot uses dots to represent the values of two different numeric variables. By plotting these dots, we can visually inspect the relationship, or correlation, between them.

This relationship can be described in three ways:

• Direction: Is it positive (as one variable increases, the other tends to increase) or negative (as one increases, the other tends to decrease)?
• Strength: Is the relationship strong (the dots form a tight, clear pattern) or weak (the dots are widely scattered)?
• Form: Is the pattern linear (resembles a straight line) or does it have some other shape (nonlinear)?

For financial analysis, this is incredibly useful. For example, you can plot the monthly returns of a stock against the monthly returns of a market index (like the S&P 500) to see how closely they move together.

2. Building a Scatter Plot with Asset Returns in Excel

Now let's apply this theory. We'll create a scatter plot to compare the returns of a stock (IBM) against a market benchmark (S&P 500).

The process involves three main steps:

1. Gathering the Data: We need historical price data for both assets. We'll use adjusted closing prices to account for dividends and stock splits.
2. Calculating Returns: Prices themselves are not ideal for comparison. We need to calculate the periodic returns (e.g., monthly) for each asset. The formula for a simple return is: $$\text{Return}=\frac{\text{Current Price}-\text{Previous Price}}{\text{Previous Price}}=\frac{\text{Current Price}}{\text{Previous Price}}-1$$
3. Creating the Chart: With the returns calculated, we can now insert a scatter plot in Excel.

The following video provides a complete walkthrough of this entire process.

3. Analyzing the Relationship: Trendlines, Beta, and R-Squared

The scatter plot gives us a visual impression, but we can quantify the relationship by adding a trendline (also known as a regression line or line of best fit). Excel can also display the equation of this line and a value called R-squared.

This is where the analysis becomes truly insightful for finance:

• The Trendline Equation: The equation takes the form $y=mx+c$.

  • y: The return of our individual stock (the dependent variable).
  • x: The return of the market index (the independent variable).
  • m (The Slope): This is the most important value. In finance, this slope is known as beta ($\beta$). It measures the stock's volatility relative to the market. A beta of 1.2 means that for every 1% move in the market, the stock is expected to move 1.2% in the same direction.
  • c (The Intercept): This is known as alpha ($\alpha$). It represents the stock's expected return if the market return was zero.

• R-Squared ($R^2$): This value, between 0 and 1, tells us what percentage of the stock's price movement can be explained by the market's movement. A high $R^2$ (e.g., 0.75) means the stock's returns are highly correlated with the market's returns. A low $R^2$ means other, stock-specific factors are more dominant.

Let's continue with the Codible video to see how to add and interpret these elements.

To reinforce the interpretation of the slope as beta, this second video provides a very clear and concise explanation.

Finally, you might hear people talk about the correlation coefficient ($r$), which ranges from -1 to +1. It's simply the square root of R-squared. You can easily calculate it from the value on your chart.

4. Enhancing Your Scatter Plot for Clarity and Impact

Your goal is to create "nice" data visualizations, which means moving beyond Excel's default settings. A well-designed scatter plot is clean, focused, and easy to interpret.

Here are a few tips, inspired by the experts at Storytelling with Data:

• Clean up the Skeleton: Remove distracting chart borders, gridlines, and any unnecessary visual noise. Ensure your axes are clearly labeled.
• Use Color Purposefully: If you are comparing multiple assets on the same chart (e.g., plotting both Stock A and Stock B against the market), use different colors for each data series and include a clear legend.
• Add Context: You can add reference lines for the average X and Y values to divide the plot into four quadrants (e.g., high return/high volatility). This helps quickly categorize the data points.
• Label Strategically: Instead of labeling every point (which creates clutter), label only the most important ones—outliers, key dates, or specific data points you want to discuss.

Excel's built-in chart styles can be a good starting point for formatting.

For a much deeper dive into advanced formatting techniques, I highly recommend bookmarking the article "how to make a scatter plot in Excel" from Storytelling with Data (resource ID: [LINK](https://www.storytellingwithdata.com/blog/how-to-make-a-scatter-plot-in-excel)). It contains excellent, detailed tutorials on creating multi-series plots, adding quadrants, and customizing labels for maximum impact.

Conclusion

Congratulations! You've just learned how to build and interpret one of the most important charts in quantitative finance. You can now take two series of financial data, visualize their relationship, and extract key metrics like beta and R-squared.

Key Takeaways:

• Scatter plots visualize the relationship (correlation) between two numerical variables.
• To analyze asset correlation, you must first calculate periodic returns from historical price data.
• Adding a trendline provides the regression equation ($y=mx+c$) and R-squared value.
• In the context of stock vs. market returns, the slope (m) is the stock's beta ($\beta$), and R-squared measures how much of the stock's movement is explained by the market.
• Effective formatting—by reducing clutter, using color wisely, and labeling strategically—is key to creating a clear and impactful visualization.

In our next lesson, we will focus on visualizing the price movement of a single stock in detail. You will learn how to build candlestick charts to analyze Open, High, Low, and Close (OHLC) price data.