creating deciline portfolios based on past returns

[Hugo van Ginkel]

Dear All,

At the moment I am researching mutual funds. Based on a big dataset of monthly mutual funds returns (25000 funds), I want to analyze if past mutual fund returns have predictive power for the future. I already know my methodology, but I am new to Stata, So I don’t know how to implement it.

My dataset starts at 12-05-1992 and I want to divide all the funds in my dataset into decile portfolios, based on one-year previous returns. So, at 12-05-1993 I want to create decile portfolios based on the returns from 12-05-1992 until 12-05-1992. Afterwards, I want to calculate the returns of all the decile portfolio over the next month, thus the returns from 12-05-1993 until 12-06-1993. After this month, I want to repeat the same construction of decline portfolios and calculation of all the returns for all the months in my dataset (300 months in total). In the table you can find I small part from my dataset.

Date	fund 1	fund 2	fund 3	fund 4	fund 5
12-5-2017	0,056299	0,056718	0,023205	0,05555	0,051085
12-4-2017	0,018607	0,017451	0,033626	0,017794	0,025729
12-3-2017	0,003946	0,040307	0,005913	0,003284	0,029364
12-2-2017	0,018345	-0,02224	0,03942	0,017649	0,015886
12-1-2017	0,030336	0,026847	0,012194	0,029575	0,030714
12-12-2016	0,052462	0,063694	0,010308	0,051673	0,042848
12-11-2016	-0,00163	0,03499	-0,05639	-0,00231	0,015182
12-10-2016	-0,00131	-0,01351	0,022562	-0,00204	-0,01041
12-9-2016	-0,0046	0,014794	-0,00945	-0,00533	-0,00046
12-8-2016	0,055299	0,096279	0,023195	0,054525	0,04768

Could anyone help me? I am totally stuck and don´t how to proceed.

Kind Regards,

HG

[Phil Bromiley]

You'll increase your chances of a useful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output (fixed spacing fonts help), and sample data using dataex.

First, you'll almost always find it easier to work with data in long format - see reshape. And you'll want to xtset your data so you can use lag and lead operators. Then, there is the percentile function (pctile). You are probably going to want rangestat to do the rolling calculations. This is quite doable, but requires a few steps.