I am having some issues with finding the right code in STATA for sorting the data into portfolios by historical volatility (standard deviation) of returns and then calculating portfolio returns and run some regressions (CAPM, Fama French).

I have the data on weekly prices ("price") of all the constituents of S&P 600 from 31 Dec 2004 - 29 Jan 2016. From these prices I calculated log returns ("return"). The time variable is called "date".

What I need to do: 1.) Calculate volatility of returns from 31 Dec 2004 - 30 December 2005 for each stock. Then move 1 year forward (yearly rebalancing) and again calculate volatility of returns, and so on.

2.) Then I need to sort the stocks ("id") into 3 portfolios based on this volatility. The portfolios are rebalanced at the end of each year (52nd week), so there will be different stocks belonging to each portfolio every period.

3.) The returns on portfolios will be calculated each time for the year following portfolio formation (so, the first year will be used for the calculation of standard deviation, the second for calculation of portfolio return, and so on).

The code I used so far:

*Calculating returns  
by id:gen return = log(price[_n] / price[_n-1])   

*Calculating stnadard deviation   
tsset id date  
rolling sd=r(sd), window(365) saving(standarddev) stepsize(365) keep(return) : sum return    

*Generate a variable that splits the stocks into 3 groups based on their standard deviation:  
sum sd, detail  
egen sd_cut=cut(sd), group(3)  
table sd_cut, contents(n sd min sd max sd)  

*Merging the original dataset with standarddev.dta:  
gen week=week(date)  
gen year=year(date)  
gen yearweek=yw(year,week)  
format yearweek %tw  

*for standarddev.dta:  
gen year=year(date)  
gen yearweek=yw(year,week)  
format yearweek %tw  

merge m:1 id yearweek using "C:\stataData\standarddev.dta"

The first issue with my code are the dates. When I ran the "rolling" command, Stata automatically created some end of year dates that were not in my original dataset (for example, I have a date 29 Dec 2006 in my dataset, while Stata gave me 30 Dec 2006, making it impossible to merge the two datasets for further calculations). I have tried to work around it by creating a new varaible "yearweek", but I am not sure whether this is OK?

Next, I still need to sort the stocks by their st.dev. into terciles and calculate the portfolio returns (arithmetic average). Is my variable sd_cut an appropriate indicator of a stock belonging to portfolio 1,2 or 3? How many variables do I need to get at the end?I am assuming 3: portfret1, portfret2 and portfret3 or something like that.

I tried using foreach and forvalues for the calculation of returns, but I always get error:invalid syntax.What am I missing? Here are some of the codes I tried so far:

local yearweek 2005w52/2006w52  
foreach yearweek{  
    egen portret1=mean(return) if ‘yearweek’==2005w52 & sd_cut==0  

local i yearweek  
forvalues i=2005w52/2006w52{  
    bysort week:egen portret1=mean(return) if ‘i’==2005w52 & sd_cut==0  

I also tried using bysort, but failed (even when I sorted first on date):

sort id date  
by date:egen portret1=mean(f365.return) if sd_cut==0 & year==2005  

Any help would be appreciated! Thank you in advance! Laura


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.