Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating 1, 3, 6 months cumulative returns and volatility from daily price data

    I have a panel data which includes daily prices of stocks and daily returns of stocks for many firms. I want to get 1, 3, 6 months cumulative lag returns as well as volatility for the corresponsding time periods for each firm. For example for Firm A I want to get the 1 month lag return and volatility on Feb 1st Year 1 then March 1st then April 1st etc all the way to Year 10 (so total 120 1 month lag returns). Then apply the same procedure to 3 month lag returns and 6 months lags returns. Then performed the entire procedure to all firms in the data set.

    I have read about ways to do this using the 'keep' command, that involve dropping daily price data and only keeping the a single cumulative return for each month, however, I need to use the daily data later on so I will need a way to do this without using the 'keep' command. Could you help me get cumulative 1, 3, 6monthly returns and 1,3 and 6 month volatilties?
    Thank you for help.
    The following is how my data look for just one firm over period of one month.
    Permno is the firm bymber, date is date, prc is daily closing price, ret is daily return.




    ----------------------- copy starting from the next line -----------------------
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input double permno long date double(shrcd exchcd siccd prc ret)
    
    
    permno    date    shrcd    exchcd    siccd    prc    ret
    10001    03jan2017    1.1e+01    2.0e+00    4925    12.60000038    .003984079
    10001    04jan2017    1.1e+01    2.0e+00    4925    12.55000019    -.003968269
    10001    05jan2017    1.1e+01    2.0e+00    4925    12.69999981    .011952161
    10001    06jan2017    1.1e+01    2.0e+00    4925    12.69999981    0
    10001    09jan2017    1.1e+01    2.0e+00    4925    12.60000038    -.007873971
    10001    10jan2017    1.1e+01    2.0e+00    4925    12.75    .011904731
    10001    11jan2017    1.1e+01    2.0e+00    4925    12.69999981    -.003921584
    10001    12jan2017    1.1e+01    2.0e+00    4925    12.64999962    -.003937023
    10001    13jan2017    1.1e+01    2.0e+00    4925    12.64999962    0
    10001    17jan2017    1.1e+01    2.0e+00    4925    12.60000038    -.003952509
    10001    18jan2017    1.1e+01    2.0e+00    4925    12.64990044    .003960322
    10001    19jan2017    1.1e+01    2.0e+00    4925    12.64999962    7.8405e-06
    10001    20jan2017    1.1e+01    2.0e+00    4925    12.64999962    0
    10001    23jan2017    1.1e+01    2.0e+00    4925    12.69999981    .003952584
    10001    24jan2017    1.1e+01    2.0e+00    4925    12.64999962    -.003937023
    10001    25jan2017    1.1e+01    2.0e+00    4925    12.69999981    .003952584
    10001    26jan2017    1.1e+01    2.0e+00    4925    12.69999981    0
    10001    27jan2017    1.1e+01    2.0e+00    4925    12.69999981    0
    10001    30jan2017    1.1e+01    2.0e+00    4925    12.64999962    -.003937023
    10001    31jan2017    1.1e+01    2.0e+00    4925    12.64999962    0
    10001    01feb2017    1.1e+01    2.0e+00    4925    12.64999962    0
    10001    02feb2017    1.1e+01    2.0e+00    4925    12.69999981    .003952584
    end
    format %d date

  • #2
    You have edited the -dataex- output you posted: it looks like you simply overwrote the data part with the output of a list command. The result is not usable without considerable "surgery." Proper -dataex- output for this data would look like this:

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input double permno float date double(shrcd exchcd siccd prc ret)
    10001 20822 11 2 4925 12.60000038  .003984079
    10001 20823 11 2 4925 12.55000019 -.003968269
    10001 20824 11 2 4925 12.69999981  .011952161
    10001 20825 11 2 4925 12.69999981           0
    10001 20828 11 2 4925 12.60000038 -.007873971
    10001 20829 11 2 4925       12.75  .011904731
    10001 20830 11 2 4925 12.69999981 -.003921584
    10001 20831 11 2 4925 12.64999962 -.003937023
    10001 20832 11 2 4925 12.64999962           0
    10001 20836 11 2 4925 12.60000038 -.003952509
    10001 20837 11 2 4925 12.64990044  .003960322
    10001 20838 11 2 4925 12.64999962  7.8405e-06
    10001 20839 11 2 4925 12.64999962           0
    10001 20842 11 2 4925 12.69999981  .003952584
    10001 20843 11 2 4925 12.64999962 -.003937023
    10001 20844 11 2 4925 12.69999981  .003952584
    10001 20845 11 2 4925 12.69999981           0
    10001 20846 11 2 4925 12.69999981           0
    10001 20849 11 2 4925 12.64999962 -.003937023
    10001 20850 11 2 4925 12.64999962           0
    10001 20851 11 2 4925 12.64999962           0
    10001 20852 11 2 4925 12.69999981  .003952584
    end
    format %td date
    The term volatility has a technical meaning in finance, and I am not a finance person. From other posts on this Forum I have gleaned that at least some people mean standard deviation, a statistical term, when they say volatility. The code below embodies that assumption; if you had something else in mind, post back with an explanation.

    Code:
    //  CALCULATE MONTHLY DATE
    gen int mdate = mofd(date)
    format mdate %tm
    
    
    foreach lag of numlist 1 3 6 {
        sort permno mdate date
        rangestat (first) prc (sd) volatility_`lag' = prc, by(permno) ///
            interval(mdate -`lag' -1)
        by permno mdate (date): gen ret_`lag' = prc[1]/prc_first - 1
        drop prc_first 
    }
    To run this code you must install the -rangestat- command, written by Robert Picard, Nick Cox, and Roberto Ferrer, available from SSC.

    Note that because your data covers only a a bit over 1 month's data, the three and six month returns and volatility cannot be computed properly and are shown as the same as the 1 month values. That will not happen in your real data if you have sufficiently long time periods there. Also note that the results are put in every observation for a given month, not just the first of the month. If you find it bothersome having the extra copies of the results, just write a loop to replace the extras with missing values. But you may find it useful to keep the extras, depending on what you will be doing next with the data. Also note that for some months, you may not actually have any observation for the first of the month: your data skips some dates (presumably weekends and holidays?).

    Comment


    • #3
      Since you have asked for cumulative returns, there are two methods to get it, depending upon how you calculated the stock returns in the first place. See this post https://fintechprofessor.com/2017/12...d-comparisons/ for a distinction of the two methods.

      Assuming that you have generated returns with a simple percentage change in the share prices, the cumulative returns would be the successive products of (1+returns) and minus 1 at the end. To do so for the first month for each gvkey, I shall use 22 trading days using asrol (available from SSC). Please note that when using option add(1), asrol adds 1 to each return and then subtracts it back accordingly at the end.

      Code:
      ssc install asrol
      
      bys permno : asrol ret, stat(product) add(1) window(date 22)
      
      * for 3 months
      bys permno : asrol ret, stat(product) add(1) window(date 66) gen(cumri_3m)
      
      * for 6 months
      bys permno : asrol ret, stat(product) add(1) window(date 132) gen(cumri_6m)
      
      * For the volatility, it will be based again on returns, however,
      * since the frequency is daily, the measure is daily, and to
      * convert it to a monthly frequency, we shall multiply the daily
      * measure with the square root of 22, and the same goes for other frequencies.
      
      bys permno: asrol ret, stat(sd) window(date 22) gen(daily_vol)
      gen monthly_vol = sqrt(22) * daily_vol
      
      
      . list date  ret product21_ret daily_vol monthly_vol in 1/10
      
           +-----------------------------------------------------------+
           |      date          ret   product~t   daily_vol   monthl~l |
           |-----------------------------------------------------------|
        1. | 03jan2017    .00398408   .00398408           .          . |
        2. | 04jan2017   -.00396827   1.028e-10   .00562316    .026375 |
        3. | 05jan2017    .01195216   .01195216   .00796022   .0373367 |
        4. | 06jan2017            0   .01195216   .00679868   .0318886 |
        5. | 09jan2017   -.00787397   .00398408   .00763416   .0358074 |
           |-----------------------------------------------------------|
        6. | 10jan2017    .01190473   .01593624    .0081919   .0384234 |
        7. | 11jan2017   -.00392158   .01195216   .00788182    .036969 |
        8. | 12jan2017   -.00393702   .00796808   .00756677   .0354913 |
        9. | 13jan2017            0   .00796808   .00708619   .0332372 |
       10. | 17jan2017   -.00395251   .00398408    .0068552   .0321537 |
           +-----------------------------------------------------------+
      Last edited by Attaullah Shah; 28 Jul 2019, 15:16.
      Regards
      --------------------------------------------------
      Attaullah Shah, PhD.
      Professor of Finance, Institute of Management Sciences Peshawar, Pakistan
      FinTechProfessor.com
      https://asdocx.com
      Check out my asdoc program, which sends outputs to MS Word.
      For more flexibility, consider using asdocx which can send Stata outputs to MS Word, Excel, LaTeX, or HTML.

      Comment


      • #4
        Sir. Schechter
        Thank you for replying!
        I have tried your code and have an error saying 'option interval() required' r(198);

        How can I fix this? Please let me know how to solve it. Thank you again.

        Comment


        • #5
          Clyde Schechter gave an example with interval() option specified

          Code:
          rangestat (first) prc (sd) volatility_`lag' = prc, by(permno) ///        
          interval(mdate -`lag' -1)
          which you can rewrite

          Code:
          rangestat (first) prc (sd) volatility_`lag' = prc, by(permno) interval(mdate -`lag' -1)
          So, although you're not showing the exact code you used (FAQ Advice #12), we have to guess that you forgot to include something, perhaps the /// joining the two parts of the command.

          Comment


          • #6
            Sir. Cox and Sir. Schechter
            Thank you so much!!!! I have got results. It was a big help for me. Thank you again!

            Comment

            Working...
            X