I'm interested in the aggregated figures for the whole sector for every quarter at the end, so I don't actually need any company-specific average of every pair with other companies. To aggregate the average number of common holders per qtr/sector, it would be enough to have the individual figures for every pair. This way I could say that the average pair of firms within a given sector in quarter Z has X common holders, which hold Y percent overall and on average in this pair of companies. That means that your were right in the point that it would not be economically correct to add up the shares of both companies. I think the right methodology here is to identify common holders for a pair of firms and calculate an average of their holdings. So in a first step, I would have to calculate the percentage of shares hold for every investor/company/quarter in order to calculate the sum (e.g. 5% in AA, 10% in BB divided by 2 = 7,5% on average in this pair for this investor for this quarter). Let me try to give you a more complex example. Shareholder Company Quarter SharesHold SharesOutstanding SectorClassifier A AA 31MAR2006 15000 1.000.000.000 2 B AA 31MAR2006 1000 1.000.000.000 2 C AA 31MAR2006 35000 1.000.000.000 2 C BB 31MAR2006 1000 250.000.000 2 B BB 31MAR2006 25000 250.000.000 2 Z BB 31MAR2006 12000 250.000.000 2 A CC 31MAR2006 25000 500.000.000 2 B CC 31MAR2006 35000 500.000.000 2 C CC 31MAR2006 45000 500.000.000 2 Z CC 31MAR2006 5000 500.000.000 2 E CC 31MAR2006 15000 500.000.000 2 B DD 31MAR2006 25000 300.000.000 2 F DD 31MAR2006 35000 300.000.000 2 E EE 31MAR2006 300000 1.000.000.000 2 F EE 31MAR2006 350000 1.000.000.000 2 A EE 31MAR2006 25000 1.000.000.000 2 Step 1) Calculate the percentage of shares owned by every investor for every company. (e.g. A holds 15000/1.000.000.000=0,0015% in AA) Step 2) Start the pairwise comparison of company AA and BB for the first quarter. They have 2 common shareholders, "B" and "C". This is the first value I need. Step 3) The second value refers to the overlap of the common holdings in order to show the economical impact. E.g. B holds 0,0001% in AA and 0,01% in BB, that means he holds (0,0001%+0,01%)/2= 0,00505% on average in both firms. The second common holder, C, has an average of (0,035%+0,0004%)/2=0,00195% in both companies. As a result, the aggregated amount of ownership stakes held by common owners, for this pair would be 0,00505%+0,00195%= 0,007%. It is, so to say, the sum of all the common holder's average stakes in both companies. Another way to aggregate this on the firm-pair level would be to use a MIN function instead of a sum function. That means, you calculate the stakes of each common shareholder for the companies, and sum up the smaller value for each shareholder instead of the average. In this case, this would mean taking the sum of min(0,0001%;0,01%)=0,0001% for A and min(0,035%;0,0004%)=0,0004% for B, so 0,0005%. These two measures are proposed by Gilje, Gormley and Levit in "The rise of common ownership", if anyone is interested. Of course it would be optimal if I had both possilities at the end to compare the results. After having compared firms AA and BB, the next comparison would be AA and CC, starting with Step 2 again. After comparing AA and CC, the next pair would be AA and DD.. until every company has been compared to AA. Then starting with BB and CC. Like I said, I do not need any average for company AA at the end. I just need the values of Step 2 and Step 3 for every pair. This way, I can, for a given quarter calculate the next step. Let's say we have 20 companies in the sector, this would mean I would get n*(n-1)/2=20*19/2=190 distinct pair values. Probably it would be helpful to create a new sheet which gives me the pair values in a format like this: Quarter Sector CompanyPair NbrCommonHolders Sum_Avg_Holdings Sum_MIN_Holdings 31MAR2006 2 1 2 0,007% 0,0004% 31MAR2006 2 2 X X,XX% X,XX% 31MAR2006 2 3 X X,XX% X,XX% 31MAR2006 2 4 X X,XX% X,XX% Step 4): Aggregate the results of every firm-pair for a given quarter. This is the main task, so aggregating the variables NbrCommonHolders, Sum_Avg_Holdings and Sum_MIN_Holdings for every quarter and every sector. A poosible result would be: "From MAR2003 to MAR2010, the number of common holders for an average pair of firms in the Airline sector has increased from 1,7 to 4,2. Their economical impact of these holdings has also risen. In MAR2010, common holders held stakes equal to 15% for an average pair of firms. In MAR2003, this figure was significantly lower with 4,3%." Of course, Step 4 is by far the easiest task. In order to measure some differences, like I said before, it would be awesome to have the pairwise results in a new sheet. Thanks a lot!
... View more