Compute standard deviation using past two year monthly return data

[Rochelle Zhang]

Dear Statalisters,
My panel dataset is by firm/year/month with monthly stock returns between 1975 to 2012. I need to construct the return volatility using standard deviation of monthly return over the past two years. My sample data is given below. I did not show the whole past three years to conserve space.

input str4 firmid year month ret
1001 1990 1 0.01
1001 1990 2 -0.2
1001 1990 3 0.3
1001 1990 4 0.01
1001 1990 5 0.03
1001 1990 6 0.05
1001 1990 7 0.3
1001 1990 8 0.1
1001 1990 9 -0.05
1001 1990 10 0.05
1001 1990 11 0.1
1001 1990 12 0.4
1001 1991 1 .
1001 1991 2 0.1
1001 1991 3 0.3
1001 1991 4 0.05
1001 1991 5 0.3
1001 1991 6 0.001
1001 1991 7 0.002
1001 1991 8 0.005
1001 1991 9 0.04
1001 1991 10 0.009
1001 1991 11 -0.001
1001 1991 12 -0.003
end

My question is: how to generate this stdev variables for firm 1001 in year 1992, using data from 1990 to 1991, then stdev in year 1993, using data from 1991-1992, and so on.


Thank you,
Rochelle

[Clyde Schechter]

I don't fully understand your question. You will have 12 observations in 1992. Do you want all 12 of them to have the same value for the standard deviation, based on the monthly data from 1990 and 1991, and reduce to 1 observation per year (Option 1)? Or do you want the observation for January 1992 to have the s.d. for Jan 1990 through Dec 1991, and the observation in Feb 1992 to have the s.d. for Feb1990 through Jan 1992, etc.? (Option 2)

Either way, the key is remembering that

sd2 = E(x2) - (E(x))2

Also, you need to have a monthly date variable, not separate year and month. So

gen int date = ym(year, month)
format date %tm
xtset firmid date

//  CALCULATE SOME RUNNING TOTALS OF RET AND RET^2
by firmid (date), sort: gen sum_ret = sum(ret)
by firmid (date): gen sum_ret_sq = sum(ret^2)
//  AND A RUNNING COUNT OF NON MISSING OBSERVATIONS
by firmid (date): gen int n_obs = sum(!missing(ret))

//  NOW CALCULATE RUNNING STANDARD DEVIATIONS
gen variance = (L1.sum_ret_sq - L25.sum_ret_sq)/(L1.n_obs-L25.n_obs)  ///
                         - ((L1.sum_ret - L25.sum_ret)/(L1.n_obs-L25.n_obs))^2
gen sd = sqrt(variance)

The above code will get you option 2. To get from there to option 1 would be:

sort firmid year
collapse (first) sd, by(firmid year)

[Rochelle Zhang]

Thank you Clyde for your detailed explanation. It is always so enlightening. I learned so much from you.

Let me answer your question about if I want to use the same stdev value for all 12 months in 1992 or get a stdev for each of the 12 months of 1992 using a rolling prior 24 month . My actual data is over 30 year monthly stock return data, I need this standard deviation measure as a regression explanatory variable. My regression using annual measures for each firm and run a panel regression.

model: firm's policy in year t = x1+ x2+ .... + return volatility ( note: x1, x2 are measure in year t), t=1975 to 2013

following prior work,return volatility is proxied with prior x number of monthly stdev, in my post, my x=24, emprically, I can only have 1 value for the regression return volatility measure for in each year, I guess both your option 1 and 2 are acceptable.

now the question may be, do I have to use rolling window 24 if my data is panel.

my posted data is short, so I apologize if it causes misunderstanding.

Rochelle

[Cen Sophia]

Hi Clyde, I hope you are well. I am revisiting this post as I am having problems calculating the variance.

Code:
// NOW CALCULATE RUNNING STANDARD DEVIATIONS gen variance = (L1.sum_ret_sq - L25.sum_ret_sq)/(L1.n_obs-L25.n_obs) /// - ((L1.sum_ret - L25.sum_ret)/(L1.n_obs-L25.n_obs))^2

when I input this into stata, it does not work. Do you have any tips at all? Thank you!

[Nick Cox]

#4 is a duplicate post. Please don't post the same question in two or more threads at once.

If interested, please follow https://www.statalist.org/forums/for...iance-in-stata