Matching Directors from Compustat Execucomp with Risk Metrics in STATA
Posted: 11 February 2015 12:07 PM   [ Ignore ]
Newbie
Rank
Total Posts:  3
Joined  2015-02-11

Hello dear WRDS Community,

I have to write a seminar paper and need to merge among other things the data sets of Execuomp with the (already) combined data set of Risk Metrics in STATA.
I run into several issues here and would appreaciate it greatly if you could help me out.
First there is as far as I know no director ID with which one can match the directors. Risk Metrics uses a different system for their IDs then Execucomp.
What I tried next is to merge them via their last name (all capital letters) but as I expected there were too many directors with the same name.
Then I tried to include the middle name but that didn’t work either for the same reasons.
My last hope was to use a combination as below:

merge m:1 gvkey fyear lastname sec_name seqnum using execucomp.dta
(the m:1 combination is the only one that works for this set of variables)

but that yielded only 1,340 matches on over 410,000 observations.

Is there a trick or better way to match these directors? I can’t think of any other parameters that exist in both Risk Metrics and Compustat which could link them together on an individual level. I even have the feeling that sometimes for the same company in the same year different directors appear in the different data sets.
I hope I described the problem sufficiently. Thank you already in advance! I appreciate any kind of help!

Profile
 
 
Posted: 11 February 2015 01:46 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

Would it be an idea to match directors in a two-step process. First, attempt to match the firms, and then, attempt to match directors (e.g. on last name only). Matching firms is more common/easier, and - given a match - it would significantly reduce the pool of potential matches for directors.

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 11 February 2015 03:43 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  3
Joined  2015-02-11

Hi,
thanks for the quick reply! Matching the companies is not the problem as I have gvkeys in both datasets. I have data on directors from Risk Metrics like membership in committees and I need the data about their salaries from execucomp. So company specific data of compustat is not really important for me here but only the director specific data. 
The issue I’m facing is that more often than not the data of Risk Metrics does not seem to match the data of execucomp in therms of names. For example the same company in both Risk Metrics and Execucomp has in the same year different directors listed. I’m not able to figure out a “system” in these irregularities.

What I tried in the meantime:

I generated a id_number in order to account for persons with the same first and last name (but maybe different middle names or sufixes like “III”). I did this in both data sets.
Then I merged the Risk Metrics with the execucomp (see code below)

bysort gvkey fyear lastname first_namegen seqnum=_n
merge 1
:m gvkey fyear lastname first_name seqnum using execucomp.dta 

This got me to the “best” result so far but I’m still miles away from where I’d like to be:

Result                   # of obs.
  ————————————————————-
  not matched               358,578
      from master             184,636 (_merge==1)
      from using               173,942 (_merge==2)

  matched                     26,786 (_merge==3)
  ————————————————————-


I guess my question really is:
Can it be that the two databases really do have different sets of data and this is the best I will get?
Or is it just a matter of coding the right variables?

Thank you already for your help!

 

Profile
 
 
Posted: 11 February 2015 05:07 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

I also would expect a better match. It is hard to tell though howcome you are losing so many obs.

If you would just try to match firm-years, without attempting to match directors, what is the % match? I can’t seem to find the variable gvkey on Riskmetrics, by the way (I see cusip is on both datasets, I wouldn’t know if that is ‘actual’ or ‘historic’ cusip though, could be a reason to lose obs if you used this to get to gvkey).

If the firm-level match is good, I would draw a small sample of firms (5-10 or so) to write a ‘good’ match and then see how this holds up for the full sample. Maybe there is something going on with the ‘case’ of the names. Risk metrics has last names in uppercase, while Execucomp has last names in normal case. It looks like ‘age’ is well populated in both datasets, so last name + same/similar age (instead of first name) could be matching criteria as well.

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 13 February 2015 04:22 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  3
Joined  2015-02-11

hi,

thank you very much for your help. I merged the Risk Metrics data with the Tool offered of CRSP/Compustat with their PERMNO and then from PERMNO to GVKEY. That is how I had GVKEY in my Risk Metrics data.
The problem itself got solved in a sense as my supervisor explained to me that I needed only the mean of the wages per annum per company. That left me with one observation per year and like you said the matching of Execucomp and Risk Metrics on a company level is fairly good.

One important distinction that I forgot to make was also the difference of the various datasets on Execucomp. Some are about just directors and others just about executives. That helped with the confusion and the bad matching.

Kind regards

Profile
 
 
Posted: 13 February 2015 08:12 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

thanks for following up—I’m glad it worked out!

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile