Announcement

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

    Merging two datasets based on a date range

    Hi,


    I am a STATA newbie and got somehow stuck merging two datasets.

    One dataset (A) contains, among other things, the announcement date of a merger of a listed company as well as its ticker symbol.

    Code:
    clear
input int DateAnnounced str6 AcquirorPrimaryTickerSymbol
14619 "AOL"  
14619 "NXLK"  
14619 "CAFC"  
22001 "6MK"
14621 "USAI"  
14622 "BFOH"  
14622 "SCHW34"
14623 "EQR"  
14624 "MWL"  
14626 "JDSU"  
end
format %tdDD.NN.CCYY DateAnnounced
    The second dataset (B) contains information on the history of a company's financial managers, as well as the time period during which the manager was in place and again the company's ticker.

    Code:
    clear
input str27 DirectorName str105 CompanyName str84 RoleName int(DateStartRole DateEndRole) str9 Ticker
"Jack Wyszomierski"   "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             13149 16026 "6MK" 
"Peter Kellogg"       "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO/Principal Financial Officer" 18204 19836 "6MK" 
"Bob Bertolini"       "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             16026 18204 "6MK" 
"Robert Davis"        "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             19836 22371 "6MK" 
"Caroline Litchfield" "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             22371     . "6MK" 
"Ira Zar"             "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO"                             14335 15979 "9C7" 
"Jeff Clarke"         "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO/COO"                         16187 16496 "9C7" 
"Nancy Cooper"        "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO"                             17028 18765 "9C7" 
"Robin Elliott"       "FIRST BUSEY CORP"                                       "Director - Financial Reporting"               16922 18628 "BUSE"
end
format %tdDD.NN.CCYY DateStartRole
format %tdDD.NN.CCYY DateEndRole
    I created two frames and imported each dataset in one frame. ("Mergers" and "Managers").

    I would like to find out which financial manager (Dataset B) was in place at the time of the announcement of a deal (dataset A).
    More precisely: In Dataset A a new variable "StartDate" should be defined using the following logic: Search Dataset B for entries about the corresponding ticker (6MK, for example). From the entries related to this ticker, return the start date (DateStartRole) that falls before the deal announcement date, and that minimizes the difference: Announcement Date - DateStartRole. This ensures that we will get the start date of the person who was most recently appointed as the Finance Manager before the deal announcement.

    Following a similar approach, a corresponding variable "EndDate" should be defined in dataset A as follows: Again, search in dataset B for entries to the corresponding ticker. From those entries, output the end date (DateEndRole) that lies behind the deal announcement date, minimizing the difference: DateEndRole - AnnouncementDate. If no value is assigned to DateEndRole, the manager is still in place. In this case, DateEndRole can be set equal to today's date. The time span from the two dates (DateEndRole-DateStartRole) is the tenure of the CFO who held that position through the deal announcement date. Last step: Define a new variable in dataset A (CFO name) and output the name of the corresponding CFO assigned to DateEndRole or DateStarRole (i.e. the CFO who was in office during the deal announcement).

    For example: Ticker 6MK announces a deal on 27.03.2020. In this case, the StartDate variable should take the value 23.04.2014 and the EndDate variable should take the value 01.04.2021. The CFO in place at that time is therefore Robert Davis.

    I would really appreciate if someone could help me out with the code or at least with some information about the necessary stata commands.

    Version: 16.1

    Thank you very much
    Best
    Tags: None
  • #2
    I think this is what you want, or, if not, it's close.

    Code:
    clear
input int DateAnnounced str6 AcquirorPrimaryTickerSymbol
14619 "AOL"  
14619 "NXLK"  
14619 "CAFC"  
22001 "6MK"
14621 "USAI"  
14622 "BFOH"  
14622 "SCHW34"
14623 "EQR"  
14624 "MWL"  
14626 "JDSU"  
end
format %tdDD.NN.CCYY DateAnnounced
rename AcquirorPrimaryTickerSymbol Ticker
isid Ticker DateAnnounced
tempfile dataset_A
save `dataset_A'

clear
input str27 DirectorName str105 CompanyName str84 RoleName int(DateStartRole DateEndRole) str9 Ticker
"Jack Wyszomierski"   "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             13149 16026 "6MK"
"Peter Kellogg"       "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO/Principal Financial Officer" 18204 19836 "6MK"
"Bob Bertolini"       "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             16026 18204 "6MK"
"Robert Davis"        "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             19836 22371 "6MK"
"Caroline Litchfield" "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             22371     . "6MK"
"Ira Zar"             "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO"                             14335 15979 "9C7"
"Jeff Clarke"         "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO/COO"                         16187 16496 "9C7"
"Nancy Cooper"        "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO"                             17028 18765 "9C7"
"Robin Elliott"       "FIRST BUSEY CORP"                                       "Director - Financial Reporting"               16922 18628 "BUSE"
end
format %tdDD.NN.CCYY DateStartRole
format %tdDD.NN.CCYY DateEndRole

rangejoin DateAnnounced DateStartRole DateEndRole using `dataset_A', by(Ticker)
drop if missing(DateAnnounced)  // SURPLUS CFOS NOT INVOLVED IN MERGERS
append using `dataset_A'
by Ticker DateAnnounced (DateStartRole), sort: keep if _n == 1
    -rangejoin- is writtren 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.

    The code assumes that any given Ticker symbol in data set A makes at most one announcement on any given date. The code will break at the -isid- command if that is not true. If it is possible for the same Ticker to announce more than one announcement on the same date, post back with an example like that and I'll modify the code to handle it accordingly.

    The part where I'm not sure what you want is the detail you want into specifying the CFO with the dates closest to the announcement date. I would expect that there wold only be a single CFO in place on a given date, so the tenure intervals of those CFOs would never overlap, and only one CFO what have start and end dates that bracket the announcement date in any case. I've written the code based on that assumption. Again, if a firm can have multiple CFOs with overlapping intervals of service, post back with an example, and I'll modify accordingly.

    Comment

    • #3
      Thank you very much, I really appreciate it.

      Originally posted by Clyde Schechter View Post
      The code assumes that any given Ticker symbol in data set A makes at most one announcement on any given date. The code will break at the -isid- command if that is not true. If it is possible for the same Ticker to announce more than one announcement on the same date, post back with an example like that and I'll modify the code to handle it accordingly.
      Glad that you mentioned it, this came to my mind seconds after posting. There are actually a few cases (~0.9% of the sample) where the company (Ticker) announced more than one deal at the same day. However, if the adjustments takes a lot of effort/time, I can simply drop those observations.

      Originally posted by Clyde Schechter View Post
      The part where I'm not sure what you want is the detail you want into specifying the CFO with the dates closest to the announcement date. I would expect that there wold only be a single CFO in place on a given date, so the tenure intervals of those CFOs would never overlap, and only one CFO what have start and end dates that bracket the announcement date in any case. I've written the code based on that assumption. Again, if a firm can have multiple CFOs with overlapping intervals of service, post back with an example, and I'll modify accordingly.
      Yes, you are right. In the final sample I make sure that there are no overlapping periods for any Ticker so that for every given announcement day, there is only one specific manager/ CFO.

      Best

      Comment

      • #4
        The modification to accommodate multiple announcements by the same firm on the same day is not that complicated. Here it is:

        Code:
        clear
input int DateAnnounced str6 AcquirorPrimaryTickerSymbol
14619 "AOL"  
14619 "NXLK"  
14619 "CAFC"  
22001 "6MK"
14621 "USAI"  
22001 "6MK"
14622 "BFOH"  
14622 "SCHW34"
14623 "EQR"  
14624 "MWL"  
14626 "JDSU"  
end
format %tdDD.NN.CCYY DateAnnounced
rename AcquirorPrimaryTickerSymbol Ticker
tempfile dataset_A
save `dataset_A'

clear
input str27 DirectorName str105 CompanyName str84 RoleName int(DateStartRole DateEndRole) str9 Ticker
"Jack Wyszomierski"   "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             13149 16026 "6MK"
"Peter Kellogg"       "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO/Principal Financial Officer" 18204 19836 "6MK"
"Bob Bertolini"       "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             16026 18204 "6MK"
"Robert Davis"        "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             19836 22371 "6MK"
"Caroline Litchfield" "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             22371     . "6MK"
"Ira Zar"             "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO"                             14335 15979 "9C7"
"Jeff Clarke"         "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO/COO"                         16187 16496 "9C7"
"Nancy Cooper"        "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO"                             17028 18765 "9C7"
"Robin Elliott"       "FIRST BUSEY CORP"                                       "Director - Financial Reporting"               16922 18628 "BUSE"
end
format %tdDD.NN.CCYY DateStartRole
format %tdDD.NN.CCYY DateEndRole

rangejoin DateAnnounced DateStartRole DateEndRole using `dataset_A', by(Ticker)
drop if missing(DateAnnounced)  // SURPLUS CFOS NOT INVOLVED IN MERGERS
gen byte source = 1
append using `dataset_A'
replace source = 2 if missing(source)
by Ticker DateAnnounced (source), sort: keep if inlist(1, _n, source)
drop source
        • 1 like

        Comment

        Previous Next
        Working...
        X