Extracting Weekly Data From Daily Time Series
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Extracting Weekly Data From Daily Time Series

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    What kind of data?

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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    prices of commodities, stocks, etc: raw data

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    ******>
    DateQty
    4/1/0210
    4/3/0225
    4/8/0240
    4/18/0255



    You could create a PivotTable like this...

    ******>
    Sum of Qty
    DateTotal
    3/31/02 - 4/6/0235
    4/7/02 - 4/13/0240
    4/14/02 - 4/20/0255
    Grand Total130



    ...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. #6
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #8
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The array is "date" and one data point

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com