How to convert weekly stock prices into monthly

[Sander Soet]

Dear all,
I am currently writing my thesis about PE-backed IPOs and therefore am calculating the BHARs. I calculated the BHARs already in a way that works for me. Nonetheless, i have weekly data and want to convert it into monthly data. I tried a lot of things and checked older statlist topics (e.g. https://www.statalist.org/forums/for...-weekly-prices), but this did not got me to monthly Dates+corresponding returns. It would be nice if the new data variable would correspond with the stock return most nearby the turn of the month. Below you can find my data, hope you can help me with this.
Greetings,
Sander

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Date double PriceCO byte Companyid int week float(rawreturn return1) double(SP600 SP500VALUE SP500EQUALWEIGHTED bharCO bharsp600 bharsp500value bharsp500equal returnproduct)
18611   9.9 1  0           .         . 408.34000000000003            575.65            1874.65                  1                  1                  1                  1 1.6202015914789347
18618  9.89 1  1 -.001010101  .9989899 420.34000000000003            589.64            1920.03   .998989898989899 1.0293872753097908 1.0243029618691915 1.0242071853412635 1.6202015914789347
18625    10 1  2  .011122346 1.0111223              420.6            590.78             1918.9 1.0101010101010102 1.0300239996081697 1.0262833318856943 1.0236044061558158 1.6202015914789347
18632 10.34 1  3        .034     1.034             420.11            601.76 1937.3600000000001 1.0444444444444443 1.0288240191996865  1.045357422044645  1.033451577627824 1.6202015914789347
18639 10.21 1  4 -.012572533  .9874275 421.96000000000004            606.48            1955.42 1.0313131313131314 1.0333545574766125 1.0535568487796405 1.0430853759368415 1.6202015914789347
18646 10.57 1  5   .03525955 1.0352596 416.21000000000004            602.92            1947.07 1.0676767676767676 1.0192731547240046 1.0473725353947712 1.0386312111594163 1.6202015914789347
18653 11.27 1  6   .06622516 1.0662252                422            612.12            1969.24 1.1383838383838383 1.0334525150609786 1.0633544688612873 1.0504574187181608 1.6202015914789347
18660 12.35 1  7   .09582964 1.0958296             422.73            617.95            1981.72 1.2474747474747474 1.0352402409756576 1.0734821506123513 1.0571146614034619 1.6202015914789347
18667    12 1  8  -.02834008  .9716599 428.90000000000003            624.49            2016.19 1.2121212121212122 1.0503501983641084 1.0848432207070269 1.0755020937241617 1.6202015914789347
18674 12.51 1  9       .0425    1.0425 437.53000000000003            631.63             2049.8 1.2636363636363637 1.0714845471910663 1.0972465908103883 1.0934307737444324 1.6202015914789347
18681 12.24 1 10 -.021582734  .9784173             422.01            621.38            1994.95 1.2363636363636363   1.03347700445707  1.079440632328672 1.0641719787693702 1.6202015914789347
18688 12.59 1 11   .02859477 1.0285947             427.75            619.42            2002.73 1.2717171717171716 1.0475339178135867 1.0760357856336316  1.068322086789534 1.6202015914789347
18695 12.49 1 12 -.007942812  .9920572             436.17            627.02            2020.16 1.2616161616161616 1.0681539893226233 1.0892382524103188 1.0776198223668418 1.6202015914789347
18702 11.86 1 13  -.05044035  .9495596             415.25            596.63            1936.64  1.197979797979798 1.0169221726992212 1.0364457569703813 1.0330675059344412 1.6202015914789347
18709 12.55 1 14   .05817875 1.0581788             429.76 615.8100000000001            1995.64 1.2676767676767677 1.0524562864279765 1.0697646139147052 1.0645400474755287 1.6202015914789347
18716 12.05 1 15  -.03984064  .9601594             444.26            629.07            2048.45 1.2171717171717171 1.0879659107606405 1.0927994441066622 1.0927106393193395 1.6202015914789347
18723 11.71 1 16  -.02821577  .9717842             452.42            633.94 2066.9900000000002 1.1828282828282828 1.1079492579712984  1.101259445843829  1.102600485423946 1.6202015914789347
18730 11.61 1 17 -.008539709  .9914603 435.59000000000003            621.46            2031.68 1.1727272727272726 1.0667336043493167 1.0795796056631635 1.0837649694609661 1.6202015914789347
18737 11.33 1 18  -.02411714  .9758829             444.29            626.87            2063.64 1.1444444444444444 1.0880393789489151 1.0889776774081474 1.1008134851839009 1.6202015914789347
18744 11.88 1 19   .04854369 1.0485437             455.95            638.02             2099.9                1.2 1.1165940147915951 1.1083470859028923  1.120155762408983 1.6202015914789347
18751 11.41 1 20  -.03956229  .9604377             443.95            633.38             2079.2 1.1525252525252525 1.0872067394818044 1.1002866325023886 1.1091137012242285 1.6202015914789347
18758 11.82 1 21   .03593339 1.0359334             449.06            628.87            2087.73 1.1939393939393939 1.0997208208845568 1.0924520107704334 1.1136638839249993 1.6202015914789347
18765 12.92 1 22    .0930626 1.0930626             445.86            628.16            2090.23  1.305050505050505 1.0918842141352794 1.0912186224268219 1.1149974661936894 1.6202015914789347
18772 13.01 1 23  .006965944  1.006966             438.76            617.62            2054.95  1.314141414141414 1.0744967429103198 1.0729088856075741 1.0961779532179339 1.6202015914789347
18779 13.07 1 24  .004611837 1.0046118             439.88            613.44            2043.99 1.3202020202020202 1.0772395552725669 1.0656475288803962 1.0903315285519963 1.6202015914789347
18786 13.03 1 25 -.003060444  .9969395             422.79            596.63 1980.8500000000001  1.316161616161616 1.0353871773522065 1.0364457569703813 1.0566505747739579 1.6202015914789347
18793  12.1 1 26  -.07137375  .9286262 419.65000000000003            590.96             1959.7  1.222222222222222 1.0276975069794778 1.0265960218883003  1.045368468780839 1.6202015914789347
18800 13.14 1 27   .08595041 1.0859504 429.96000000000004            602.09 2000.3700000000001 1.3272727272727274 1.0529460743498065 1.0459306870494225 1.0670631851278904 1.6202015914789347
18807 13.35 1 28  .015981736 1.0159818             439.67            608.77            2034.07 1.3484848484848484 1.0767252779546457  1.057534960479458 1.0850398741098337 1.6202015914789347
18814 13.07 1 29 -.020973783  .9790262 454.71000000000004 619.4300000000001            2081.28 1.3202020202020202 1.1135573296762502  1.076053157300443 1.1102232416717788 1.6202015914789347
end
format %tdnn/dd/CCYY Date

[Phil Bromiley]

Remember, we are mainly not from your area. Only a very few of us (if any) know what a BHAR is. If you tell us exactly what you need to calculate, then we can help.

I can imagine two things you want. If you want means or sums of weekly returns in the original dataset (with data still at the week level), bysort month: egen meanx=mean(x). You will see egen has a wide variety of alternatives - counts, means, sums, etc. If you want to move to just monthly data, look at the collapse command.

[Clyde Schechter]

I think it's more complicated than means or sums of weekly returns. The monthly return would not equal either of those. In fact, I don't think that monthly returns can be calculated from weekly data at all. The problem is that the weekly data does not provide a value at the beginning or end of the month: those bookmarks on the month typically fall in the middle of a week. Moreover, that one week's returns need to be allocated somehow to the preceding and following month--but at best any algorithm for doing this would just be a guess, because the returns may fluctuate within the week. It is easy enough to calculate yearly returns from monthly returns, because a year is 12 months, but getting monthly from weekly, not so much since a month is not a set of weeks.

[Sander Soet]

Thanks for your replies guys,
I think it is indeed impossible because of the fact that a month is not always four weeks. I will extract monthly data or otherwise leave it at weekly i suppose.