Following different portfolios over time

[Koen van Hanegem]

I have a data set of American companies over the period 1985-2015 with circa 300k observations of leverage-ratios. So every company has 30 observations.

For a figure I have to construct the average leverage ratios of four portfolios in 'event time' . So what I want to do is the following: each year, I sort firms into quartiles (creating four portfolios) according to their leverage-ratio. The portfolio is denoted event year 0 and I want to compute the average leverage for each portfolio in each of the following 20 years. I want to do this by creating portfolios for year=1985 and so on. How can I follow the different portfolios over the years and compute their average leverage-ratios?

To illustrate, from one year I can derive the 4 quartiles and their average leverage. The next step would be to isolate each quartile (each group of firms) and follow particular group and see how their average leverage develops over time. Can anyone help me with isolating a quartile and filtering the data in a way that you can observe the leverage of these firms in t+1, t+2, etc.

Please let me know if my issue is not clear.

Thanks in advance,

Koen van Hanegem

[Clyde Schechter]

If I have understood correctly what you want, the following code will do it.

You did not provide example data, so I have made a toy data set to illustrate the approach. To the extent that your data is organized differently you may have difficulty adapting the code to your needs. In the future, when asking for help with code, you should always use the -dataex- command and provide an example of your data to avoid this kind of difficulty.

You do not specify in your problem what you want to do, for example, when you get to a base year of 1996. At that point, you no longer have 20 years worth of future data to look at. The code below just uses as many years as you have. You also do not say whether you want your 20 year follow-up to include the base year itself or not. The code below includes the base year and years 1 through 20 thereafter.

Finally, you did not specify how you want the results organized. I have put them in a separate data set in long layout. (Note: the code leaves this new data set in memory at the end, but it is not saved as a permanent file at this point.) If you plan additional analysis of this data, you probably will want this arrangement. But for some purposes you may find that you need to reshape it, or perhaps find some way to combine it with the original data. I leave all of that to you.

//    CREATE A DEMONSTRATION DATA SET
clear*
set obs 100
gen id = _n
expand 31
by id, sort: gen year = 1984 + _n
set seed 1234
gen lev_ratio = rgamma(0.1, 2)
xtset id year

//    CREATE A FILE TO HOLD THE RESULTS
capture postutil clear
tempfile results
postfile handle int base_year int portfolio int follow_year float avg_lev_ratio ///
    using `results'
    
    
    
//    CALCULATE RESULTS
foreach b of numlist 1985/2015 { // `b' == base year
    xtile portfolio= lev_ratio if year == `b', nq(4)
    by id (portfolio), sort: replace portfolio = portfolio[1]
    by portfolio year, sort: egen avg_lev_ratio = mean(lev_ratio)
    forvalues p = 1/4 {    // 4 PORTFOLIOS
        forvalues i = 0/20 { // FOLLOW OVER 20 YEARS
            summ lev_ratio if year == `b'+`i' & portfolio == `p', meanonly
            if `r(N)' > 0 {
                post handle (`b') (`p') (`b'+`i') (`r(mean)')
            }
        }
    }
    drop portfolio avg_lev_ratio
}
postclose handle
use `results', clear

If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.



When asking for help with code, always show example data. When showing example data, always use -dataex-.