Compute annual standard deviation of daily stock returns for firms with different fiscal year end-dates

[Fabian Schneider]

Dear statalist members,

Unfortunately I'm stuck with my analysis and I cannot find further information anywhere. Hopefully someone else already dealt with this kind of problem.

I have to generate the stock return volatility (=standard deviation) of daily stock returns on a per firm and per fiscal year basis.
Starting with daily stock returns (from CRSP) for a large number of firms between 1993 and 2013, I will have to arrive to 1 line for return volatility per firm-year. The main problem here: the accounting data from Compustat, to which stock return volatility should be merged, is on a fiscal year basis. The included firms have different fiscal year end-dates and some even change their end-dates over time.

So my question is: what should my code be to arrive at this 1 line per firm-year such that my complete dataset at the end looks like the following:

Firm fyear fyr (end-date) sales return volatility (%)
1 1996 6 1000 2.5
1 1997 6 1100 1.8
2 2004 12 500 3.7
2 2009 3 800 1.5
3 1999 12 2200 2.2


Many thanks and best regards,
Fabian

[Clyde Schechter]

This question is essentially the same one that was asked by another poster yesterday, with daily/fiscal year substituting for monthly/yearly, respectively. See the thread at http://www.statalist.org/forums/foru...ly-return-data

[Fabian Schneider]

Thank you Clyde, I was able to manage it.

[Asad Khan]

I have actually the same problem and I have tried the solution advised by Clyde Schechter However, my data is based on daily returns, so i have adjusted your solution to my data. Unfortunately I dont get the right results and alot of values are missing.

These are the changes I have made to your solution.

format date %d
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 - L253.sum_ret_sq)/(L1.n_obs-L253.n_obs)-((L1.sum_ret - L253.sum_ret)/(L1.n_obs-L253.n_obs))^2
gen sd = sqrt(variance)


Could someone please tell me what I am doing wrong ?

[Clyde Schechter]

Without seeing the actual results you are getting, it is hard to know. The code looks basically correct.

Here's one hypothesis. You refer to daily returns. Does this include observations for weekends and holidays? If not, and if your date variable is an ordinary Stata numeric date, then the lag operators will not work properly. For example, if today is Monday, the 1st lag operator will seek values for Sunday. But if there are no observations on Sunday then it will be a missing value. You will have missing values for at least all the Mondays as a result. You will also have missing values for any day that follows a holiday.

The solution to this is to create business calendar dates. See the online help and manual for how to create and use a business calendar in Stata. (I can't advise you because these never come up in my line of work and I have no direct experience of them.) But with business calendar dates, the "day before" a Monday will be the preceding Friday. Everything should work fine then.

If this is not the problem you are having, I suggest you repost showing your output (or at least a representative sample of it) so we can try to troubleshoot it more specifically.

An alternative to consider is applying the new command -tsegen- written by Robert Picard and Nick Cox to this problem (though I believe you will still need to put your dates into business calendar representation to use it for this application). You can get it from SSC.

[Asad Khan]

Thank You Clyde Schechter, it really helped me, I have managed to find the volatility.
But now I have a bigger problem, I can not merge my CRSP dataset with CRSP/compustat dataset. I have tried it with CUSIP, and managed to make them both 8digits codes. However, if I want to merge these files by newly created cusip but stata wont merge it, I don't know what I am doing wrong. I am not so good in explaining and in Stata :-), I hope you understand what I mean.
Do you have any solution for my problem ?

[Clyde Schechter]

Asad, you don't need to explain so much as show. Post a sample of the data in each data set you are using, along with the code you tried and what Stata responded. All of these should be put in code blocks (see FAQ for how to create code blocks on this forum). And all should be directly copy/pasted into the code blocks, not re-typed by hand.

Also, this is really a new problem, not a continuation of your original question. It will be more helpful to other users of the forum if you start a new thread for this.