-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Extracting Weekly Data From Daily Time Series
- Thread starter crm13
- Start date
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
What kind of data?
What kind of value do you want to compute/extract/retrieve? An average, a sum, a max,...
What kind of value do you want to compute/extract/retrieve? An average, a sum, a max,...
Upvote
0
Chris Davison
MrExcel MVP
- Joined
- Feb 15, 2002
- Messages
- 1,790
you may need to be a bit more specific - post some examples of the data you are working with together with which ranges they are located and what type of results you want and where you want them....
there's 7 days in a week : how do you want a weekly figure produced ?
there's 30ish days in a month : how do you want a monthly figure produced ?
some concrete examples would enable us to get started on helping...
there's 7 days in a week : how do you want a weekly figure produced ?
there's 30ish days in a month : how do you want a monthly figure produced ?
some concrete examples would enable us to get started on helping...
Upvote
0
Suppose this is your data...<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Date</td><td>Qty</td></tr><tr><td>4/1/02</td><td>10</td></tr><tr><td>4/3/02</td><td>25</td></tr><tr><td>4/8/02</td><td>40</td></tr><tr><td>4/18/02</td><td>55</td></tr></table>
</body></html>
You could create a PivotTable like this...<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Sum of Qty</td><td></td></tr><tr><td>Date</td><td>Total</td></tr><tr><td>3/31/02 - 4/6/02</td><td>35</td></tr><tr><td>4/7/02 - 4/13/02</td><td>40</td></tr><tr><td>4/14/02 - 4/20/02</td><td>55</td></tr><tr><td>Grand Total</td><td>130</td></tr></table>
</body></html>
...which was created by Grouping 'Date' by Days using 12/30/2001 as the Start date, 1/4/2003 as the End date, and 7 as the Number of days in each interval.
This message was edited by Mark W. on 2002-04-07 16:26
Upvote
0
I didn't make myself clear. I have time series data with differing frequencies I want to transform to a common interval. Say daily data:
3/4/02 1200
3/5/02 1234
3/6/02 1198
3/7/02 1239
3/8/02 1176
I just want the end of week (month) value that I can then combine with other data. Similarly to get annual data from monthly time series, etc. Data is extracted and then compiled to a new file for further analysis. Thanks.
3/4/02 1200
3/5/02 1234
3/6/02 1198
3/7/02 1239
3/8/02 1176
I just want the end of week (month) value that I can then combine with other data. Similarly to get annual data from monthly time series, etc. Data is extracted and then compiled to a new file for further analysis. Thanks.
Upvote
0
I have time series data, say daily with one data point each day
3/4/02 56
3/5/02 58
3/6/02 67
3/7/02 54
3/8/02 55
....hundreds of daily data points. An array with data and one variable.
Say 3/8/02 is Friday, I want only "3/8/02 55"
but I want to extract this weekly data from the time series of daily points without using a brute force method.
Same if monthly data and I want quarterly or annual.
No manipulation of data at this point.
3/4/02 56
3/5/02 58
3/6/02 67
3/7/02 54
3/8/02 55
....hundreds of daily data points. An array with data and one variable.
Say 3/8/02 is Friday, I want only "3/8/02 55"
but I want to extract this weekly data from the time series of daily points without using a brute force method.
Same if monthly data and I want quarterly or annual.
No manipulation of data at this point.
Upvote
0
Filter the data using an Advanced AutoFilter and the following computed criteria...
Weekly: =TEXT('Date',"ddd")="Fri"
Monthly: ='Date'=EOMONTH('Date',0)
Quarterly: ='Date'=VLOOKUP('Date',({"1/1","3/31";"4/1","6/30";"7/1","9/30";"10/1","12/31"}&"/"&YEAR('Date'))+0,2)
Yearly: ='Date'=DATE(YEAR('Date'),12,31)
Copy and paste the displayed rows to a new worksheet.
This message was edited by Mark W. on 2002-04-11 13:49
Weekly: =TEXT('Date',"ddd")="Fri"
Monthly: ='Date'=EOMONTH('Date',0)
Quarterly: ='Date'=VLOOKUP('Date',({"1/1","3/31";"4/1","6/30";"7/1","9/30";"10/1","12/31"}&"/"&YEAR('Date'))+0,2)
Yearly: ='Date'=DATE(YEAR('Date'),12,31)
Copy and paste the displayed rows to a new worksheet.
This message was edited by Mark W. on 2002-04-11 13:49
Upvote
0
