Generating quarterly observations from daily observations

[Bart Jagers]

Hello,

I have a sample consisting of daily stock returns from CRSP database ranging from 3 January 2007 to 31 december 2016.

I have calculated the daily stock returns per firm with these codes:

bysort permno ( date ): gen ret = prc - prc[_n-1]

bysort permno ( date ): gen Stock_returns = ret/ prc[_n-1]

This gave me the data in the picture:
I have a formula which calculates the volatility of stock returns per permno over the entire period:

bysort permno : egen sd_returns = sd( Stock_returns )

However I would like stata to generate the volatility per quarter, for example: calculating the volatility per permno over the stock returns from January to March, from April to June, etc so that there will be 40 volatilities per permno in total (4 per year per permno * 10 years)

Does anybody have a suggestion how to manage this?

Thanks!

with regards,

Bart

[Clyde Schechter]

So, first you need to create a variable that captures the quarter for each observation:

gen quarterly_date = qofd(date)
format quarterly_date %tq

Now you just want the standard deviation for each stock within each quarter:

by permno quarterly_date, sort: egen quarterly_volatility = sd(Stock_returns)

It appears you were already familiar with -egen-, so I presume you got stuck at identifying quarters from dates. Stata has a rich set of functions for translating dates from one time unit to another. If you will be using Stata regularly, it is well worth the time it takes to read -help datetimes- and the associated manual chapter. There's a lot there, and you won't remember all the details. But you will become familiar with the general approach and the scheme used for naming most of the functions. Then, when necessary, you can refer to the specific help for those functions as needed.

By the way: in general posting screenshots of data is not helpful. If this were complicated enough that testing out some code were needed, there is no way to import data from the screenshot into Stata. The best way to show example data is with the -dataex- command. Run -ssc install dataex- to install it, and then run -help dataex- to read the simple instructions for using it. In the future, whenever you post example data, use -dataex- to do it. It enables those who want to help you to import your data into Stata with a simple copy/paste operation and end up with a 100% faithful replica of what you are working with.

[William Lisowski]

Perhaps

generate qtr = qofd(date)
format qtr %tq
bysort permno qtr (date): egen sd_returns = sd( Stock_returns )

will do what you need. I included the (date) to ensure that your data remain sorted by permno and date, not by permno and quarter then randomly within quarter.

[Bart Jagers]

Dear Clyde and William,

Thank you very much for the code, it works perfectly!

Also thanks for the suggestion to use dataex for further posts, I was not aware of this function.

Bart