Generate Variable "quarterly_rebalancing" if "date" < "rebalancing"

#1
Hi all,

I have about 8 years daily stock returns that I would like to transform to cumulative returns over a quarters.

I would like to create a variable "rebalancing" that is equal to the portfolio rebalancing date if the variable "date" <= to the next rebalancing date. Rebalancing happens 4 times a year from 2006 to 2014 at the following dates:
- February 15
- May 16th
- August 15th
- Nov 15th

Data look like that now:
date ret
15aug2006 .026666641
16aug2006 .043290105
17aug2006 -.020746967
18aug2006 -.008474569
21aug2006 -.002136699
22aug2006 -.008565302
23aug2006 .03671708
24aug2006 .024999974

I would like to get the following:
date rebalancing ret
9-Nov-10 15-Nov-10 -0.007201
10-Nov-10 15-Nov-10 -0.006551
11-Nov-10 15-Nov-10 -0.006594
12-Nov-10 15-Nov-10 -0.009246
15-Nov-10 15-Nov-10 -0.008615
16-Nov-10 15-Feb-11 -0.005672
17-Nov-10 15-Feb-11 0.003763
18-Nov-10 15-Feb-11 0.02104
19-Nov-10 15-Feb-11 0.008171

Thanks a lot for your help in advance!
 
#2
Here is one way working rowwise:

Code:
clear all
set more off

*----- example data -----

set obs 10

set seed 18306

gen dat = floor(runiform()*1000)
gen ret = runiform()
replace dat = 362 in 1

format dat %td

sort dat
list

*----- what you want -----

* rebalancing dates
gen reb1 = mdy(2,15,year(dat))
gen reb2 = mdy(5,16,year(dat))
gen reb3 = mdy(8,15,year(dat))
gen reb4 = mdy(11,15,year(dat))
format reb* %td

forvalues i = 1/4 {
    * compute differences in dates
    gen dreb`i' = reb`i' - dat
    * assign missing to differences corresponding to dates that have already passed
    replace dreb`i' = . if dreb`i' < 0
}

* compute the minimum difference
egen mindif = rowmin(dreb*)

forvalues i = 1/4 {
    * "delete" rebalancing dates that do not correspond to mindif
    replace reb`i' = . if dreb`i' != mindif
}

* compute variable with final date and adjust for "passing" dates
egen rebal = rowmin(reb*)
replace rebal = mdy(month(reb1),day(reb1),year(reb1)+1) if missing(mindif)
format rebal %td

*list
drop reb1-reb4 dreb1-dreb4 mindif
list
See -help datetime- ad -help forvalues-.
An alternative is to -reshape- at the beginning and work with data in long form.