Standard deviation over a period of 12 Quarters.

[Zeeshan Fareed]

Hi,
My all dataset is annual but for the dependent variable, I want to calculate the Standard deviation of CFO over a period of 12 quarters. My dataset for CFO is looking like this. Please help me as this is my first post.

date	code	cashflow
2007q1	1	0.457409
2007q2	1	0.345435
2007q3	1	0.923858
2007q4	1	0.435334
2008q1	1	0.59045
2008q2	1	0.49754
2008q3	1	0.55433
2008q4	1	0.948373
2009q1	1	0.94955
2009q2	1	0.093455

[William Lisowski]

Welcome to Statalist.

You may find the user-written rangestat command helpful. Ru the Stata command

search rangestat

and in the results displayed in Stata's Viewer window, find the entry for rangestat and click on it.

With that said, some advice on effective posting on Statalist. Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question.

In particular, please read FAQ #12 and help those whose help you seek by posting example data using the dataex command. If you are running Stata 15.1 or later, it is already installed. For earlier versions of Stata, install dataex by typing ssc install dataex. Type help dataex to read the simple instructions for using it. Using dataex will enable those who want to help you to quickly and easily create a 100% faithful replica of your situation to test their ideas and code on.

With useful example data to test on, I would likely have shown tested code for accomplishing what you want. But the data you present doesn't even have a full 12 observations to test on, and it requires a fair amount of manual tweaking to get it into Stata.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

[Zeeshan Fareed]

Thank you very much. I understand.

[Attaullah Shah]

I assume that the code variable in your example is a panel identifier. So using asrol (from SSC), the rolling standard deviation for each panel identifier would be calculated as

* First enter the data
clear
input str6 date byte code float cashflow
"2007q1" 1 .457409
"2007q2" 1 .345435
"2007q3" 1 .923858
"2007q4" 1 .435334
"2008q1" 1  .59045
"2008q2" 1  .49754
"2008q3" 1  .55433
"2008q4" 1 .948373
"2009q1" 1  .94955
"2009q2" 1 .093455
end

* Generate a quarterly date from the string date
gen quarter =  quarterly(date, "YQ")

* Install asrol
ssc install asrol


* Rolling window sd for each firm
bys code : asrol cashflow, stat(sd) window(quarter 12)
    +------------------------------------------------+
     |   date   code   cashflow   quarter   sd12_ca~w |
     |------------------------------------------------|
  1. | 2007q1      1    .457409       188           . |
  2. | 2007q2      1    .345435       189   .07917758 |
  3. | 2007q3      1    .923858       190   .30678059 |
  4. | 2007q4      1    .435334       191   .26011389 |
  5. | 2008q1      1     .59045       192   .22636971 |
     |------------------------------------------------|
  6. | 2008q2      1     .49754       193   .20362222 |
  7. | 2008q3      1     .55433       194   .18594237 |
  8. | 2008q4      1    .948373       195   .22389215 |
  9. | 2009q1      1     .94955       196   .24062571 |
 10. | 2009q2      1    .093455       197   .28397452 |
     +------------------------------------------------+

[Nick Cox]

This follows up the suggestion of William Lisowski that rangestat (SSC) can be used for this relatively simple calculation. As Attaullah Shah shows, asrol (also SSC) here is equivalent and the difference for this example is just in syntax.

I find it a good idea to document how many values are available for use. asrol can calculate that too.

#1 leaves it ambiguous when your window ends. rangestat is flexible about this but the point is trivial as any 12 period window is just another 12 period window translated.

clear
input str6 date byte code float cashflow
"2007q1" 1 .457409
"2007q2" 1 .345435
"2007q3" 1 .923858
"2007q4" 1 .435334
"2008q1" 1  .59045
"2008q2" 1  .49754
"2008q3" 1  .55433
"2008q4" 1 .948373
"2009q1" 1  .94955
"2009q2" 1 .093455
end

* Generate a quarterly date from the string date
gen quarter =  quarterly(date, "YQ")

rangestat (sd) sd1 = cashflow (count) count1 = cashflow , int(quarter -11 0) by(code) 

rangestat (sd) sd2 = cashflow (count) count2 = cashflow , int(quarter -12 -1) by(code) 

list 

     +------------------------------------------------------------------------------+
     |   date   code   cashflow   quarter         sd1   count1         sd2   count2 |
     |------------------------------------------------------------------------------|
  1. | 2007q1      1    .457409       188           .        1           .        . |
  2. | 2007q2      1    .345435       189   .07917758        2           .        1 |
  3. | 2007q3      1    .923858       190   .30678059        3   .07917758        2 |
  4. | 2007q4      1    .435334       191   .26011389        4   .30678059        3 |
  5. | 2008q1      1     .59045       192   .22636971        5   .26011389        4 |
     |------------------------------------------------------------------------------|
  6. | 2008q2      1     .49754       193   .20362222        6   .22636971        5 |
  7. | 2008q3      1     .55433       194   .18594237        7   .20362222        6 |
  8. | 2008q4      1    .948373       195   .22389215        8   .18594237        7 |
  9. | 2009q1      1     .94955       196   .24062571        9   .22389215        8 |
 10. | 2009q2      1    .093455       197   .28397452       10   .24062571        9 |
     +------------------------------------------------------------------------------+

[Zeeshan Fareed]

Thank you so much Sir Attaullah Shah and Nick CoX