Match SDC, CRSP & Compustat to calculate abnormal returns
Posted: 13 January 2014 07:11 AM   [ Ignore ]
Newbie
Rank
Total Posts:  28
Joined  2014-01-13

Hey guys,

I am about to write a finance paper and I need data from SDC, CRSP & Compustat. I use Stata to match the databases and to do my evaluations.

Basically, I want to download a sample of IPOs from the SDC database (over a 10-year period) and assign the corresponding stock returns. Then, I want to compare these returns to different benchmarks (compare to control firms) to see whether the IPO firms out- or underperform the benchmarks (calculate abnormal returns over a 2-year-holding period).

Right now, I am trying to create the sample of control firms. There will be two types of control firms:
1) Control for marketcap and book-to-market effects
2) Control for marketcap and industry effects

I am really facing difficulties how to do this. For the past two days, I was thinking about this and I got several questions in my mind. In particular, how do I find the control firms?
I have a sample containing all ordinary shares listed on NYSE, Amex or Nasdaq. From that sample, I want to pick my control firms. But how can I possibly match the sample and the control firms? Is it possible to calculate B/M ratio on a daily basis? Is this necessary for a match?

Can anybody tell me where to start with this at all? I am quite confused right now.

Thanks

Profile
 
 
Posted: 13 January 2014 08:46 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

It sounds (that for your study) of the universe of firms you would like to compare firms that do a carve-out with firms that don’t (and that are reasonably comparable).

One way to go about this is as follows:
- compute the main metrics (market-to-book ratio, size, etc) for all firms
- match carve-out firms with non-carve-out firms
- compare 2 year abnormal returns

If you have many dimensions that you need for matching, you will run out of firms. Typically, you can match on industry and size, or industry and market-to-book, but matching on another third one may not work (no obs left to match). Nonetheless, you can match on any dimensions by computing a ‘distance’ on all metrics; for example: when matching on size, mtb and roa, select all combinations of firms in that industry and compute the difference in size, mtb and roa, and ‘weigh’ these into a score. Then, the firm with the lowest score is the best match.

Another possibility is to use propensity score matching; you would do a logit regression with ‘carve out’ as the dependent (1 for carve out, 0 for non-carve-out). Include any independent variable you deem relevant (size, age, profitability, mtb etc). Then, you can match on the fitted value (probability of carve-out). So, you would match a carved out firm with a fitted value (probability) of for example 0.7 with a non-carved out firm with the same probability of carving out. This addresses endogeneity, but also, it happens to be that the sample of carved out and matched non-carved out firms are very similar in the independent variables. (so, you get the ‘matching’ for free). This is nice because this will typically be a high quality match on much more variables than ‘simple’ matching.

It may be an idea to start thinking/playing with this using a single carved-out observation. Once you are comforable with an approach, then scale up to your full sample.

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 January 2014 09:39 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  28
Joined  2014-01-13

Hi Joost,

first of all: Thank you very much for your answer. I really appreciate answers to this specific question.

Yes, I am trying to compare the performance of IPO firms to the performance of similar/control firms in order to calculate abnormal returns. At max, I will have two dimensions for matching (see my post above).

The first step is now to merge these two databases and to calculate the metrics, which I later use to match the control firms with my “main” firm sample. The main problem I have right now is how to calculate the metrics in a reasonable way. One the one hand, I have daily stock data and on the other hand I have annual data from Compustat such as Common Equity (necessary to calculate Book-to-market ratio).

Obviously, I need to calculate these metrics for the control firms as well and then compare them somehow in order to match them.

Thanks again!!!

Profile
 
 
Posted: 13 January 2014 08:16 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

Ok, I understand. The problem is basically that for each potential match, you would need to compute the market cap for each potential match firm for each trading day of each year, which blows up the size of your dataset (basically, it will be 252 times as large as when you would compute market cap also at year end).

I doubt that the original paper actually have done what they describe, because it doesn’t make sense. The criterion to match on size is very loose between 70% and 130%, so this ‘micro-management’ in matching on the right day is basically pretty much undone by this loose range. (And of course, whatever size effect would be the result of inaccurate matching would be undone in the ‘abnormal return’ calculation, i.e., if size is used as a risk factor by cleaning up the returns).

Nevertheless, if you really want to follow this paper you will either end up with a very large intermediate dataset for potential matches, or you could wrap the matching procedure in a macro, and run it carveout firm-by-carveout firm (one firm at the time).

If you have some flexibility in how to do this, I would find some sort of compromise, and for example match on market cap on fiscal year end as well as have some ‘momentum’ matching in stock returns (requiring a similar stock return over the fiscal year, so that you don’t match a falling rock with a rising star); both requirements would basically give a match on market cap at carve-out date.

There is probably also the issue whether a matched firm is used once, or if it can enter the sample multiple times. If it can enter just once, you will need to re-run your procedure for carve-outs that had the same matched firm.

On the last issue: as of the end of the last calendar year before carveout. (what does this mean?)—that means that firms can be a match at any point in time when they were active; so a non-carveout firm that gets delisted in 2005 can be used as a match in the years before 2005. That sentence basically indicates that the date of the most recent end-of-year (prior to carveout) was used for the carveout firms to match with non-carveout firms.

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: 14 January 2014 03:48 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  28
Joined  2014-01-13

Hey Joost,
thank you very much for you answer!

What I still don’t understand is:
1) We match on size one day after IPO - This is no problem, because we can calculate marketcap on each day in the sample for the IPO & control firms.
2) After we matched on size, we match - for example - on the closest Book-to-market ratio. But at what point in time? Because fiscal years are different.

I am really new to all this stuff. I have never done something like it before. So it’s quite a jump in the cold water wink But I will figure it out thanks to forums like this.

Thanks

 

Profile
 
 
Posted: 14 January 2014 07:46 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

You’re welcome smile We are getting there though!

As for computing the earnings-price ratio, it probably makes most sense to compute this on an annual basis (based on annual report). And then use the most recent one available for matching purposes.

Example.
Firm A has an end of year of December 31. Firm B (the carveout firm) has end of year on June 30. Firm B carves out on September 2007. For earnings-price matching purposes, you use the most recent e-p ratios. For firm A that will be December 31 2006, for firm B it would be June 30 2007.

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: 26 January 2014 06:28 AM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  28
Joined  2014-01-13

Hi Joost,

thanks. I am still working on it. I have prepared the datasets now and I am ready to find the control firms.

I want to find a control firm for every sample firm. As far as I know, I have to create a loop or something.

I got two datasets:
1) Includes my sample firms for which I want to find control firms
2) Includes all possible control firms

I want Stata now to find a control firm based on the following characteristics:
1) The control firm’s market cap shall be between 80% and 120% of the sample firm’s market cap. All other control firms from dataset 2 shall be immediately dismissed.
2) From the remaining firms in dataset (2), pick the one as control firm with the closest book-to-market ratio.

I know that I somehow have to create a loop. But I am completely new to this. Do you have some hints/ideas?

Thanks

Profile
 
 
Posted: 26 January 2014 09:27 AM   [ Ignore ]   [ # 7 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

The most beautiful solution would to have a recursive function that calls itself with the yet-to-match records and returns and empty set when done. I wouldn’t recommend that in Stata though (see here for article on recursive stuff in Stata: http://www.econometricsbysimulation.com/2012/07/recursive-programming-in-stata.html). Recursive stuff is also painful in SAS, but it would be easy in R.

I would go for a simple loop that runs 3-5 times or so, something like this:
- make a dataset (say X) with the observations to match (initially that is your full carveout sample)
- make an empty dataset (say Y); this will hold matched results
- start a loop:
forvalues i=1/5 {
// matching procedure goes here, matched records are appended to Y and removed from X
}
- when the loop is done, X should contain unmatched records (hopefully close to 0 records), Y should hold matched records

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: 26 January 2014 02:31 PM   [ Ignore ]   [ # 8 ]
Newbie
Rank
Total Posts:  28
Joined  2014-01-13

Hi Joost,

thanks for your answer. I am still confused.

1) In your example, where exactly is my dataset with all the possible control firms.
I have dataset X—> sample and Y—> completely empty. Where is - let’s say Z - the dataset, from which Stata should pick a control firm for the firms in dataset X.
2) In which dataset do I start the loop? Within the forvalues function, am I be able to access other *dta files? I mean this must be possible somehow, because I have different datasets.
It has to be something like: For firm A in dataset X, look for a similar firm in dataset Z based on the control factors and give them back both back in dataset Y. Is that correct?
3) Why should it run 3-5 times? You mean for each control factor/characteristic once? e.g. 1) Year 2) MarketCap 3) Book-to-market

Thanks!!

Profile
 
 
Posted: 26 January 2014 08:05 PM   [ Ignore ]   [ # 9 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

No problem, I realize I didn’t include the possible matching firms.

1. X is your sample of carveouts. Z are all the other (non-carveout) firms that are potential matches. These datasets you have or are easy to construct (X you have, Z is the ‘universe’ of firms excluding X).
2. When you start the loop for the first time, X and Z exist, and Y is empty. The code in the loop would match X to Z and move these records into Y (that contains details on the matching). The matched firms should be removed from X and Z, because these firms are now matched and should not be matched a second time.
3. I included a loop just in case the matching is not complete the first time. Maybe you want matched firms to be unique (i.e., a matched firm is matched to 1 carve out, not to multiple carveouts). A loop would then take care of the unmatched carveout firms. (Maybe you don’t need a loop)

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: 28 January 2014 05:18 AM   [ Ignore ]   [ # 10 ]
Newbie
Rank
Total Posts:  28
Joined  2014-01-13

Hi Joost,

thanks for your answer. I managed to code a loop and it works more or less. However, there is one problem, which I couldn’t solve for far.

Different sample firms can be matched to the same matching firms. This is not what I want. You pointed this out as well. If a firm got matched, it should be removed from dataset Z so that it cannot be matched again to another sample firm. Do you know how I could code this with Stata? The firms that have been matched should be removed from dataset Z before the loop runs again.

Thanks

Profile
 
 
Posted: 28 January 2014 08:38 AM   [ Ignore ]   [ # 11 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

The code you need is on this page: http://www.ats.ucla.edu/stat/stata/notes/countn.htm

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: 04 February 2014 08:26 AM   [ Ignore ]   [ # 12 ]
Newbie
Rank
Total Posts:  28
Joined  2014-01-13

Hi Joost,

thanks for your help. I managed to create my dataset including my sample as well as control firms.

Basically, it looks like the following:

Sample firm     Sample Return       Matching Firm     Matching Return

I have daily returns for each firm. Now, I want to calculate the abnormal returns. What would be the best way to approach this?
Should I define a panel or what’s the best way to approach return calculations in Stata?

Thanks again

Profile
 
 
Posted: 04 February 2014 07:50 PM   [ Ignore ]   [ # 13 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

This may be helpful:
http://www.sunwoohwang.com/Event_Study_STATA.pdf
http://dss.princeton.edu/online_help/stats_packages/stata/eventstudy.html
http://www.eventstudytools.com/stata 

The last link has a zip with a Stata .do file.

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: 06 February 2014 01:54 PM   [ Ignore ]   [ # 14 ]
Newbie
Rank
Total Posts:  28
Joined  2014-01-13

Thanks Joost, I managed to calculate some abnormal returns (sample vs. control firms).
However, I get quite different results than the original paper :( Where is a good start to look for errors?
Obviously, I cannot replicate the matching procedure 1:1, so I guess this is quite a big error source…

I did this for sample and control firm, then took the average of the yearly returns and:
Abnormal return = Sample average yearly return - Matching average yearly return

Profile
 
 
Posted: 06 February 2014 07:28 PM   [ Ignore ]   [ # 15 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

I don’t know the paper you are replicating, but I would think that ‘abnormal’ return equals actual (or, ‘raw’) return minus ‘normal’ return. The normal return can be computed using the market model, CAPM, factor models (like Fama and French 3 factor model) etc. That way, you would have an abnormal return for both the carveout firms and the matched firms.

Does the paper include descriptive statistics for the carvout firms and matched firms separately; that could be a better starting point to see if things are going 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