Stock returns daily to weekly

[Yahya Ghazali]

Dear All

I need some help with the stock returns. I would like to calculate weekly stock returns from the daily returns data. However, I need the value only for those weeks in which the stock was traded and have data for. For instance, following is the daily returns data:

input long StockCode float(YEAR month day) double dailyreturn
1 2005 1 4 -.010622
1 2005 1 5 -.009202
1 2005 1 6 .009288
1 2005 1 7 -.001534
1 2005 1 10 .012289
1 2005 1 12 -.009105
1 2005 1 13 .006126
1 2005 1 14 -.010654
1 2005 1 17 -.038462
1 2005 1 18 -.0048
1 2005 1 19 -.009646
1 2005 1 20 -.024351
1 2005 1 21 .066556
1 2005 1 24 .00936
1 2005 1 25 -.02473
1 2005 1 26 -.001585
1 2005 1 27 -.025397
1 2005 1 28 .004886
1 2005 1 31 -.017828
1 2005 2 1 .00495
1 2005 2 2 .050903
1 2005 2 3 -.014063
1 2005 2 4 .045959
1 2005 2 16 -.001515
1 2005 2 17 -.004552
1 2005 2 18 .006098
1 2005 2 21 .016667
1 2005 2 22 .004471
1 2005 2 23 -.010386
1 2005 2 24 -.005997
1 2005 2 25 .001508
1 2005 2 28 -.024096
end


The format I require is below:

input byte StockCode str7 TradingWeek double return
1 "2005-09" .006061
1 "2005-08" 0
1 "2005-06" .069692
1 "2005-05" -.037441
1 "2005-04" -.013846
1 "2005-03" -.001536
1 "2005-02" -.01214
end


Would highly appreciate if I can get some help with the above command.

Regards

Yahya

[Clyde Schechter]

The challenge here is to get the "weeks" the way you want them. By Stata's usual reckoning, the week containing 4-7Jan2005 is the first week of 2005, because its weeks always begin on the first day of the year, regardless of what day of the week that is. But I suppose you are thinking of weeks as beginning with a Monday, so we have to go through some gymnastics to emulate your weeks. That said, in the results I show, I retain the year and the week within year as if you try to sort your date on "week", it sorts alphabetically, which is wrong chronologically.

The calculation of the compound return over the week is routine. My results come out slightly different than yours, perhaps due to different rounding/precision.

clear
input long StockCode float(YEAR month day) double dailyreturn
1 2005 1 4 -.010622
1 2005 1 5 -.009202
1 2005 1 6 .009288
1 2005 1 7 -.001534
1 2005 1 10 .012289
1 2005 1 12 -.009105
1 2005 1 13 .006126
1 2005 1 14 -.010654
1 2005 1 17 -.038462
1 2005 1 18 -.0048
1 2005 1 19 -.009646
1 2005 1 20 -.024351
1 2005 1 21 .066556
1 2005 1 24 .00936
1 2005 1 25 -.02473
1 2005 1 26 -.001585
1 2005 1 27 -.025397
1 2005 1 28 .004886
1 2005 1 31 -.017828
1 2005 2 1 .00495
1 2005 2 2 .050903
1 2005 2 3 -.014063
1 2005 2 4 .045959
1 2005 2 16 -.001515
1 2005 2 17 -.004552
1 2005 2 18 .006098
1 2005 2 21 .016667
1 2005 2 22 .004471
1 2005 2 23 -.010386
1 2005 2 24 -.005997
1 2005 2 25 .001508
1 2005 2 28 -.024096
end

//    CREATE STATA INTERNAL FORMAT DATE
gen date = mdy(month, day, YEAR)
format date %td

//    GET DATE OF THE WEEK'S (OR CONCURRENT) MONDAY
gen week_start = date - dow(date) + 1
format week_start %td

//    CALCULATE WEEK # WITHIN YEAR BASED ON # OF PRECEDING MONDAYS
gen wwy = 1+ceil((week_start-mdy(1, 1, yofd(week_start)))/7)

//    COMBINE YEAR AND wwy TO IDENTIFY "WEEK"    
egen week = concat(YEAR wwy), punct("-")

//    AGGREGATE RETURNS TO WEEK
by StockCode week (date), sort: gen double ratio = 1 + dailyreturn if _n == 1
by StockCode week (date): replace ratio = ratio[_n-1]*(1+dailyreturn) if _n > 1
collapse (last) return = ratio, by(StockCode week YEAR wwy)
replace return = return - 1
sort YEAR week

However, I need the value only for those weeks in which the stock was traded and have data for.

I'm not sure what you're trying to say with this sentence. To get values for weeks in which there was no trading and no data would require not a statistics consult but an oracle. Am I missing something?

[Yahya Ghazali]

Dear Clyde

Thank you for this. Highly appreciate it.

Also, I need to further investigate two more scenarios here.

Firstly, I would like a column which can provide me with the number of consecutive weeks trading (for each firm and each year).

Secondly, I also would like to have a column, lets say transaction age, which can tell me how old has been the last transaction.

Regards

Yahya

[Clyde Schechter]

These questions don't relate to the title of this thread. To assure that others who are interested in these new questions would be able to find them in a search, it would be better for you to re-post these questions in a new thread.

That said, I don't understand either of the two new questions. If a firm trades for 5 consecutive weeks, then there is a gap for 3 weeks, and then there are 7 consecutive weeks of trading after that, is the answer to the first question 5, or 7, or 12? If 5 or 7, is it based on order of occurrence or length of the consecutive period?

And what does transaction age mean? How does it relate to any of the variables in the data?

So when you start the new thread, please try to ask the questions more clearly.

[Attaullah Shah]

For converting daily returns to weekly, monthly, quarterly, or yearly frequency, you can also use a user written program ascol, which can be downloaded from ssc

ssc install ascol
help ascol

[Nick Cox]

Yahya: You started a thread on weeks but then failed to answer any of Clyde's questions.

I comment on weeks alone.

As I understand it Attaullah's ascol offers just one kind of reduction to weeks, namely to Stata weeks in which

1. Week 1 always starts on 1 January

2. Week 2 always starts on 8 January

and so on, except that

52. Week 52 always lasts 8 or 9 days depending on whether the year isn't or is a leap year.

Here "always" mean what it says. No exceptions.

I've not heard of any body (or anybody) outside StataCorp ever using that definition for data production or analysis, unless by accident they assumed that Stata's definition corresponded to some other definition they knew.

search week, sj

points to some lengthier discussions.