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 1 – Data 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 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 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