Google Sheets Volatility Calculator
My Google Sheet will give you a stock's realized volatility in seconds
- Free, instant download of up to date stock price history
- Calculates realized volatility for a given symbol
- Customizable price history and volatility length
- Graphs both price vs volatility
- Unlocked and editable
Volatility Calculator
Enter your details and I'll send the workbook straight to your inbox.
What is Volatility?
Volatility is a measurement of uncertainty. You've probably already heard this term in an every day setting where referencing a behavior can be said to be "so volatile". The context may have been an observed behavior that was referenced as being wild and unpredictable.
In finance it's the same concept; the behavior in our case is a stock's price movement where it can be said to be highly volatile if observed to be unstable and unpredictable.
High Volatility
Low Volatility
The above examples show the difference between a stock that is highly volatile and one that exhibits low volatility.
While NIO's current vol is 87% it has spent almost a third of the time seen in the chart at or above 100% volatility. And the price range has gone from $10 to over $65 (+550%) in the same period.
Conversely, take a look at the Dow Jones ETF (DIA) pictured for the same range. It has a current vol of 10%, topping out at 30% but having a price range of $260 to $340 (+31%). This is the type of impact that high volatility makes to stock prices.
Why is Volatility Important to Traders?
Of all the inputs that go into valuing an option contract the two most uncertain factors are the underlying price itself and the volatility of the underlying — or more specifically the volatility that is expected for the underlying from now until the expiration date.
For option traders, volatility means opportunity. When considering the strike price of an out-of-the-money option contract, more volatility means more chances that the option will be profitable by the expiration date. For example, take NIO above. If you didn't have any insight into the volatility implied by the market prices, would you have bought a $50 call option that expires in 4 months with the stock trading at $12? It seems very unlikely to bet on the stock going up by 400% in that time. But when you know that prices are implying a volatility of 150% it makes the opportunity of that move seem more likely.
And because of the increase in likelihood that an option will be profitable, options that are perceived to have more chance of being in-the-money will be more valuable — hence the option premiums will be higher relative to a comparable stock that has low volatility.
Generally, higher volatility = higher option prices.
Implied Volatility vs Historical Volatility
When the volatility of the underlying is expected to increase, the demand for these options increases their prices in the market. The volatility that is derived from the option bid/ask prices is called the implied volatility. Implied volatility is calculated from an option pricing model where instead of generating a theoretical price, the model uses the market price as the input and reverses the calculation to derive the volatility.
Historical Volatility
Also known as "realized" or "statistical" volatility. Taken from the closing prices of a stock and shows where the stock's volatility currently is. Historical = past volatility.
Implied Volatility
Derived from the market prices of the option contracts. The estimate of where the market believes the stock's realized volatility will be from now until expiration. Download my option workbook for the method. Implied = future volatility.
Can You Profit from Volatility?
You've probably laughed at the old adage of buying low and selling high when being referenced to trading stocks. What is high and low? A stock can't trade lower than zero but it can keep going up and up.
Volatility, however, tends to be a mean reverting asset. Not all of the time, but certainly enough of the time to justify using it in your decision making. Take a look at this selection of implied volatility graphs for stocks, index and commodity ETFs:
AMZN Stock
SLV ETF
SPY ETF
TSLA Stock
I'm not saying that you can predict the direction of volatility at any point in time but the ebbs and flows of the movement tends to revert to an average — more often when volatility spikes. You will often see a huge bump in implied volatility followed by a drop.
These are opportunities many retail traders look for — selling options when volatility is high (expensive) and buying options when volatility is low (cheap).
Earnings Plays and the IV Crush
Macroeconomic events and surprise company announcements are example drivers for increased volatility. But these types of events aren't known ahead of time.
But we do know when publicly traded companies release their earnings results — every quarter of every year.
Every 3 months the top companies go through periods of increased volatility in the lead up to their earnings announcements. Company results aren't fully known until publicly released, which in itself creates uncertainty. As unexpected results can cause massive price swings, option traders try and anticipate post earnings stock price movements, which drives up the prices (and hence implied volatility) of the options that expire after those earnings results.
The first thing new option traders think of when it comes to earnings plays are long straddles. That is, buying a call and a put at the same strike price, which creates an each way bet on the stock so that if it does move wildly in one direction you'll be covered either way. This isn't a bad idea in itself but straddles come at a cost as you're buying the options. This means that the stock has to move more than the strike price plus the total premium you've paid. So, straddles are most effective when prices and volatility are low.
If volatility IS indeed low pre-earnings, then yes, long option strategies make sense. However, what you'll notice is that most stocks experience increasing levels of volatility in the lead up to their earnings release. This volatility greatly inflates the option prices making strategies such as straddles much less attractive — the more you have to pay increases your breakeven points and the amount the stock has to travel before you start making any money.
The IV Crush
Take a look at this graph, which will help me explain what an IV Crush is:
This is the at-the-money term structure for United States Steel (X). It plots the ATM volatility at each option expiration. This graph is telling you where the market believes volatility will be at each of the dates along the x-axis. Notice the massive spike 2 days out?
This spike is because United Steel are releasing their quarterly earnings numbers tomorrow after market. After the numbers are out and all the relevant information is now with the market, volatility is expected to drop in the following expirations. This drop in volatility is what traders refer to as IV crush (implied volatility crush).
Option traders look to take advantage of the vol crush by using short option strategies prior to the announcement and then looking to exit or buy back the options immediately after the earnings are out. These strategies are most effective when the stock price movement post earnings is less than the expected move as indicated by the implied volatility.
The two most common market neutral earnings plays are Short Iron Condor and Long Double Calendar spreads. Both are market neutral — meaning you're not biased in either direction regarding price movement. But both require the underlying stock to stay within the upper bounds of the strike selection.
If the earnings numbers are a huge surprise to the market then these levels will be tested and could result in the strategy hitting its maximum threshold in one move. I'm a bit cautious these days playing earnings due to the lottery style of trading it is — one day you're in the middle of a condor range and the next morning you're outside the long strike with little opportunity to adjust.
Iron Condors and Double Calendars are a key strategy used in the Trading as a Business course found on this site. If you'd like to know more, send me an email and start a conversation.
57 Comments
Peter January 14th, 2010 at 11:29pm
Mmm, works fine for me. What symbol are you trying? Can you retrieve that data directly from the Yahoo! site?
Kyle January 12th, 2010 at 6:50am
"Error! Symbol not found, or Yahoo! has changed the data format".
Have they changed the code?
Peter January 11th, 2010 at 6:16am
Yep, the lookback period is configurable, so you can enter 100 if you like...I chose 50 as a default arbitary value.
Milos January 11th, 2010 at 3:58am
Shouldn't the rolling window be bigger than 50? When the sample is small you can get evolution of volatility just by chance - Say you had one day very extraordinary large observation, this was in day 1, and on day 51 you have relatively small observation. Once you delete day one and add day 51 you have a change and volatility become time varying. I have seen that in other places 100 day rolling window is used, that is why I am asking
Artun January 5th, 2010 at 11:32pm
There are couple of obvious fixes required to fit into actual trading volatility calculation, I think the most obvious one is there are not 365 trading days, so you need to use 252 trading days for the sqrt part.
Dave November 7th, 2009 at 12:30pm
Peter - You're welcome. I thought that might be the case. The Offset() method of defining a range is an incredibly powerful tool, one which seems to be little known or used, even among savvy Excel users. I was one of them ... a whole new world was opened to me when I "discovered" it. Keep up the good work! Dave
Peter November 5th, 2009 at 2:41pm
Hi Dave,
Volatility Days certainly is used in the calculation...it's written into the Macro...not in the formula. When you hit the "Extract Data" button the array for the volatility calculation changes according to what you've entered into the "Volatility Days" cell.
Having said that...I do appreciate your formula below. The reason I included it in the Macro was because I didn't know how to do it with a formula ;-) But now I know, thanks a lot...very useful!
Dave November 5th, 2009 at 11:05am
Wont' accept less than symbol. =IF(ROW() is less than (Days+11),"",STDEV(OFFSET(C100,0,0,-Days,1))*SQRT($B$2)). Copy it to all the cells in column D.
Dave November 5th, 2009 at 11:00am
=IF(ROW()<Days+11,"",STDEV(OFFSET(C100,0,0,-Days,1))*SQRT($B$2)) and copy it to all the cells in column D.
Dave November 5th, 2009 at 10:57am
Clearly the "Volatiliy Days" variable isn't used in the volatility calculations, but it can be. Assign the name "Days" to cell B3, then relace the formulas in cell D100 with =IF(ROW()<Days+11,"",STDEV(OFFSET(C100,0,0,-Days,1))*SQRT($B$2)) and copy it to all the rest of the relevant cells in column D. Now you can vary the number of volatility days and the volatility will change accordingly.
Add a Comment