calculate excess returns
Posted: 23 July 2014 05:24 AM   [ Ignore ]
Newbie
Rank
Total Posts:  26
Joined  2014-05-03

Hi joost,

I want to calculate the value of cash holdings following Faulkender and Wang(2006).  The dependent variable is excess stock returns during fiscal year. They calculate excess stock returns as stock i’s fiscal year returns less the return of stock i’s benchmark portfolio. The benchmark protfolio is 25 Fama French protfolios formed on size and book-to-market.

I now know how to calculate stock i’s return during fiscal year using monthly returns.I also have acess to the 25 Fama French protfolios returns. My question is how to calculate excess returns? I mean, how to assign stock i to each 25 protfolios?

Thanks.
Xinjiao

Profile
 
 
Posted: 24 July 2014 10:08 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Xinjiao,

I suppose your problem is knowing to which portfolio each observation belongs to? For this, you will need to know the cut-offs of size and btm that were used to construct the portfolios.

I am assuming that the 25 size/btm portfolios you are using are from Kenneth French (these are on WRDS, but also on his website). The cutoff data used to construct the portfolios can be downloaded here:
http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html—scroll to U.S. Research Breakpoints Data (Downloadable Files)

For each observation you will need to ‘lookup’ in which quintile it falls (for both size and btm), and then determine the correct portfolio.

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: 22 April 2015 12:17 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  4
Joined  2015-04-22
Joost Impink - 24 July 2014 10:08 AM

hi Xinjiao,

I suppose your problem is knowing to which portfolio each observation belongs to? For this, you will need to know the cut-offs of size and btm that were used to construct the portfolios.

I am assuming that the 25 size/btm portfolios you are using are from Kenneth French (these are on WRDS, but also on his website). The cutoff data used to construct the portfolios can be downloaded here:
http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html—scroll to U.S. Research Breakpoints Data (Downloadable Files)

For each observation you will need to ‘lookup’ in which quintile it falls (for both size and btm), and then determine the correct portfolio.

Hope this helps,

Joost

Hi Joost,

I am new to the WRDS-CRSP database, and I am struggling to understand how to obtain the book-to-market ratio for each security. I have never used SAS before, and so far I have been working with the database using the web interface and importing the data I need on STATA (or Matlab).  Do you know if I can compute the btm values myself using the Web interface in the first place? Any input on that would be greatly appreciated.


p.s.
I am writing in this Topic because I was also planning to sort them in btm deciles\quintiles, but please move the question on a specific topic if you think it is better so.

Profile
 
 
Posted: 22 April 2015 02:05 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

The btm ratio is straightforward to compute as book equity divided by market cap. You cannot construct new variables in the web interface, but you could use the WRDS web interface to download the needed variables (ceq, csho, prcc_f) and then import the data into Stata/etc to compute it there.

If you were to use SAS you could use remote submit (rsubmit) to write a query that would compute mtb and then download the resulting dataset (using a proc sql and a proc download). It probably depends on how much ‘SAS’ you plan to do (or would need) in the future to decide whether or not it is worth learning it.

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: 30 April 2015 05:59 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  4
Joined  2015-04-22
Joost Impink - 22 April 2015 02:05 PM

hi,

The btm ratio is straightforward to compute as book equity divided by market cap. You cannot construct new variables in the web interface, but you could use the WRDS web interface to download the needed variables (ceq, csho, prcc_f) and then import the data into Stata/etc to compute it there.

If you were to use SAS you could use remote submit (rsubmit) to write a query that would compute mtb and then download the resulting dataset (using a proc sql and a proc download). It probably depends on how much ‘SAS’ you plan to do (or would need) in the future to decide whether or not it is worth learning it.

Hope this helps,

Joost

Thank you so much Joost for your prompt reply! This helps indeed.  Quick question: is there any specific reason it may not be recommendable to use directly the variable bkvlps (book value per share)?

 

Profile
 
 
Posted: 30 April 2015 06:52 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

bkvlps is slightly less well populated compared with csho and ceq. On 2001+ data in Compustat Annual, there are about 2200 firmyears with missing bkvlps, while ceq and csho are available. The reverse (no csho, ceq, but bkvlps available) also happens (just 9 firmyears though).

This is the code that identifies the obs with available bkvlps and missing csho, ceq:

data testcomp (keep gvkey fyear csho ceq bkvlps );
set comp.funda;
/* more recent data */
where fyear 2000;
/* standard filtering */
if indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
/* missing data for csho, ceq */
if cmiss (of csho ceq) > 0;
/* but bkvlps available */
if missing(bkvlpseq 0;
run

It appears that ceq is missing for these cases.

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: 30 April 2015 08:23 AM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  4
Joined  2015-04-22

I see now, thanks a lot!

Profile