For my finance thesis I need to replicate the Fama & French (1993) study. Sadly, I cannot just use their portfolio (which is on their website) since my dataset differs too much. Since probably not everybody is familiar with F&F let me explain what they do. They form a portfolio of stock returns into six groups based on the size of the firm (2 groups), in terms of Market capitalization) and on the Book to Market ratio (3 groups; low, medium, high). Eventually, this portfolio is used to predict future stock returns of a company. The Size group is calculated once, in my case, based on the average market capitalization of a firm, whereas the B2M ratio is calculated for every date. This can be depicted in the matrix below.
So far, no problems. However, now I get stuck. Let's focus on one point (I think I can do the others myself 
). At position [1,1] I would like to depict the average weighted return of all companies over the whole sample period with a below average market capitalization and a low B2M ratio. I thought I should do it stepwise and first calculate the total market capitalization of the firms matching the criteria and then calculating the weights of the return and finally summing those weights. However, I have no idea how code this.
Untill now I have the following code:
The code for SLsize does work partially, but it only returns the average market capitalization instead of summing the average market capitalization by date. In addition, since the B2M ratio is calculated quarterly (i.e. for every observation) a company might be included in the small size group and in the low B2M group at date t, but not at date t+1 since the B2M ratio might have increased. However, I can not think of a way to report the market capitalization by date, since -gen- doesn't allow a -by()- option, and to take the B2M ratio into account over time.
Furthermore, if the previous is calculated, I need to calculate the weighted returns, but I again I don't know how I can do this. If you could help me with this I would be really pleased. I've included a snippet of data in which REITID represents the unique firm number. In case more observations are needed, I've included a link to the dta file with my whole dataset.
Code:
Low Medium High Small . . . Big . . .
). At position [1,1] I would like to depict the average weighted return of all companies over the whole sample period with a below average market capitalization and a low B2M ratio. I thought I should do it stepwise and first calculate the total market capitalization of the firms matching the criteria and then calculating the weights of the return and finally summing those weights. However, I have no idea how code this.Untill now I have the following code:
Code:
*Generate B2M ratio. gen B2M = Equity/MarketCap *Generate percentiles for HML categories. medsize is generated once over whole sample. egen medsize = pctile(MarketCap), p(50) egen b2m30 = pctile(BEME), p(30) by(Date) egen b2m70 = pctile(BEME), p(70) by(Date) *Generate average market capitalization per firm egen RMarketCap = mean(MarketCap), by(REIT) *Calculate the value of SL, or position [1,1] for every date. SL = small firm with a low B2M ratio (low meaning below 30%). gen SLsize = RMarketCap if RMarketCap <= medsize & B2M <= B2M30 & B2M!=.
Furthermore, if the previous is calculated, I need to calculate the weighted returns, but I again I don't know how I can do this. If you could help me with this I would be really pleased. I've included a snippet of data in which REITID represents the unique firm number. In case more observations are needed, I've included a link to the dta file with my whole dataset.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input float Date double MarketCap float(REITID ExcessReturn B2M medsize B2M30 B2M70 RMarketCap) 171 30580.82040288 1 . .7350686 1018852.6 .59556943 .8608105 25747.87 172 31072.528295704997 1 .0005987668 .7050279 1018852.6 .6056303 .8530382 25747.87 173 32306.846208754003 1 .027023055 .6566719 1018852.6 .5451224 .7528589 25747.87 174 37297.04904134799 1 .2054389 .6518746 1018852.6 .51244944 .7250816 25747.87 175 36036.069678048 1 -.11209878 .6673591 1018852.6 .4784175 .6816458 25747.87 176 40161.678815915 1 .10108407 .59128505 1018852.6 .4613801 .652849 25747.87 177 30425.160502463998 1 .29007214 1.091958 1018852.6 .50761425 .7542423 25747.87 178 42887.125836002 1 -.2375491 .941961 1018852.6 .4887427 .7049394 25747.87 179 42819.691209471996 1 -.0026751994 .9306232 1018852.6 .4364324 .652559 25747.87 180 42039.731268582 1 -.02139074 .9325226 1018852.6 .5005072 .720836 25747.87 181 43924.018689261 1 .04315041 1.0405697 1018852.6 .4409446 .6727391 25747.87 182 44054.206913496004 1 -.06540422 1.0246922 1018852.6 .44259685 .6782482 25747.87 183 45246.68913177 1 .021553816 .9899951 1018852.6 .4522922 .7046509 25747.87 184 45418.510138365 1 .0035902506 .9722908 1018852.6 .4017629 .6540089 25747.87 185 44956.528470713005 1 -.0105394 .9622407 1018852.6 .43901145 .709092 25747.87 186 46411.884877585006 1 .03075045 .9106504 1018852.6 .41792175 .6718607 25747.87 187 45757.4671706 1 -.0227728 .9025412 1018852.6 .4135093 .6672567 25747.87 188 45876.411699412005 1 -.002828495 .8812372 1018852.6 .4260648 .6645321 25747.87 189 44889.198915288 1 -.022453817 .8780509 1018852.6 .4490281 .7052955 25747.87 190 44152.54853861 1 -.017860636 .8774579 1018852.6 .465342 .7428529 25747.87 191 38478.507049789994 1 -.13885278 .9889157 1018852.6 .5472356 .9280793 25747.87 192 40266.307164301 1 .04804619 .9331623 1018852.6 .5622402 .9331623 25747.87 193 33035.566815328006 1 -.1987754 1.6655988 1018852.6 .56953835 1.0385497 25747.87 194 23182.847539095997 1 -.20155098 2.3101563 1018852.6 .5234427 1.055613 25747.87 195 7760.34413 1 -1.3011998 6.807301 1018852.6 .6987443 2.0393057 25747.87 196 8622.586326 1 .4121676 5.938937 1018852.6 .993713 2.686053 25747.87 197 8499.73404 1 -.3263563 5.458171 1018852.6 .8338882 1.820923 25747.87 198 14645.8004 1 .544717 3.1195974 1018852.6 .6827873 1.375261 25747.87 199 13172.9416 1 -.11059936 2.97314 1018852.6 .6377322 1.2437276 25747.87 200 13150.912740000002 1 -.009022038 2.941165 1018852.6 .6090338 1.0971746 25747.87 201 15918.4460278 1 .1894077 2.2296774 1018852.6 .6410594 1.1495655 25747.87 202 15611.32134 1 -.01988218 1.9742082 1018852.6 .5664189 1.0481322 25747.87 203 14929.4454452 1 -.04799976 2.0165517 1018852.6 .5870725 1.007211 25747.87 204 19753.77002 1 .278052 1.4968282 1018852.6 .5657899 .9600379 25747.87 205 19027.460900000002 1 -.04353433 1.4578403 1018852.6 .6099839 .9964955 25747.87 206 16147.62898 1 -.16551 1.3100995 1018852.6 .7067347 1.2440052 25747.87 207 13186.305279999999 1 -.2181156 1.6018133 1018852.6 .6753996 1.1112103 25747.87 208 15349.68349 1 .13681607 1.337487 1018852.6 .56365186 1.0353396 25747.87 209 14285.48538 1 -.09799263 1.3862323 1018852.6 .56275475 .9905596 25747.87 210 14078.44936 1 -.04919879 1.0566505 1018852.6 .5941754 .9438451 25747.87 211 12365.97687 1 -.193281 1.1817101 1018852.6 .5950864 .9441252 25747.87 212 14168.32652 1 .08786592 1.0595465 1018852.6 .54727787 .8958662 25747.87 213 11336.54621 1 -.27611095 1.2770203 1018852.6 .55804485 .9769845 25747.87 214 9213.41544 1 -.2012403 1.3918834 1018852.6 .5986384 1.0272286 25747.87 215 9043.2579664 1 -.07125671 1.4778966 1018852.6 .61545 1.0220538 25747.87 216 7433.91568 1 -.24266677 1.7144396 1018852.6 .59337777 1.0100846 25747.87 217 7949.89245 1 .02178277 1.5248005 1018852.6 .56043124 .9467077 25747.87 218 7650.84932 1 -.07571455 1.507545 1018852.6 .6042414 .983747 25747.87 219 9926.50886 1 .3004057 1.0280553 1018852.6 .54293126 1.009786 25747.87 220 30503.65184 1 .3333415 .7494513 1018852.6 .5398651 .9773962 25747.87 221 29098.8784 1 -.070746735 .7918518 1018852.6 .6241224 1.0190083 25747.87 222 25486.60384 1 -.1509467 .8759504 1018852.6 .6450021 1.0765688 25747.87 223 25293.0064256 1 -.022240305 .9348038 1018852.6 .5955595 1.0859509 25747.87 224 27912.36904 1 .07483417 .8175229 1018852.6 .59092426 1.0745182 25747.87 225 28445.21993 1 .005392993 .7617097 1018852.6 .5416646 1.0330153 25747.87 226 24557.0244 1 -.15608214 .8591024 1018852.6 .54196024 1.0085274 25747.87 227 22744.41615 1 -.08716157 .8946812 1018852.6 .56851125 1.017011 25747.87 228 23770.9884465 1 .033446163 .821716 1018852.6 .5670303 1.0011584 25747.87 229 16805.597420000002 1 -.3587571 1.1517591 1018852.6 .55462635 .9895355 25747.87 171 . 2 . . 1018852.6 .59556943 .8608105 427924.7 172 . 2 . . 1018852.6 .6056303 .8530382 427924.7 173 . 2 . . 1018852.6 .5451224 .7528589 427924.7 174 . 2 . . 1018852.6 .51244944 .7250816 427924.7 175 . 2 . . 1018852.6 .4784175 .6816458 427924.7 176 . 2 . . 1018852.6 .4613801 .652849 427924.7 177 . 2 . . 1018852.6 .50761425 .7542423 427924.7 178 . 2 . . 1018852.6 .4887427 .7049394 427924.7 179 . 2 . . 1018852.6 .4364324 .652559 427924.7 180 . 2 . . 1018852.6 .5005072 .720836 427924.7 181 . 2 . . 1018852.6 .4409446 .6727391 427924.7 182 . 2 . . 1018852.6 .44259685 .6782482 427924.7 183 . 2 . . 1018852.6 .4522922 .7046509 427924.7 184 . 2 . . 1018852.6 .4017629 .6540089 427924.7 185 . 2 . . 1018852.6 .43901145 .709092 427924.7 186 . 2 . . 1018852.6 .41792175 .6718607 427924.7 187 . 2 . . 1018852.6 .4135093 .6672567 427924.7 188 . 2 . . 1018852.6 .4260648 .6645321 427924.7 189 226469.02462826198 2 . .8505164 1018852.6 .4490281 .7052955 427924.7 190 214096.106757012 2 -.04525205 1.0485187 1018852.6 .465342 .7428529 427924.7 191 199070.626705727 2 -.06908408 1.1869885 1018852.6 .5472356 .9280793 427924.7 192 187315.01348945202 2 -.06023172 1.307879 1018852.6 .5622402 .9331623 427924.7 193 172726.821612599 2 -.07613714 1.4490894 1018852.6 .56953835 1.0385497 427924.7 194 89516.00559491309 2 -.6353577 2.658772 1018852.6 .5234427 1.055613 427924.7 195 46227.9383715856 2 -.6234017 4.971857 1018852.6 .6987443 2.0393057 427924.7 196 39596.0936369996 2 -.1449161 4.804929 1018852.6 .993713 2.686053 427924.7 197 60737.0322621344 2 .3658099 3.254868 1018852.6 .8338882 1.820923 427924.7 198 71838.9064670057 2 .15046953 2.4670064 1018852.6 .6827873 1.375261 427924.7 199 224107.193924809 2 -.51856714 1.4453292 1018852.6 .6377322 1.2437276 427924.7 200 225541.846844576 2 -.032640215 1.4488925 1018852.6 .6090338 1.0971746 427924.7 201 231109.67303105703 2 -.0034563364 1.4099534 1018852.6 .6410594 1.1495655 427924.7 202 245325.401770264 2 .05861522 1.4064418 1018852.6 .5664189 1.0481322 427924.7 203 341394.97050577105 2 .019814253 1.2132915 1018852.6 .5870725 1.007211 427924.7 204 358914.787711336 2 -.0361932 1.2956892 1018852.6 .5657899 .9600379 427924.7 205 395498.926086955 2 .069699235 1.2173187 1018852.6 .6099839 .9964955 427924.7 206 335400.101452198 2 -.15492634 1.3860345 1018852.6 .7067347 1.2440052 427924.7 207 323567.148923976 2 -.05174617 1.446986 1018852.6 .6753996 1.1112103 427924.7 208 397178.866975748 2 .1863093 1.2541463 1018852.6 .56365186 1.0353396 427924.7 209 422442.420095279 2 .035208814 1.1669307 1018852.6 .56275475 .9905596 427924.7 210 515295.35717779404 2 .1538234 1.0239992 1018852.6 .5941754 .9438451 427924.7 211 548877.556869632 2 .01918229 .98487 1018852.6 .5950864 .9441252 427924.7 end format %tq Date
HTML Code:
https://www.dropbox.com/s/in64nkpsr381zae/Statalist%20export.dta?dl=0
