Investor represents investor. Lead Manager represents manager. StockName represents firm，Some firms are hot firms( JOFHot=1)，for the focal investor-manager-firm pair, we are trying to count the number of times (issuedate -1826, -1) the focal manager has appeared with other managers in the same firm. Other managers are those who have hot firms with the focal investor (within the subsequent year of the focal issuedate. How can we count the number? Thanks!
We try to count in followings way, but do not know whether it is correct.
use "underwriter relation", clear
drop NumberofLeadManagers
duplicates drop IssueDate Investor StockName LeadManager, force
preserve
keep Investor
duplicates drop
gen Investorid=_n
save investor1, replace
restore
preserve
keep LeadManager
duplicates drop
gen managerid=_n
save manager1, replace
restore
preserve
keep StockName
duplicates drop
gen stockid=_n
save stockid1, replace
restore
merge m:1 LeadManager using manager1
drop _merge
merge m:1 StockName using stockid1
drop _merge
merge m:1 Investor using investor1
drop _merge
gen id=_n
egen investor_manager = group(managerid Investorid), label lname(sic_year)
save underwritersample, replace
levelsof stockid,local(stockids)
dis `stockids'
foreach i of local stockids {
dis `i'
use underwritersample,clear
keep if stockid==`i'
preserve
keep if JOFHot==1
levelsof investor_manager, local(investor_manager)
dis "`investor_manager'"
restore
foreach t of local investor_manager{
gen y`t'=.
}
reshape long y,i(id) j(investor_managerid)
drop y
save id`i', replace
}
openall id*
save idall1, replace
use idall1, clear
tostring managerid, replace
tostring Investorid, replace
gen investor_manager3=managerid+" "+Investorid
gen investor_manager1=investor_manager
keep investor_manager3 investor_manager1
duplicates drop
rename investor_manager1 investor_managerid
save investor_managercode, replace
drop if Investorid==.
merge m:1 investor_managerid using investor_managercode
keep if _merge==3
drop if investor_manager==investor_managerid
split investor_manager2
destring investor_manager21, replace
drop if managerid==investor_manager21
destring investor_manager22, replace
keep if Investorid==investor_manager22
save idall2, replace
use idall2, clear
gen long obs_no = _n
rangestat (count) wanted = obs_no, by(investor_managerid investor_manager ) interval( IssueDate -1826 -1)
replace wanted=0 if wanted==.
bysort investor_manager: egen relation_underwriter=sum(wanted)
rename investor_manager21 other_manager
save underwriter_relation_wanted, replace
Followings are our data sample:
We try to count in followings way, but do not know whether it is correct.
use "underwriter relation", clear
drop NumberofLeadManagers
duplicates drop IssueDate Investor StockName LeadManager, force
preserve
keep Investor
duplicates drop
gen Investorid=_n
save investor1, replace
restore
preserve
keep LeadManager
duplicates drop
gen managerid=_n
save manager1, replace
restore
preserve
keep StockName
duplicates drop
gen stockid=_n
save stockid1, replace
restore
merge m:1 LeadManager using manager1
drop _merge
merge m:1 StockName using stockid1
drop _merge
merge m:1 Investor using investor1
drop _merge
gen id=_n
egen investor_manager = group(managerid Investorid), label lname(sic_year)
save underwritersample, replace
levelsof stockid,local(stockids)
dis `stockids'
foreach i of local stockids {
dis `i'
use underwritersample,clear
keep if stockid==`i'
preserve
keep if JOFHot==1
levelsof investor_manager, local(investor_manager)
dis "`investor_manager'"
restore
foreach t of local investor_manager{
gen y`t'=.
}
reshape long y,i(id) j(investor_managerid)
drop y
save id`i', replace
}
openall id*
save idall1, replace
use idall1, clear
tostring managerid, replace
tostring Investorid, replace
gen investor_manager3=managerid+" "+Investorid
gen investor_manager1=investor_manager
keep investor_manager3 investor_manager1
duplicates drop
rename investor_manager1 investor_managerid
save investor_managercode, replace
drop if Investorid==.
merge m:1 investor_managerid using investor_managercode
keep if _merge==3
drop if investor_manager==investor_managerid
split investor_manager2
destring investor_manager21, replace
drop if managerid==investor_manager21
destring investor_manager22, replace
keep if Investorid==investor_manager22
save idall2, replace
use idall2, clear
gen long obs_no = _n
rangestat (count) wanted = obs_no, by(investor_managerid investor_manager ) interval( IssueDate -1826 -1)
replace wanted=0 if wanted==.
bysort investor_manager: egen relation_underwriter=sum(wanted)
rename investor_manager21 other_manager
save underwriter_relation_wanted, replace
Followings are our data sample:
| IssueDate StockName Investor Lead Manager JOFHot Number of Lead Manager | ||
| 21810 "DATADOG INC." "1 NORTH WEALTH SERVICES, LLC" "Barclays Capital" | 1 | 7 |
| 21810 "DATADOG INC." "1 NORTH WEALTH SERVICES, LLC" "Credit Suisse" | 1 | 7 |
| 21810 "DATADOG INC." "1 NORTH WEALTH SERVICES, LLC" "Goldman Sachs & Co" | 1 | 7 |
| 21810 "DATADOG INC." "1 NORTH WEALTH SERVICES, LLC" "JP Morgan (JPM)" | 1 | 7 |
| 21810 "DATADOG INC." "1 NORTH WEALTH SERVICES, LLC" "Jefferies & Co Inc" | 1 | 7 |
| 21810 "DATADOG INC." "1 NORTH WEALTH SERVICES, LLC" "Morgan Stanley & Co" | 1 | 7 |
| 21810 "DATADOG INC." "1 NORTH WEALTH SERVICES, LLC" "RBC Capital Markets" | 1 | 7 |
| 19928 "ADVANCED DRAINAGE SYSTEMS INC." "12 WEST CAPITAL MGMT, L.P." "Barclays Capital" | 1 | 4 |
| 20117 "SHAKE SHACK INC." "12 WEST CAPITAL MGMT, L.P." "Barclays Capital" | 1 | 5 |
| 19928 "ADVANCED DRAINAGE SYSTEMS INC." "12 WEST CAPITAL MGMT, L.P." "CITIGROUP" | 1 | 4 |
| 19928 "ADVANCED DRAINAGE SYSTEMS INC." "12 WEST CAPITAL MGMT, L.P." "Deutsche Bank Securities Corp" | 1 | 4 |
| 20117 "SHAKE SHACK INC." "12 WEST CAPITAL MGMT, L.P." "Goldman Sachs & Co" | 1 | 5 |
| 20117 "SHAKE SHACK INC." "12 WEST CAPITAL MGMT, L.P." "JP Morgan (JPM)" | 1 | 5 |
| 21300 "SMARTSHEET INC." "12 WEST CAPITAL MGMT, L.P." "JP Morgan (JPM)" | 1 | 4 |
| 20117 "SHAKE SHACK INC." "12 WEST CAPITAL MGMT, L.P." "Jefferies & Co Inc" | 1 | 5 |
| 21300 "SMARTSHEET INC." "12 WEST CAPITAL MGMT, L.P." "Jefferies & Co Inc" | 1 | 4 |
| 20117 "SHAKE SHACK INC." "12 WEST CAPITAL MGMT, L.P." "Morgan Stanley & Co" | 1 | 5 |
| 21300 "SMARTSHEET INC." "12 WEST CAPITAL MGMT, L.P." "Morgan Stanley & Co" | 1 | 4 |
| 21362 "NEURONETICS INC." "12 WEST CAPITAL MGMT, L.P." "Piper Jaffray Inc" | 1 | 2 |
| 19928 "ADVANCED DRAINAGE SYSTEMS INC." "12 WEST CAPITAL MGMT, L.P." "RBC Capital Markets" | 1 | 4 |
| 21300 "SMARTSHEET INC." "12 WEST CAPITAL MGMT, L.P." "RBC Capital Markets" | 1 | 4 |
| 21362 "NEURONETICS INC." "12 WEST CAPITAL MGMT, L.P." "William Blair & Co" | 1 | 2 |
| 19739 "RSP PERMIAN INC." "1492 CAPITAL MANAGEMENT, LLC" "Barclays Capital" | 1 | 6 |
| 20037 "THE JOINT CORP." "1492 CAPITAL MANAGEMENT, LLC" "Feltl & Co" | 1 | 2 |
| 19739 "RSP PERMIAN INC." "1492 CAPITAL MANAGEMENT, LLC" "JP Morgan (JPM)" | 1 | 6 |
| 19948 "C1 FINANCIAL INC" "1492 CAPITAL MANAGEMENT, LLC" "Keefe Bruyette & Woods Inc" | 0 | 2 |
| 19739 "RSP PERMIAN INC." "1492 CAPITAL MANAGEMENT, LLC" "RBC Capital Markets" | 1 | 6 |
| 19948 "C1 FINANCIAL INC" "1492 CAPITAL MANAGEMENT, LLC" "Raymond James & Associates Inc" | 0 | 2 |
| 19739 "RSP PERMIAN INC." "1492 CAPITAL MANAGEMENT, LLC" "Raymond James & Associates Inc" | 1 | 6 |
| 21216 "ONE STOP SYSTEMS INC." "1492 CAPITAL MANAGEMENT, LLC" "Roth Capital Partners Inc" | 0 | 1 |
| 20037 "THE JOINT CORP." "1492 CAPITAL MANAGEMENT, LLC" "Roth Capital Partners Inc" | 1 | 2 |
| 21361 "THE LOVESAC CO." "1492 CAPITAL MANAGEMENT, LLC" "Roth Capital Partners Inc" | 1 | 1 |
| 19739 "RSP PERMIAN INC." "1492 CAPITAL MANAGEMENT, LLC" "TUDOR-PICKERING" | 1 | 6 |
| 19739 "RSP PERMIAN INC." "1492 CAPITAL MANAGEMENT, LLC" "UBS Investment Bank" | 1 | 6 |
| 19094 "FORUM ENERGY TECHNOLOGIES INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "BOA-MERRILL" | 1 | 5 |
| 18828 "SUNCOKE ENERGY INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "BOA-MERRILL" | 1 | 3 |
| 19276 "REALOGY HOLDINGS CORP." "1798 GLOBAL PTNR (CAYMAN) LTD" "Barclays Capital" | 1 | 4 |
| 19094 "FORUM ENERGY TECHNOLOGIES INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "CITIGROUP" | 1 | 5 |
| 19094 "FORUM ENERGY TECHNOLOGIES INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "Credit Suisse" | 1 | 5 |
| 18786 "FUSION-IO INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "Credit Suisse" | 1 | 4 |
| 19276 "REALOGY HOLDINGS CORP." "1798 GLOBAL PTNR (CAYMAN) LTD" "Credit Suisse" | 1 | 4 |
| 18828 "SUNCOKE ENERGY INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "Credit Suisse" | 1 | 3 |
| 19094 "FORUM ENERGY TECHNOLOGIES INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "Deutsche Bank Securities Corp" | 1 | 5 |
| 18786 "FUSION-IO INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "Goldman Sachs & Co" | 1 | 4 |
| 19276 "REALOGY HOLDINGS CORP." "1798 GLOBAL PTNR (CAYMAN) LTD" "Goldman Sachs & Co" | 1 | 4 |
| 18828 "SUNCOKE ENERGY INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "Goldman Sachs & Co" | 1 | 3 |
| 19094 "FORUM ENERGY TECHNOLOGIES INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "JP Morgan (JPM)" | 1 | 5 |
| 18786 "FUSION-IO INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "JP Morgan (JPM)" | 1 | 4 |
| 19276 "REALOGY HOLDINGS CORP." "1798 GLOBAL PTNR (CAYMAN) LTD" "JP Morgan (JPM)" | 1 | 4 |
| 18786 "FUSION-IO INC." "1798 GLOBAL PTNR (CAYMAN) LTD" "Morgan Stanley & Co" | 1 | 4 |
| 19886 "MEMORIAL RESOURCE DVLP CORP." "1832 ASSET MANAGEMENT L.P." "BMO Capital Markets" | 1 | 8 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "BMO Capital Markets" | 1 | 13 |
| 19844 "ARES MANAGEMENT LP" "1832 ASSET MANAGEMENT L.P." "BOA-MERRILL" | 0 | 5 |
| 19886 "MEMORIAL RESOURCE DVLP CORP." "1832 ASSET MANAGEMENT L.P." "BOA-MERRILL" | 1 | 8 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "BOA-MERRILL" | 1 | 13 |
| 20935 "FLOOR & DECOR HOLDINGS INC." "1832 ASSET MANAGEMENT L.P." "Barclays Capital" | 1 | 8 |
| 19886 "MEMORIAL RESOURCE DVLP CORP." "1832 ASSET MANAGEMENT L.P." "Barclays Capital" | 1 | 8 |
| 20193 "PARTY CITY HOLDCO INC." "1832 ASSET MANAGEMENT L.P." "Barclays Capital" | 1 | 7 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "Barclays Capital" | 1 | 13 |
| 20838 "KEANE GROUP INC." "1832 ASSET MANAGEMENT L.P." "CITIGROUP" | 1 | 4 |
| 19886 "MEMORIAL RESOURCE DVLP CORP." "1832 ASSET MANAGEMENT L.P." "CITIGROUP" | 1 | 8 |
| 19753 "NEW HOME CO INC." "1832 ASSET MANAGEMENT L.P." "CITIGROUP" | 1 | 3 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P. "CITIGROUP" | 1 | 13 |
| 20935 "FLOOR & DECOR HOLDINGS INC." "1832 ASSET MANAGEMENT L.P." "Credit Suisse" | 1 | 8 |
| 20850 "INVITATION HOMES INC." "1832 ASSET MANAGEMENT L.P." "Credit Suisse" | 0 | 8 |
| 19753 "NEW HOME CO INC." "1832 ASSET MANAGEMENT L.P." "Credit Suisse" | 1 | 3 |
| 20193 "PARTY CITY HOLDCO INC." "1832 ASSET MANAGEMENT L.P." "Credit Suisse" | 1 | 7 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "Credit Suisse" | 1 | 13 |
| 19829 "SPORTSMAN'S WAREHOUSE HLDG INC." "1832 ASSET MANAGEMENT L.P." "Credit Suisse" | 1 | 2 |
| 20850 "INVITATION HOMES INC." "1832 ASSET MANAGEMENT L.P." "Deutsche Bank Securities Corp" | 0 | 8 |
| 20193 "PARTY CITY HOLDCO INC." "1832 ASSET MANAGEMENT L.P." "Deutsche Bank Securities Corp" | 1 | 7 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "Deutsche Bank Securities Corp" | 1 | 13 |
| 19844 "ARES MANAGEMENT LP" "1832 ASSET MANAGEMENT L.P." "Goldman Sachs & Co" | 0 | 5 |
| 20935 "FLOOR & DECOR HOLDINGS INC." "1832 ASSET MANAGEMENT L.P." "Goldman Sachs & Co" | 1 | 8 |
| 20850 "INVITATION HOMES INC." "1832 ASSET MANAGEMENT L.P." "Goldman Sachs & Co" | 0 | 8 |
| 19886 "MEMORIAL RESOURCE DVLP CORP." "1832 ASSET MANAGEMENT L.P." "Goldman Sachs & Co" | 1 | 8 |
| 20193 "PARTY CITY HOLDCO INC." "1832 ASSET MANAGEMENT L.P." "Goldman Sachs & Co" | 1 | 7 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "Goldman Sachs & Co" | 1 | 13 |
| 19829 "SPORTSMAN'S WAREHOUSE HLDG INC." "1832 ASSET MANAGEMENT L.P." "Goldman Sachs & Co" | 1 | 2 |
| 19844 "ARES MANAGEMENT LP" "1832 ASSET MANAGEMENT L.P." "JP Morgan (JPM)" | 0 | 5 |
| 20850 "INVITATION HOMES INC." "1832 ASSET MANAGEMENT L.P." "JP Morgan (JPM)" | 0 | 8 |
| 20838 "KEANE GROUP INC." "1832 ASSET MANAGEMENT L.P." "JP Morgan (JPM)" | 1 | 4 |
| 19753 "NEW HOME CO INC." "1832 ASSET MANAGEMENT L.P." "JP Morgan (JPM)" | 1 | 3 |
| 20193 "PARTY CITY HOLDCO INC." "1832 ASSET MANAGEMENT L.P." "JP Morgan (JPM)" | 1 | 7 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "JP Morgan (JPM)" | 1 | 13 |
| 20935 "FLOOR & DECOR HOLDINGS INC." "1832 ASSET MANAGEMENT L.P." "Jefferies & Co Inc" | 1 | 8 |
| 20935 "FLOOR & DECOR HOLDINGS INC." "1832 ASSET MANAGEMENT L.P." "Merrill Lynch & Co Inc" | 1 | 8 |
| 20850 "INVITATION HOMES INC." "1832 ASSET MANAGEMENT L.P." "Merrill Lynch & Co Inc" | 0 | 8 |
| 20838 "KEANE GROUP INC." "1832 ASSET MANAGEMENT L.P." "Merrill Lynch & Co Inc" | 1 | 4 |
| 20193 "PARTY CITY HOLDCO INC." "1832 ASSET MANAGEMENT L.P." "Merrill Lynch & Co Inc" | 1 | 7 |
| 19844 "ARES MANAGEMENT LP" "1832 ASSET MANAGEMENT L.P." "Morgan Stanley & Co" | 0 | 5 |
| 20850 "INVITATION HOMES INC." "1832 ASSET MANAGEMENT L.P." "Morgan Stanley & Co" | 0 | 8 |
| 20838 "KEANE GROUP INC." "1832 ASSET MANAGEMENT L.P." "Morgan Stanley & Co" | 1 | 4 |
| 20193 "PARTY CITY HOLDCO INC." "1832 ASSET MANAGEMENT L.P." "Morgan Stanley & Co" | 1 | 7 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "Morgan Stanley & Co" | 1 | 13 |
| 20935 "FLOOR & DECOR HOLDINGS INC." "1832 ASSET MANAGEMENT L.P." "Piper Jaffray Inc" | 1 | 8 |
| 20850 "INVITATION HOMES INC." "1832 ASSET MANAGEMENT L.P." "RBC Capital Markets" | 0 | 8 |
| 19886 "MEMORIAL RESOURCE DVLP CORP." "1832 ASSET MANAGEMENT L.P." "RBC Capital Markets" | 1 | 8 |
| 19745 "SANTANDER CONSUMER USA HLDGS" "1832 ASSET MANAGEMENT L.P." "RBC Capital Markets" | 1 | 13 |
| 19886 "MEMORIAL RESOURCE DVLP CORP." "1832 ASSET MANAGEMENT L.P." "Raymond James & Associates Inc" | 1 | 8 |
| end | ||
| format %tdnn/dd/CCYY IssueDate |
