Standard Deviation of Stock Price Returns Google Sheets Formula

What Is 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 mean. 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.

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 their average price return of the pervious 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%

Calculate the variance :
Since Standard Deviation is the square root of variance, we must first find the variance of each price return and then divide the sum of the squares by 20.
=(-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%

Standard Deviation:

= √ 0.06%
= 2.41%



Standard Deviation for Analysis

The standard deviation has different use cases, depending on the overall trading methodology. For instance, it can be used determine the significance of a particular move, as a volatility scan/filter for a shortlist or to measure the risk profile of a potential trade.
The scatter plot chart below illustrates how the standard deviation can be utilized as a market monitor for general volatility.



Standard Deviation Google Sheets Calculation

In 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 in order 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.
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 in order have the previous days 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 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")

Step 3.2
Calculate 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"))

Complete.

View and copy the spreadsheet on the link below and tailor the data to your needs.

2 comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.