Rolling Terciles (or tertiles)

[Carlo Da Dalt]

Hi there,

My dataset is organised in three columns.

First column (date): weekday dates (excluding public holidays) from 2002 to 2014
Second column (index): S&P500 index level
Third column (return_1d): daily returns (computed from the second column)

I would like to generate 1-day return "terciles" (or tertiles) using 1-year rolling windows. In other words, I would like to generate 2 new columns: the first one should contain for each day, the value of the 33rd percentile of 1-day return for the previous 1-year, and in the second column, the value of the 66th percentile of 1-day returns.

I have tried mvsumm without much success (to be precise, to the best of my knowledge mvsumm would not give me terciles anyways, but I would have been happy at this stage to generate quartiles too). I get an entire column with missing observations. The code I am using is:
"mvsumm return_1d, stat(p25) win(365) gen(bottom_quartile) end"
"mvsumm return_1d, stat(p75) win(365) gen(top_quartile) end"
I think one of the reason why the code is returning me missing values, is because mvsumm requires me to "tsfill" the date column (i.e., mvsummdoes not allow gaps in the timeseries, and I do not have observations for weekends and public holidays), and all the filled dates have missing values for return_1d.

I am aware of the command "rolling" but I cannot figure out how to generate tertiles out of it.

Any help would be much appreciated.

Best,

Carlo

[Nick Cox]

mvsumm is a user-written (community contributed) command from SSC, as you are asked to explain.
See https://www.statalist.org/forums/help#stata

12.1 What to say about your commands and your problem

If you are using user-written commands, explain that and say where they came from: the Stata Journal, SSC, or other archives. This helps (often crucially) in explaining your precise problem, and it alerts readers to commands that may be interesting or useful to them.

I haven't used mvsumm for some time but rangestat (also SSC) offers such functionality. A small tweak of one of its examples seems to serve:

clear
set obs 10000

* daily dates without weekends
gen t = _n + 10000
format t %td
drop if inlist(dow(t), 6, 0)

* random stuff
set seed 2803
gen ret = rnormal()

* ssc inst moremata needed for -mm_quantile()-
mata:  
    mata clear
    real rowvector myquantile(real colvector X) {
         if (rows(X) < 2) return(rows(X), J(1, 2, .))
         else return(rows(X), mm_quantile(X, 1, (0.33, 0.67)))
    }
end

rangestat (myquantile) ret, interval(t -364 0)
rename myquantile1 count
label var count
label var myquantile2 "lower tertile"
label var myquantile3 "upper tertile"

set scheme s1color
line ret myquantile? t, lc(gs12%20 red blue) xtitle("") ytitle(tertiles)

Omit the transparency syntax %20 in Stata <15.

More generally, searching the forum for mentions of this command will give you other examples.

[Carlo Da Dalt]

Excellent. It works a beauty. Thank you for your prompt reply.

12.1 What to say about your commands and your problem

If you are using user-written commands, explain that and say where they came from: the Stata Journal, SSC, or other archives. This helps (often crucially) in explaining your precise problem, and it alerts readers to commands that may be interesting or useful to them.

→ Noted.