Converting Inconsistent Daily Data into Weekly Data

[sshelt15]

Hello,

I am currently working with daily market return data for 10 specific stocks dating back to 1990 (6000+ rows). The returns are reported Monday thru Friday. However, if a particular week had a holiday (in which the market was closed), no return was reported. I also have data for the daily market returns for the entire stock market. I am trying to compare the weekly returns for each specific stock, compared to the overall market returns, so that I can determine the beta of each stock thru a linear regression in excel.

Right now, I am trying to methodically group the daily data, so that I can convert to weekly returns. I am familiar with the grouping feature found in pivot tables, but am unsure how to proceed with the grouping of weeks that had holidays and skipped reporting for that day. After thinking it through, I decided that I should start the weekly grouping on a Tuesday or Wednesday, since most holidays do not fall on these days.

I have tried to group the data outside of a pivot table by using:

=IF($M7="Tuesday",($o7/$o2)-1,"")

The "True" statement in the above formula is the formula for the return for the week. However, because several weeks have inconsistent ranges of reported returns, the resulting returns have gaps where there was a Tuesday holiday, or incorrect values where the range of values were different than the norm.

If anyone has any feedback on how I should proceed, I would GREATLY appreciate it! Thank you for your time!!

-Sally

 

[sshelt15]

My first post was too wordy and I've revised my question:

I need to group 6000+ rows of daily data by weekly data in a pivot table. The weekly groupings need to overlap, e.g., ("group by days", "8 days") :

1/02/1990 - 1/09/1990
1/09/1990 - 1/16/1990
1/16/1990 - 1/23/1990

Using the "grouping function" in a pivot table, I cannot find a function on how to overlap the range of dates.

How can I group the data so the ranges overlap?

 

[Pup Denab]

Hi Sally
Welcome to the forum
Another one of those, seems easier enough questions, however excel normally put 53 weeks a year, sometimes even 54, so with your range their will be errors, however these will be around end of December & the beginning of January probably ever year of your data but I hope these 2 formula's will help you on the way to a solution, all their do is identify the Mon to Mon groups your require, you can then use sumifs to calculate the results, I have put the date in column A & the 2 formula in B & C to make it easier to understand how it works.

https://www.dropbox.com/s/x60hgvf0lgnqqri/Sally.xlsx?dl=0

 

[sshelt15]

Thank you Pub Denab!! The formulas you provided are extremely helpful!

-Sally