Hi everybody,
I have a question about how to make the daily values in the month of a variable equal to the last observation for that month.
In particular, my general purpose is to create the daily time series for 10 beta-sorted portofolios starting from the stock returns time series.
My data is of the following form:
PERMNO (identifier) DATE RETURN BETA BETAENDMONTH BETADECILE
X1--------------------------- t1----- R11 -------Beta11-- .---------------------- .
X1--------------------------- t2----- R12------- Beta12 --Beta12------------- y
X2--------------------------- t1----- R21------- Beta21 --.---------------------- .
and so on.
I managed to get the betas that I need (in particular, I get one beta for every stock for every day). Now what I need is to sort the stocks according to their beta at the end of every month in order to create 10 portfolios based on the beta-deciles. Then, I will have to repeat the sorting at the end of every month, always using daily data. Hence, I'd like to have a variable that (for every month) is equal to the beta or a decile-rank of a certain stock at the end of that month so that later the sorting will be "easy" even with daily data.
For this purpose, I created a variable ("dummyendmonth) that has missing everywhere except for the last days of every month, which contain the desired beta (I also had to create the variable month from the date to do this). "Betadecile" contains the number of decile a particular beta belongs to in one specific day. Now:how do I fill in the missing values such that every month contain the same observation as the last one in that month (both for "betaendmont?" and "betadecile")?
I tried the following:
replace betadecile=betadecile[_n+1] if missing(betadecile)
but obviously this is done also one time so it does not fill in all the gaps. I thought repeating the command at least 22 time (the average number of trading days in one month is 22) would be enough or close. However, even if I try let's say 40 times there are still missing values. I actually created the betaendofmonth variable using a suggestion from a previous post:
(Given a certain date in elapsed format)
gen month = mofd(date)
format month %tm
by permno: month (date), sort: keep if _n == _N
gen dummyendofmonth=0
by permno month (date), sort: replace dummyendofmonth=1 if _n == _N
drop month
gen betaendofmonth=beta if dummyendofmonth==1
I hope to have been clear and that you can help me. Thank you in advance
I have a question about how to make the daily values in the month of a variable equal to the last observation for that month.
In particular, my general purpose is to create the daily time series for 10 beta-sorted portofolios starting from the stock returns time series.
My data is of the following form:
PERMNO (identifier) DATE RETURN BETA BETAENDMONTH BETADECILE
X1--------------------------- t1----- R11 -------Beta11-- .---------------------- .
X1--------------------------- t2----- R12------- Beta12 --Beta12------------- y
X2--------------------------- t1----- R21------- Beta21 --.---------------------- .
and so on.
I managed to get the betas that I need (in particular, I get one beta for every stock for every day). Now what I need is to sort the stocks according to their beta at the end of every month in order to create 10 portfolios based on the beta-deciles. Then, I will have to repeat the sorting at the end of every month, always using daily data. Hence, I'd like to have a variable that (for every month) is equal to the beta or a decile-rank of a certain stock at the end of that month so that later the sorting will be "easy" even with daily data.
For this purpose, I created a variable ("dummyendmonth) that has missing everywhere except for the last days of every month, which contain the desired beta (I also had to create the variable month from the date to do this). "Betadecile" contains the number of decile a particular beta belongs to in one specific day. Now:how do I fill in the missing values such that every month contain the same observation as the last one in that month (both for "betaendmont?" and "betadecile")?
I tried the following:
replace betadecile=betadecile[_n+1] if missing(betadecile)
but obviously this is done also one time so it does not fill in all the gaps. I thought repeating the command at least 22 time (the average number of trading days in one month is 22) would be enough or close. However, even if I try let's say 40 times there are still missing values. I actually created the betaendofmonth variable using a suggestion from a previous post:
(Given a certain date in elapsed format)
gen month = mofd(date)
format month %tm
by permno: month (date), sort: keep if _n == _N
gen dummyendofmonth=0
by permno month (date), sort: replace dummyendofmonth=1 if _n == _N
drop month
gen betaendofmonth=beta if dummyendofmonth==1
I hope to have been clear and that you can help me. Thank you in advance
Comment