Standard Deviation of Stock Price Returns Google Sheets Formula

Last Updated on 11/05/2023 by Ndanileka

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.

Historical Close Price Request,Google Sheets Formula
20-Day Price Returns bars, with red lines representing the standard deviation.

HOW TO CALCULATE STANDARD DEVIATION

Suppose there is a stock with the below price returns over a 20-day period.

HOW TO CALCULATE STANDARD DEVIATION

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%)2 + ( -0.38% – 0.03%)2 + (0.27% -0.03%)2 + (-2.04% -0.03%)2 + (-1.78% -0.03%)2 + (-3.12% – 0.03%)2 + (0.92% -0.03%)2 + ( 4.06% -0.03%)2 +……..
= 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

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 1Data 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()))
Historical Close Price Request, Google Sheets Formula
Historical Close Price Request, Google Sheets Formula

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''")
Historical Close Price Request, Google Sheets Formula
Historical Close Price Request, Google Sheets Formula

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''")
Historical Close Price Request, Google Sheets Formula
Historical Close Price Request, Google Sheets Formula

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''")}
Historical Close Price Request, Google Sheets Formula
Historical Close Price Request, Google Sheets Formula

Step 3Calculations

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")
Historical Close Price Request, Google Sheets Formula
20 Day Price Returns, Google Sheets Formula

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"))
Stock Price Return Standard Deviation, Google Sheets Formula
Stock Price Return Standard Deviation, Google Sheets Formula

USING THE STANDARD DEVIATION IN PRICE ANALYSIS

The standard deviation has various uses depending on the trading approach.
For instance, it can help assess the importance of a particular market move:

Sigma Spike

Function as a volatility filter for a shortlist:

Shortlist, Most and Least Volatile Stocks
Shortlist, Most and Least Volatile Stocks

Or as a visualization of general market volatility when tracking an index:

Single Stock Volatility of the S&P 100

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. This is a crucial concept to consider when using statistical measures in financial analysis, as it enables a more practical comprehension of the potential price “deviations”.

One comment

Leave a Reply