I looked at different sites to find an easy formula to calculate stock volatility. Everybody is using Excel or some specialized software. In all cases, calculation is done for one position only. I wrote the following query to calculate volatility for all stocks in the database:
select t.id, t.currticker,
stddev(ln(p.close_price/p1.close_price)) * sqrt(252) volatility
from tickers t, prices p, prices p1
where t.id = p.tickerid
and t.id = p1.tickerid
and p1.trade_date = (select max(p2.trade_date) from prices p2
where p2.tickerid = t.id
and p2.trade_date < p.trade_date)
and p.trade_date between current_date - 31 and current_date - 1
group by t.id, t.currticker
This query work on Postgresql (Version 12 and up, should work on earlier versions as well). To use Oracle, replace current_date with trunc(sysdate). To use with MS SQLServer or mysql you nned to change date functions as well.
Tables and columns used:
tickers: table of stocks,
columns: id: internal id,
currtickers: stock ticker.
prices: daily stock prices, adjusted for splits,
columns: tickerid: ticker id
close_price: ptice at closing,
trade_date: self-explaining.
This SQL calculates annualized volatility for the last 30 calendar days the way it's calculated on most financial sites. You can change parameters in the WHERE clause to change the range.
Posts
