# googleFinance calculating the standard deviation of a stock over a time period

Can I calculate the standard deviation of a stock over a time period in GoogleFinance?

I believe I figured it out.

It's:

`````` =STDEV(INDEX(GoogleFinance("GOOGL","all",WORKDAY(TODAY(),-50),TODAY()),,3))
``````
• Any one know why this function rarely works and displays #REF most of the time? Jul 30, 2018 at 19:05

Here is a formula for computing historical stock volatility using log price changes (returns) over the last 50 days (B is the ticker column):

``````=STDEV(arrayformula(LN(
query(GoogleFinance(\$B2,"close",WORKDAY(TODAY(),-50),TODAY()),"select Col2 label Col2 ''")/
query(GoogleFinance(\$B2,"close",WORKDAY(TODAY(),-51),WORKDAY(TODAY(),-1)),"select Col2 label Col2 ''")
)))*SQRT(252)
``````

You need to use query() to remove the header from GoogleFinance results.

Notice that a stock gaining 1% every day will have a high price variance (the original question) but low (zero) volatility of returns.

I liked iggy's answer but holidays at the beginning or end of the time periods can cause errors. Here's an updated version, again to calculate historical volatility over the last 50 trading days using daily closing prices.

``````=STDEV(arrayformula(LN(
query(query(query(GoogleFinance(\$B2,"close",WORKDAY(TODAY(),-55),TODAY()),"select * ORDER BY Col1 DESC LIMIT 50"),"select * ORDER BY Col1 ASC"),"select Col2 label Col2 ''")/
query(query(query(GoogleFinance(\$B2,"close",WORKDAY(TODAY(),-55),TODAY()),"select * ORDER BY Col1 DESC LIMIT 51"),"select * ORDER BY Col1 ASC LIMIT 50"),"select Col2 label Col2 ''")
)))*SQRT(252)
``````

The 55 "workday" quantity is simply to ensure that there are at least 50 trading days within the dataset, thinking Thanksgiving, etc complicates things.

It was caused by my localisation of Google sheets :)

In Czech language correct formula is :

`=SMODCH.VÝBĚR(index(googleFinance("googl";"price";DNES()-720;DNES();"weekly");;2))`