Calculating prior 3 years' international acquisitions experience and

[ZHIDI LIU]

Dear all,

My data structure is acqcusip targetcusip dealnumber Yrannounced dateannounced acqnation targetnation,
first, acqcusip indicates the code of the acquirer company,
second, targetcusip indicates the code of the target company,
third, dealnumber represents the transaction code,
fourth, Yrannounced represents the acquisition year,
next, dateannounced represents the specific acquisition time, and acqnation represents the acquirer's country code and targetnation represents the target's country code.
Note: Each acquirer may have multiple acquisitions in the same year.

I want to calculate two variables: first, the number of international acquisitions in the three years before each acquisition by the acquirer company (acq prior 3 years' international acquisitions experience), provided that the country of the host country is excluded for the international acquisitions in the first three years. International acquisition refers to all international acquisition except the current acquisition of acqnation and targetnation, to avoid double calculation with the next variable.
second, acq prior 3 years' host-country acquisition experience (acq prior 3 years' host-country acquisition experience), that is, the number of acquisitions in the host country in the three years before this acquisition.
for example, in my following sample, "1830804040" "00011X" "81646E" "01/17/2007" 2007 "AU" "NZ", 00011X 81646E prior 3 years' international acquisitions experience=0 in 2007 (host country's international acquisitions do not count as the targetnation is NZ, So the previous NZ in 2005 and 2006 do not count), prior 3 years' host-country acquisition experience=3; 00011X 1C8854 prior 3 years' international acquisitions experience=2 in 2014 (two NZ acquisitions in 2011), prior 3 years' host-country acquisition experience=0 (No HK acquisition in 2011, 2012, 2013)

Sorry for the long intro. I hope the explanation is clear. Your help is appreciated.

With kind regards,
Zhidi

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 dealnumber str9(acqcusip targetcusip) str10 dateannounced int Yrannounced str6(acqnation targetnation)
"1348548040" "00008W" "00263H" "10/01/2002" 2002 "AU" "AU"
"1531438040" "00008W" "28382W" "06/01/2004" 2004 "AU" "AU"
"1705049040" "00008W" "67186L" "10/27/2005" 2005 "AU" "AU"
"1718617040" "00008W" "04537E" "12/09/2005" 2005 "AU" "AU"
"1717549040" "00008W" "00609R" "12/06/2005" 2005 "AU" "AU"
"1726593040" "00008W" "89267Z" "01/10/2006" 2006 "AU" "AU"
"1912635040" "00008W" "25559E" "10/08/2007" 2007 "AU" "AU"
"1912631040" "00008W" "78855W" "10/08/2007" 2007 "AU" "AU"
"1894440040" "00008W" "87021Z" "07/31/2007" 2007 "AU" "AU"
"1912621040" "00008W" "90642K" "10/08/2007" 2007 "AU" "AU"
"2532169040" "00008W" "4A5478" "06/24/2013" 2013 "AU" "AU"
"3493667040" "00008W" "9J2790" "11/22/19" 2019 "AU" "AU"
"3480562040" "00008W" "8J3265" "11/22/19" 2019 "AU" "AU"
"1335110020" "00009A" "46164W" "10/14/2002" 2002 "AU" "US"
"1350608040" "00009C" "36835E" "12/09/2002" 2002 "NT" "CT"
"1696179040" "00009T" "36061P" "09/22/2005" 2005 "JP" "JP"
"770037040" "00009Z" "30281F" "04/21/1998" 1998 "AS" "IT"
"1888935040" "00010J" "85535Z" "07/17/2007" 2007 "TH" "TH"
"1953367040" "00010J" "85535Z" "02/18/2008" 2008 "TH" "TH"
"1321040040" "00010L" "92644Y" "06/19/2002" 2002 "FR" "FR"
"574898040" "00010P" "89051E" "07/19/1995" 1995 "EA" "EA"
"1306015040" "00011R" "74083V" "01/23/2002" 2002 "UK" "UK"
"2258414040" "00011X" "54342E" "03/01/2005" 2005 "AU" "NZ"
"1799617040" "00011X" "31711X" "10/06/2006" 2006 "AU" "NZ"
"1743696040" "00011X" "86285Z" "03/15/2006" 2006 "AU" "NZ"
"1830804040" "00011X" "81646E" "01/17/2007" 2007 "AU" "NZ"
"1884842040" "00011X" "89044Z" "07/03/2007" 2007 "AU" "AU"
"1938101040" "00011X" "83124L" "12/20/2007" 2007 "AU" "NZ"
"2337902040" "00011X" "07360H" "08/18/2011" 2011 "AU" "NZ"
"2287077040" "00011X" "67671N" "03/05/2011" 2011 "AU" "NZ"
"2388325040" "00011X" "67235H" "11/08/2011" 2011 "AU" "AU"
"2426573040" "00011X" "10540K" "06/21/2012" 2012 "AU" "AU"
"2610863040" "00011X" "05291M" "02/19/2014" 2014 "AU" "AU"
"2709021040" "00011X" "4C9990" "12/22/2014" 2014 "AU" "AU"
"2657268040" "00011X" "1C8854" "07/18/2014" 2014 "AU" "HK"
"3024640040" "00011X" "2F0519" "10/24/2016" 2016 "AU" "AU"
end

[Clyde Schechter]

I don't find the explanation clear, but I may have figured it out. Try this:

isid dealnumber
preserve
keep acqcusip Yrannounced *nation
drop if acqnation == targetnation    // KEEP INTERNATIONAL DEALS ONLY
tempfile international_deals
save `international_deals'

restore
rangejoin Yrannounced -3 -1 using `international_deals', by(acqcusip acqnation)
by dealnumber, sort: egen wanted = ///
    total(targetnation != targetnation_U & !missing(targetnation_U))
by dealnumber: keep if _n == 1
drop *_U

-rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

I have interpreted "prior 3 years" to mean from the year before Yrannounced back through three years before Yrannounced. If you mean it to mean from Yrannounced back to 2 years before Yrannounced, change -3 -1 in the -rangejoin- command to -2 0.

[ZHIDI LIU]

I don't find the explanation clear, but I may have figured it out. Try this:

isid dealnumber
preserve
keep acqcusip Yrannounced *nation
drop if acqnation == targetnation // KEEP INTERNATIONAL DEALS ONLY
tempfile international_deals
save `international_deals'

restore
rangejoin Yrannounced -3 -1 using `international_deals', by(acqcusip acqnation)
by dealnumber, sort: egen wanted = ///
total(targetnation != targetnation_U & !missing(targetnation_U))
by dealnumber: keep if _n == 1
drop *_U

-rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

I have interpreted "prior 3 years" to mean from the year before Yrannounced back through three years before Yrannounced. If you mean it to mean from Yrannounced back to 2 years before Yrannounced, change -3 -1 in the -rangejoin- command to -2 0.

Dear Prof. Schechter

I really appreciated your help! After I ran the code I realized that the same acqcusip can be located in different countries, i.e. one acqcusip will have more than one acqnation, so I just want to group by acqcusip, does this mean that I should use the code:

rangejoin Yrannounced -3 -1 using `international_deals', by (acqcusip) ? I tried that and it failed.

Also, how do I calculate the number of acquisitions the acquirer has made in the host country in the previous 3 years (i.e., the acquirer's experience in acquisitions in the host country in the previous 3 years)?

Thanks again for your help!

[Clyde Schechter]

so I just want to group by acqcusip, does this mean that I should use the code:

rangejoin Yrannounced -3 -1 using `international_deals', by (acqcusip) ? I tried that and it failed.

I don't understand exactly which acquisitions you want to count. Specifically what are the conditions on the acqcusip, acqnation and targetnation in the deals that are to be counted?

[quote]Also, how do I calculate the number of acquisitions the acquirer has made in the host country in the previous 3 years (i.e., the acquirer's experience in acquisitions in the host country in the previous 3 years)?[quote]
Again, please clarify exactly what conditions on acqcusip, acqnation, and targetnation define the deals that are to be counted here. By the way, if you are going to continue to use the term "host country" please define what it means. I have assumed it refers to acqnation, but now I have my doubts.

[ZHIDI LIU]

[QUOTE=Clyde Schechter;n1738004]
I don't understand exactly which acquisitions you want to count. Specifically what are the conditions on the acqcusip, acqnation and targetnation in the deals that are to be counted?

[quote]Also, how do I calculate the number of acquisitions the acquirer has made in the host country in the previous 3 years (i.e., the acquirer's experience in acquisitions in the host country in the previous 3 years)?

Again, please clarify exactly what conditions on acqcusip, acqnation, and targetnation define the deals that are to be counted here. By the way, if you are going to continue to use the term "host country" please define what it means. I have assumed it refers to acqnation, but now I have my doubts.

Hi Prof. Schechter,

Thank you for your reply. I am sorry, maybe my English is not good and I didn't make it clear. First of all, in my sample, if acqnation!=targetnation then it is an international acquisition, the host country is the country where the target company is located.

The first variable I want to calculate is the acquirer's international acquisition experience in the previous 3 years, which is captured by the number of international acquisitions the acquirer completed in the 3 years prior to the announcement of the focal acquisition, but previous international acquisitions do not include acquisitions that took place in the host country of the focal acquisition, For example, if in 2020, firm A, located in the United States, acquires firm B, located in France, i.e., acqnation=US, targetnation=France, then when calculating A's experience with international acquisitions in the three years prior to this acquisition, we should count international acquisitions that took place in countries other than France in 2019, 2018, and 2017, that is to say We don't need to count acquisitions that occurred in France, because the second variable I want to calculate is to count the number of acquisitions that occurred in the host country during the first three years of the focal acquisition, i.e., only the number of acquisitions that occurred in France three years ago.

Your previous code basically fulfills the requirements of my first variable, but I want to make it clear that the same acquirer in my sample may be located in different countries, e.g. in 2019 A is located in the US and in 2020 A is located in the UK, but it's A who is making the acquisitions, so I'm just going to group by acquirer. So, does the previous code “rangejoin Yrannounced -3 -1 using `international_deals', by(acqcusip acqnation)” need to be changed to “rangejoin Yrannounced -3 -1 using `international_deals', by(acqcusip)”. I am not sure and would appreciate your judgment.

Could you please write the code for these two variables for me?
I hope the explanation is clearer now, English is not my first language, thanks for your patience.

Thanks again for your help!

[Clyde Schechter]

Very clear now, thank you! So, what you want is:

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 dealnumber str9(acqcusip targetcusip) str10 dateannounced int Yrannounced str6(acqnation targetnation)
"1348548040" "00008W" "00263H" "10/01/2002" 2002 "AU" "AU"
"1531438040" "00008W" "28382W" "06/01/2004" 2004 "AU" "AU"
"1705049040" "00008W" "67186L" "10/27/2005" 2005 "AU" "AU"
"1718617040" "00008W" "04537E" "12/09/2005" 2005 "AU" "AU"
"1717549040" "00008W" "00609R" "12/06/2005" 2005 "AU" "AU"
"1726593040" "00008W" "89267Z" "01/10/2006" 2006 "AU" "AU"
"1912635040" "00008W" "25559E" "10/08/2007" 2007 "AU" "AU"
"1912631040" "00008W" "78855W" "10/08/2007" 2007 "AU" "AU"
"1894440040" "00008W" "87021Z" "07/31/2007" 2007 "AU" "AU"
"1912621040" "00008W" "90642K" "10/08/2007" 2007 "AU" "AU"
"2532169040" "00008W" "4A5478" "06/24/2013" 2013 "AU" "AU"
"3493667040" "00008W" "9J2790" "11/22/19" 2019 "AU" "AU"
"3480562040" "00008W" "8J3265" "11/22/19" 2019 "AU" "AU"
"1335110020" "00009A" "46164W" "10/14/2002" 2002 "AU" "US"
"1350608040" "00009C" "36835E" "12/09/2002" 2002 "NT" "CT"
"1696179040" "00009T" "36061P" "09/22/2005" 2005 "JP" "JP"
"770037040" "00009Z" "30281F" "04/21/1998" 1998 "AS" "IT"
"1888935040" "00010J" "85535Z" "07/17/2007" 2007 "TH" "TH"
"1953367040" "00010J" "85535Z" "02/18/2008" 2008 "TH" "TH"
"1321040040" "00010L" "92644Y" "06/19/2002" 2002 "FR" "FR"
"574898040" "00010P" "89051E" "07/19/1995" 1995 "EA" "EA"
"1306015040" "00011R" "74083V" "01/23/2002" 2002 "UK" "UK"
"2258414040" "00011X" "54342E" "03/01/2005" 2005 "AU" "NZ"
"1799617040" "00011X" "31711X" "10/06/2006" 2006 "AU" "NZ"
"1743696040" "00011X" "86285Z" "03/15/2006" 2006 "AU" "NZ"
"1830804040" "00011X" "81646E" "01/17/2007" 2007 "AU" "NZ"
"1884842040" "00011X" "89044Z" "07/03/2007" 2007 "AU" "AU"
"1938101040" "00011X" "83124L" "12/20/2007" 2007 "AU" "NZ"
"2337902040" "00011X" "07360H" "08/18/2011" 2011 "AU" "NZ"
"2287077040" "00011X" "67671N" "03/05/2011" 2011 "AU" "NZ"
"2388325040" "00011X" "67235H" "11/08/2011" 2011 "AU" "AU"
"2426573040" "00011X" "10540K" "06/21/2012" 2012 "AU" "AU"
"2610863040" "00011X" "05291M" "02/19/2014" 2014 "AU" "AU"
"2709021040" "00011X" "4C9990" "12/22/2014" 2014 "AU" "AU"
"2657268040" "00011X" "1C8854" "07/18/2014" 2014 "AU" "HK"
"3024640040" "00011X" "2F0519" "10/24/2016" 2016 "AU" "AU"
end


isid dealnumber
preserve
keep acqcusip Yrannounced *nation
drop if acqnation == targetnation    // KEEP INTERNATIONAL DEALS ONLY
tempfile international_deals
save `international_deals'

restore
rangejoin Yrannounced -3 -1 using `international_deals', by(acqcusip)
by dealnumber, sort: egen other_host_acquisitions_3_yrs = ///
    total(targetnation != targetnation_U & !missing(targetnation_U))
by dealnumber, sort: egen same_host_acquisitions_3_yrs = ///
    total(targetnation == targetnation_U)
by dealnumber: keep if _n == 1
drop *_U

[ZHIDI LIU]

Hi Prof. Schechter,

Thank you very much for your help, and you managed to solve my problem!
Thanks again!

Zhidi