Recently, I am doing my dissertation that covers asset pricing theory. The empirical test of Fama 3 factors model is an important part of this dissertation. Please let me review the fama model.
Fama 3 factors model is $r-R_f=α+β_m(K_m−R_f)+\beta_s⋅SMB+\beta_v⋅HML+e$
where $R_f$ is risk free return, ($K_m−R_f$) is premium return and $K_m$ is market return, SMB is the "Small Minus Big" market capitalization risk factor. HML is is the "High Minus Low" value premium risk factor.
Please let mt detail my question. I have over 500 stocks and their monthly return ,monthly market value and monthly book-to-market ratio (call it b/m). The time interval is from 01.2010 to 12.2015, then because the data is monthly so I have 60 periods. Those data is in one excel document.
its methodology and what I want to do is:
- For all stocks at each period, compute the mean for market value.
- Divide them into two groups (say big and small size) by comparing each stock with mean. If stock's market value greater than mean, put them into big size group. If small than mean, put them into small size group. Thus, for each period we have two groups, big and small group in terms of market value.
- For each period, we compare all the stocks' b/m within each group, then furtherly divide them into 3 groups , named high b/m (top 30%) group, medium b/m (middle hierarchy from 30% to 70%) group and low b/m (bottom 30%) group. Namely, for each period, we finally divide 6 groups. They are S/L, S/M, S/H and B/L, B/M, B/H. For example, S/L group contains all stocks that are small market value and low b/m ratio simultaneously.
These 6 groups are just 6 portfolio. So for each period, we have 6 different portfolios.
So far this is my first stage. Then I have second stage:
- After we get 6 different portfolios at each period, we need to compute weighted average return for all stocks at each period.
- We need to compute the SMB and HML for each period.
Where S/L,S/M....B/L is the summation return of each group. Like S/L is the summation of each stock's return.
Finally, we got time series data, then can regress this series data, and figure out α and 3 $\beta$.
So, basically those are want I want to do.
Now I compute those manually although I use excel, this is really a huge amount of workload, so tired to do this. I am still in step 1 at first stage. Is anyone interesting in telling me how to achieve dividing stocks into portfolios by compute, and compute those data by compute?
People usually just download the data from French's website. But my goal is to check Fama model in Asian stock market, like Shanghai and Hong Kong. All answers welcome, really really crazy and tired to do this manually.
Anyway, thanks everyone in advanced.