Annualised standard deviation from the monthly stock returns over the previous 5 years

[salma ktat]

Hi,
I'm trying to generate the standard deviation from monthly and daily stock returns. For generating the standard deviation from daily stock returns, I did the following :
egen SD = sd(return), by(id year). And it seem to be working. But, I just have trouble with generating the standard deviation from monthly stock returns over the previous 5 years. The issue is that I don't know how to do the grouping for the 5 previous years.
My monthly data seems like this
ID date returns
01 Jan1986 0.0021
01 Feb1986 0.0023
01 Mar1986 0.0013
01 .... ...
01
...

Thanks for your help.
Ps: I don't really know if what I did to generate the standard deviation from daily stock returns over the past year is correct !

[ben earnhart]

So you have a variable for year you're not showing us (logically, you must, in order to do the yearly SDs)? Do you also have one for month?

[salma ktat]

Yes I have a variable for year. And the variable for month is shown earlier. So, again my data is like this:
ID monthlydate year returns
01 Jan1986 1986 0.0021
01 Feb1986 1986 0.0023
01 Mar1986 1986 0.0013
01 .... ...
01

[Attaullah Shah]

You can generate month numbers by

gen month=month(date)

, then you can try the following code

egen SDm = sd(return), by(id year month).

[salma ktat]

So you have a variable for year you're not showing us (logically, you must, in order to do the yearly SDs)? Do you also have one for month?

Thanks Ben. I had the month and year variables but I just have trouble with generating the standard deviation from monthly stock returns over the previous 5 years. Thanks everybody!

[Attaullah Shah]

For the five years rolling window, I have a rough code, senior members might improve it

forval i=1996(1)2012{ //replace 1996 with your starting year and 2012 with ending year
local j=`i'+5
local m=`i'-1
bys id: egen vol`i'=sd(re) if year>`m' & year<`j'
}

forval i=1996(1)2012{
by id: egen vol2`i'=min(vol`i')
}

gen volall=.
forval i=1996(1)2012{
local m=`i'+5
replace volall=vol2`i' if year==`m'
}

[salma ktat]

For the five years rolling window, I have a rough code, senior members might improve it

Thanks Attaullah. I'm trying to adopt your proposed solution to my data but I just don't understand the reason you used min fuction in the second loop.

[Attaullah Shah]

The first loop creates seperate vol variable in year year, the second loop spreads the values to fill the whole column, and the third loop accumulates all the relevant values from different columns into one, later on you can keep only the last column i.e. vol2, and delete all other columns

[salma ktat]

The first loop creates seperate vol variable in year year, the second loop spreads the values to fill the whole column, and the third loop accumulates all the relevant values from different columns into one, later on you can keep only the last column i.e. vol2, and delete all other columns

Thanks a lot Attaullah. I just saw your message. I tried it too and it seems to be working well. But, I guess I have to keep the last column volall which seems to have all the relevant values for different deffered volatilities.
Thanks again

[Attaullah Shah]

Good to know that it worked for you.

[salma ktat]

For the five years rolling window, I have a rough code, senior members might improve it

Hi Attaullah,
I'd like to thank you again for your help with the annualised standard deviation from the monthly returns over the past five years. It worked well. And that's the reason I tried the same solution with little modifications to generate the standard deviation from the daily returns over the past year.
So my data is like this:
ID dailydate year ret
01 01Jan1986 1986 0.0021
01 02Jan1986 1986 0.0023
01 03Jan1986 1986 0.0013
01 .... ...
01
The solution adapted to my data is:

forval i=1986(1)2013{
local m=`i'-1
bys permno yr: egen SD`i'=sd(ret) if yr==`m'
}

forval i=1986(1)2013{
by permno yr: egen SD2`i'=min(SD`i')
}

gen SDdailyyearpastret=.
forval i=1986(1)2013{
local m=`i'-1
replace SDpastyear=SD2`i' if yr==`m'
}

The problem is that it's taking forever to run just for a sample data of 2 years of daily returns.
I really appreciate your help with this. Thanks.

[Attaullah Shah]

Looping over observation is always a bad idea, but in the absence of any other solution, one can chew his/her nails and wait. I had a similar problem in finding standardard deviation for whole industry, but excluding focal firm. I developed a code, which would loop over observations. The code took more than 3 days, I lost patience and stopped it. I requested tenured members to help, but no one replied. You can wait till the code finalizes or some senior member replies.

[Nick Cox]

I am looking at this without understanding what you want to do. That's always dangerous.

But here we go. The first loop (1) calculates several variables containing SDs, which are disjoint because yr can only have one value in each observation.

(1)

forval i=1986(1)2013{
local m=`i'-1
bys permno yr: egen SD`i'=sd(ret) if yr==`m'
}

So that seems to collapse to a single statement creating a single variable

egen SD = sd(ret), by(permno yr)

The second loop (2)

(2)

forval i=1986(1)2013{
by permno yr: egen SD2`i'=min(SD`i')
}

finds minimums within each block of observations. But by construction in (1) each SD is constant in each block any way, so that just copies data.

The third chunk of code

(3)

gen SDdailyyearpastret=.
forval i=1986(1)2013{
local m=`i'-1
replace SDpastyear=SD2`i' if yr==`m'
}

also seems redundant.

So, I think you have a need for just one command here. Evidently it's important in this work to refer to the previous year's values, but time series operators exist to do that.

There is an orthogonal comment. Never set a program doing something time-consuming until you are sure from working with a small sandbox of sample data that you are doing the right thing.

[salma ktat]

I am looking at this without understanding what you want to do. That's always dangerous.

But here we go. The first loop (1) calculates several variables containing SDs, which are disjoint because yr can only have one value in each observation.

(1)

forval i=1986(1)2013{
local m=`i'-1
bys permno yr: egen SD`i'=sd(ret) if yr==`m'
}

So that seems to collapse to a single statement creating a single variable

egen SD = sd(ret), by(permno yr)

The second loop (2)

(2)

forval i=1986(1)2013{
by permno yr: egen SD2`i'=min(SD`i')
}

finds minimums within each block of observations. But by construction in (1) each SD is constant in each block any way, so that just copies data.

The third chunk of code

(3)

gen SDdailyyearpastret=.
forval i=1986(1)2013{
local m=`i'-1
replace SDpastyear=SD2`i' if yr==`m'
}

also seems redundant.

So, I think you have a need for just one command here. Evidently it's important in this work to refer to the previous year's values, but time series operators exist to do that.

There is an orthogonal comment. Never set a program doing something time-consuming until you are sure from working with a small sandbox of sample data that you are doing the right thing.

Thanks Prof. Cox for your comments. I already tried using one simple command as you mentioned it: egen SD = sd(ret), by(permno yr). But, as you said I need to refer to the previous year's values that's why I tried the latter codes.
So, you said that time series operators exist to do that. Could you please explain that alittle more.
Thanks again.

[Nick Cox]

help varlist

gives a good start.

As you're dealing with panel data, you really need to know how these operators can help you.

Another massive issue is that if you wish to reduce a daily dataset to a yearly dataset, doing it yourself with egen is much less satisfactory than using collapse.