Announcement

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

  • Rolling Geometric Mean

    Dear Sir or Madam,

    for most of my problems I found a proper solution in an earlier posts. But since weeks I´m struggeling with calclutating a rolling geometric mean for daily stock returns. My data is as follows: Panel-Data. 2.200 Companies/Stocks (variable:permno). Date variable DDMMYYY (but with gaps, just trading days variable:date). Trading day - variable that counts from 1 to T - with no gaps (variable:trading_day), daily stock return (variable:ret). Now I want to calculate a rolling geometric mean over the next 22 days for each company . I`m really don`t know how to combine the codes to solve this problem. I tried the use runby but after a backcheck my outcome is always wrong. Hopefully tehre is someone out there who can help me solving this problem.

    Thanks a lot in advance.

    Best regards

    Stefanie Leupold

  • #2
    There have been many relevant posts here, but first we need to establish exactly what you want. People using your kind of data often add 1, take the geometric mean of that and then subtract 1 from the result. Calling this recipe the "geometric mean" seems a little perverse to me, but there can be good reasons for doing it.

    If that's what you want then please tell us. I think this is what (e.g.) asrol from SSC does.

    Otherwise if you are really after a geometric mean -- for input that is always positive -- then please tell us and code suggestions can follow.

    Comment


    • #3
      Dear Mr. Cox,

      thank you for your answer. I think I`m looking for the as you called it "perverse version" of the "geometric" mean. I´ll now check the asrol command and try to figure out how it works. Thank you very much so far!

      Comment


      • #4
        I rustled something up with rangestat (SSC) -- which you must install before you can use it.

        Code:
        clear
        set obs 50
        set seed 2803
        gen id = ceil(_n/25)
        bysort id : gen time = _n
        gen return = runiform(-1, 1)
        
        mata :
        
        mata clear
        real scalar gmean (real vector X) {
            return(exp(mean(log(X :+ 1)) - 1))
        }
        
        end
        
        rangestat (gmean) return (count) return, int(time -21 0) by(id)
        list, sepby(id)
        Here I look back (window is today PLUS the previous 21 days), but rangestat allows forward-looking windows too.

        (I am absolutely no authority on financial data or what people do with them, being a geographer on the physical or environmental side. So, check the recipe very carefully.)

        This code does absolutely nothing smart about gaps.

        Comment


        • #5
          Thank you very much for your reply. It seems to be exactly what I was looking for.

          Comment


          • #6
            Good, but triple check the formula please. Also, if you have a worked example from an authoritative text with known answer, do test it. (I have a financial statistics text accessible now, which may be helpful.)

            Comment


            • #7
              Just one more question about the asrol command. The window is always backward-looking. Is there an option that allows for forward-looking windows in this command?

              Comment


              • #8
                Solved this problem by my own. Combined this command with rangestat and that works perfectly now. Thank you so much Mr. Cox!

                Comment


                • #9
                  Dear Nick, Do you have any plan to add this function (gmean) to -rangestat- command?
                  Ho-Chuan (River) Huang
                  Stata 17.0, MP(4)

                  Comment


                  • #10
                    Here is an example of the geometric mean with asrol.
                    Code:
                    *Install asrol from ssc
                    ssc install asrol
                    
                    * Use the SP500 data for our example
                    sysuse sp500, clear
                    
                    * declare the data as time-series
                    tsset date
                    gen returns = d.close/l.close
                    
                    *  Find geometric mean of returns in a rolling window of 22 days,
                    *  adding one to the returns before calculation, the same is automaticallysubstracted
                    *  back
                    
                    asrol returns, stat(gmean) window(date 22) add(1)
                    
                    list date close returns gmean22_returns in 1/20
                         +---------------------------------------------+
                         |      date     close     returns   gmean22~s |
                         |---------------------------------------------|
                      1. | 02jan2001   1283.27           .           . |
                      2. | 03jan2001   1347.56    .0500986   .05009861 |
                      3. | 04jan2001   1333.34   -.0105525   .01932206 |
                      4. | 05jan2001   1298.35   -.0262424   .00390182 |
                      5. | 08jan2001   1295.86           .   .00390182 |
                         |---------------------------------------------|
                      6. | 09jan2001    1300.8    .0038122   .00387941 |
                      7. | 10jan2001   1313.27    .0095864   .00501822 |
                      8. | 11jan2001   1326.82    .0103177   .00589953 |
                      9. | 12jan2001   1318.55   -.0062329   .00415731 |
                     10. | 16jan2001   1326.65           .   .00415731 |
                         |---------------------------------------------|
                     11. | 17jan2001   1329.47    .0021256   .00390312 |
                     12. | 18jan2001   1347.97    .0139153   .00501069 |
                     13. | 19jan2001   1342.54   -.0040282   .00410311 |
                     14. | 22jan2001    1342.9           .   .00410311 |
                     15. | 23jan2001    1360.4    .0130315   .00491152 |
                         |---------------------------------------------|
                     16. | 24jan2001    1364.3    .0028668   .00474097 |
                     17. | 25jan2001   1357.51   -.0049769   .00024035 |
                     18. | 26jan2001   1354.95   -.0018858   .00096753 |
                     19. | 29jan2001   1364.17           .   .00347854 |
                     20. | 30jan2001   1373.73    .0070079   .00377218 |
                         +---------------------------------------------+

                    The above output is from the existing version fo asrol. I have a plan to add the following to the next version of asrol for improving the calculation of geometric mean and products.

                    The next version of asrol improves the calculation of product of values and the geometric mean. Since both the statistics involve the multiplication of values in a given window, the presence of missing values and zeros present a challenge to getting desired results. Following will be the defaults in asrol to deal with missing values and zeros:

                    7.1 : Missing values are ignored when calculating the product or the geometric mean of values.

                    7.2 : To be consistent with Stata's default for geometric mean calculations, (see ameans), the default in asrol is to ignore zeros and negative numbers. So the geometric mean of 0,2,4,6 is 3.6342412, that is [2 * 4 * 6]^(1/3). And the geometric mean of 0,-2,4,6 is 4.8989795, that is [4 * 6]^(1/2).


                    7.3 : Zeros are considered when calculating the product of values. So the product of 0,2,4,6 is 0

                    Two variations are possible when we want to treat zeros differently. These are discussed below:

                    7.4 Option ignorezero: This option can be used to ignore zeros when calculating the product of values. Therefore, when the zero is ignored, the product of 0,2,4,6 is 48

                    7.5 Option add(#) : This option adds a constant # to each values in the range before calculating the product or the geometric mean. Once the required statistic is calculated, then the constant is substracted back. So using option add(1), the product of 0,.2,.4,.6 is 1.6880001 that is [1+0 * 1+.2 * 1+.4 * 1+.6] - 1 and the geometric mean is .280434 is [(1+0 *1+.2 * 1+.4 * 1+.6)^(1/4)] - 1.

                    The Stata's ameans command calculates three types of means, including the geometric mean. The difference between asrol' gmean function and the Stata ameans command lies in the treatment of option add(#). ameans does not subtract the constant # from the results, whereas asrol does.
                    Last edited by Attaullah Shah; 10 Nov 2018, 01:33.
                    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


                    • #11
                      In #9 River Huang asked

                      Do you have any plan to add this function (gmean) to -rangestat- command?
                      Yes and no. Code for various extensions, including geometric mean as classically defined, is already agreed privately between the authors, but I don't think either of us still working on the program (@Robert Picard, myself) has a firm idea when a revised version will be released.

                      No in the sense that geometric mean return is not on the list, except that it might well be an example in the help. What we want to stress most is that rangestat is extensible: you can write your own Mata functions to do many things not provided directly. We are wary of bloating it by adding too many extra bells and whistles.

                      There are many good reasons for that, not least wanting to focus on a core group of statistics used across statistical science, rather than specialised measures.

                      Comment


                      • #12
                        Dear Nick, I see, and thank you for the reply.
                        Ho-Chuan (River) Huang
                        Stata 17.0, MP(4)

                        Comment


                        • #13
                          Dear Mr. Shah ,

                          thought I found a soulution for a forward-looking window by combining asrol with rangestat, but this wasnt so. Do you have any suggestions using asrol to calculate a geometric mean or product in a forward-looking window?

                          Thanks for your reply so far!

                          Comment


                          • #14
                            As of now, asrol can only work with a backward-looking window, therefore, you can use rangestat. Since your data is time-series / panel in nature, one workaround with asrol is to use a 22-periods lead operator with the rolling geometric mean generated by asrol. So,
                            Code:
                            asrol returns, stat(gmean) window(date 22) add(1)
                            gen fr22 = F22.gmean22_returns
                            
                            . list date returns gmean22_returns fr22 in 1/40
                            
                                 +------------------------------------------------+
                                 |      date     returns   gmean22_~s        fr22 |
                                 |------------------------------------------------|
                              1. | 02jan2001           .            .     .004741 |
                              2. | 03jan2001    .0500986    .05009861    .0002403 |
                              3. | 04jan2001   -.0105525    .01932206    .0009675 |
                              4. | 05jan2001   -.0262424    .00390182           . |
                              5. | 08jan2001           .    .00390182    .0037722 |
                                 |------------------------------------------------|
                              6. | 09jan2001    .0038122    .00387941    .0029828 |
                              7. | 10jan2001    .0095864    .00501822    .0026406 |
                              8. | 11jan2001    .0103177    .00589953    .0003128 |
                              9. | 12jan2001   -.0062329    .00415731           . |
                             10. | 16jan2001           .    .00415731    3.58e-06 |
                                 |------------------------------------------------|
                             11. | 17jan2001    .0021256    .00390312   -.0006406 |
                             12. | 18jan2001    .0139153    .00501069   -.0027328 |
                             13. | 19jan2001   -.0040282    .00410311           . |
                             14. | 22jan2001           .    .00410311   -.0030898 |
                             15. | 23jan2001    .0130315    .00491152   -.0042496 |
                                 |------------------------------------------------|
                             16. | 24jan2001    .0028668    .00474097   -.0038491 |
                             17. | 25jan2001   -.0049769    .00024035   -.0049287 |
                             18. | 26jan2001   -.0018858    .00096753           . |
                             19. | 29jan2001           .    .00347854   -.0051818 |
                             20. | 30jan2001    .0070079    .00377218   -.0073072 |
                                 |------------------------------------------------|
                             21. | 31jan2001   -.0056197    .00298281   -.0070026 |
                             22. | 01feb2001    .0054611    .00264065   -.0079138 |
                             23. | 02feb2001    -.017474    .00031278           . |
                             24. | 05feb2001           .    .00090997   -.0070918 |
                             25. | 06feb2001   -.0015137    .00070777   -.0081584 |
                                 |------------------------------------------------|
                             26. | 07feb2001   -.0084081    3.581e-06   -.0073742 |
                             27. | 08feb2001   -.0062347   -.00064064    -.007328 |
                             28. | 09feb2001   -.0133355   -.00273285           . |
                             29. | 12feb2001           .   -.00262482   -.0053941 |
                             30. | 13feb2001   -.0086521   -.00308976   -.0041399 |
                                 |------------------------------------------------|
                             31. | 14feb2001   -.0021838    -.0042496   -.0037712 |
                             32. | 15feb2001    .0081235   -.00384908   -.0065209 |
                             33. | 16feb2001   -.0189053    -.0049287           . |
                             34. | 20feb2001           .   -.00518185   -.0054377 |
                             35. | 21feb2001   -.0185075   -.00730718   -.0035589 |
                                 |------------------------------------------------|
                             36. | 22feb2001   -.0019518   -.00700256   -.0049273 |
                             37. | 23feb2001   -.0055554   -.00791381           . |
                             38. | 26feb2001           .   -.00704011   -.0063654 |
                             39. | 27feb2001   -.0076599   -.00709177   -.0071597 |
                             40. | 28feb2001   -.0143091   -.00815838   -.0063697 |
                                 +------------------------------------------------+
                            Please note that this approach will generate missing values for the last 22 observations (as expected). Also, if your data has time-series gaps, the lead operator will show missing values where a matching observation in the 22-period forward-looking window is missing.
                            Last edited by Attaullah Shah; 10 Nov 2018, 08:52.
                            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


                            • #15
                              For those interested, asrol has been updated to version 4.5.1 where the calculations of the geometric mean and the products have been improved. The Statalist post can be accessed here.
                              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

                              Working...
                              X