How to calculate the annualized three-month rolling sample standard deviation (based on the actual trading days)?

[Noah Liu]

Dear stata users,

I' m following the Campbell et al.(2008) to calculate the failure probability of firms.
Now I have a panel data of the firms' daily stock returns, and try to calculate each firm's annualized three-month rolling standard deviation.
the equation is like this:

k is the index of trading days in month t-1, t-2, t-3, N is the total number of trading days in the three month period.
SIGMA is treated as missing if there are less than five nonzero observations over the three months in the rolling window

I've tried the following code I googled

*month identifier
gen mofd = mofd(date)
format mofd %tm

sort permno date
*3 month rolling window sd
bys permno: asrol ret, window(mdate 3) stat(sd)

*annualized
gen annual_sd = sd3_ret * sqrt(12) / sqrt(3)

but this code seems cannot identify the actual trading days of the past three months.

so I wonder if anyone could help me:
1. to find the code which can generate three-month rolling sample standard deviation based on the actually trading days ?
2. to count the nonzero observations over the three month rolling window.

here's part of my data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date double ret float mdate
10000 10024                    . 329
10001  9505                    . 312
10001  9506  .021739130839705467 312
10001  9509                    0 312
10001  9510                    0 312
10001  9511   .03191489353775978 312
10001  9512  .030927835032343864 312
10001  9513                    0 312
10001  9516  .009999999776482582 312
10001  9517 -.019801979884505272 312
10001  9518                    0 312
10001  9519                    0 312
10001  9520                    0 312
10001  9523                    0 312
10001  9524 -.010101010091602802 312
10001  9525 -.010204081423580647 312
10001  9526                    0 312
10001  9527  .010309278033673763 312
10001  9530                    0 313
10001  9531                    0 313
10001  9532                    0 313
10001  9533                    0 313
10001  9534                    0 313
10001  9537  .010204081423580647 313
10001  9538  .020202020183205605 313
10001  9539 -.009900989942252636 313
10001  9540                    0 313
10001  9541 -.009999999776482582 313
10001  9545                    0 313
10001  9546  .010101010091602802 313
10001  9547                    0 313
10001  9548                    0 313
10001  9551  .009999999776482582 313
10001  9552                    0 313
10001  9553                    0 313
10001  9554  .009900989942252636 313
10001  9555 -.019607843831181526 313
10001  9558                    0 314
10001  9559                    0 314
10001  9560                    0 314
10001  9561  .009999999776482582 314
10001  9562 -.009900989942252636 314
10001  9565  .015200004912912846 314
10001  9566                    0 314
10001  9567                    0 314
10001  9568                    0 314
10001  9569                    0 314
10001  9572                    0 314
10001  9573                    0 314
10001  9574                    0 314
10001  9575                    0 314
10001  9576  .019999999552965164 314
10001  9579                    0 314
10001  9580 -.009803921915590763 314
10001  9581                    0 314
10001  9582                    0 314
10001  9586                    0 314
10001  9587                    0 315
10001  9588                    0 315
10001  9589                    0 315
10001  9590                    0 315
10001  9593                    0 315
10001  9594                    0 315
10001  9595                    0 315
10001  9596                    0 315
10001  9597                    0 315
10001  9600                    0 315
10001  9601                    0 315
10001  9602                    0 315
10001  9603                    0 315
10001  9604                    0 315
10001  9607  .009900989942252636 315
10001  9608                    0 315
10001  9609                    0 315
10001  9610                    0 315
10001  9611                    0 315
10001  9614                    0 315
10001  9615                    0 315
10001  9616                    0 315
10001  9617                    0 316
10001  9618                    0 316
10001  9621                    0 316
10001  9622                    0 316
10001  9623                    0 316
10001  9624                    0 316
10001  9625                    0 316
10001  9628                    0 316
10001  9629                    0 316
10001  9630                    0 316
10001  9631                    0 316
10001  9632                    0 316
10001  9635                    0 316
10001  9636                    0 316
10001  9637                    0 316
10001  9638 -.009803921915590763 316
10001  9639                    0 316
10001  9643                    0 316
10001  9644                    0 316
10001  9645                    0 316
10001  9646                    0 316
end
format %d date
format %tm mdate

Thank you for your help in advance!

Best regards,

Noah Liu

[William Lisowski]

It seems likely that you need to create a Stata "business calendar" to define trading days.

See the output of

help business calendar

for more details, and the full documentation in the "Datetime business calendar" section of the Stata Data Management Reference Manual PDF included in your Stata installation and accessible from Stata's Help menu.

Let me add that if you've been goggling to understand Stata's handling of dates and times, then you should know that Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now - before tackling business calendars. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.