Announcement

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

  • calculation of covariances, variances and downside betas

    ymdate excess_ret mom rmw cma
    432 3.714887 .56 -.56 2.26
    433 -9.23 .58 1.02 -1.78
    434 -1.5 -1.89 1.4 -.95
    ... ... .... .... ....
    623 3.0503345 3.9 1.46 2.96



    I was looking for a calculation of monthly downside betas for every single risk factor in the table above (mom, rmw, cma), which is defined as "β= ((cov(excess return,risk factor) | risk factor < 0)/ Var(risk factor))". So, I want to calculate the covariance between the excess returns and risk factors (one after another), but only for these observations where the risk factor is negativ. Dividing this term by the variance of the respective risk factor will result in my beta "β". All in all, I will a get monthly downside betas for every single risk factor (e.g. βrmw).

    If I consider my table above:

    corr excess_ret rmw if rmw < 0, cov

    delivers the variance-covariance matrix with the variance of the excess returns, the variance of the respective risk factor (here:rmw) and the covariance between both variables. How can I put these numbers in a command to get my downside beta as defined without copy & paste the results out of the var-cov matrix?

    I also tried to work with:
    corr excess_ret mktrf if mktrf < 0, cov scalar β = `r(cov_12)'/`r(Var_2)'
    in oder to explicitly state that I am referring to a matrix, but the option "scalar" was not allowed. How can I put that into the same context? Is it necessary that I generate a new variable named "β" upfront?


  • #2
    THe problem is that you ran the two commands together on a single line. It should be:

    Code:
    corr excess_ret mktrf if mktrf < 0, cov
    scalar β = `r(cov_12)'/`r(Var_2)'
    on two separate lines.

    Comment


    • #3
      Thank you - two separate lines it was.

      How can I calculate a "β" as defined above for every single date (i.e. ymdate)? So that I get several Betas for every risk factor instead of a single number per factor.

      Comment


      • #4
        Based on the data layout you showed in #1, that request doesn't make sense. You only have one observation per date, so there is no variation within a single date, and hence no covariance or beta. If your data is more complicated, please show us the configuration.

        Comment


        • #5
          Based on the command above:

          corr excess_ret rmw if rmw < 0, cov

          I generated Betas for every factor like:

          gen βrmw = `r(cov_12)'/`r(Var_2)'

          The next step in my analysis is to test the significance of these risk factors´ betas on predicting monthly returns (dependent variable) via a regression.

          reg excess_ret βmom βrmw βcma

          But the betas like "βmom" are omitted because of collinearity. As a result, I was thinking of generating different betas for a single risk factor within the time horizon (Jan 1996 to December 2011).

          I can also start a new thread for regression.

          Comment


          • #6
            Suppose I am running the variance-covariance matrix for every single id in my dataset:

            by id: corr excess_ret mktrf if mktrf < 0, cov

            How can I adjust:
            gen βmktrf = `r(cov_12)'/`r(Var_2)'

            so that it gives me a number per single id?

            by id: gen βmktrf = `r(cov_12)'/`r(Var_2)'

            seems to be an invalid syntax.

            Comment


            • #7
              You won't be able to do this using the -by- prefix, because -by- only applies to a single command. Try this

              Code:
              levelsof id, local(ids)
              
              gen  βmktrf = .
              foreach i of local ids {
                  corr excess_ret rmw if rmw < 0 & id == `i'
                  replace βmktrf = `r(cov_12)'/`r(var_2)' if id == `i'
              }

              Comment


              • #8
                I could follow your code, but it ends up only with "." in the column "βmktrf".

                Comment


                • #9
                  Oh, sorry, I forgot the -cov- option on the -corr- command, and the v in r(var_2) should have been upper case V. It should be:

                  Code:
                  levelsof id, local(ids)
                  
                  gen  βmktrf = .
                  foreach i of local ids {
                      corr excess_ret rmw if rmw < 0 & id == `i', cov
                      replace βmktrf = `r(cov_12)'/`r(Var_2)' if id == `i'
                  }

                  Comment


                  • #10
                    Ok I adjusted the code, but still the same result ("." in column "βmktrf").

                    Comment


                    • #11
                      Please post a sample of your data using -dataex-. When I create some fake data and run the code myself I get results:

                      Code:
                      // CREATE TEST DATA
                      sysuse auto, clear
                      rename rep78 id
                      drop if missing(id)
                      rename price excess_ret
                      rename mpg rmw
                      replace rmw = -rmw
                      
                      // TRY THE CODE
                      levelsof id, local(ids)
                      
                      gen  βmktrf = .
                      foreach i of local ids {
                          display `i'
                          corr excess_ret rmw if rmw < 0 & id == `i', cov
                          replace βmktrf = `r(cov_12)'/`r(Var_2)' if id == `i'
                      }
                      
                      tabstat βmktrf, by(id)
                      Also please copy directly from the Results window or your log file the exact code you are using and Stata's exact response. Do not edit in any way. Every detail is important.

                      Comment


                      • #12
                        input double id float(ymdate ew_mean_excess_ret) double mktrf
                        30885 432 2.67882 2.26
                        20173 432 -.2983019 2.26
                        21020 432 .008313257 2.26
                        20856 432 -1.1834285 2.26
                        41462 432 .3535417 2.26
                        636 432 -5.680714 2.26
                        70338 432 1.0134896 2.26
                        709 432 -1.3383386 2.26
                        20602 432 -1.2920834 2.26
                        436 432 -1.8696778 2.26
                        20048 432 1.0577778 2.26
                        1960 432 .5051563 2.26
                        20131 432 3.753871 2.26
                        21565 432 .3732432 2.26
                        21620 432 -.26148 2.26
                        21788 432 .3127234 2.26
                        1330 432 .65475 2.26
                        321 432 -.8838235 2.26
                        906 432 .06452379 2.26
                        2460 432 1.265139 2.26
                        16828 623 .25083336 .74
                        21765 623 .21754584 .74
                        4439 623 .5201551 .74
                        24578 623 .3499646 .74
                        11944 623 .3573016 .74
                        53928 623 -.09612907 .74
                        64254 623 1.086279 .74
                        21780 623 .4304719 .74
                        21669 623 .5346552 .74
                        16107 623 1.452734 .74
                        36100 623 .3706521 .74
                        68905 623 1.2405263 .74
                        35327 623 1.0047255 .74
                        43588 623 .45289475 .74
                        71543 623 .10648029 .74
                        60444 623 .27133724 .74
                        58185 623 .17285715 .74
                        16829 623 .6349167 .74
                        51544 623 -.008695663 .74
                        60259 623 .689079 .74
                        33950 623 .08268983 .74
                        69329 623 .1755128 .74
                        49534 623 .9767593 .74
                        64036 623 1.4964516 .74
                        64844 623 .3573958 .74
                        70026 623 .4765714 .74
                        69998 623 1.8815888 .74
                        21772 623 .5514042 .74
                        6407 623 .4757511 .74
                        26736 623 .28928408 .74
                        27494 623 .395272 .74
                        38490 623 -.6190909 .74
                        48466 623 .9877778 .74
                        230 623 .27883106 .74
                        77839 623 1.0179187 .74
                        34148 623 -.4025 .74
                        25606 623 .4680303 .74
                        53693 623 .5123529 .74
                        13140 623 1.251515 .74
                        69630 623 .829011 .74
                        36521 623 .37348485 .74
                        2260 623 .62225 .74
                        33018 623 .4715942 .74
                        20463 623 .8429636 .74
                        31095 623 1.0303174 .74
                        68247 623 .747619 .74
                        61425 623 .5355318 .74
                        56867 623 .18333334 .74
                        43096 623 .4963399 .74
                        71287 623 1.026535 .74
                        47279 623 .6961458 .74
                        67500 623 .3019792 .74
                        64959 623 1.3717593 .74
                        51838 623 1.4613414 .74
                        77353 623 -.4331507 .74
                        65737 623 16.521719 .74
                        67266 623 1.0822523 .74
                        75040 623 .4321937 .74
                        28024 623 -1.453077 .74
                        75049 623 .7443541 .74
                        75987 623 1.2970356 .74
                        68481 623 .2400671 .74
                        29306 623 -.10334573 .74
                        7737 623 .3796228 .74
                        22831 623 1.1816107 .74
                        38455 623 .7873809 .74
                        70384 623 .3846807 .74
                        56209 623 -.2315942 .74
                        33459 623 -.339412 .74
                        10605 623 1.3550394 .74
                        21266 623 .4944271 .74
                        71103 623 1.0337427 .74
                        64936 623 3.259286 .74
                        72217 623 -.6417518 .74
                        14668 623 .11879635 .74
                        63357 623 .17500086 .74
                        64352 623 .5952028 .74
                        65386 623 1.438681 .74
                        51331 623 1.27 .74
                        14774 623 1.203884 .74
                        3570 623 .4558667 .74
                        68013 623 -.04043301 .74
                        71983 623 .389286 .74
                        23729 623 .2707913 .74
                        20305 623 .51484376 .74
                        34158 623 -1.1236666 .74
                        61473 623 .24631287 .74
                        13525 623 .4325 .74
                        6907 623 -.39886585 .74
                        69344 623 .4432 .74
                        36223 623 .08999999 .74
                        41301 623 1.0348438 .74
                        66006 623 3.170238 .74
                        73998 623 .53229344 .74
                        40244 623 .69875 .74
                        70902 623 2.7762964 .74
                        63119 623 .52397776 .74
                        48496 623 1.614516 .74
                        24713 623 .4188972 .74
                        54668 623 1.3642424 .74
                        69970 623 .4495402 .74
                        29556 623 .3660135 .74
                        52169 623 .3932203 .74
                        52140 623 -.26033333 .74
                        37004 623 .3669697 .74
                        35308 623 .08750005 .74
                        34190 623 -.10101124 .74
                        67502 623 .1575141 .74
                        37595 623 1.1297959 .74
                        61612 623 .029870134 .74
                        25127 623 .3451515 .74
                        62848 623 3.2158096 .74
                        29319 623 .3813021 .74
                        46124 623 .8115152 .74
                        12072 623 -.07994457 .74
                        47570 623 1.3673214 .74
                        63427 623 1.0883929 .74
                        16384 623 .3419271 .74
                        16941 623 .5319512 .74
                        7738 623 .7719778 .74
                        69747 623 .6037536 .74
                        57466 623 .3623313 .74
                        14583 623 -.5611192 .74
                        35023 623 3.7523255 .74
                        69977 623 1.8559335 .74
                        30949 623 .9519048 .74
                        35808 623 1.4124 .74
                        69901 623 .9429078 .74
                        53448 623 .2926316 .74
                        63539 623 2.9284766 .74
                        74412 623 .363871 .74
                        67941 623 .27311927 .74
                        16214 623 1.5815217 .74
                        27843 623 .27359375 .74
                        51462 623 .5303334 .74
                        20255 623 .4431771 .74
                        35763 623 1.1626415 .74
                        50005 623 .4223529 .74
                        69583 623 .9433854 .74
                        70601 623 .51881945 .74
                        12214 623 .3924078 .74
                        54472 623 1.2696154 .74
                        77123 623 -.08450216 .74
                        63564 623 .15532526 .74
                        54693 623 1.535926 .74
                        64632 623 .3449694 .74
                        31012 623 .4357055 .74
                        54901 623 .21857142 .74
                        48291 623 -.2659375 .74
                        52547 623 2.2094116 .74
                        59682 623 1.6194737 .74
                        31344 623 .3650704 .74
                        67187 623 .413854 .74
                        16884 623 2.3207507 .74
                        34405 623 .06103447 .74
                        76707 623 .1563903 .74
                        29058 623 -.7976056 .74
                        4370 623 .12982456 .74
                        52472 623 -.8043751 .74
                        71660 623 .892985 .74
                        34812 623 3.624722 .74
                        35179 623 .12486365 .74
                        34205 623 .7181 .74
                        51452 623 .8744186 .74
                        34187 623 .26257145 .74
                        36886 623 -.3691667 .74
                        64801 623 .436063 .74
                        65043 623 1.095 .74
                        24328 623 .4640541 .74
                        30808 623 1.1521453 .74
                        53246 623 .782439 .74
                        57267 623 -1.6908333 .74
                        71297 623 1.2808334 .74
                        73735 623 .3057292 .74
                        4721 623 .5030303 .74
                        7698 623 -.1543884 .74
                        34057 623 1.4762857 .74
                        7724 623 -.12534977 .74
                        59248 623 .9966667 .74
                        47906 623 1.4376364 .74
                        12900 623 .6656989 .74
                        76694 623 -.10851426 .74
                        26288 623 .58671874 .74
                        37402 623 .9645455 .74
                        67453 623 .55958813 .74
                        13623 623 .0088332305 .74
                        69105 623 1.8698702 .74
                        62631 623 .6416129 .74
                        20080 623 .4813542 .74
                        46787 623 -.014776125 .74
                        36185 623 .7789286 .74
                        17732 623 -.033251207 .74
                        56058 623 -.46545455 .74
                        38236 623 .6425 .74
                        64728 623 .8870968 .74
                        36882 623 .93384 .74
                        77715 623 1.6530952 .74
                        24257 623 .2689091 .74
                        35455 623 1.0957143 .74
                        77711 623 .6064625 .74
                        34809 623 -.031666655 .74
                        78340 623 .382174 .74
                        2222 623 .4316023 .74
                        61280 623 1.1955556 .74
                        35127 623 .10107142 .74
                        13120 623 1.0134809 .74
                        20709 623 .6111459 .74
                        78329 623 .7406521 .74
                        60179 623 .9127013 .74


                        Does this help as a sample?

                        Comment


                        • #13
                          No! It doesn't contain the variables excess_ret and rmw that are involved in the code. I can imagine that ew_mean_excess_ret is excess_ret. But where is rmw? It clearly is not any of the variables inhere because the focus is on observations where rmw < 0, and there are no variables in this data that take any values < 0. If you're trying to apply my code to this data, of course, you wlll not get any results. But you should get error messages indicating that the variables in the code are not found.

                          Also, this data set already contains a variable βmktrf. So what's that about?

                          Comment


                          • #14
                            input double id float(ymdate ew_mean_excess_ret) double rmw
                            30885 432 2.67882 -.56
                            20173 432 -.2983019 -.56
                            21020 432 .008313257 -.56
                            20856 432 -1.1834285 -.56
                            41462 432 .3535417 -.56
                            636 432 -5.680714 -.56
                            70338 432 1.0134896 -.56
                            709 432 -1.3383386 -.56
                            20602 432 -1.2920834 -.56
                            436 432 -1.8696778 -.56
                            20048 432 1.0577778 -.56
                            1960 432 .5051563 -.56
                            20131 432 3.753871 -.56
                            21565 432 .3732432 -.56
                            21620 432 -.26148 -.56
                            21788 432 .3127234 -.56
                            1330 432 .65475 -.56
                            321 432 -.8838235 -.56
                            906 432 .06452379 -.56
                            2460 432 1.265139 -.56
                            64352 623 .5952028 .62
                            65386 623 1.438681 .62
                            51331 623 1.27 .62
                            14774 623 1.203884 .62
                            3570 623 .4558667 .62
                            68013 623 -.04043301 .62
                            71983 623 .389286 .62
                            23729 623 .2707913 .62
                            20305 623 .51484376 .62
                            34158 623 -1.1236666 .62
                            61473 623 .24631287 .62
                            13525 623 .4325 .62
                            6907 623 -.39886585 .62
                            69344 623 .4432 .62
                            36223 623 .08999999 .62
                            41301 623 1.0348438 .62
                            66006 623 3.170238 .62
                            73998 623 .53229344 .62
                            40244 623 .69875 .62
                            70902 623 2.7762964 .62
                            63119 623 .52397776 .62
                            48496 623 1.614516 .62
                            24713 623 .4188972 .62
                            54668 623 1.3642424 .62
                            69970 623 .4495402 .62
                            29556 623 .3660135 .62
                            52169 623 .3932203 .62
                            52140 623 -.26033333 .62
                            37004 623 .3669697 .62
                            35308 623 .08750005 .62
                            34190 623 -.10101124 .62
                            67502 623 .1575141 .62
                            37595 623 1.1297959 .62
                            61612 623 .029870134 .62
                            25127 623 .3451515 .62
                            62848 623 3.2158096 .62
                            29319 623 .3813021 .62
                            46124 623 .8115152 .62
                            12072 623 -.07994457 .62
                            47570 623 1.3673214 .62
                            63427 623 1.0883929 .62
                            16384 623 .3419271 .62
                            16941 623 .5319512 .62
                            7738 623 .7719778 .62
                            69747 623 .6037536 .62
                            57466 623 .3623313 .62
                            14583 623 -.5611192 .62
                            35023 623 3.7523255 .62
                            69977 623 1.8559335 .62
                            30949 623 .9519048 .62
                            35808 623 1.4124 .62
                            69901 623 .9429078 .62
                            53448 623 .2926316 .62
                            63539 623 2.9284766 .62
                            74412 623 .363871 .62
                            67941 623 .27311927 .62
                            16214 623 1.5815217 .62
                            27843 623 .27359375 .62
                            51462 623 .5303334 .62
                            20255 623 .4431771 .62
                            35763 623 1.1626415 .62
                            50005 623 .4223529 .62
                            69583 623 .9433854 .62
                            70601 623 .51881945 .62
                            12214 623 .3924078 .62
                            54472 623 1.2696154 .62
                            77123 623 -.08450216 .62
                            63564 623 .15532526 .62
                            54693 623 1.535926 .62
                            64632 623 .3449694 .62
                            31012 623 .4357055 .62
                            54901 623 .21857142 .62
                            48291 623 -.2659375 .62
                            52547 623 2.2094116 .62
                            59682 623 1.6194737 .62
                            31344 623 .3650704 .62
                            67187 623 .413854 .62
                            16884 623 2.3207507 .62
                            34405 623 .06103447 .62
                            76707 623 .1563903 .62
                            29058 623 -.7976056 .62
                            4370 623 .12982456 .62
                            52472 623 -.8043751 .62
                            71660 623 .892985 .62
                            34812 623 3.624722 .62
                            35179 623 .12486365 .62
                            34205 623 .7181 .62
                            51452 623 .8744186 .62
                            34187 623 .26257145 .62
                            36886 623 -.3691667 .62
                            64801 623 .436063 .62
                            65043 623 1.095 .62
                            24328 623 .4640541 .62
                            30808 623 1.1521453 .62
                            53246 623 .782439 .62
                            57267 623 -1.6908333 .62
                            71297 623 1.2808334 .62
                            73735 623 .3057292 .62
                            4721 623 .5030303 .62
                            7698 623 -.1543884 .62
                            34057 623 1.4762857 .62
                            7724 623 -.12534977 .62
                            59248 623 .9966667 .62
                            47906 623 1.4376364 .62
                            12900 623 .6656989 .62
                            76694 623 -.10851426 .62
                            26288 623 .58671874 .62
                            37402 623 .9645455 .62
                            67453 623 .55958813 .62
                            13623 623 .0088332305 .62
                            69105 623 1.8698702 .62
                            62631 623 .6416129 .62
                            20080 623 .4813542 .62
                            46787 623 -.014776125 .62
                            36185 623 .7789286 .62
                            17732 623 -.033251207 .62
                            56058 623 -.46545455 .62
                            38236 623 .6425 .62
                            64728 623 .8870968 .62
                            36882 623 .93384 .62
                            77715 623 1.6530952 .62
                            24257 623 .2689091 .62
                            35455 623 1.0957143 .62
                            77711 623 .6064625 .62
                            34809 623 -.031666655 .62
                            78340 623 .382174 .62
                            2222 623 .4316023 .62
                            61280 623 1.1955556 .62
                            35127 623 .10107142 .62
                            13120 623 1.0134809 .62
                            20709 623 .6111459 .62
                            78329 623 .7406521 .62
                            60179 623 .9127013 .62



                            Indeed ew_mean_excess_ret is excess_ret.
                            mktrf was just another factor like rmw. There are observations where rmw < 0 as you can see above< 0. This sample leaves out observations for ymdate between 432 and 623, where mktrf < 0.

                            For the variable mktrf I used:

                            gen βmktrf = .
                            foreach i of local ids {
                            display `i'
                            corr ew_mean_excess_ret mktrf if mktrf < 0 & id == `i', cov
                            replace βmktrf = `r(cov_12)'/`r(Var_2)' if id == `i'
                            }

                            and for the variable rmw I worked with:

                            gen βrmw = .
                            foreach i of local ids {
                            display `i'
                            corr ew_mean_excess_ret rmw if rmw < 0 & id == `i', cov
                            replace βrmw = `r(cov_12)'/`r(Var_2)' if id == `i'
                            }

                            Also, this data set already contains a variable βmktrf. So what's that about?
                            βmktrf is a consequence of mktrf.
                            βrmw is a consequence of rmw.
                            I did not get an error message, however there is also no result in the new generated columnes βrmw or βmktrf.

                            Comment


                            • #15
                              • Are you seeing the ids displayed (you have the code di "`i'" )?
                                • If not, are you including the levelsof code and running the code in a do file?
                                • Stata will generate an empty variable then never get to the replace command if the macro ids is empty.
                              • In the example you give, there is only a single observation per id. is this typical? If so, you do not have enough observations per id.
                              Stata/MP 14.1 (64-bit x86-64)
                              Revision 19 May 2016
                              Win 8.1

                              Comment

                              Working...
                              X