Creating and summarizing monthly portfolios

[chiaihuang]

Hi everyone,

I have the following variables:

CompanyID | Year | Month | EarnRank | MV | Return | EarnRank Quintile (0-4)

I wish to find the following:

1. The average monthly return from each earnrank quintile ie. for quintile 1, each month, buying the companies with EarnRank quintile=0

2. A portfolio of selling quintile 0 and buying quintile 4 each month

3. Additional limitation: only include months has more than 10 companies

4. I also need both equal weighted and value weighted portfolios

I'm not sure if this explanation is clear enough, please let me know if it is not. Thank you all in advance.

Henry

[mkeintz]

1. Exactly how many portfolios are you asking to be constructed?  What are their attributes?
2. Are you rebalancing every month?
3. How many equities should be in the portfolio?  Do you intend to include all companies every month?  If a company is present in month K but no present in K+1, does that mean it equities are to be sold at the end of month K?
4. What is the total portfolio starting value?
5. What does the phrase "include only months with 10 companies" mean?  No rebalancing for any month with fewer than 10 companies?  No portfolio?  Carry forward the previous month's portfolio?
6. When you refer to an "equal weighted" portfolio, do you mean that every month should include transactions to redivide current and new holdings into equal amounts?
7. When you say "value weighted", what is the value measure?

This is far from a description which allows someone to operationalize the topic.  How about some actual data?  Not just a list of variables.

[chiaihuang]

Sorry for the unclarity.

Quintile portfolios mean five portfolios based on the quintile variable which has values of 0-4. I do not need rebalancing or compounding, just simple returns. I'll put it this way, I have five groups labeled 0-4, and each group needs to be further subgrouped by year and month, and each month's equal-weighted returns and value-weighted returns are the simple average returns and market value (MV) weighted average returns, respectively, of the individual stocks that month. There is no need to consider starting value, because everything will be in terms of percentage returns. Also, they are monthly portfolios, so literally it means holding for the month only. Number of stocks in each portfolio depends on the data, they are not the same for each portfolio, but I'd like to include only portfolios have more than 10 stocks. The following is what the actual dataset looks like.

Company	Year	Quarter	EPS	EarnRank	Date	date_pred	year_stated	month	MV	Return	retpercent	rank_er
X13194E	2009	4	-1.36	9.4	18484	18849	2009	8	5706.43	-0.04531	-4.53138	2
X13194E	2010	1	-4.54	8.6	18564	18929	2010	10	5819.21	0.007602	0.760184	2
X13194E	2010	2	4.86	11.6	18655	19020	2010	1	6811.63			2
X13194E	2010	3	11	15.8	18380	18745	2010	4	5864.32	-0.05186	-5.18571	2
X13194E	2010	4	4.34	7.2	18849	19214	2010	8	7014.63	0.016144	1.614413	2
X13194E	2011	1	-5.64	7.6	18928	19293	2011	10	7014.63	0.003276	0.327562	2
X13194E	2011	2	0.27	11.4	19022	19387	2011	1	7691.28	0.042808	4.280803	2
X13194E	2011	3	10.14	16	18745	19110	2011	4	7713.83	-0.01756	-1.75573	2
X13194E	2011	4	7.94	8.8	19214	19579	2011	8	8819.04	-0.03526	-3.52556	2
X13194E	2012	1	0.71	6.4	19296	19661	2012	10	9292.69	0.061905	6.190519	2
X13194E	2012	2	7.9	10	19388	19753	2012	1	9450.57	0.036804	3.6804	2
X13194E	2012	3	13.36	16.4	19110	19475	2012	4	9112.25	0.097753	9.775325	2
X13194E	2012	4	3.21	10.2	19578	19943	2012	8	9105.9	-0.0115	-1.14952	2
X13194E	2013	1	6.385	5.6	19659	20024	2013	10	9681.44	0.010461	1.04607	2
X13194E	2013	2	13.509	9.8	19753	20118	2013	1	9701.99	-0.03024	-3.02444	2
X13194E	2013	3	16.725	15.6	19474	19839	2013	4	9044.58	-0.01728	-1.72788	2
X13194E	2013	4	7.213	10	19942	20307	2013	8	10154.21	-0.02736	-2.73604	2
X13194E	2014	1	6.53	5.6	20023	20388	2014	10	10544.75	0.050631	5.063119	2
X13194E	2014	2	9.966	10.4	20117	20482	2014	1	9311.45	-0.1122	-11.2196	2
X13194E	2014	3	17.65	15.4	19841	20206	2014	4	10298.09	0.055309	5.530861	2
X13194E	2014	4	9.989	8	20310	20675	2014	8	9229.23	-0.0302	-3.02009	2
X13194E	2015	1	8.536	4.8	20390	20755	2015	10	9969.21	0.049979	4.997879	2