Monthly market-adjusted returns for the fiscal year and firm age
Posted: 25 March 2015 10:47 AM   [ Ignore ]
Newbie
Rank
Total Posts:  8
Joined  2015-03-25

Hi,

I am a new Compustat/CRSP user and have the following two questions:

1. I want to create a firm age variable which is defined as current fiscal year minus first annual appearance in Compustat. Where do I find information on the first annual appearance in Compustat? Which variable do I need for this?

2. I would like to calculate the standard deviation of monthly market-adjusted returns for the fiscal year. Unfortunately I do not know which Compustat or CRSP variables I do need for this.

Could someone help me, please?

Thank you very much.
Diedrich

Profile
 
 
Posted: 25 March 2015 12:47 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Diedrich,

See this post for computing firm age using including in CRSP as the ‘birth’ of the company (IPO date): http://www.wrds.us/index.php/forum_wrds/viewthread/447/

Alternatively, you could use the first fiscal year a given firm appears in Compustat as year 0. Keep in mind though that the IPO prospectus is used to ‘backfill’ a few years of data. In other words, a company with an IPO in 2002 may have data from 2000 onwards in Compustat. (One way to adjust for that is to require prcc_f to be nonmissing in determining the first fiscal year)

To compute the standard deviation of market-adjusted monthly returns:
- get monthly returns from crsp.msf over the fiscal year
- get market return from crsp.msix for each month, and compute market-adjusted return
- for each fiscal year, compute the standard deviation of the 12 adjusted returns

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: 25 March 2015 02:19 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  8
Joined  2015-03-25

Thanks for your quick response, Joost.

Unfortunately your answer did not solve my problems. Maybe I just didn’t describe it correctly.

I have already seen the posting regarding firm age before.
But since I am replicating “Corporate Payout, Cash Retention, and the Supply of Credit: Evidence from the 2008-09 Credit Crisis” (http://papers.ssrn.com/sol3/papers.cfm?abstract_id=2255211) in which firm age is defined as current fiscal year minus first annual appearance, I have to use this definition for firm age. However, I don’t know how to identify which fiscal year was the first one, in that a firm was listed in Compustat. What variables do I need for it?

Regarding the standard deviation of market-adjusted monthly returns, I have the following questions:
Where do I get monthly returns over the fiscal year from? In the CRSP Security Monthly database there is the item “TRT1M—Monthly Total Return”. Do I have to use this item? How do I match the data with annual fiscal year data?
What item from what database do I need for the market return?
How do I compute the market-adjusted return? Is it monthly return minus market return? Do I use e.g. Stata for those calculations?

The variable definitions of the paper can be found in the appendix. It would be great if you could have a look at the definitions of the variables “Firm age” and “Volatility”. Those are the two things I am struggling with.

Maybe those questions sound stupid to you, but this is the very first time that I am using a database and also the very first time that I use Stata.

 

Profile
 
 
Posted: 25 March 2015 02:35 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Hi,

Sounds like an exciting adventure! smile

First appearance would be the first year that the firm is included in Compustat Fundamental Annual. The fiscal year is in the variable ‘fyear’. So, for a given firm, take the minimum value of fyear, and that is the first year they are in Compustat. To compute firm age for any year, take the fiscal year (fyear) minus the minimum value of fyear.

Monthly returns are ‘RET’ in crsp.msf. The variable ‘datadate’ in Compustat is the last day (approx) of the fiscal year. Subtract 12 months and add 1 day to get the first day of the fiscal year. Then, retrieve all RET from MSF where MSF’s date is within that range: should be 12 monthly returns.

Crsp.msix has a few options with respect to measuring market return: either value weighted or equal weighted, or dividend adjusted or not. It probably doesn’t matter smile

Market adjusted return means subtract the market return from the firms return. For example, if the firm’s return in a month is 2%, and the market return is 1.5%, then the market-adjusted return for that firm-month is 0.5%. There are two ways of getting to a yearly return: (1) compute monthly abnormal return and compound it, (2) compound firm return and compound market return and take the difference. Different people believe different things as to what is the ‘right’ way.

I am a big fan of Stata for the statistical packages, not for data management. I would recommend learning SAS to create variables like these.

Best,

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: 25 March 2015 03:02 PM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  8
Joined  2015-03-25

Thank you so much.

Now I got the point regarding firm age and how to compute market-adjusted returns. Unfortunately I can’t find the Crsp.msix file. Is it the same as Crsp.msi?

And still some technical questions: How do I retrieve all “RET” from MSF where MSF’s date is within that range? Do I do this with Stata?
Is it possible to combine the monthly return dataset with Compustat data like Total Assets in one data output, or do I need two excel files/ stata documents (one for the Compustat output and one for the monthly return output)?

Profile
 
 
Posted: 25 March 2015 03:14 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Hi,

This is msix on wrds: https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=137&file_id=67030

crps.msi seems to have similar/the same data (never used it, but it could be just fine)

Creating a dataset is like making a beads-necklace:
- filter Compustat and get variables you need, like gvkey, fyear, datadate, sich (industry), sale, at, etc
- construct variables like firm age, beginning fiscal year
- join with CCM linktable to get lpermno (historic permno)
- using lpermno, match on permno and get monthly returns from crsp.msf
- match with crsp.msix and append market return
- construct variables like abnormal return
- compute standard deviation of returns
- etc

I.e., in each step you ‘add’ a variable to your dataset, using whatever package you want to use/learn. Stata/SAS/R/SPSS would all be able to do this. Possibly Excel, but that would be a lot of manual work (and error prone/time consuming).

Best,

Joost

 Signature 

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

Profile