Thanks:  0
Likes:  0

# Thread: Extracting Weekly Data From Daily Time Series

1. There must be a simple solution. I have daily data and want to extract one value per week to have weekly data. Similarly with monthly.

2. What kind of data?

What kind of value do you want to compute/extract/retrieve? An average, a sum, a max,...

3. prices of commodities, stocks, etc: raw data

4. 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...

5. On 2002-04-06 13:49, crm13 wrote:
There must be a simple solution. I have daily data and want to extract one value per week to have weekly data. Similarly with monthly.
Suppose this is your data...

******>
 Date Qty 4/1/02 10 4/3/02 25 4/8/02 40 4/18/02 55

You could create a PivotTable like this...

******>
 Sum of Qty Date Total 3/31/02 - 4/6/02 35 4/7/02 - 4/13/02 40 4/14/02 - 4/20/02 55 Grand Total 130

...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 ]

6. 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.

7. On 2002-04-08 06:58, crm13 wrote:
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.
You still haven't made yourself clear. Provide both sample data and desired results.

8. 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.

9. The array is "date" and one data point

10. 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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•