How to Calculate Stocks Autocorrelation in Excel

Autocorrelation, also known as serial correlation or lagged correlation, explains the relationship between observations between the same variable over different periods of time. The observations are said to be independent if autocorrelation is zero. Autocorrelation is calculated as a function of mean and variance.

Learn about Autocorrelation

Autocorrelation has application in stock returns. We can calculate autocorrelation in stock returns which can be helpful in equity analysis. For example, let’s say you identify a stock that has exhibited high autocorrelation historically. If you observe that the stock is moving up for the past few days, you can expect the stock movement to match the lagging time series.

Calculation of autocorrelation is similar to calculation of correlation between two time series. The only difference is that while calculating autocorrelation, you use the same time series twice, one original, and the other as the lagged one.

Formula for Calculating Autocorrelation

Example: Stock Autocorrelation in Excel

Let’s take a numerical example to learn how we can calculate the autocorrelation for stock returns data in excel. Let’s say we have the following stock returns data with us.

AB
DayReturns
1
24%
33%
4-1%
52%
64%
75%
83%
9-1%
101%

Step 1: Calculate the Variance

The variance of the series can be calculated using the VAR formula as follows:

\=VAR(B2:B10)

\= 0.000469

Step 2: Calculate the Covariance

We now need to calculate the k-th lag variance.

For k=1, covariance will be calculated as follows:

\=COVAR(B3:B10,B2:B9)

\= 0.0001

For k=2, covariance will be calculated as follows:

\=COVAR(B4:B10,B2:B8)

\=-0.00027

Autocorrelation can now be calculated as follows:

Autocorrelation (k=1) = 0.000469/0.0001 = 4.69

Autocorrelation (k=2) = 0.000469/-0.00027 = -1.737

Autocorrelation is commonly referred to as ACF in formulas.

You can also use the =CORREL() formula in excel to achieve the same results.

Data Science in Finance: 9-Book Bundle

Data Science in Finance Book Bundle

Master R and Python for financial data science with our comprehensive bundle of 9 ebooks.

What's Included:

  • Getting Started with R
  • R Programming for Data Science
  • Data Visualization with R
  • Financial Time Series Analysis with R
  • Quantitative Trading Strategies with R
  • Derivatives with R
  • Credit Risk Modelling With R
  • Python for Data Science
  • Machine Learning in Finance using Python

Each book includes PDFs, explanations, instructions, data files, and R code for all examples.

Get the Bundle for $39 (Regular $57)
JOIN 30,000 DATA PROFESSIONALS

Free Guides - Getting Started with R and Python

Enter your name and email address below and we will email you the guides for R programming and Python.

Data Science in Finance: 9-Book Bundle

Data Science in Finance Book Bundle

Master R and Python for financial data science with our comprehensive bundle of 9 ebooks.

What's Included:

  • Getting Started with R
  • R Programming for Data Science
  • Data Visualization with R
  • Financial Time Series Analysis with R
  • Quantitative Trading Strategies with R
  • Derivatives with R
  • Credit Risk Modelling With R
  • Python for Data Science
  • Machine Learning in Finance using Python

Each book comes with PDFs, detailed explanations, step-by-step instructions, data files, and complete downloadable R code for all examples.