+ Reply to Thread
Results 1 to 13 of 13

STDEV and OFFSET functions to calculate moving volatility

  1. 07-02-2014, 12:42 PM #1
    m4rsellus
    m4rsellus is offline
    Registered User
    Join Date
    07-02-2014
    Location
    London
    MS-Off Ver
    2013
    Posts
    3

    STDEV and OFFSET functions to calculate moving volatility

    I am trying to obtain values for the moving volatility of stock prices using STDEV and OFFSET functions. I have a set of prices for one stock over 50 consecutive days (column B from B5 to B54). I cannot simply use STDEV because I need to change the number of days in the volatility calculation (in cell D1) and the spreadsheet needs to automatically adapt to this. First I calculate Ui - change in the stock price LN(Underlying/Underlying-1) - which starts in C5 until C54 (C5 is zero, so actual Ui starts in C6). Then I calculate moving volatility of Ui (below formula for day 2 - in cell D6) using this formula:

    =STDEV(OFFSET(C6,0,0,$D$1))*SQRT(365)

    But this yields incorrect results. The correct results I am aiming to achieve are in column E. In order to calculate annual volatility I multiply daily volatility by SQRT(365).

    Any help to get the right formula which achieves results in column E would be greatly appreciated. Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-02-2014, 12:51 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: STDEV and OFFSET functions to calculate moving volatility

    Your standard deviation is looking FORWARDS not backwards... For example, D6 is calculating the stdev on C6:C10... into the future. You need to modify the equation slightly

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    and you'll get your expected result.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 07-02-2014, 01:06 PM #3
    m4rsellus
    m4rsellus is offline
    Registered User
    Join Date
    07-02-2014
    Location
    London
    MS-Off Ver
    2013
    Posts
    3

    Re: STDEV and OFFSET functions to calculate moving volatility

    Thank you very much Glenn!! That resolves the problem, but now I get the #REF! error for the initial cells once I increase the length of the days, e.g. to 10. Do you know how to modify the formula to remove this problem?

    I've tried changing rows from 0 to C:C and COUNT(C:C) but both yield the wrong answer. Thank you for your help!
    Reply With QuoteRegister To Reply
  4. 07-02-2014, 01:20 PM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: STDEV and OFFSET functions to calculate moving volatility

    As set up currently, the first few days worth are going to be nonsense, as they are looking back to cells with no data. So D6 is looking back to calculate the STDEV of C2 to c6. Once you increase the interval to 7 or more, it'll be trying to go back to row zero, or lower; which it can't.

    I suppose it is essential to get it working properly from Day 1??
    Reply With QuoteRegister To Reply
  5. 07-02-2014, 01:36 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: STDEV and OFFSET functions to calculate moving volatility

    I've had another look at this & this seems to fit the bill, even when you increase the day range

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by Glenn Kennedy; 07-02-2014 at 01:41 PM.
    Reply With QuoteRegister To Reply
  6. 07-02-2014, 01:37 PM #6
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: STDEV and OFFSET functions to calculate moving volatility

    In E6

    =STDEV(C6:INDEX(C:C,MAX(ROW()-$D$1,5)))*SQRT(365)
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  7. 07-02-2014, 01:56 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: STDEV and OFFSET functions to calculate moving volatility

    Ace_XL... Something strange happened on my PC when I tried to reply a moment or two ago. So, I'll try again.

    Your equation is certainly nicer than mine, but (as written) is giving the STDEV of 6 days, not the 5 specified in D1...
    Reply With QuoteRegister To Reply
  8. 07-02-2014, 01:59 PM #8
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: STDEV and OFFSET functions to calculate moving volatility

    @ Glenn - you're right..my bad. It should be

    =STDEV(C6:INDEX(C:C,MAX(ROW()-$D$1+1,5)))*SQRT(365)
    Reply With QuoteRegister To Reply
  9. 07-02-2014, 02:05 PM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: STDEV and OFFSET functions to calculate moving volatility

    There you go, m4rsellus - two solutions for the price of one...
    Reply With QuoteRegister To Reply
  10. 07-02-2014, 02:10 PM #10
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: STDEV and OFFSET functions to calculate moving volatility

    Could this work?

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  11. 07-02-2014, 02:31 PM #11
    m4rsellus
    m4rsellus is offline
    Registered User
    Join Date
    07-02-2014
    Location
    London
    MS-Off Ver
    2013
    Posts
    3

    Re: STDEV and OFFSET functions to calculate moving volatility

    Absolute superstars!!!! Thank you very very much for the help, all works well now
    Reply With QuoteRegister To Reply
  12. 07-02-2014, 02:34 PM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: STDEV and OFFSET functions to calculate moving volatility

    If that's it solved, can you please mark the thread as solved and (preferably) say thanks by clicking the Add Reputation button at the foot of the post(s) of those who helped.
    Reply With QuoteRegister To Reply
  13. 07-02-2014, 04:30 PM #13
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: STDEV and OFFSET functions to calculate moving volatility

    Thank you...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Stdev / offset function to ignore #N/A
    By joshnathan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2014, 08:44 AM
  2. Offset (volatility)
    By gregg141 in forum Excel General
    Replies: 2
    Last Post: 03-16-2011, 11:34 AM
  3. Calculate Volatility ?
    By Simon-ch in forum Excel General
    Replies: 0
    Last Post: 11-18-2008, 06:28 AM
  4. [SOLVED] Using the OFFSET and COUNT functions to get a moving total
    By Eduardo in forum Excel General
    Replies: 2
    Last Post: 10-31-2005, 10:05 PM
  5. AVERAGE and STDEV functions with logic
    By t-rung in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-26-2005, 02:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1