rebalance portfolio using June of each year

Zhongda — Wed, 09 Nov 2022 04:34:04 GMT

Hi ALL,

Again, thank you so much for getting the help so far.

Currently, I am working on rebalance the portfolio, and make it ten groups. based on the monthly return of the stocks. Before I know how to rebalance it each month, but at this moment, I need to rebalance it using the market cap of each June as the weights for the coming one year. I do not know how to deal with it.

there are two difficulties here,

First is how to deal with the missing value, for example,

in June 1986 we have 10 stocks and according to the market cap you rank them from 1 to 10. but for July 1986, you may have some refill of the stocks, either the original 10 stocks could delist some or there are some new adding stock in July.

Second is how to use this weight for each June to group the data for next coming 12 month.

I upload a very simple dataset which the first problem is excluded, but I still would be more than happy if you can include the code that solve this first difficulty as well.

here is the dataset, please let me know if you have any suggestions, thanks a lot.

MTHCALDT	PERMNO	MTHCAP	MTHRET	year	month
1/31/86	10000	16100	0.707317	1986	1
1/31/86	10001	6033.125	0.065217	1986	1
1/31/86	10002	13659.38	-0.01064	1986	1
1/31/86	10003	41800	0.189189	1986	1
2/28/86	10000	11960	-0.25714	1986	2
2/28/86	10001	6156.25	0.020408	1986	2
2/28/86	10002	15568.75	0.139785	1986	2
2/28/86	10003	42275	0.011364	1986	2
3/31/86	10000	16330	0.365385	1986	3
3/31/86	10001	6217.813	0.025352	1986	3
3/31/86	10002	16670.31	0.070755	1986	3
3/31/86	10003	41800	-0.01124	1986	3
4/30/86	10000	15172	-0.09859	1986	4
4/30/86	10001	6279.375	0.009901	1986	4
4/30/86	10002	17551.56	0.052863	1986	4
4/30/86	10003	39187.5	-0.0625	1986	4
5/30/86	10000	11793.86	-0.22266	1986	5
5/30/86	10001	6217.813	-0.0098	1986	5
5/30/86	10002	17184.38	-0.02092	1986	5
5/30/86	10003	44792.5	-0.00606	1986	5
6/30/86	10000	11734.59	-0.00503	1986	6
6/30/86	10001	6033.125	-0.01324	1986	6
6/30/86	10002	14907.81	-0.13248	1986	6
6/30/86	10003	47523.75	0.060976	1986	6
7/31/86	10000	10786.34	-0.08081	1986	7
7/31/86	10001	5971.563	-0.0102	1986	7
7/31/86	10002	15642.19	0.049261	1986	7
7/31/86	10003	42061.25	-0.11494	1986	7
8/29/86	10000	4148.594	-0.61538	1986	8
8/29/86	10001	6402.5	0.072165	1986	8
8/29/86	10002	15642.19	0	1986	8
8/29/86	10003	42607.5	0.012987	1986	8
9/30/86	10000	3911.531	-0.05714	1986	9
9/30/86	10001	6317.625	-0.00397	1986	9
9/30/86	10002	16743.75	0.070423	1986	9
9/30/86	10003	40149.38	-0.05769	1986	9
10/31/86	10000	3002.344	-0.24242	1986	10
10/31/86	10001	6565.375	0.039216	1986	10
10/31/86	10002	15715.63	-0.0614	1986	10
10/31/86	10003	45338.75	0.129252	1986	10
11/28/86	10000	3182.484	0.06	1986	11
11/28/86	10001	6937	0.056604	1986	11
11/28/86	10002	15715.63	0	1986	11
11/28/86	10003	44246.25	-0.0241	1986	11
12/31/86	10000	1981.547	-0.37736	1986	12
12/31/86	10001	6937	0.016154	1986	12
12/31/86	10002	14540.63	-0.07477	1986	12
12/31/86	10003	42061.25	-0.04938	1986	12
1/30/87	10000	1581.531	-0.21212	1987	1
1/30/87	10001	6689.25	-0.03571	1987	1
1/30/87	10002	15935.94	0.09596	1987	1
1/30/87	10003	47523.75	0.12987	1987	1
2/27/87	10000	1581.531	0	1987	2
2/27/87	10001	6193.75	-0.07407	1987	2
2/27/87	10002	15715.63	-0.01382	1987	2
2/27/87	10003	48070	0.011494	1987	2
3/31/87	10000	973.25	-0.38462	1987	3
3/31/87	10001	6317.625	0.037486	1987	3
3/31/87	10002	15128.13	-0.03738	1987	3
3/31/87	10003	48070	0	1987	3
4/30/87	10000	912.4219	-0.0625	1987	4
4/30/87	10001	6069.875	-0.03922	1987	4
4/30/87	10002	15275	0.009709	1987	4
4/30/87	10003	45611.88	-0.05114	1987	4
5/29/87	10000	851.5938	-0.06667	1987	5
5/29/87	10001	5636.313	-0.07143	1987	5
5/29/87	10002	15862.5	0.038462	1987	5
5/29/87	10003	39985.75	-0.12575	1987	5
6/30/87	10000	851.5938	0	1987	6
6/30/87	10001	5822.125	0.052687	1987	6
6/30/87	10002	15862.5	0	1987	6
6/30/87	10003	38890.25	-0.0274	1987	6
7/31/87	10001	5946	0.021277	1987	7
7/31/87	10002	16450	0.037037	1987	7
7/31/87	10003	36973.13	-0.0493	1987	7
8/31/87	10001	6441.5	0.083333	1987	8
8/31/87	10002	15862.5	-0.03571	1987	8
8/31/87	10003	39164.13	0.065144	1987	8
9/30/87	10001	6200	-0.02163	1987	9
9/30/87	10002	14687.5	-0.07407	1987	9
9/30/87	10003	37520.88	-0.04196	1987	9
10/30/87	10001	6324	0.02	1987	10
10/30/87	10002	11750	-0.2	1987	10
10/30/87	10003	29030.75	-0.22628	1987	10
11/30/87	10001	6138	-0.02941	1987	11
11/30/87	10002	11896.88	0.0125	1987	11
11/30/87	10003	31813	0.113208	1987	11
12/31/87	10001	5828	-0.03354	1987	12
12/31/87	10002	11750	-0.01235	1987	12
12/31/87	10003	30716	-0.03448	1987	12

topic rebalance portfolio using June of each year in SAS Programming

rebalance portfolio using June of each year