Announcement

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

  • Rolling 24 month expected shartfall and Var on panel data

    Hi,

    I have a large panel data set with securities (id) and time (months). I want to calculate expected shortfall at 5% significance level on a rolling 24 month basis. So approximately calculating the average of two worst returns in a rolling 24 month period for each id.

    Trying to find out if I can use rangestat to do this exercise because I have over a million observations.

    Any help/suggestions is much appreciated.

    Thanks,
    john

  • #2
    As often flagged here not many of the most active members here have financial data within their comfort zone.

    But the answer is certainly yes; you can do that with rangestat (SSC, as you are asked to explain; FAQ Advice #12)

    It is not a wired-in statistic, so you need to write your own Mata code.

    Here is my guess at what you want because I don't see a precise recipe, just what is supposedly an approximation.

    Without example data, I reached for a convenient panel dataset.


    Code:
    webuse grunfeld , clear
    gen ln_invest = ln(invest)
    
    mata:  
    mata clear
    real rowvector prettybad(real colvector X) {
        X = sort(X, 1)
        return(rows(X) == 1 ? X[1] : (X[1] + X[2])/2)
    }
    end
    
    rangestat (prettybad) ln_invest, interval(year -4 0) by(company)
    
    list year *invest prettybad if company == 1
    
        +--------------------------------------+
         | year   invest   ln_inv~t   prettyb~1 |
         |--------------------------------------|
      1. | 1935    317.6   5.760793   5.7607927 |
      2. | 1936    391.8   5.970751    5.865772 |
      3. | 1937    410.6    6.01762    5.865772 |
      4. | 1938    257.7   5.551796   5.6562943 |
      5. | 1939    330.8   5.801514   5.6562943 |
         |--------------------------------------|
      6. | 1940    461.2   6.133832   5.6766551 |
      7. | 1941      512   6.238325   5.6766551 |
      8. | 1942      448   6.104793   5.6766551 |
      9. | 1943    499.6   6.213808   5.9531536 |
     10. | 1944    547.5   6.305362   6.1193125 |
         |--------------------------------------|
     11. | 1945    561.2   6.330077   6.1593003 |
     12. | 1946    688.1   6.533934   6.1593003 |
     13. | 1947    568.9   6.343705   6.2595849 |
     14. | 1948    529.2   6.271367   6.2883644 |
     15. | 1949    555.1   6.319148   6.2952573 |
         |--------------------------------------|
     16. | 1950    642.9   6.465989   6.2952573 |
     17. | 1951    755.9   6.627909   6.2952573 |
     18. | 1952    891.2   6.792569   6.2952573 |
     19. | 1953   1304.4   7.173499   6.3925686 |
     20. | 1954   1486.7   7.304314   6.5469491 |
         +--------------------------------------+
    Last edited by Nick Cox; 12 Oct 2017, 13:14.

    Comment


    • #3
      Hi Nick,

      Thanks, this is very helpful. I am including some sample data for illustration. ex_ret is the monthly return of the securities.

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input long id float(ex_ret newdate) int year byte month
       5    .007135526 408 1994  1
       5     .04686265 409 1994  2
       5     -.0521462 410 1994  3
       5    -.03191356 412 1994  5
       5   .0003904014 413 1994  6
       5     .02415652 414 1994  7
       5     .05768865 415 1994  8
       5    .006272078 416 1994  9
       5    -.04605908 417 1994 10
       5   -.033803094 418 1994 11
       5    -.07200204 419 1994 12
       5    -.08217537 420 1995  1
       5    -.04307023 421 1995  2
       5    .016433334 423 1995  4
       5   -.006503144 424 1995  5
       5 -.00028255107 425 1995  6
       5    .004845794 426 1995  7
       5        -.0043 428 1995  9
       5    -.05573758 429 1995 10
       5    -.08221418 430 1995 11
       5    .002151282 431 1995 12
       5  -.0004807766 432 1996  1
       5   -.008338808 433 1996  2
       5    -.07077898 434 1996  3
       5   -.019309897 435 1996  4
       5    -.03222314 436 1996  5
       5     .09798147 437 1996  6
       5    -.05392935 439 1996  8
      11         .0594 408 1994  1
      11        -.0275 409 1994  2
      11        -.0164 410 1994  3
      11         -.004 411 1994  4
      11         .0343 412 1994  5
      11         .0201 413 1994  6
      11         .0049 415 1994  8
      11        -.0037 416 1994  9
      11        -.0195 417 1994 10
      11        -.0149 418 1994 11
      11        -.0192 419 1994 12
      11         .0007 420 1995  1
      11         .0028 421 1995  2
      11         .0173 423 1995  4
      end
      format %tm newdate

      Comment


      • #4
        One follow up question. If I want to calculate expected shortfall for a bunch of factor returns for e.g. Factors k1 to K10 with similar monthly time series returns as ex_ret, is there a way to do this at one go.

        Appreciate any help/suggestions.

        Best,
        john.

        Comment


        • #5
          Yes indeed. It's explicit in the help and examples for rangestat that several results can be calculated in one call, and that theme is repeated in many examples here.

          Comment


          • #6
            Thanks Nick. Another follow up question. I am now trying to replicate my analysis for expanding window ES and VAR @5% confidence interval (starting with 24 months and increasing by one month increment based on data available for each id).

            Any help/suggestion is much appreciated.

            Best,
            John.

            Comment

            Working...
            X