Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Computing st. deviations per year

    Hi all,

    For the firms in my sample, I need to calculate the volatility of earnings. However, I keep on failing to do this. Measuring volatility can be done by calculating the st. deviations of the last 4 quarters' ROA numbers. So for example, for the following firm it looks like this:

    fyearq fqtr cusip conm atq niq ROA
    2012 1 55336V100 MPLX LP 1435.2 30.4 .0021182
    2012 2 55336V100 MPLX LP 1501.6 29.8 .0198455
    2012 3 55336V100 MPLX LP 1250.2 36.9 .0295153
    2012 4 55336V100 MPLX LP 1301.3 28.9 .0222086
    2013 1 55336V100 MPLX LP 1311.9 17.6 .0134157
    2013 2 55336V100 MPLX LP 1217.7 18.6 .0152747
    2013 3 55336V100 MPLX LP 1218.7 21.5 .0176417
    2013 4 55336V100 MPLX LP 1208.5 20.2 .0167149
    2014 1 55336V100 MPLX LP 1193.7 34.2 .0286504
    2014 2 55336V100 MPLX LP 1181.4 28.8 .0243779
    2014 3 55336V100 MPLX LP 1194.3 29.1 .0243657
    2014 4 55336V100 MPLX LP 1214.5 29.2 .0240428
    2015 1 55336V100 MPLX LP 1354 68 .0502216
    2015 2 55336V100 MPLX LP 1382.6 75 .0542456
    2015 3 55336V100 MPLX LP 1391.1 63 .0452879
    2015 4 55336V100 MPLX LP 15677 42 .0026791
    2016 1 55336V100 MPLX LP 15978 -14 -.0008762
    2016 2 55336V100 MPLX LP 16079 71 .0044157
    2016 3 55336V100 MPLX LP 16415 192 .0116966
    2016 4 55336V100 MPLX LP 16646 183 .0109936
    2017 1 55336V100 MPLX LP 18285 186 .0101723
    2017 2 55336V100 MPLX LP 18601 190 .0102145
    2017 3 55336V100 MPLX LP 19238 216 .0112278
    2017 4 55336V100 MPLX LP 19500 238 .0122051
    2018 1 55336V100 MPLX LP 21006 421 .0200419
    2018 2 55336V100 MPLX LP 21412 453 .0211564
    2018 3 55336V100 MPLX LP 22379 510 .0227892
    2018 4 55336V100 MPLX LP 22779 434 .0190526


    So what I exactly need is:
    - One column added that calculates the st. deviation for the 4 quarters every year.
    - When I would have this, I need to change the dataset so that I will have one row for every firm-year combination (so that I can merge this with my other dataset).

    Hopefully it is clear what I need and what my problems are, and hopefully someone knows which codes/handlings I need to do!

    Kind regards
    Tags: None
  • #2
    Anyone who knows?

    Comment

    • #3
      If this is impossible, is it possible to go from 4 quarters per year to one row per firm-year (maybe I’m able to calculate the st. dev through Excel)

      Comment

      • #4
        please use -dataex- to give data samples (read the FAQ) so we can test code before making suggestions
        • 1 like

        Comment

        • #5
          I read this about three times and found it ambiguous. I am not clear whether you want the last 4 quarters or just to do it separately by year. Either way some hundreds of posts here detail how to approach this using rangestat (SSC).

          Or — if it’s the second — collapse is closer to what you want.

          If you don’t get a quick reply, improve the question. Give a data example using dataex. Show what your other file looks like. Read the FAQ Advice to see what you missed. Bumping after just a few hours looks impatient. That too is explained in the FAQ Advice.



          Comment

          • #6
            Hi guys,

            Thanks for your advice. Reading the FAQ, the following should be the right way to communicate:
            Note: I don't know why the dataex says "double" and also shows str10 and float, these are not in my STATA file...
            The order of variables that you see is:
            fyear | FiscalQuarterNumber | CUSIP | CompanyName | TotalAssets | NetIncome | CIK number | ROA (calculated by NetIncome/TotalAssets)

            Code:
            * Example generated by -dataex-. To install: ssc install dataex
clear
input double(fyearq fiscalquarter) str10 CUSIP str58 CompanyName double(TotalAssets NetIncome) str10 CIK float ROA
2010 1 "68234X102" "ONCOMED PHARMACEUTICALS"       .       . "0001302573"            .
2010 2 "68234X102" "ONCOMED PHARMACEUTICALS"       .       . "0001302573"            .
2010 3 "68234X102" "ONCOMED PHARMACEUTICALS"       .       . "0001302573"            .
2010 4 "68234X102" "ONCOMED PHARMACEUTICALS" 129.894       . "0001302573"            .
2011 1 "68234X102" "ONCOMED PHARMACEUTICALS"       .       . "0001302573"            .
2011 2 "68234X102" "ONCOMED PHARMACEUTICALS"       .       . "0001302573"            .
2011 3 "68234X102" "ONCOMED PHARMACEUTICALS"       .       . "0001302573"            .
2011 4 "68234X102" "ONCOMED PHARMACEUTICALS" 107.205 -15.034 "0001302573"   -.14023599
2012 1 "68234X102" "ONCOMED PHARMACEUTICALS"       . -10.526 "0001302573"            .
2012 2 "68234X102" "ONCOMED PHARMACEUTICALS"       .  -3.759 "0001302573"            .
2012 3 "68234X102" "ONCOMED PHARMACEUTICALS"       .  -3.655 "0001302573"            .
2012 4 "68234X102" "ONCOMED PHARMACEUTICALS"  79.768  -4.295 "0001302573"   -.05384365
2013 1 "68234X102" "ONCOMED PHARMACEUTICALS"  69.824  -8.598 "0001302573"   -.12313817
2013 2 "68234X102" "ONCOMED PHARMACEUTICALS"  66.234  -9.644 "0001302573"   -.14560498
2013 3 "68234X102" "ONCOMED PHARMACEUTICALS"  145.21  -3.485 "0001302573"  -.023999725
2013 4 "68234X102" "ONCOMED PHARMACEUTICALS" 333.685  -4.344 "0001302573"  -.013018265
2014 1 "68234X102" "ONCOMED PHARMACEUTICALS" 301.886 -13.871 "0001302573"   -.04594781
2014 2 "68234X102" "ONCOMED PHARMACEUTICALS" 284.304 -15.597 "0001302573"   -.05486029
2014 3 "68234X102" "ONCOMED PHARMACEUTICALS"  268.94  -5.486 "0001302573"    -.0203986
2014 4 "68234X102" "ONCOMED PHARMACEUTICALS" 247.842 -15.056 "0001302573"   -.06074838
2015 1 "68234X102" "ONCOMED PHARMACEUTICALS" 229.008 -14.529 "0001302573"    -.0634432
2015 2 "68234X102" "ONCOMED PHARMACEUTICALS" 209.081  -21.62 "0001302573"    -.1034049
2015 3 "68234X102" "ONCOMED PHARMACEUTICALS" 184.574  -24.48 "0001302573"   -.13262974
2015 4 "68234X102" "ONCOMED PHARMACEUTICALS" 237.887 -24.778 "0001302573"    -.1041587
2016 1 "68234X102" "ONCOMED PHARMACEUTICALS"  204.93 -27.213 "0001302573"   -.13279168
2016 2 "68234X102" "ONCOMED PHARMACEUTICALS" 181.893 -27.689 "0001302573"   -.15222687
2016 3 "68234X102" "ONCOMED PHARMACEUTICALS" 218.188 -25.865 "0001302573"   -.11854456
2016 4 "68234X102" "ONCOMED PHARMACEUTICALS" 195.482 -22.335 "0001302573"   -.11425605
2017 1 "68234X102" "ONCOMED PHARMACEUTICALS" 166.946 -22.608 "0001302573"   -.13542104
2017 2 "68234X102" "ONCOMED PHARMACEUTICALS" 139.345 -15.225 "0001302573"   -.10926118
2017 3 "68234X102" "ONCOMED PHARMACEUTICALS" 120.481 -10.692 "0001302573"   -.08874428
2017 4 "68234X102" "ONCOMED PHARMACEUTICALS" 110.322   9.463 "0001302573"    .08577618
2018 1 "68234X102" "ONCOMED PHARMACEUTICALS"  95.131  -5.574 "0001302573"   -.05859289
2018 2 "68234X102" "ONCOMED PHARMACEUTICALS"  85.909  -3.976 "0001302573"   -.04628153
2018 3 "68234X102" "ONCOMED PHARMACEUTICALS"  77.153   6.115 "0001302573"     .0792581
2018 4 "68234X102" "ONCOMED PHARMACEUTICALS"  65.078  -4.666 "0001302573"  -.071698576
            @Nick, apologies that you found it ambiguous. What I need, in this code example, is to calculate per year the standard deviation of the 4 quarters that are included in that year. In this example, for 2018 that would be the standard deviation of the -.05859, -.04528, .07926, -.0716986. This would mean that for all firm-year combinations I would get one standard deviation (based on the four quarters).

            After this, I need to bring back the dataset from 4 rows per firm-year to just one row per firm-year. I don't need quarterly numbers anymore when I have the standard deviations.

            For the other file; that are just 'simple' firm-year combinations (not quarterly) with identifiers such as CUSIP and CIK.

            Hope this explains my issue better; if you need more information please ask!
            Last edited by Pepijn Peters; 10 Oct 2019, 16:40.

            Comment

            • #7
              Thanks for the extra details. I recommend collapse (sd).

              Comment

              • #8
                Hi Nick,

                As I'm not an expert in STATA, what are your thoughts on the specific code I would need? Do you know one?
                I read about collapse but I think my case is more specific than the 'general' collapse commands.

                Comment

                • #9
                  This does not read an expert on Stata (*), I think. First collapse; then merge.

                  Code:
                  collapse (sd) ROA, by(CUSIP fyearq) 

list 

     +-------------------------------+
     | fyearq       CUSIP        ROA |
     |-------------------------------|
  1. |   2010   68234X102          . |
  2. |   2011   68234X102          . |
  3. |   2012   68234X102          . |
  4. |   2013   68234X102   .0676678 |
  5. |   2014   68234X102    .017799 |
     |-------------------------------|
  6. |   2015   68234X102   .0284411 |
  7. |   2016   68234X102   .0171244 |
  8. |   2017   68234X102   .1002951 |
  9. |   2018   68234X102   .0698334 |
     +-------------------------------+
                  (*) https://www.statalist.org/forums/help#spelling

                  Comment

                  • #10
                    It works, perfect!

                    Comment

                    Previous Next
                    Working...
                    X