Merging SDC / CRSP in STATA
Posted: 29 July 2014 03:59 AM   [ Ignore ]
Newbie
Rank
Total Posts:  3
Joined  2014-07-29

Hello Everyone,

First of all I really appreciate the help of this forum. It is my first time to do a statistical analysis for my upcoming thesis therefore I ask for some advise and guidance from you guys. Since I am still at an early stage it would be great if someone could give me ideas for the right approach to solve the following problem:

Basically I will have to analyze abnormal stock returns for specific types of acquisitions. To do so I will collect all the necessary M&A data from SDC Platinum. Then as I know this has to be merged with daily stock prices. Since I focus on public US comps only , I want to retrieve the stock data from CRSP. The analysis process should be done with STATA. I will work with a classical event stuy approach.

The big question to me is now in very general what is the best approach to
1) collect the data within WRDS (CRSP) and SDC in terms of variables and then
2) what do I need to do to merge the datasets.

I read something about extending the 6 digit CUSIP from SDC to make it fit the 8 or 9 digit CUSIP in CRSP. Is that what you guys recommend me to do? Or are there other more efficient ways? And how does actually the process of merging the different identifiers work? Would you reccomend doing this in STATA or with the WRDS CRSP Tool (translate to PERMCO/PERMNO)?

It would be helpful if someone could give me a brief “big picture”. Also if there is any guidline or explanation guide you recommend let me know.

Looking forward to your helpful feedback.

Thanks a lot and best regards

Lukas

Profile
 
 
Posted: 31 July 2014 07:31 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Lukas,

The best approach is to start with the method on paper and ‘work through’ the math/method to see what you need. I suppose it is normal to go back to the drawingboard and improve the model and collect
additional data, so it is helpful to have some sort of a pipeline (I am using SAS remote submit to retrieve data programmatically, as opposed to ‘manual’ with the web interface on WRDS).
There are some examples on this website that will get you started with that.

See post on cusips on SDC: http://www.wrds.us/index.php/forum_wrds/viewthread/309/

Programs/tools for matching. I prefer to use SAS, which is best equipped for data processing. Stata will work as well (Google ‘Stata merge’ for help). Alternatively,
you could use Excel (Google ‘Excel VLookup’), but the main drawback of Excel is that it is manual (and therefore prone to errors when done repeatedly/mindlessly).
I have not used the WRDS CRSP tool you are mentioning.

Hope this helps,

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: 09 September 2014 05:19 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  3
Joined  2014-07-29

Hello Joost,

thanks a lot for your answer a while ago. In the meantime I got used to Stata and was able to collect first data. I still have a few open questions. It would be great if you could share your experience regarding those:
- What identifier can you recommend for merging SDC event dates and CRSP daily stock prices? CUSIP?PERMNO?PERMCO?Ticker?
- Is there a way to retrieve stock data from CRSP directly through Stata? This would be something you mentioned earlier for SAS.
- What is usually chosen when there is no daily closing stock price available for a particular day? Sometimes only an average of bid and ask is available. Just this average?

Thanks a lot and best regards

Profile
 
 
Posted: 10 September 2014 07:46 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Lukas,

I have used Cusip to merge SDC data. I’m not sure if SDC has other firm identifiers (it has been some time I last used it), I expect permno/permco would work fine as well.
Stata can’t connect with WRDS the way SAS can (through remote connect). A good reason to learn some SAS in my opinion smile

If a stock is not traded, the PRC field in CRSP is actually the average of the bid and ask price (multiplied by -1 to identify this is the bid-ask average).

So, if there is no PRC data in CRSP on a given day, there will also be no bid-ask data for that day. This can be the case if the firm was (temporarily) traded on OTC/Pink-sheets, for example while the firm violates some exchange rules (e.g. delayed with SEC filings).

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: 23 September 2014 05:38 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  3
Joined  2014-07-29

Hi Everyone,

I have a two more questions related to my bankruptcy acquisition event study. Most of the earlier stuff got resolved already. Thanks to Joost for his great support.

So for my event study I have a problem that a few event dates are not on trading days and so I am not able to match these. Can someone provide me with a solution so that non-trading event days can be matched to the next available trading day. I have no clue how to arrange for that in Stata. So basically I want to check if there is no matching trading day for the event date in then in that case to choose the next available trading day.

Another issue is to match for the market model an index and index return to all trading days. Since I have a lot of different companies I need to have some kind of relation of the company’s returns to the market returns, Maybe someone could give me an idea of an optimal solution. Should I match each trading day of each individual company to the market data or should you have the market index in the dataset once and work with that? In case one matches the market data onto each company’s daily trading data how can I in stata so that the matching index data is set up to each particular company stock data? Is that possible with a “by companyID: ” function?

Thanks a lot guys and best regards

Profile
 
 
Posted: 23 September 2014 11:33 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Lukas,

To deal with non-trading days: try the following approach. Instead of matching your event date with one trading day, do a match that allows for a range. For example, get the data from trading days that are within 0 and 3 days of your event date. In the matched sample, drop trading days with no trade/price, then sort on permno/gvkey-date and keep the first date for each permno/gvkey. That way, you’ll have the first trading day relative to your event.

To estimate beta for each firm-event, you need a dataset that holds: permno, eventdate, date, firm-return, index-return. Then, do a group-by regression for each permno-eventdate. I don’t have Stata code for this, but in the tutorials there is SAS code that will do this (see http://www.wrds.us/index.php/tutorial/view/17). (I understand you might prefer to do ‘everything’ in Stata, but SAS is better equipped for this. Regressions and data exploration are great in Stata, by the way).

Hope this helps,

Joost

 

 Signature 

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

Profile