Standard Deviation of Stock Price Returns Google Sheets Formula

Last Updated on 11/05/2023 by DEUS Harvest

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.

---

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%)² + ( -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

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"))

---