Extracting Weekly Data From Daily Time Series

[crm13]

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.

[Aladin Akyurek]

What kind of data?

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

[crm13]

prices of commodities, stocks, etc: raw data

[Chris Davison]

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

[Mark W.]

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 ]

[crm13]

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.

[Mark W.]

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.

[crm13]

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.

[crm13]

The array is "date" and one data point

[Mark W.]

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 ]