Standard Deviation of Stock Price Returns Google Sheets Formula

What is a Standard Deviation

The Standard Deviation of Daily Price Returns is a statistical measure representing the volatility or risk in an instrument. It tells you how much the daily price return can deviate from the historical average. The higher the Standard Deviation, the higher the fluctuations will be.

Standard Deviation Calculation

Suppose there is a stock with the below price returns over 20 days.

Price Returns:
(-0.81%, -0.38%, 0.27%, -2.04%, -1.78%, -3.12%, 0.92%, 4.06%, 1.09%, 0.66%, -0.13%, 2.11%, -0.25%, 6.94%, 0.41%, -0.62%, 1.44%, -1.57%, -3.64%, -2.93%)

Calculate The Average:
We will calculate the average price return of the stock over the previous 20 days.
= (-0.81% + -0.38% + 0.27% + -2.04% + -1.78% + -3.12% + 0.92% + 4.06% + 1.09% + 0.66% + -0.13% + 2.11% + -0.25% + 6.94% + 0.41% + -0.62% + 1.44% + -1.57% + -3.64% + -2.93%) / 20
= 0.03%

In statistics, “mean” and “average” are often used interchangeably to refer to the same concept, which is the measure of central tendency. This value is considered typical or representative of a set of data.

Calculate The Variance :
To calculate the standard deviation, we need to first find the variance of each price return by subtracting the mean return from each daily return, squaring the difference, and then summing up the squared differences. After that, we divide the sum of squared differences by the number of returns, which is 20, to get the variance.
=(-0.81% – 0.03%)² + ( -0.38% – 0.03%)² + (0.27% -0.03%)² + (-2.04% -0.03%)² + (-1.78% -0.03%)² + (-3.12% – 0.03%)² + (0.92% -0.03%)² + ( 4.06% -0.03%)² +……..
= 1.16% / 20
= 0.06%

Variance is a measure of how spread out a set of data is. It is defined as the average of the squared differences from the mean. Variance is often used to quantify the degree of variability or dispersion of a set of data points. A high variance indicates that the data points are spread out over a wide range of values, while a low variance indicates that the data points are clustered around the mean value. Variance is an important concept in statistics and probability theory and is widely used in fields such as finance, physics, and engineering.

Standard Deviation:

= √ 0.06%
= 2.41%

STDEV vs. STEVP
When analyzing a population sample, the STDEV formula estimates the standard deviation based on a set of data points. This formula is represented as:
STDEV = sqrt(sum((x – mean)^2) / (n-1))
Where x refers to the data point, mean is the average of the data set, and n is the number of data points in the sample.

Alternatively, when analyzing the entire population, the STDEVP formula is used to calculate the standard deviation directly. This formula is represented as:
STDEVP = sqrt(sum((x – mean)^2) / n),
Where x refers to the data point, the mean is the average of the data set, and n is the total number of data points in the population.

To summarize, STDEV is used to estimate the standard deviation of the whole population when only a sample is available. On the other hand, STDEVP is used to calculate the standard deviation directly when data for the entire population is available.

Standard Deviation Google Sheets Formula (Tutorial)

In the below example, we are going to calculate the standard deviation of stock price returns over 20 trading days; “STDEVP( Returns(20)).“

Cell B1 = “INTC” (Intel Corporation)

Step 1 – Data Request

Request historical close prices from the previous 40 Days.
We request more days than required to account for weekends and holidays. More about this here.

(GOOGLEFINANCE(B1,"close",today()-40 ,today()))

Step 2 – Sort, Filter, Clean and Create a dataset.

Step 2.1
Sort prices in descending order, then remove the date column and header rows, and limit the number of values returned to 20.

QUERY(GOOGLEFINANCE(B1,"close",today()-40 ,today()),"SELECT Col2 ORDER by Col1 DESC limit 20 LABEL Col2''")

Step 2.2

Create a copy of the query in Step 2.1, then offset the values returned by 1.
We do this to have the previous day’s return on the adjacent column.

=QUERY(GOOGLEFINANCE(B1,"close",TODAY()-(40) ,TODAY()),"SELECT Col2 ORDER by Col1 DESC limit 20 OFFSET 1 LABEL Col2''")

Step 2.3

Combine the queries from 2.1 and 2.2 into a single dataset by wrapping them in brackets{} and using “,” as a separator.
2.3 ={2.1, 2.2}

={QUERY(GOOGLEFINANCE(B1,"close",today()-40 ,today()),"SELECT Col2 ORDER by Col1 DESC limit 20 LABEL Col2''"),QUERY(GOOGLEFINANCE(B1,"close",TODAY()-(40) ,TODAY()),"SELECT Col2 ORDER by Col1 DESC limit 20 OFFSET 1 LABEL Col2''")}

Step 3 – Calculations

Step 3.1
Calculate daily returns over 20 days.
Daily return = ((Todays Close – Yesterdays Close) / Yesterdays Close) ,
which can also be expressed as (Todays Close / Yesterdays Close -1 ).
The dataset created in 2.3 is wrapped in a new query to calculate the daily returns over the 20 day period.

3.1 = Query( 2.3 , “Select Col1 / Col2 -1”)

=QUERY({QUERY(GOOGLEFINANCE(B1,"close",today()-40 ,today()),"SELECT Col2 ORDER by Col1 DESC limit 20 LABEL Col2''"),QUERY(GOOGLEFINANCE(B1,"close",TODAY()-(40) ,TODAY()),"SELECT Col2 ORDER by Col1 DESC limit 20 OFFSET 1 LABEL Col2''")},"Select Col1/Col2-1")

Step 3.2
Calculate the Standard Deviation.
Google Sheets has a built-in standard deviation function, for the final calculation, we wrap the query from step 3.1 in the STDEVP function;

3.2 = STDEVP(3.1)

=STDEVP(QUERY({QUERY(GOOGLEFINANCE(B1,"close",today()-80 ,today()),"SELECT Col2 ORDER by Col1 DESC limit 40 LABEL Col2''",1),QUERY(GOOGLEFINANCE(B1,"close",TODAY()-(80) ,TODAY()),"SELECT Col2 ORDER by Col1 DESC limit 40 OFFSET 1 LABEL Col2''",1)},"Select Col1/Col2-1 LIMIT 20"))

A Volatility Scan that returns The top 10 of the Most/Least Volative

The Volatility Scatter Plot Chart is used to evaluate the groupings and outliers of volatility in a watchlist. If you are not familiar with scatter plot charts, you can learn more about them in this post.

Standard Deviation Custom Function

Data Preperation

We start by fetching the date and close price columns from Google Finance.
Then, we use the QUERY function to remove header row. (detailed example)

QUERY(QUERY(GOOGLEFINANCE(B1,"close",TODAY()-100,TODAY()), "SELECT *", 1),"SELECT* OFFSET 1",0)

Function Parameters

The function is structured to calculate the Standard Deviation, utilizing two parameters:
range, period.

• range: The “range” parameter represents the date and close price data required for the calculation.
• period: This parameter specifies the length of the range for each Stdev Value.

Apps Script Code

/**
 * Calculates the Standard Deviation based on the provided price data range.
 * @param {array} range A range containing  date and close price price data from Google Finance.
 * range example Range example ,QUERY(QUERY(GOOGLEFINANCE(B1,"close",TODAY()-200,TODAY()), "SELECT *", 1),"SELECT* OFFSET 1",0)
 * @param {number} period The period for calculating the STDEV.
 * @return {array} The STDEV values along with corresponding dates.
 * @customfunction
 */

function STDEV_GOOGLEFINANCE(range, period) {
  // Check if the period is greater than the length of the range
  if (period > range.length) {
    throw "Error: The specified period exceeds the length of the range.";
  }

  const dates = range.map(row => new Date(row[0])); // Convert date strings to Date objects
  const closePrices = range.map(row => parseFloat(row[1])); // Parse close prices as floats

  // Initialize arrays to stdev values
  let stdevValues = [];


  // Calculate price changes
  let pctChange = [];
  for (let i = 1; i < closePrices.length; i++) {
    let priceChange = (closePrices[i]/closePrices[i- 1]-1);
      pctChange.push(priceChange); // Placeholder for gains
    }
  
  // Calculate moving standard deviation for each data point
  pctChange.forEach((_, index) => {
    if (index >= period - 1) {
      // Get the slice of percentage changes for the specified period
      const periodPctChange = pctChange.slice(index - period + 1, index + 1);

      // Calculate the mean
      const mean = periodPctChange.reduce((acc, val) => acc + val, 0) / period;

      // Calculate the squared differences
      const squaredDifferences = periodPctChange.map(val => Math.pow(val - mean, 2));

      // Calculate the variance
      const variance = squaredDifferences.reduce((acc, val) => acc + val, 0) / period;

      // Calculate the standard deviation
      const standardDeviation = Math.sqrt(variance);

      // Append the standard deviation value along with the corresponding date
      stdevValues.push([dates[index+1],standardDeviation]);
    }
  });

  // Prepare the final data structure with the date column
  const stdevData = [['Date','Standard Deviation'], ...stdevValues];

  return stdevData;
}

How To Use:

1. In Google Sheets open the “Extensions” menu and select “Apps Scripts”.
2. Paste the STDEV_GOOGLEFINANCE code into the script editor and save.
3. Go back to your Google Sheets document.
4. Enter “=STDEV_GOOGLEFINANCE()” in a cell to use the function.

Market Note

Given the unpredictability of prices in financial markets, it is vital to recognize that statistical measures like the standard deviation should be regarded as guiding tools rather than definitive indicators of price movement. The purpose of the standard deviation is to enable a more practical comprehension of the potential price “deviations” and what we can reasonably expect.

Happy Trading!