Stock price daily to weekly

[Travis Chang]

Dear All

I would like to calculate weekly stock prices from the daily prices data. But I don't know how to average weekly stock price.

The format I require is below:
week / stock_price
"2022-W30" xxx
"2022-W29" yyy
"2022-W28" zzz
.
.
.
"2022-W02" aaa
"2022-W01" bbb
"2021-W52" ccc
"2021-W51" ddd

clear all
set more off

import excel "/Users/travischang/Desktop/ifo380.xlsx", sheet("sheet1") firstrow

drop if price ==.
gen date1 = date(Date,"YMD")

format date %td
order date1, after(Date)


gen week_start = date - dow(date) + 1
format week_start %td

gen wwy = ceil((week_start-mdy(1, 1, yofd(week_start)))/7)

gen YEAR = yofd(date1)

egen week = concat(YEAR wwy), punct("-")

Thank you very much in advance,

Travis

[Nick Cox]

Please read the FAQ Advice on not attaching spreadsheets and on using dataex to show examples. https://www.statalist.org/forums/help#stata

[Joro Kolev]

It depends on how you want to handle week. Weeks do not nest nicely into years.

Stata has the concept of a week, but it is quite awkward, like there are weeks in the end of the year that are one-three days.

I would suggest that you compute the returns from Monday to Friday, and you call these "weekly returns".

[Travis Chang]

Please read the FAQ Advice on not attaching spreadsheets and on using dataex to show examples. https://www.statalist.org/forums/help#stata

Hi Nick, thank you for your reply. Is it correct if I ask this way?

clear
input int date float close
15005 1373.73
15006 1366.01
15007 1373.47
15008 1349.47
15011 1354.31
15012 1352.26
15035 1241.23
15036 1234.18
15067 1145.87
15068 1106.46
15069 1103.25
15110 1249.44
15111 1284.99
15112 1288.49
15338 1178.02
15339 1181.66
15340 1161.97
15341 1165.31
15342 1162.09
15343 1157.26
15432 1119.38
15433 1123.09
15434 1134.36
end
format %td date

gen week_start = date - dow(date) + 1
format week_start %td

gen wwy = ceil((week_start-mdy(1, 1, yofd(week_start)))/7)

gen YEAR = yofd(date)

egen week = concat(YEAR wwy), punct("-")

[Nick Cox]

Thanks for your data example and code. You appear to know the tricks you need, and so a dataset of mean weekly prices would start with

egen mean = mean(close), by(week_start)

or

collapse close , by(week_start)