Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# Re: st: Sorting data in deciles and then regressing and storing coefficients. (Looping)

 From "C. Evans" To statalist@hsphsun2.harvard.edu Subject Re: st: Sorting data in deciles and then regressing and storing coefficients. (Looping) Date 10 Jul 2013 13:59:01 +0100

```Dear Satalist

```
Yes I think you understand it correctly. The problems you have identified are the problems I was having conceptually. Yet stata command wise I was even struggling with creating these portfolios (remembering I am trying to create new portfolios every year dependent on the variable size). The code I tried to use was:
```
bysort year size: egen portfolio = xtile(size), nq(10)

```
this brings up the error value "too many variables" Is there a way around this? I was thinking of -sample()- do you know if this is the only option I have at this point to create the portfolios?
```
```
Chris
```
On Jul 9 2013, Jeph Herrin wrote:

```
```So if I understand this model

R = constant + beta*RM

```
it is estimated using all the stocks in the portfolio? in which case it doesn't make sense that R is constant for that portfolio, as you suggest. On the other hand, if the model is estimated using the 10 portfolios as observations, it's not clear how RM is constructed for each portfolio.
```
J

On 7/9/2013 6:35 AM, C. Evans wrote:
```
```Dear Statalist

```
Ultimately I would like to do both. Try it with one regression per portfolio per year and then different regressions each day of the year and compare results. To understand the concept easier I think trying the first method might be a good idea, as such I have collapsed my dataset into years as suggested.
```
+---------------------------------------------------+
| Id       year      return     size             RM |
|---------------------------------------------------|
1. |  10006   1964    .0010771   219303.1    .00074666 |
2. |  10006   1965    .0007731   249306.1     .0012575 |
3. |  10006   1966    -.000666   272942.9   -.00020371 |
4. |  10006   1967    .0007695   277828.9     .0022902 |
5. |  10006   1968    .0018014   291790.6    .00155318 |
|---------------------------------------------------|
6. |  10006   1969   -.0008518   289380.5   -.00113132 |
7. |  10006   1970   -.0001759   244459.8   -.00029807 |
8. |  10006   1971    .0005789   297727.2    .00081096 |
9. |  10006   1972   -.0002126   267790.8    .00027013 |
10. |  10006   1973    .0009468   256238.1    -.0016938 |
|---------------------------------------------------|
11. |  10006   1974   -.0019488   240341.8    -.0010482 |
12. |  10006   1975    .0007506   230729.7    .00203316 |
13. |  10006   1976    .0013387   283286.3    .00160779 |
14. |  10006   1977    4.13e-06   304096.8    .00082065 |
15. |  10006   1978   -.0003554   288889.5    .00089401 |
|---------------------------------------------------|
16. |  10006   1979    .0005206   297761.1    .00133951 |
17. |  10006   1980    .0013472   337569.1     .0013785 |
18. |  10006   1981   -.0003744   396982.8   -.00006056 |
19. |  10006   1982   -.0007193   292631.7    .00083913 |
20. |  10006   1983    .0018631   318310.7    .00121923 |
|---------------------------------------------------|
21. |  10014   1967    .0049118    20656.6     .0022902 |

J you are correct I need to create R as to then run the regression:

R= contant + BETA.RM + error

```
Then at the end of each year I rank the stocks by -Size- and divide them into 10 equal portfolios. Then I calculate the portfolio return R which is the -mean- of -return- for that porfolio. Finally estimating the regression and saving the BETA.
```
Chris

On Jul 8 2013, Jeph Herrin wrote:

```
```It's not really clear what you are doing here.

```
Do you want one regression per portfolio, per year? IN that case, the first step would be -collapse- or otherwise restructure your dataset to have one record per stock for each year-portfolio; it's not critical to do this, but it will make things much easier.
```
Or do you intend a different regression for each day of the year?

```
Is the variable -reurn- (sic) the same as -R-, the portfolio return, or do you need to create that as well?
```
J

On 7/8/2013 11:14 AM, C. Evans wrote:
```
```Dear Satalist
I'm using Stata/SE 12.1

```
I am trying to create a new variable called BETA. It follows the computation of many financial papers. Amihud 2002 gives a good description of what I am trying to do.
```
```
Extract paraphrased from Amihud (2002)accessed here http://www.sciencedirect.com/science/article/pii/S1386418101000246 :
```
```
At the end of each year Y, stocks are ranked by their size and divided into ten equal portfolios. Next, the porfolio return R is calculated as the equally weighted average of stock returns in portfolio P on day T in year Y. Then, the market model is estimated for each portfolio P, P=1,2,....10
```
R= constant + BETA.RM + error  <- this is the Market Model (regression)

```
RM is the equally weighted market return and BETA is the slope coefficient. I will then save the BETA values and store them as a new variable.
```
```
Below is a sample of my data, the ID goes from 10006 to 93201 but with gaps inbetween. I am using daily stock data and therefore there are missing date values (such as weekends). The date goes from Jan 1964 to Dec 1997 (unless a stock has dropped out).
```
ID date day month year reurn size RM |
```
| | -----------------------------------------------------------------------|
```|  10006 02jan1964 2 1 1964 -.0120968 180565 0.01|
|  10006 03jan1964 3 1 1964 .0081633 182039 0.04|
|  10006 06jan1964 6 1 1964 .0121457 184250 0.02|
|  10006 07jan1964 7 1 1964 .022 188303.5 -0.1|
```
| 10006 08jan1964 8 1 1964 .0136986 190883 -0.3| | -----------------------------------------------------------------------|
```|  10006 09jan1964 9 1 1964 .003861 191620 0.7|
|  10006 10jan1964 10 1 1964 -.0192308 187935 0.8|
|  10006 13jan1964 13 1 1964 .0039216 188672 1|
|  10006 14jan1964 14 1 1964 .0136719 191251.5 0.1|
```
| 10006 15jan1964 15 1 1964 .0250482 196042 -0.02| | -----------------------------------------------------------------------|
```|  10006 16jan1964 16 1 1964 -.0150376 193094 -0.6|
|  10006 17jan1964 17 1 1964 -.0038168 192357 0.1|
|  10006 20jan1964 20 1 1964 -.0114943 190146 0 .4|
|  10006 21jan1964 21 1 1964 -.003876 189409 0.6|
```
| 10006 22jan1964 22 1 1964 .0233463 193831 1.2| | -----------------------------------------------------------------------|
```
```
In the same dataset I have also created a date variable called date2 and a variable labelled lastdate which is the last date in that year. I was thinking this may help in solving my problem.
```
+---------------------------------------+
| permno        date   date2   lastdate |
|---------------------------------------|
1. |  10006   02jan1964    1462       1826 |
2. |  10006   03jan1964    1463       1826 |
3. |  10006   06jan1964    1466       1826 |
4. |  10006   07jan1964    1467       1826 |
5. |  10006   08jan1964    1468       1826 |
|---------------------------------------|
6. |  10006   09jan1964    1469       1826 |
7. |  10006   10jan1964    1470       1826 |
8. |  10006   13jan1964    1473       1826 |
9. |  10006   14jan1964    1474       1826 |
10. |  10006   15jan1964    1475       1826 |

```
I have been reading about -xtile- and about looping. I have managed to sort by -size- and create a variable labelled -portfolio- that uses the -xtile- function. I have never looped before but have read that -foreach- or -forval- might be useful.
```

Chris

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

```
```*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

```
```*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

```
```*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

```
```*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/
```