Dear Statalist Forum,
I am trying to work with a financial dataset, which tracks the performance of >10k stocks from 1986-2000. A stock is identified with the variable permno. The vertical structure of the data set is the following: It tracks permno A from 1986-2000 and then continues with the data of permco B from 1986-2000 and so on. Here is a -dataex- table. (each variable which appears in my data set is written in BOLD)
**The dataex table is too short to show two full month. I have uploaded a stata-file with 1657 observations (January + February) in the following google drive: https://drive.google.com/file/d/1cQC...ew?usp=sharing
General Goal: Generate a value weighted series of returns for quintile portfolios
First regress each stocks excess_ret (excess returns) on VXO_change (change in VXO - volatility index) while controlling for the mktrf (market excess return) in month t. Sort stocks into quintile portfolios, based on their realized b_VXO_change in month t.
The regression was run with rangestat():
..and the quintile sorting was done by:
Now to my question:
Now I have sorted each permno into a quintile portfolio in each month. The final goal is to create a value-weighted daily return series for each quintile portfolio over the whole sample.
In the following table I try to provide a scheme of the loop(?).
The first month of the sample, January, has the sole purpose to sort stocks into quintile portfolios. Every following month has two purposes. Take February as an example. First, it allows for calculating the daily vw-returns of each quintile portfolio, formed in the previous month (January). Second, in February new quintile portfolios are formed, which daily-vw returns will be calculated over the next month (march).
As mentioned before the quintile portfolios formation process in each month has been done. Now I would like to calculate the daily value-weighted return for each quintile portfolio over the next month t+1. So each stock's (permno) daily return (ret) over the next month has to be value weighted.
Value weighted means: (market_cap of permno A, day1 / daily_mcap_sum_quint portfolio X, day 1) * (return permno A, day 1)
I am not sure which would be a good strategy to follow. I thought about creating 5 seperate Stata sheets for each portfolio, for easily linking the vw-daily returns for each quintile. The problem is that then I can't calculate the next months returns properly, because in the next month, the stock of month t may be assigned to a different quintile and thus it's returns wouldn't be listed in the seperate file.
I hope I was clear in explaining. Thanks!
Best regards,
Felix
I am trying to work with a financial dataset, which tracks the performance of >10k stocks from 1986-2000. A stock is identified with the variable permno. The vertical structure of the data set is the following: It tracks permno A from 1986-2000 and then continues with the data of permco B from 1986-2000 and so on. Here is a -dataex- table. (each variable which appears in my data set is written in BOLD)
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input double permno long date double(mktrf VXO_change) float(monthly market_cap excess_ret) double b_VXO_change float quintiles_VXO_change double daily_mcap_sum_quint 10015 9498 -.0063 . 312 39880 -.01259568 .0012782511065976877 2 1649245.875 10031 9498 -.0063 . 312 12576.563 -.00025 . . 3506078.53125 10057 9498 -.0063 . 312 84766.5 .07975 .0031428510413872954 3 1073218.75 10066 9498 -.0063 . 312 2496.469 -.00025 . . 3506078.53125 10074 9498 -.0063 . 312 11169 -.01948077 . . 3506078.53125 10082 9498 -.0063 . 312 6675.75 .1023141 .0252103275109307 5 13999.5 10103 9498 -.0063 . 312 4556.25 -.00025 . . 3506078.53125 10137 9498 -.0063 . 312 1651881 -.004023585 -.0019313611866725382 1 9925597.5 10145 9498 -.0063 . 312 8212219 .002423797 -.0016342289680333411 1 9925597.5 10146 9498 -.0063 . 312 19860 -.04025 . . 3506078.53125 10153 9498 -.0063 . 312 61497.5 .03005303 -.004800142404493367 1 9925597.5 10154 9498 -.0063 . 312 63607.75 .033648305 .003048355024045976 3 1073218.75 10161 9498 -.0063 . 312 924844.5 -.00942431 .001853317824614699 3 1073218.75 10189 9498 -.0063 . 312 13254 -.00025 . . 3506078.53125 10218 9498 -.0063 . 312 7323.75 .03821154 .00736943650525498 5 13999.5 10225 9498 -.0063 . 312 3599290.5 -.0059426 .003919062450566495 4 3599290.5 10233 9498 -.0063 . 312 3442166 .0018161156 . . 3506078.53125 10241 9498 -.0063 . 312 1609366 .0018333334 .0001344947699049566 2 1649245.875 10015 9499 .005600000000000001 -.10999999999999943 312 39381.5 -.01275 .0012782511065976877 2 1668822.625 10031 9499 .005600000000000001 -.10999999999999943 312 12744.25 .013083334 . . 3514170.96875 10057 9499 .005600000000000001 -.10999999999999943 312 84766.5 -.00025 .0031428510413872954 3 1070090.5 10066 9499 .005600000000000001 -.10999999999999943 312 2496.469 -.00025 . . 3514170.96875 10074 9499 .005600000000000001 -.10999999999999943 312 11169 -.00025 . . 3514170.96875 10082 9499 .005600000000000001 -.10999999999999943 312 7296.75 .09277326 .0252103275109307 5 15163 10103 9499 .005600000000000001 -.10999999999999943 312 4556.25 -.00025 . . 3514170.96875 10137 9499 .005600000000000001 -.10999999999999943 312 1664395.3 .007325758 -.0019313611866725382 1 9939920.5 10145 9499 .005600000000000001 -.10999999999999943 312 8212219 -.00025 -.0016342289680333411 1 9939920.5 10146 9499 .005600000000000001 -.10999999999999943 312 20687.5 .04141667 . . 3514170.96875 10153 9499 .005600000000000001 -.10999999999999943 312 63306.25 .029161764 -.004800142404493367 1 9939920.5 10154 9499 .005600000000000001 -.10999999999999943 312 60479.5 -.04943033 .003048355024045976 3 1070090.5 10161 9499 .005600000000000001 -.10999999999999943 312 924844.5 -.00025 .001853317824614699 3 1070090.5 10189 9499 .005600000000000001 -.10999999999999943 312 13254 -.00025 . . 3514170.96875 10218 9499 .005600000000000001 -.10999999999999943 312 7866.25 .07382408 .00736943650525498 5 15163 10225 9499 .005600000000000001 -.10999999999999943 312 3606159.5 .001658397 .003919062450566495 4 3606159.5 10233 9499 .005600000000000001 -.10999999999999943 312 3449263.5 .0018118557 . . 3514170.96875 10241 9499 .005600000000000001 -.10999999999999943 312 1629441 .012224013 .0001344947699049566 2 1668822.625 10015 9502 -.0004 -.9100000000000001 312 39381.5 -.00025 .0012782511065976877 2 1672168.5 10031 9502 -.0004 -.9100000000000001 312 12744.25 -.00025 . . 65540.21875 10057 9502 -.0004 -.9100000000000001 312 88690.88 .04604629 .0031428510413872954 3 1065451.5 10066 9502 -.0004 -.9100000000000001 312 2496.469 -.00025 . . 65540.21875 10074 9502 -.0004 -.9100000000000001 312 11388 .019357843 . . 65540.21875 10082 9502 -.0004 -.9100000000000001 312 6831 -.064079784 .0252103275109307 5 14426 10103 9502 -.0004 -.9100000000000001 312 4556.25 -.00025 . . 65540.21875 10137 9502 -.0004 -.9100000000000001 312 1676909.5 .007268797 -.0019313611866725382 1 9976142.5 10145 9502 -.0004 -.9100000000000001 312 8234118 .0024166666 -.0016342289680333411 1 9976142.5 10146 9502 -.0004 -.9100000000000001 312 21101.25 .01975 . . 65540.21875 10153 9502 -.0004 -.9100000000000001 312 65115 .02832143 -.004800142404493367 1 9976142.5 10154 9502 -.0004 -.9100000000000001 312 60479.5 -.00025 .003048355024045976 3 1065451.5 10161 9502 -.0004 -.9100000000000001 312 916281.1 -.00950926 .001853317824614699 3 1065451.5 10189 9502 -.0004 -.9100000000000001 312 13254 -.00025 . . 65540.21875 10218 9502 -.0004 -.9100000000000001 312 7595 -.03473276 .00736943650525498 5 14426 10225 9502 -.0004 -.9100000000000001 312 3564946 -.01167857 .003919062450566495 4 3564946 10233 9502 -.0004 -.9100000000000001 312 . . . . 65540.21875 10241 9502 -.0004 -.9100000000000001 312 1632787 .01371304 .0001344947699049566 2 1672168.5 10000 9503 .0138 .33999999999999986 312 9430 . .005234203120508643 4 3622458.25 10015 9503 .0138 .33999999999999986 312 39381.5 -.00025 .0012782511065976877 2 1739086 10031 9503 .0138 .33999999999999986 312 12744.25 -.00025 . . 65701.28125 10057 9503 .0138 .33999999999999986 312 91045.5 .02629867 .0031428510413872954 3 1127749.75 10066 9503 .0138 .33999999999999986 312 2657.531 .06426613 . . 65701.28125 10074 9503 .0138 .33999999999999986 312 11388 -.00025 . . 65701.28125 10082 9503 .0138 .33999999999999986 312 6831 -.00025 .0252103275109307 5 14697.25 10103 9503 .0138 .33999999999999986 312 4556.25 -.00025 . . 65701.28125 10137 9503 .0138 .33999999999999986 312 1708195 .018406715 -.0019313611866725382 1 10032944.625 10145 9503 .0138 .33999999999999986 312 8256017 .002409574 -.0016342289680333411 1 10032944.625 10146 9503 .0138 .33999999999999986 312 21101.25 -.00025 . . 65701.28125 10153 9503 .0138 .33999999999999986 312 68732.5 .05530556 -.004800142404493367 1 10032944.625 10154 9503 .0138 .33999999999999986 312 60479.5 -.00025 .003048355024045976 3 1127749.75 10161 9503 .0138 .33999999999999986 312 976224.8 .065170564 .001853317824614699 3 1127749.75 10189 9503 .0138 .33999999999999986 312 13254 -.00025 . . 65701.28125 10218 9503 .0138 .33999999999999986 312 7866.25 .035464287 .00736943650525498 5 14697.25 10225 9503 .0138 .33999999999999986 312 3613028 .013237476 .003919062450566495 4 3622458.25 10241 9503 .0138 .33999999999999986 312 1699704.5 .04073361 .0001344947699049566 2 1739086 10000 9504 -.0216 2.5799999999999983 312 9200 -.024640244 .005234203120508643 4 3608490.5 10015 9504 -.0216 2.5799999999999983 312 39381.5 -.00025 .0012782511065976877 2 1712319 10031 9504 -.0216 2.5799999999999983 312 12744.25 -.00025 . . 64666.90625 10057 9504 -.0216 2.5799999999999983 312 87906 -.03473276 .0031428510413872954 3 1090356.75 10066 9504 -.0216 2.5799999999999983 312 2657.531 -.00025 . . 64666.90625 10074 9504 -.0216 2.5799999999999983 312 11388 -.00025 . . 64666.90625 10082 9504 -.0216 2.5799999999999983 312 6831 -.00025 .0252103275109307 5 14697.25 10103 9504 -.0216 2.5799999999999983 312 4556.25 -.00025 . . 64666.90625 10137 9504 -.0216 2.5799999999999983 312 1670652.4 -.022228023 -.0019313611866725382 1 9880479.625 10145 9504 -.0216 2.5799999999999983 312 8146521 -.0135126 -.0016342289680333411 1 9880479.625 10146 9504 -.0216 2.5799999999999983 312 20066.875 -.0429951 . . 64666.90625 10153 9504 -.0216 2.5799999999999983 312 63306.25 -.07919736 -.004800142404493367 1 9880479.625 10154 9504 -.0216 2.5799999999999983 312 60479.5 -.00025 .003048355024045976 3 1090356.75 10161 9504 -.0216 2.5799999999999983 312 941971.3 -.03533772 .001853317824614699 3 1090356.75 10189 9504 -.0216 2.5799999999999983 312 13254 -.00025 . . 64666.90625 10218 9504 -.0216 2.5799999999999983 312 7866.25 -.00025 .00736943650525498 5 14697.25 10225 9504 -.0216 2.5799999999999983 312 3599290.5 -.0040522814 .003919062450566495 4 3608490.5 10241 9504 -.0216 2.5799999999999983 312 1672937.5 -.015998032 .0001344947699049566 2 1712319 10000 9505 -.011699999999999999 .23000000000000043 312 9200 -.00025 .005234203120508643 4 3512326.25 10001 9505 -.011699999999999999 .23000000000000043 312 5663.75 . . . 69975.28125 10015 9505 -.011699999999999999 .23000000000000043 312 40378.5 .025066456 .0012782511065976877 2 1693240.75 10031 9505 -.011699999999999999 .23000000000000043 312 12744.25 -.00025 . . 69975.28125 10057 9505 -.011699999999999999 .23000000000000043 312 87906 -.00025 .0031428510413872954 3 1122524.75 10066 9505 -.011699999999999999 .23000000000000043 312 2657.531 -.00025 . . 69975.28125 10074 9505 -.011699999999999999 .23000000000000043 312 11169 -.01948077 . . 69975.28125 10082 9505 -.011699999999999999 .23000000000000043 312 6831 -.00025 .0252103275109307 5 14697.25 10103 9505 -.011699999999999999 .23000000000000043 312 4556.25 -.00025 . . 69975.28125 10137 9505 -.011699999999999999 .23000000000000043 312 1626852.5 -.02646723 -.0019313611866725382 1 9811163.25 end format %d date format %tm monthly
General Goal: Generate a value weighted series of returns for quintile portfolios
First regress each stocks excess_ret (excess returns) on VXO_change (change in VXO - volatility index) while controlling for the mktrf (market excess return) in month t. Sort stocks into quintile portfolios, based on their realized b_VXO_change in month t.
The regression was run with rangestat():
Code:
rangestat (reg) excess_r mktrf VXO_change if include, by(permno) interval(monthly 0 0)
Code:
ssc install egenmore egen quintiles_VXO_change = xtile(b_VXO_change), by(monthly) nq(5)
Now I have sorted each permno into a quintile portfolio in each month. The final goal is to create a value-weighted daily return series for each quintile portfolio over the whole sample.
In the following table I try to provide a scheme of the loop(?).
| January | February | March | April |
| = month t | = month t +1 | ||
| -> form quintiles in January | calculate vw-daily returns of month t (January) quint. portf. | ||
| = month t | = month t +1 | ||
| -> form quintile portfolios in February | calculate vw-daily returns of month t (February) quint. portf | ||
| = month t | = month t+1 | ||
| -> form quintile portfolios in March | ... |
As mentioned before the quintile portfolios formation process in each month has been done. Now I would like to calculate the daily value-weighted return for each quintile portfolio over the next month t+1. So each stock's (permno) daily return (ret) over the next month has to be value weighted.
Value weighted means: (market_cap of permno A, day1 / daily_mcap_sum_quint portfolio X, day 1) * (return permno A, day 1)
I am not sure which would be a good strategy to follow. I thought about creating 5 seperate Stata sheets for each portfolio, for easily linking the vw-daily returns for each quintile. The problem is that then I can't calculate the next months returns properly, because in the next month, the stock of month t may be assigned to a different quintile and thus it's returns wouldn't be listed in the seperate file.
I hope I was clear in explaining. Thanks!
Best regards,
Felix
Comment