Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Linking announcement dates with score dates

    Hi,

    I am currently doing a research project for university. We want to research the effect of the ESG dimensions on the M&A premiums. On one dataset, we have the data of the different deals over the years (in this small sample only 2013):

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str9 dealid str8 targetticker str5 acquirerticker str11 announcementdate float transactionvaluemm
"3101704MM" "PTXTQ" "JPM"  "12-Feb-2019"   302.95
"3091671MM" "IMDZ"  "MRK"  "21-Feb-2019"   187.05
"3094709MM" "WP"    "FIS"  "18-Mar-2019" 42607.66
"3096046MM" "QTNA"  "ON"   "27-Mar-2019"   809.83
"3096392MM" "SRMC"  "MSA"  "29-Mar-2019"     32.3
"3096796MM" "APU"   "UGI"  "02-Apr-2019"  5250.03
"3098596MM" "ADSW"  "WM"   "15-Apr-2019"  4662.77
"3099451MM" "KEYW"  "J"    "22-Apr-2019"   878.71
"3098661MM" "APC"   "OXY"  "24-Apr-2019" 54085.73
"3100112MM" "PETX"  "ELAN" "26-Apr-2019"   211.05
"3101209MM" "AMPY"  "MPO"  "06-May-2019"   516.12
"3101549MM" "ANDX"  "MPLX" "08-May-2019" 14270.49
"3102791MM" "CRAY"  "HPE"  "17-May-2019"  1288.81
"3105580MM" "RTN"   "UTX"  "09-Jun-2019" 89727.25
"3105732MM" "DATA"  "CRM"  "10-Jun-2019" 14772.19
"3106138MM" "TST"   "MVEN" "12-Jun-2019"    20.61
"3106689MM" "ARRY"  "PFE"  "17-Jun-2019" 10421.35
"3106683MM" "CJ"    "FRAC" "17-Jun-2019"   680.83
"3107212MM" "ONSE"  "WLDN" "19-Jun-2019"    24.91
end

    And on the other dataset we have the ESG scores over the years:

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str10 scoredate str29 aspectname byte scorevalue str5 ticker
"28/1/2022"  "Social Dimension"              49 "JPM"
"17/1/2018"  "Social Dimension"              39 "JPM"
"18/12/2020" "Social Dimension"              34 "JPM"
"10/9/2015"  "Environmental Dimension"       70 "JPM"
"7/9/2017"   "Environmental Dimension"       63 "JPM"
"28/1/2022"  "S&P Global ESG Score"          40 "JPM"
"7/9/2017"   "Economic Governance Dimension" 62 "JPM"
"12/11/2021" "Environmental Dimension"       55 "JPM"
"8/9/2016"   "Environmental Dimension"       67 "JPM"
"11/9/2014"  "Economic Governance Dimension" 67 "JPM"
"13/11/2020" "Social Dimension"              48 "JPM"
"13/9/2019"  "Social Dimension"              46 "JPM"
"10/9/2015"  "Economic Governance Dimension" 61 "JPM"
"12/9/2013"  "Social Dimension"              51 "JPM"
"17/1/2018"  "S&P Global ESG Score"          59 "JPM"
"18/12/2020" "Social Dimension"              48 "JPM"
"10/9/2015"  "S&P Global ESG Score"          61 "JPM"
"23/9/2022"  "Environmental Dimension"       44 "JPM"
"13/9/2019"  "S&P Global ESG Score"          51 "JPM"
"11/9/2014"  "S&P Global ESG Score"          62 "JPM"
"13/11/2020" "S&P Global ESG Score"          53 "JPM"
"12/9/2013"  "S&P Global ESG Score"          62 "JPM"
"7/9/2017"   "Economic Governance Dimension" 57 "JPM"
"8/9/2016"   "S&P Global ESG Score"          58 "JPM"
"17/1/2018"  "Economic Governance Dimension" 55 "JPM"
"13/9/2018"  "Economic Governance Dimension" 60 "JPM"
"7/9/2017"   "Social Dimension"              39 "JPM"
"13/9/2019"  "Economic Governance Dimension" 38 "JPM"
"17/1/2018"  "Economic Governance Dimension" 62 "JPM"
"13/9/2019"  "Environmental Dimension"       49 "JPM"
"13/11/2020" "Environmental Dimension"       60 "JPM"
"28/1/2022"  "Environmental Dimension"       55 "JPM"
"10/9/2015"  "Social Dimension"              44 "JPM"
"23/9/2022"  "Social Dimension"              33 "JPM"
"17/1/2018"  "Environmental Dimension"       63 "JPM"
"12/11/2021" "Economic Governance Dimension" 55 "JPM"
"18/12/2020" "Environmental Dimension"       60 "JPM"
"28/1/2022"  "Economic Governance Dimension" 45 "JPM"
"7/9/2017"   "S&P Global ESG Score"          59 "JPM"
"13/9/2018"  "Environmental Dimension"       60 "JPM"
end

    Is it possible to get the closest date to the announcement date and then get the scores of the 3 dimensions? To make my question clear, this is what I'm trying to achieve for for example the JPM deal (first one of the first sample). The announcement date of the JPM deal is 12-Feb-2019. I want Stata to search for the closest date in the ESG dataset, but it has to be before the announcement date, so in this example the data of 17/1/2018. Then the result would be as following:

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str9 dealid str8 targetticker str5 acquirerticker str11 announcementdate float transactionvaluemm closest_date env_score social_score gov_score
"3101704MM" "PTXTQ" "JPM"  "12-Feb-2019"  302.95 "17-Jan-2018" 63 39 62
end
    Is this feasible in Stata? I'm aware that I might have to transpose the second dataset in order to be able to merge the two datasets.

    Thanks.


    Tags: None
  • #2
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str9 dealid str8 targetticker str5 acquirerticker str11 announcementdate float transactionvaluemm
"3101704MM" "PTXTQ" "JPM"  "12-Feb-2019"   302.95
"3091671MM" "IMDZ"  "MRK"  "21-Feb-2019"   187.05
"3094709MM" "WP"    "FIS"  "18-Mar-2019" 42607.66
"3096046MM" "QTNA"  "ON"   "27-Mar-2019"   809.83
"3096392MM" "SRMC"  "MSA"  "29-Mar-2019"     32.3
"3096796MM" "APU"   "UGI"  "02-Apr-2019"  5250.03
"3098596MM" "ADSW"  "WM"   "15-Apr-2019"  4662.77
"3099451MM" "KEYW"  "J"    "22-Apr-2019"   878.71
"3098661MM" "APC"   "OXY"  "24-Apr-2019" 54085.73
"3100112MM" "PETX"  "ELAN" "26-Apr-2019"   211.05
"3101209MM" "AMPY"  "MPO"  "06-May-2019"   516.12
"3101549MM" "ANDX"  "MPLX" "08-May-2019" 14270.49
"3102791MM" "CRAY"  "HPE"  "17-May-2019"  1288.81
"3105580MM" "RTN"   "UTX"  "09-Jun-2019" 89727.25
"3105732MM" "DATA"  "CRM"  "10-Jun-2019" 14772.19
"3106138MM" "TST"   "MVEN" "12-Jun-2019"    20.61
"3106689MM" "ARRY"  "PFE"  "17-Jun-2019" 10421.35
"3106683MM" "CJ"    "FRAC" "17-Jun-2019"   680.83
"3107212MM" "ONSE"  "WLDN" "19-Jun-2019"    24.91
end
tempfile deals
save `deals'

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 scoredate str29 aspectname byte scorevalue str5 ticker
"28/1/2022"  "Social Dimension"              49 "JPM"
"17/1/2018"  "Social Dimension"              39 "JPM"
"18/12/2020" "Social Dimension"              34 "JPM"
"10/9/2015"  "Environmental Dimension"       70 "JPM"
"7/9/2017"   "Environmental Dimension"       63 "JPM"
"28/1/2022"  "S&P Global ESG Score"          40 "JPM"
"7/9/2017"   "Economic Governance Dimension" 62 "JPM"
"12/11/2021" "Environmental Dimension"       55 "JPM"
"8/9/2016"   "Environmental Dimension"       67 "JPM"
"11/9/2014"  "Economic Governance Dimension" 67 "JPM"
"13/11/2020" "Social Dimension"              48 "JPM"
"13/9/2019"  "Social Dimension"              46 "JPM"
"10/9/2015"  "Economic Governance Dimension" 61 "JPM"
"12/9/2013"  "Social Dimension"              51 "JPM"
"17/1/2018"  "S&P Global ESG Score"          59 "JPM"
"18/12/2020" "Social Dimension"              48 "JPM"
"10/9/2015"  "S&P Global ESG Score"          61 "JPM"
"23/9/2022"  "Environmental Dimension"       44 "JPM"
"13/9/2019"  "S&P Global ESG Score"          51 "JPM"
"11/9/2014"  "S&P Global ESG Score"          62 "JPM"
"13/11/2020" "S&P Global ESG Score"          53 "JPM"
"12/9/2013"  "S&P Global ESG Score"          62 "JPM"
"7/9/2017"   "Economic Governance Dimension" 57 "JPM"
"8/9/2016"   "S&P Global ESG Score"          58 "JPM"
"17/1/2018"  "Economic Governance Dimension" 55 "JPM"
"13/9/2018"  "Economic Governance Dimension" 60 "JPM"
"7/9/2017"   "Social Dimension"              39 "JPM"
"13/9/2019"  "Economic Governance Dimension" 38 "JPM"
"17/1/2018"  "Economic Governance Dimension" 62 "JPM"
"13/9/2019"  "Environmental Dimension"       49 "JPM"
"13/11/2020" "Environmental Dimension"       60 "JPM"
"28/1/2022"  "Environmental Dimension"       55 "JPM"
"10/9/2015"  "Social Dimension"              44 "JPM"
"23/9/2022"  "Social Dimension"              33 "JPM"
"17/1/2018"  "Environmental Dimension"       63 "JPM"
"12/11/2021" "Economic Governance Dimension" 55 "JPM"
"18/12/2020" "Environmental Dimension"       60 "JPM"
"28/1/2022"  "Economic Governance Dimension" 45 "JPM"
"7/9/2017"   "S&P Global ESG Score"          59 "JPM"
"13/9/2018"  "Environmental Dimension"       60 "JPM"
end
tempfile esg_scores
save `esg_scores'

use `deals', clear
gen ann_date = daily(announcementdate, "DMY")
assert missing(ann_date) == missing(announcementdate)
format ann_date %td
drop announcementdate
rename acquirerticker ticker
tempfile holding
save `holding'

use `esg_scores', clear
gen score_date = daily(scoredate, "DMY")
assert missing(score_date) == missing(scoredate)
format score_date %td
drop scoredate

joinby ticker using `holding', unmatched(using)
gen delta = abs(datediff(ann_date, score_date, "day"))
by ticker aspectname ann_date (delta), sort: keep if _n == 1
drop _merge delta
replace aspectname = lower(substr(aspectname, 1, 3)) + "_"
replace aspectname = "glo_" if aspectname == "s&p_"
reshape wide @score_date @scorevalue, i(ticker ann_date) j(aspectname) string
    The major obstacle here is that your dates are string variables, which makes identifying "nearest" date impossible. So the early work in the code is just a matter of converting these to Stata internal format numeric date variables, which lend themselves readily to this task.

    Your phrasing of your request leaves me with the impression that you want to link the ESG scores with the acquirer firm, not the target firm, though you do not explictly say this. The code above assumes this is the case.

    Finally, there is a remaining ambiguity in your request. If there are two score dates that tie for "closest" to the acquisition date (e.g. acquisition date = 18 jan 2023, and the two score dates are 16 jan 2023 and 20 jan 2023, both of them 2 days away), you do not specify whether you want the one that precedes or the one that follows. In the above code, such ties are broken randomly, and irreproducibly.

    I'm aware that I might have to transpose the second dataset in order to be able to merge the two datasets.
    The above code joins (not -merge-s, -merge- won't work at all with this data) the data sets before -reshape-ing (not transposing.) In fact, given that different ESG dimensions that correspond to the same announcement date may be scored on different dates, I don't think it's even possible to get this right by -reshape-ing before joining.

    Comment

    • #3
      Thank you very much for your response Clyde! I actually want both the ESG scores for the acquirer and the target, so this would give for 1 deal 8 scores (global, environmental, social and governance for both acquirer and target). Regarding the other aspect, I want the closest date before the announcement, so in your example 16 jan 2023.

      I think the code you provided could work with my data, so I thank you for that, but can there be problems with the code if an acquirer acquires multiple targets over time or would it still work fine?

      Comment

      • #4
        OK, your example data for ESG scores includes only one ticker, JPM, and JPM appears only as an acquirer, not a target, in the other data set. I believe the following modification will also capture the correct ESG scores for target firms, but, evidently, I could not really test this well. I have also modified the code so that it selects the score date that precedes the announcement date in case of a tie for closest to announcement date.

        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str9 dealid str8 targetticker str5 acquirerticker str11 announcementdate float transactionvaluemm
"3101704MM" "PTXTQ" "JPM"  "12-Feb-2019"   302.95
"3091671MM" "IMDZ"  "MRK"  "21-Feb-2019"   187.05
"3094709MM" "WP"    "FIS"  "18-Mar-2019" 42607.66
"3096046MM" "QTNA"  "ON"   "27-Mar-2019"   809.83
"3096392MM" "SRMC"  "MSA"  "29-Mar-2019"     32.3
"3096796MM" "APU"   "UGI"  "02-Apr-2019"  5250.03
"3098596MM" "ADSW"  "WM"   "15-Apr-2019"  4662.77
"3099451MM" "KEYW"  "J"    "22-Apr-2019"   878.71
"3098661MM" "APC"   "OXY"  "24-Apr-2019" 54085.73
"3100112MM" "PETX"  "ELAN" "26-Apr-2019"   211.05
"3101209MM" "AMPY"  "MPO"  "06-May-2019"   516.12
"3101549MM" "ANDX"  "MPLX" "08-May-2019" 14270.49
"3102791MM" "CRAY"  "HPE"  "17-May-2019"  1288.81
"3105580MM" "RTN"   "UTX"  "09-Jun-2019" 89727.25
"3105732MM" "DATA"  "CRM"  "10-Jun-2019" 14772.19
"3106138MM" "TST"   "MVEN" "12-Jun-2019"    20.61
"3106689MM" "ARRY"  "PFE"  "17-Jun-2019" 10421.35
"3106683MM" "CJ"    "FRAC" "17-Jun-2019"   680.83
"3107212MM" "ONSE"  "WLDN" "19-Jun-2019"    24.91
end
tempfile deals
save `deals'

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 scoredate str29 aspectname byte scorevalue str5 ticker
"28/1/2022"  "Social Dimension"              49 "JPM"
"17/1/2018"  "Social Dimension"              39 "JPM"
"18/12/2020" "Social Dimension"              34 "JPM"
"10/9/2015"  "Environmental Dimension"       70 "JPM"
"7/9/2017"   "Environmental Dimension"       63 "JPM"
"28/1/2022"  "S&P Global ESG Score"          40 "JPM"
"7/9/2017"   "Economic Governance Dimension" 62 "JPM"
"12/11/2021" "Environmental Dimension"       55 "JPM"
"8/9/2016"   "Environmental Dimension"       67 "JPM"
"11/9/2014"  "Economic Governance Dimension" 67 "JPM"
"13/11/2020" "Social Dimension"              48 "JPM"
"13/9/2019"  "Social Dimension"              46 "JPM"
"10/9/2015"  "Economic Governance Dimension" 61 "JPM"
"12/9/2013"  "Social Dimension"              51 "JPM"
"17/1/2018"  "S&P Global ESG Score"          59 "JPM"
"18/12/2020" "Social Dimension"              48 "JPM"
"10/9/2015"  "S&P Global ESG Score"          61 "JPM"
"23/9/2022"  "Environmental Dimension"       44 "JPM"
"13/9/2019"  "S&P Global ESG Score"          51 "JPM"
"11/9/2014"  "S&P Global ESG Score"          62 "JPM"
"13/11/2020" "S&P Global ESG Score"          53 "JPM"
"12/9/2013"  "S&P Global ESG Score"          62 "JPM"
"7/9/2017"   "Economic Governance Dimension" 57 "JPM"
"8/9/2016"   "S&P Global ESG Score"          58 "JPM"
"17/1/2018"  "Economic Governance Dimension" 55 "JPM"
"13/9/2018"  "Economic Governance Dimension" 60 "JPM"
"7/9/2017"   "Social Dimension"              39 "JPM"
"13/9/2019"  "Economic Governance Dimension" 38 "JPM"
"17/1/2018"  "Economic Governance Dimension" 62 "JPM"
"13/9/2019"  "Environmental Dimension"       49 "JPM"
"13/11/2020" "Environmental Dimension"       60 "JPM"
"28/1/2022"  "Environmental Dimension"       55 "JPM"
"10/9/2015"  "Social Dimension"              44 "JPM"
"23/9/2022"  "Social Dimension"              33 "JPM"
"17/1/2018"  "Environmental Dimension"       63 "JPM"
"12/11/2021" "Economic Governance Dimension" 55 "JPM"
"18/12/2020" "Environmental Dimension"       60 "JPM"
"28/1/2022"  "Economic Governance Dimension" 45 "JPM"
"7/9/2017"   "S&P Global ESG Score"          59 "JPM"
"13/9/2018"  "Environmental Dimension"       60 "JPM"
end
tempfile esg_scores
save `esg_scores'

use `deals', clear
gen ann_date = daily(announcementdate, "DMY")
assert missing(ann_date) == missing(announcementdate)
format ann_date %td
drop announcementdate
tempfile holding
save `holding'

use `esg_scores', clear
gen score_date = daily(scoredate, "DMY")
assert missing(score_date) == missing(scoredate)
format score_date %td
drop scoredate
tempfile cleaned_esg
save `cleaned_esg'

clonevar acquirerticker = ticker
joinby acquirerticker using `holding', unmatched(using)
gen delta = abs(datediff(ann_date, score_date, "day"))
gen rank = cond(score_date <= ann_date, 1, 2)
by ticker aspectname ann_date (delta rank), sort: keep if _n == 1
drop _merge delta rank
replace aspectname = lower(substr(aspectname, 1, 3)) + "_"
replace aspectname = "glo_" if aspectname == "s&p_"
reshape wide @score_date @scorevalue, i(ticker ann_date) j(aspectname) string
rename (*score*) =_acquirer
save `holding', replace


use `cleaned_esg', clear
clonevar targetticker = ticker
joinby targetticker using `holding', unmatched(using)
gen delta = abs(datediff(ann_date, score_date, "day"))
gen rank = cond(score_date <= ann_date, 1, 2)
by ticker aspectname ann_date (delta rank), sort: keep if _n == 1
drop _merge delta rank
replace aspectname = lower(substr(aspectname, 1, 3)) + "_"
replace aspectname = "glo_" if aspectname == "s&p_"
reshape wide @score_date @scorevalue, i(ticker ann_date) j(aspectname) string
rename (*_scorevalue *_score_date) =_target
        Added: It will work fine if an acquirer acquires multiple targets.

        Comment

        • #5
          Thank you so much!!! It is exactly how I wanted it, however it gives a small error. If there is ESG data for a certain ticker, but there is no data before the announcement date, it takes the closest date to the announcement date. This would work fine if I had a lot of ESG observations, but with most observations in recent years, for deals in 2013 for example, it takes ESG data from 2020 (which is the oldest ESG data available for some tickers). Is it possible, if there is no data before the announcement date, to only take the ESG score if the score date is within 1 year after the announcement date?
          Last edited by Juan Gonzalex; 17 Jan 2023, 05:10.

          Comment

          • #6
            I believe the following will do that:
            Code:
            use `deals', clear
gen ann_date = daily(announcementdate, "DMY")
assert missing(ann_date) == missing(announcementdate)
format ann_date %td
drop announcementdate
tempfile holding
save `holding'

use `esg_scores', clear
gen score_date = daily(scoredate, "DMY")
assert missing(score_date) == missing(scoredate)
format score_date %td
drop scoredate
tempfile cleaned_esg
save `cleaned_esg'

clonevar acquirerticker = ticker
joinby acquirerticker using `holding', unmatched(using)
gen delta = abs(datediff(ann_date, score_date, "day"))
gen rank = cond(score_date <= ann_date, 1, 2)
by ticker aspectname ann_date (delta rank), sort: keep if _n == 1
replace score_date = . if delta > 365 & rank == 2
replace scorevalue = . if delta > 365 & rank == 2
drop _merge delta rank
replace aspectname = lower(substr(aspectname, 1, 3)) + "_"
replace aspectname = "glo_" if aspectname == "s&p_"
reshape wide @score_date @scorevalue, i(ticker ann_date) j(aspectname) string
rename (*score*) =_acquirer
save `holding', replace


use `cleaned_esg', clear
clonevar targetticker = ticker
joinby targetticker using `holding', unmatched(using)
gen delta = abs(datediff(ann_date, score_date, "day"))
gen rank = cond(score_date <= ann_date, 1, 2)
by ticker aspectname ann_date (delta rank), sort: keep if _n == 1
replace score_date = . if delta > 365 & rank == 2
replace scorevalue = . if delta > 365 & rank == 2
drop _merge delta rank
replace aspectname = lower(substr(aspectname, 1, 3)) + "_"
replace aspectname = "glo_" if aspectname == "s&p_"
reshape wide @score_date @scorevalue, i(ticker ann_date) j(aspectname) string
rename (*_scorevalue *_score_date) =_target

            Comment

            • #7
              Thank you very very much!! You have helped me a lot!

              Comment

              Previous Next
              Working...
              X