Questions on portfolio analysis

[Helen Chang]

Dear all,

I am having trouble in replicating a paper which involves the following procedures:

"1. each year, sort firms into quartiles (four portfolios) according to their MainVar, which we denote: very high, high, medium, and low. The portfolio formation year is denoted event year 0.
2. compute the average MainVar for each portfolio in each of the subsequent 20 years, holding the portfolio composition constant (but for firms that exit the sample).
3. repeat these two steps of sorting and averaging for every year in the sample period--generating sets of event-time averages, one for each calendar year in the sample."

Example of the data is shown below. Thank you in advance for any help you can provide.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long gvkey float MainVar int fyear
1000  -.05931227 1975
1000   -.0446973 1976
1004  -.03979895 1976
1004  -.03485366 1977
1004   -.0398857 1978
1004 -.032126352 1979
1004 -.033116795 1980
1004 -.008839214 1981
1004 -.016435781 1982
1004 -.016051311 1983
1004  -.01591699 1984
1004 -.016392171 1985
1004  -.01386716 1986
1004  -.04086016 1987
1004  -.03886304 1988
1004 -.032641336 1989
1004 -.028031914 1990
1004  -.02731301 1991
1004  -.02894183 1992
1004 -.033087466 1993
1004  -.04258972 1994
1004 -.027932523 1995
1004 -.019193865 1996
1004 -.015728466 1997
1004  -.02274823 1998
1004  -.02632774 1999
1004 -.024203593 2000
1004 -.020064574 2001
1004 -.024589034 2002
1004  -.04445133 2003
1004  -.03749997 2004
1004 -.036466308 2005
1004  -.03313513 2006
1004  -.03753422 2007
1004 -.016174972 2008
1004 -.026035206 2009
1004  -.02514132 2010
1004  -.01839163 2011
1004  -.02391577 2012
1004 -.027015166 2013
1004  -.02264704 2014
1004  -.01491521 2015
1004  -.03123271 2016
1007  -.13332947 1981
1007  -.13764992 1982
1009 -.020148207 1990
1009 -.021322455 1991
1009  -.02343671 1992
1009 -.020914357 1993
1010  -.01667689 1975
1010 -.018094808 1976
1010  -.01788943 1977
1010 -.013921067 1978
1010 -.013467587 1979
1010   -.0101989 1980
1010 -.004100148 1981
1010 -.003745733 1982
1010 -.005040575 1983
1010  -.02240258 1984
1010  -.04920172 1985
1010  -.04853592 1986
1010  -.05111405 1987
1010 -.032642946 1988
1010 -.025421444 1989
1010  -.10748334 1990
1010   -.1068446 1991
1010  -.11349825 1992
1010  -.09598474 1993
1012  -.05157713 1981
1012 -.070317276 1982
1012 -.070496686 1983
1012 -.071034856 1984
1012  -.05647805 1985
1012  -.03422371 1986
1012 -.018793667 1987
1012  -.02133924 1988
1013 -.071882375 1981
1013  -.06237599 1982
1013  -.05837036 1983
1013  -.06488147 1984
1013 -.067318715 1985
1013  -.03853164 1986
1013 -.026800627 1987
1013 -.016855292 1988
1013 -.015205235 1989
1013  -.02089499 1990
1013 -.033264887 1991
1013 -.027485143 1992
1013   -.0312597 1993
1013 -.026402794 1994
1013 -.030541744 1995
1013  -.02542709 1996
1013  -.02722378 1997
1013   -.0274456 1998
1013 -.036690764 1999
1013  -.06832756 2000
1013 -.068572104 2001
1013   -.0772918 2002
1013   -.0953388 2003
1013  -.06854397 2004
end

[Clyde Schechter]

//    IDENTIFY THE QUARTILES IN EACH FYEAR
capture program drop one_year
program define one_year
    xtile portfolio = MainVar, nq(4)
    exit
end

runby one_year, by(fyear)

//    CALCULATE THE PERFORMANCE OF EACH GVKEY IN EACH OF THE20  SUBSEQUENT YEARS
xtset gvkey fyear
forvalues i = 1/20 {
    gen out_year_`i' = F`i'.MainVar
}

//    AND AVERAGE OVER PORTFOLIO
forvalues i = 1/20 {
    by fyear portfolio, sort: egen portfolio_out_year_`i' = mean(out_year_`i')
}

To use this code you need to install the -runby- command, written by Robert Picard and me, available from SSC.

Note: In your example data, for several years there are fewer than 4 firms, so that it is not possible to assign them to quartiles. I imagine that is not the case in your real data. But if there are such years, the -runby- command will notify you that it has encountered errors, and there will be no results for those fiscal years.

[Attaullah Shah]

You can also use astile for creating quantile groups each year. astile is much faster than the Stata built-in xtile. Here is how to find the quartiles each year with astile

ssc install astile

bys year: astile portfolios = MainVar, nq(4)