Generating portfolios with egen's, xtile (egen portfolio = xtile(ret6), nquantiles(10) by(dm))

[Thomas Maurer]

Party people,

I have 3.3 million observations in panel data format, so simple question does the command egen portfolio = xtile(ret6), nquantiles(10) by(dm), so generating 10 portfolios take usually more than 3 hours to do so? I know I can't expect an exact answer but a simple yes or no if it usually takes long would be fine.

Best,
T.

[Ahmed Abdalla]

It will take long time.
I advise you to drop some years and try your code first with limited number of years and months until you feel it works properly and then run your do file and come back later to see the out put

Ahmed

[Joe Canner]

Thomas,

I agree with Ahmed. Here is what the xtile() function is doing:

        by `touse' `by', sort: gen `byvar' = 1 if _n==1 & `touse'
        by `touse' (`by'): replace `byvar' = sum(`byvar')
        
        levels `byvar', local(K)
        foreach k of local K {
            local i 1
            _pctile `varlist' `weight' if `byvar' == `k' & `touse' , percentiles(`percnum') `altdef'
            foreach p of numlist `percnum' {
                if `i' == 1 {
                    replace `h' = `i' if `varlist' <= r(r`i') & `byvar' == `k' & `touse'
                }
                replace `h' = `++i' if `varlist' > r(r`--i')  & `byvar' == `k' & `touse'
                local i = `i' + 1
            }
        }

In other words, for each level of the by variable (dm) it is running pctile and then for each decile going through the data set and setting the value of portfolio. You could try running a pctile on a single level of the by variable and see how long it takes, in order to get an idea of what the overall time should be.

Incidentally, xtile() is part of egenmore, from SSC, which you should have mentioned.

Regards,
Joe

[Thomas Maurer]

Thanks you two, would you maybe have an idea how to generate a gap of 1 month between a formation period an investment period. To clarify, lets assume I have a momentum strategy of 12 month formation period skipping the preceding month i.e. the 12th and a holding period of 12 months. Lets say I want a portfolio return of such a strategy in t, that means I need the return of t-24 to t-14 to determine the 10 portfolios (best to worst), so to assign them into portfolios. 2 step i need the forward return so I guess f12.return, I need to skip t-13 and then invest or look at the 10 portfolios and their performance until t. BUT IT WOULD HELP ME THE MOST IF ONE COULD ADVISE ME HOW TO DELETE 36month periods which do not have consecutive monthly returns.
I use tsspell to get the spell, but don't know how to delete those that are not consecutive over 36 month periods.

[Roberto Ferrer]

The user-written command fastxtile by Micheal Stepner (SSC) is meant to be much faster than xtile. Give that a try, also.

[Ahmed Abdalla]

Roberto, I tried also fastxtile after being installed fromm SSC but it didn't work with the command Thomas has mentioned.
Ahmed

[Joe Canner]

Ahmed (and Thomas),

True; it will take a little work. fastxtile outputs the same set of scalars (r(r1), r(r2), ... , r(r10)) that xtile does. Accordingly, you can use the code I posted above (post #3) from the egenmore xtile() function to do the same thing. Just substitute fastxtile for _pctile and use the nquantiles() option instead of percentiles().

Regards,
Joe

[Thomas Maurer]

Disregard this post did not see the other two, will try the code right now
well currently I am trying it with egen portfolio = xtile(ret6), nquantiles(10) by(dm) to create the portfolios, but I looked under help fpr fastxtile, and I am not sure how to transform this to the previous command this is how the sintax for fastxtile looks like fastxtile newvar = exp [if] [in] [weight] [, options], I need 10 portfolios and for another task 3, but let's focus on the 10 right now. And this should be preferably indexed as t+11

[Thomas Maurer]

I deleted some of the data and saw that there is a problem with xtile, if I have 10 observations 1 missing, stata sorts 9 portfolios that is understandable but it does not assign the portfolio value 10. I mean one of those stocks should have the highest return in that range at that given time or not? or does xtile works differently I need a function which will at a given timeperiod for the return range at that given time t, assign the stocks into 10 portfolios. Oke if there are not even 10 stocks that is bad, but should not be the case with my data it was just a test.