Identifying chains of connections across observations

[Dalhia Mani]

Hello,

I'm struggling with a problem that I can't seem to get my head around and will appreciate any suggestions. Here is how the data looks:

company_name	shareholder	percent
indian acrylics ltd	bloomen flora ltd	0.02
indian acrylics ltd	sab udyog ltd	0.13
indian acrylics ltd	SS infra ltd	0.03
SS infra ltd	bloomen flora ltd	0.04
SS infra ltd	sab udyog ltd	0.02
SS infra ltd	Strip wheels ltd	0.04
Strip wheels ltd	bloomen flora ltd	0.04
Strip wheels ltd	sab udyog ltd	0.06

What I am trying to do is this: I want to identify chains of ownership ties (and the ultimate owner ownership) for each firm. For instance, in row 3 below, Indian acrylics is owned by SS infrastructures ltd (at 3% equity), which in turn is owned by bloomen flora ltd (at 4% equity) etc. I would also like to also identify the ultimate parent in each of these ownership chains (last col below). How do I do this? Any suggestions will be very much appreciated.

company_name	shareholder	percent	shareholder2	percent2	shareholder3	percent3	ultimate_parent
indian acrylics ltd	bloomen flora ltd	0.02					bloomen floren ltd
indian acrylics ltd	sab udyog ltd	0.13					sab udyog ltd
indian acrylics ltd	SS infra ltd	0.03	bloomen flora ltd	0.04			bloomen floren ltd
indian acrylics ltd	SS infra ltd	0.03	sab udyog ltd	0.02			sab udyog ltd
indian acrylics ltd	SS infra ltd	0.03	Strip wheels ltd	0.04	bloomen flora ltd	0.04	bloomen floren ltd
indian acrylics ltd	SS infra ltd	0.03	Strip wheels ltd	0.04	sab udyog ltd	0.06	sab udyog ltd

Thanks again

[William Lisowski]

One question that comes to mind: is it possible that companyB could be a shareholder of companyA, and also that companyA could be a shareholder of companyB?

[Dalhia Mani]

Thanks very much for the response. I appreciate it.

Yes. Cross-shareholdings are possible. Here is an example:

company_name, shareholder, percent
A, B, .05
B, A, .02
B, C, .01

In this case, I need to figure out how to make the final data look like this:

company_name, shareholder, percent, shareholder2, percent2, ultimate_parent
A, B, .05, C, .01, C
B, A, .02,,,A
B, C, .01,,,C

Thanks

[Andrew Musau]

If we ignore the infinite loop problem that William points out in #2, you can talk about levels of largest shareholders. You can define level1 as the largest shareholder of Company "X". Level 2 would be the largest shareholder of the largest shareholder of Company "X". The following will give you as many levels as you need. A general comment on your approach is that it is arbitrary. For example, there may not be much difference between a shareholder who owns 3% of a company and one who holds 4% ownership. Therefore, I would not consider it correct that the parent company of the firm holding 4% interest in a company is the ultimate owner of the company. You can constrain it to companies that have a controlling interest in the current company, defined as at least 50% of the outstanding shares plus one share.

input str99(company_name shareholder) float percent
"indian acrylics ltd" "bloomen flora ltd" 0.02
"indian acrylics ltd" "sab udyog ltd" 0.13
"indian acrylics ltd" "SS infra ltd" 0.03
"SS infra ltd"    "bloomen flora ltd" 0.04
"SS infra ltd"    "sab udyog ltd"    0.02
"SS infra ltd"    "Strip wheels ltd" 0.04
"Strip wheels ltd" "bloomen flora ltd" 0.04
"Strip wheels ltd" "sab udyog ltd" 0.06
end
tempfile data
save `data'

*Generate list of largest owner by company

*LARGEST SHAREHOLDERS
bys company_name (percent): gen level1= shareholder[_N]

*TAG TIES
bys company_name (percent): gen tie = percent[_N]==  percent[_N-1]

*DECIDE WHAT TO DO WITH TIES. Largest shareholders & ties
*browse if tie

*DATA SET OF LARGEST SHAREHOLDERS
contract company_name level1
drop _freq
tempfile level1
sort level1
save `level1'
clear

use `data'
merge m:1 company_name using `level1'
drop if _merge==2
drop _merge
sort level1
save `data', replace

*LEVEL 1 TO LEVEL K OWNERS, WHERE  1 < K < ∞
*Here K=5
forval i=1/5{
local k= `i'+1
use `level1'
rename (*) (level`i' level`k')
sort level`i'
save `level1', replace
use `data'
merge m:1 level`i' using `level1'
drop if _merge==2
drop _merge
save `data', replace
}

Here, we only have level 1 owners

. list company_name shareholder  percent level*, sepby(company_name)

     +--------------------------------------------------------------------------------------------------------------------+
     |        company_name         shareholder   percent              level1   level2   level3   level4   level5   level6 |
     |--------------------------------------------------------------------------------------------------------------------|
  1. |        SS infra ltd    Strip wheels ltd       .04   bloomen flora ltd                                              |
  2. |        SS infra ltd       sab udyog ltd       .02   bloomen flora ltd                                              |
  3. |        SS infra ltd   bloomen flora ltd       .04   bloomen flora ltd                                              |
     |--------------------------------------------------------------------------------------------------------------------|
  4. |    Strip wheels ltd       sab udyog ltd       .06       sab udyog ltd                                              |
  5. |    Strip wheels ltd   bloomen flora ltd       .04       sab udyog ltd                                              |
     |--------------------------------------------------------------------------------------------------------------------|
  6. | indian acrylics ltd   bloomen flora ltd       .02       sab udyog ltd                                              |
  7. | indian acrylics ltd       sab udyog ltd       .13       sab udyog ltd                                              |
  8. | indian acrylics ltd        SS infra ltd       .03       sab udyog ltd                                              |
     +--------------------------------------------------------------------------------------------------------------------+