0

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

https://support.google.com/docs/answer/3093281?hl=en-GB

1

I believe I figured it out.

It's:

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

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.

1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.