# Converting Inconsistent Daily Data into Weekly Data

#### sshelt15

##### New Member
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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### sshelt15

##### New Member
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

##### Active Member
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

##### New Member
Thank you Pub Denab!! The formulas you provided are extremely helpful!

-Sally

Replies
0
Views
208
Replies
11
Views
1K
Replies
1
Views
265
Replies
6
Views
392
Replies
0
Views
152

1,191,215
Messages
5,985,314
Members
439,956
Latest member
venky2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back