NexusFi: Find Your Edge


Home Menu

 





Need Volatility & ATR formula's for Excel


Discussion in Traders Hideout

Updated
    1. trending_up 4,692 views
    2. thumb_up 6 thanks given
    3. group 3 followers
    1. forum 4 posts
    2. attach_file 0 attachments




 
Search this Thread

Need Volatility & ATR formula's for Excel

  #1 (permalink)
 
paps's Avatar
 paps 
SF Bay Area + CA/US
 
Experience: None
Platform: TS, TOS, Ninja(Analytics)
Trading: NQ CL, ES when volatile mrkts
Posts: 1,739 since Oct 2011
Thanks Given: 2,176
Thanks Received: 1,726

Guys if we have O/H/L/C and Volume could you give me formulas to calculate the following in excel:-
1) Volatility (Not Implied Volatility)
2) ATR

Started this thread Reply With Quote

Can you help answer these questions
from other members on NexusFi?
NexusFi Journal Challenge - May 2024
Feedback and Announcements
ZombieSqueeze
Platforms and Indicators
Request for MACD with option to use different MAs for fa …
NinjaTrader
 
Best Threads (Most Thanked)
in the last 7 days on NexusFi
Retail Trading As An Industry
66 thanks
NexusFi site changelog and issues/problem reporting
46 thanks
Battlestations: Show us your trading desks!
35 thanks
GFIs1 1 DAX trade per day journal
32 thanks
What percentage per day is possible? [Poll]
25 thanks

  #3 (permalink)
 
Fat Tails's Avatar
 Fat Tails 
Berlin, Europe
Market Wizard
 
Experience: Advanced
Platform: NinjaTrader, MultiCharts
Broker: Interactive Brokers
Trading: Keyboard
Posts: 9,888 since Mar 2010
Thanks Given: 4,242
Thanks Received: 27,102



paps View Post
Guys if we have O/H/L/C and Volume could you give me formulas to calculate the following in excel:-
1) Volatility (Not Implied Volatility)
2) ATR


A) Annualized volatility

first column: daily closes
second column: calculate the daily returns (percent change from prior day)
third column: logarithm of daily returns
fourth column: sample standard deviation of logarithm of daily returns over the last 252 days
fifth column: multiply sample standard deviation with the square root of 252 to obtain the annualized volatility

B) Average true range

Basically you take the true range and calculated an exponential or a simple moving averages of the true range.

first column: calculate Max(high, prior close)
second column: calculate Min( low, prior close)
third column: subtract second column from first column (this is the true range)
fourth column: calculate a N-period simple moving average from the third column

If you wish to use an EMA instead of the SMA for calculating the average, this is possible. The original ATR was based on an EMA, because Welles Wilder had to calculate it manually. The EMA is based on a simple recursive formula, so it is less work to calculate it manually than a SMA. Wilder was just lazy. With PCs you can use both EMA or SMA.

Reply With Quote
The following 6 users say Thank You to Fat Tails for this post:
  #4 (permalink)
 
paps's Avatar
 paps 
SF Bay Area + CA/US
 
Experience: None
Platform: TS, TOS, Ninja(Analytics)
Trading: NQ CL, ES when volatile mrkts
Posts: 1,739 since Oct 2011
Thanks Given: 2,176
Thanks Received: 1,726


Fat Tails View Post
A) Annualized volatility

first column: daily closes
second column: calculate the daily returns (percent change from prior day)
third column: logarithm of daily returns
fourth column: sample standard deviation of logarithm of daily returns over the last 252 days
fifth column: multiply sample standard deviation with the square root of 252 to obtain the annualized volatility

B) Average true range

Basically you take the true range and calculated an exponential or a simple moving averages of the true range.

first column: calculate Max(high, prior close)
second column: calculate Min( low, prior close)
third column: subtract second column from first column (this is the true range)
fourth column: calculate a N-period simple moving average from the third column

If you wish to use an EMA instead of the SMA for calculating the average, this is possible. The original ATR was based on an EMA, because Welles Wilder had to calculate it manually. The EMA is based on a simple recursive formula, so it is less work to calculate it manually than a SMA. Wilder was just lazy. With PCs you can use both EMA or SMA.

Wow...thanks @Fat Tails

Started this thread Reply With Quote
  #5 (permalink)
 
paps's Avatar
 paps 
SF Bay Area + CA/US
 
Experience: None
Platform: TS, TOS, Ninja(Analytics)
Trading: NQ CL, ES when volatile mrkts
Posts: 1,739 since Oct 2011
Thanks Given: 2,176
Thanks Received: 1,726

playing around with few values...will post them here at a later date hopefully..

thnx guys

Started this thread Reply With Quote





Last Updated on May 14, 2014


© 2024 NexusFi™, s.a., All Rights Reserved.
Av Ricardo J. Alfaro, Century Tower, Panama City, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada)
All information is for educational use only and is not investment advice. There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
About Us - Contact Us - Site Rules, Acceptable Use, and Terms and Conditions - Privacy Policy - Downloads - Top
no new posts