**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