stata function wofd() and friday-to-friday returns

[Nicolai Mueller]

hello,

currently I work with stata on my master thesis - unfortunately I struggle to get my panel data into the correct date formats in order to calculate weekly stock returns (friday to friday) from daily returns. Maybe you can help me to get this correct. After sorting out my raw data problems I want to set the xtset command. My current data looks like this:

id	date	ret	day	price	dw	week_d	fret
1	30sep1986	x1	1	a1	1986w39	2	.
1	01oct1986	x2	2	a2	1986w40	3	.
1	02oct1986	x3	3	a3	1986w40	4	.
1	03oct1986	x4	4	a4	1986w40	5	1+x4
1	06oct1986	x5	5	a5	1986w40	1	.
1	07oct1986	x6	6	a6	1986w40	2	.
1	08oct1986	x7	7	a7	1986w41	3	.
1	09oct1986	x8	8	a8	1986w41	4	.
1	10oct1986	x9	9	a9	1986w41	5	1+x9
2	30sep1986	y1	1	b1	1986w39	2	.
2	01oct1986	y2	2	b2	1986w40	3	.
2	02oct1986	y3	3	b3	1986w40	4	.
2	03oct1986	y4	4	b4	1986w40	5	1+y4
2	06oct1986	y5	5	b5	1986w40	1	.
2	07oct1986	y6	6	b6	1986w40	2	.
2	08oct1986	y7	7	b7	1986w41	3	.
2	09oct1986	y8	8	b8	1986w41	4	.
2	10oct1986	y9	9	b9	1986w41	5	1+y9

What I want to do with this data is calculating returns per id from friday-to-friday and generate a date variable that gives me the right calender week per day. I tried to use the daily date variable (long, %d) to generate a weekly variable which gives me the right week for a day:

gen dw = wofd(date)

format dw %tw

By checking the dates I realized that the 30sep1986 and 01oct1986 are Tuesday and Wednesday of the same week which ends (in trading terms) with Friday 03oct1986 - thus all three dates should have the same calender week, the 40th. Do you know why this conversion does not work?

If I manage to get the calender week variable right, I want to calculate weekly returns from friday to friday, e.g. the return for the 41th week for id 1 is calculated by (1+x4)*(1+x5)*(1+x6)*(1+x7)*(1+x8). I generated two variables, one that counts the days per week and one that gives me the 5th return of one week (so if no public holidays occur, this should be friday every week):

gen week_d=dow(date)

gen fret=(1+ret) if week_d==5

I want to use the value of fret as starting point for my implementation of the above return formula, do you know how to do this?
Your help will be very much appreciated!

Best Nicolai

[Nick Cox]

Stata weeks have nothing whatsoever to do with day of the week.

Confusing when first met, but true. And an inevitable consequence of what is documented.

In Stata week 1 always starts on 1 January, etc., and week 52 always has 8 or 9 days. So, depending on the year, week 1 and indeed any week could start on any day of the week.

For what to do instead, see

. search week, sj

Search of official help files, FAQs, Examples, SJs, and STBs

SJ-12-4 dm0065_1  . . . . . Stata tip 111: More on working with weeks, erratum
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q4/12   SJ 12(4):765                                     (no commands)
        lists previously omitted key reference

SJ-12-3 dm0065  . . . . . . . . . .  Stata tip 111: More on working with weeks
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q3/12   SJ 12(3):565--569                                (no commands)
        discusses how to convert data presented in yearly and weekly
        form to daily dates and how to aggregate such data to months
        or longer intervals

SJ-10-4 dm0052  . . . . . . . . . . . . . . . . Stata tip 68: Week assumptions
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q4/10   SJ 10(4):682--685                                (no commands)
        tip on Stata's solution for weeks and on how to set up
        your own alternatives given different definitions of the
        week

Typing the command yourself will bring up clickable links to ,pdf papers freely readable. Start with the 2010 reference.

[Nicolai Mueller]

Hi Nick,

thank you for your response. Finally I got what I wanted but it is not an elegant way or one how it should be done.
I receive friday to friday returns (retw) by:

generate retw = cond(week_d==5,(1+ret[_n-4])*(1+ret[_n-3])*(1+ret[_n-2])*(1+ret[_n-1])*(1+ret[_n]),(1+ret[_n-3])*(1+ret[_n-2])*(1+ret[_n-1])*(1+ret[_n]))

replace retw = . if week_d ==1
replace retw = . if week_d ==2
replace retw = . if week_d ==3
replace retw = . if retw[_n+1] != .

This works, as there are only weeks with at least 4 trading days. Next I get the last closing price (fprice) for the trading week, collapse all by id dw and set it as panel data:

gen fprice=price if week_i != .

preserve
collapse fret retw fprice, by(id dw)
sort id dw
xtset id dw

I am happy that it works, but what would be the elegant way to do this?

Best Nicolai

[Clyde Schechter]

I think this does what you want more "elegantly."

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float(date ret)
1 9769   .003488717
1 9770   .002668857
1 9771   .001366463
1 9772 .00028556868
1 9775   .008689333
1 9776   .003508549
1 9777   .000711051
1 9778  .0032336796
1 9779   .005551032
2 9769    .00875991
2 9770  .0020470947
2 9771  .0089275865
2 9772   .005844658
2 9775  .0036977914
2 9776   .008506309
2 9777   .003913819
2 9778  .0011966132
2 9779   .007542434
end
format %td date

//    GET THE DATE OF THE FRIDAY CLOSE OF THE WEEK
gen int day_of_week = dow(date)
gen int friday_close = date + 5 - day_of_week if day_of_week <= 5
replace friday_close = date + 12 - day_of_week if day_of_week > 5
format friday_close %td

//    FRIDAY CLOSE NOW SERVES AS A PROXY FOR THE WEEK OF THE YEAR
//    CALCULATE WEEKLY RETURNS FROM DAILY RETURNS
gen increase = 1 + ret
//        RUNNING PRODUCT OF INCREASE
by id friday_close (date), sort: gen weekly_return = 1 if _n == 1
by id friday_close (date): replace weekly_return = weekly_return[_n-1]*increase if _n > 1
//        WEEKLY RETURN IS THE RUNNING PRODUCT FOR CLOSING FRIDAY - 1.
by id friday_close (date): replace weekly_return = weekly_return[_N] - 1

The first part of the code is a -dataex- generated example data set. It uses your id's and dates, but as you provided no return information, I just filled those with some small random numbers.

The second part finds the date of the closing Friday of each week. The third line of that section handles Saturdays and Sundays, attributing them to the following Friday. I suspect that detail is irrelevant and that there is no weekend data anyway, but I wanted to be complete, just in case.

Now the friday_close variable is an identifier for the week (in the sense that you want it, not in the sense of Stata weeks). So working -by id friday_close- we proceed to calculate a running product of 1 + ret. The value of the running product on the closing Friday itself is the weekly return + 1, so we subtract 1 and attribute that to every variable in the week.

I have nothing to say about last closing price, etc, but it seems you have no real problems with that part of things.

In the future, please use -dataex- to post example data, as I have done. Your example in #1 was quite burdensome to import into Stata to develop and test this code. In fact, importing your data took a great deal longer than developing and testing the code. By using -dataex- you will ensure that those who want to help you can create a completely faithful replica of your Stata example with a simple copy paste operation. To get the -dataex- command, run -ssc install dataex-. Then run -help dataex- to see the simple instructions for using it. All Forum members are asked to use -dataex- whenever they post example data.

[Nicolai Mueller]

Hi Clyde,

thank you for your valuable response! The code worked out perfectly and the part with the running product will be very usefull in other applications of my thesis. In the future I will make all examples with dataex, I did not know this command before.

Best Nicolai

[Robert Picard]

A simple way to group weekly observations together is to sort by date within id and start a new week when the day of the week is lower than the previous observation (in Stata's days of the week, Sunday is day 0). If you use a running sum of when this condition is true, you get a weekly identifier.

I'm pretty sure that this type of calculation is usually done by a sum of logs. You can easily implement this using rangestat (from SSC). The interval, as specified in the interval below, includes all observations in the group and is the same for each observation within a group of id week so the calculation will be done once and carried over for the other observations in the group.

Note that the results will be different from Clyde's because he uses a value of 1 for the first observation in the group.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float(date ret)
1 9769   .003488717
1 9770   .002668857
1 9771   .001366463
1 9772 .00028556868
1 9775   .008689333
1 9776   .003508549
1 9777   .000711051
1 9778  .0032336796
1 9779   .005551032
2 9769    .00875991
2 9770  .0020470947
2 9771  .0089275865
2 9772   .005844658
2 9775  .0036977914
2 9776   .008506309
2 9777   .003913819
2 9778  .0011966132
2 9779   .007542434
end
format %td date

* a new week starts when the day of week is less than the previous day
bysort id (date): gen week = sum( dow(date) < dow(date[_n-1]) )

* a running product is usually done as a sum of logs
gen double lret1 = log(ret+1)
rangestat (sum) lret1, interval(week 0 0) by(id week)
gen wret = exp(lret1_sum) - 1

[Clyde Schechter]

A simple way to group weekly observations together is to sort by date within id and start a new week when the day of the week is lower than the previous observation....

Well, this is true if you define a week as Sunday through the following Saturday. In this case, what was wanted was weeks ending on Fridays. If, as may well be the case here, there is no Saturday data, then it makes no difference. But if there is Saturday data, Robert Picard's approach will assign them to the same week as the immediately preceding Friday, whereas mine will assign them to the week ending the following Friday. I think that my approach is more true to the spirit of "close of the week on Friday." But, again, it won't make any difference unless there is Saturday data.

I certainly endorse the running sum of logarithms with -rangestat- as better than my running product approach.

[Robert Picard]

I did not put much thought about the week thing and I guess I was trying to keep it as simple as possible. On further thought, if there are gaps in the data, it's possible that my approach would miss a week change if the gap is large enough. Clyde, your solution is clearly better.

[Nicolai Mueller]

Hi,

thank you for suggesting another way Robert! As there is no Saturday/Sunday data, both ways of calculating the returns work.

Best Nicolai