+ Reply to Thread
Results 1 to 10 of 10

Computing monthly standard deviation from daily data

Hybrid View

  1. #1
    Registered User
    Join Date
    04-06-2014
    Location
    Milano
    MS-Off Ver
    Excel 2013
    Posts
    7

    Computing monthly standard deviation from daily data

    Hi guys,
    as the title suggests, I have to compute monthly standard deviation (volatility) from daily data.
    The problem consists on the fact that the days during a month are not constant and change over time.
    I attach the excel file to allow you to help me.
    Price_Time_Series.xlsx
    In this xls file, you can find dates in column A, price in column B and returns in column C.
    I want to compute standard deviation at the end of the month for stock returns (column C), by considering all availables days.
    As you can note from the file, the days change over time and they are note constant.
    How can I compute standard deviation of them?
    Can you help me, please?
    Thanks a lot!

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Computing monthly standard deviation from daily data

    Use a pivot table: group your Date field by year / month, use Returns as your value field, summarised by StdDev.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    04-06-2014
    Location
    Milano
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Computing monthly standard deviation from daily data

    I thought about this, but I think it is not worthwhile, because I've many time series like the example one and I need to find some function or macro to simplify that process.
    Anyway, thanks to mind my problem!

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Computing monthly standard deviation from daily data

    Hi,

    Does the attached help
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    04-06-2014
    Location
    Milano
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Computing monthly standard deviation from daily data

    Hi Richard,
    can you explain me how to use that sheet?
    when I open that file, SD is equal to an error as "#NAME?".

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Computing monthly standard deviation from daily data

    Strange,

    I wonder if it's your regional settings for the 'list separator'. In the UK we tend to use the comma as a separator, I think on continental Europe you tend to use the semi colon ;

    There are 6 names defined in the workbook, look in the Name manager. Which separators do you see?

    If you see commas, change the , in each range to your list separator. I'm surprised that Excel doesn't do this automatically.

  7. #7
    Registered User
    Join Date
    04-06-2014
    Location
    Milano
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Computing monthly standard deviation from daily data

    no, that's ok. Excel changed automatically comma with the semi-colon;
    I think the problem in this case is in the Name Manager, because when I click on SD formula I find:

    =_xlfn.STDEV.P(SD_Data)
    and I think that on my PC SD_DATA is not defined.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Computing monthly standard deviation from daily data

    Hi,

    Which OS and version of Excel are you in fact using?

    See this http://office.microsoft.com/en-gb/ex...010204569.aspx

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Computing monthly standard deviation from daily data

    ...try

    STDEVP rather than STDEV.P

  10. #10
    Registered User
    Join Date
    04-06-2014
    Location
    Milano
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Computing monthly standard deviation from daily data

    My version is MS Excel 2007.
    Anyway I solved replacing STDEVP with DEV.ST.P.
    The problem consisted on the fact that Excel didn't convert English formula with the Italian one.
    Thanks a lot Richard for the help.
    You have been help to me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Computing the monthly mean value from daily data
    By Quantopic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2014, 03:47 PM
  2. Help with tidal data - computing daily max and min of sin curve
    By rlowgren in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2014, 04:02 PM
  3. Daily Standard Deviation
    By Andul in forum Excel General
    Replies: 0
    Last Post: 08-08-2011, 05:55 AM
  4. Data with fixed mean and standard deviation
    By Klorgrodan in forum Excel General
    Replies: 2
    Last Post: 02-10-2009, 10:23 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1