Weighted returns for the entire sector(all panels) in a panel study

[Aamina Khurraa]

Hi

I am stuck in understanding how to generate the sector_return (SR) using the average of returns (R) weighted by the Lagged Assets (LAit) for a panel data of 33 firms and monthly time period (mT) of 10years. (i is the individual panel/firm, t is the time period). T_LAit is the total (sum) of lagged assets in a single month for all the panels. The sector return are meant to be the weighted average of all the firm (260) in a particular time period(Tm). please see the data below for two months over few panels:

Tm	Panle ID	R	Avg_R	LAit	T_LAit	SR
2000m3	271	-0.04945	-0.00169	22918.31	295536.1
2000m3	23	-0.08254	-0.00169	6302.238	295536.1
2000m3	169	0.036959	-0.00169	11351.4	295536.1
2000m3	227	-0.01166	-0.00169	255.2777	295536.1
2000m3	139	0.048581	-0.00169	53.15778	295536.1
2000m3	245	0.062884	-0.00169	697.7322	295536.1
2000m3	280	-0.03818	-0.00169	355.6699	295536.1
2000m3	124	-0.2198	-0.00169	583.3955	295536.1
2000m3	130	0.017411	-0.00169	128.8693	295536.1
2000m3	109	-0.08013	-0.00169	50071.98	295536.1
2000m3	249	0.03796	-0.00169	103.0366	295536.1
2000m3	173	-0.07338	-0.00169	7.051637	295536.1
2000m3	49	0.068058	-0.00169	268.2257	295536.1
2000m3	241	-0.00655	-0.00169	852.3345	295536.1
2000m3	219	-0.01759	-0.00169	614.9119	295536.1
2000m3	47	-0.02502	-0.00169	28783.69	295536.1
2000m3	331	-0.1405	-0.00169	401.0868	295536.1
2000m3	270	0.070845	-0.00169	59.16751	295536.1
2000m3	230	0.258599	-0.00169	2166.762	295536.1
2000m3	250	-0.04024	-0.00169	229.3511	295536.1
2000m3	243	-0.04036	-0.00169	470.4795	295536.1
2000m3	296	0.004157	-0.00169	31455.94	295536.1
2000m3	319	0.223983	-0.00169	146.6953	295536.1
2000m3	175	0.021453	-0.00169	1077.749	295536.1
2000m3	76	-0.08253	-0.00169	2741.97	295536.1
2000m3	327	-0.00013	-0.00169	352.8115	295536.1
2000m3	167	0.007846	-0.00169	1.140574	295536.1
2000m3	236	-0.02666	-0.00169	131126.2	295536.1
2000m3	170	0.073028	-0.00169	520.3007	295536.1
2000m3	238	-0.02326	-0.00169	969.5063	295536.1
2000m3	129	-0.0261	-0.00169	469.6174	295536.1
2000m4	319	-0.09971	-0.09524	179.5526	286426.1
2000m4	130	0.242249	-0.09524	131.113	286426.1
2000m4	131	-0.17476	-0.09524		286426.1
2000m4	280	-0.15066	-0.09524	342.0907	286426.1
2000m4	270	-0.13837	-0.09524	63.35926	286426.1
2000m4	243	-0.06466	-0.09524	451.4931	286426.1
2000m4	47	-0.07226	-0.09524	28063.59	286426.1
2000m4	227	-0.07598	-0.09524	252.3005	286426.1
2000m4	23	0.163546	-0.09524	5782.043	286426.1
2000m4	109	-0.04672	-0.09524	46059.64	286426.1
2000m4	219	-0.04588	-0.09524	604.0935	286426.1
2000m4	76	-0.01293	-0.09524	2515.687	286426.1
2000m4	241	-0.00187	-0.09524	846.7531	286426.1
2000m4	250	-0.11132	-0.09524	220.1228	286426.1
2000m4	271	-0.04753	-0.09524	21785.08	286426.1
2000m4	175	-0.2	-0.09524	1100.87	286426.1
2000m4	170	-0.08341	-0.09524	558.2969	286426.1
2000m4	296	-0.18951	-0.09524	31586.7	286426.1
2000m4	238	-0.14271	-0.09524	946.9567	286426.1
2000m4	124	-0.74851	-0.09524	455.1642	286426.1
2000m4	249	-0.27064	-0.09524	106.9478	286426.1
2000m4	139	-0.1223	-0.09524	55.74022	286426.1
2000m4	327	-0.2192	-0.09524	352.7651	286426.1
2000m4	173	0	-0.09524	6.534202	286426.1
2000m4	49	0.329509	-0.09524	286.4805	286426.1
2000m4	245	-0.0775	-0.09524	741.6086	286426.1
2000m4	236	-0.11906	-0.09524	127629.8	286426.1
2000m4	331	-0.26907	-0.09524	344.7355	286426.1
2000m4	169	-0.07287	-0.09524	11770.93	286426.1
2000m4	229	-0.20032	-0.09524		286426.1
2000m4	129	-0.14667	-0.09524	457.3594	286426.1
2000m4	167	0.106096	-0.09524	1.149523	286426.1
2000m4	230	-0.08	-0.09524	2727.085	286426.1

Exactly what I need to measure from the data is "find SR which is generated by taking average returns (R), weighted by Lagged Assets (LAit)"

Best Regards

[Clyde Schechter]

I do not work in finance or economics, so I am relying entirely on your definition of sector average returns and translating it into code.

by Tm, sort: egen numerator = total(LAit*R)
by Tm: egen denominator = total(LAit)
gen SR = numerator/denominator

Gentle reminder to always use -dataex- when showing example data. You have used it successfully before. Please continue to do so when showing data.

[Aamina Khurraa]

Thanks a million Clyde Schechter

Your support is remarkable as always. I did manage to get around what I wanted to do in above.

hope to have further assistance from you next time .

Best Regards