Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: extracting weekly data from daily data

  1. #1
    Join Date
    4th September 2005
    Posts
    9

    extracting weekly data from daily data

    Hi there,

    I have a spreadsheet with about 5000 sequential days in column A and corresponding prices in column B. I would like to copy consective sets of weeks and quarters into column c and do analyses on them, in a loop. The problem is some weeks are 4 days and others have missing data. I need to extract the data by week, 1 week at a time, for each work-week even if it is only 3 days long because of missing data.

    Any help would be greatly appreciated.

    Cheers,

    Delberte

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: extracting weekly data from daily data

    Hi Delberte,

    how about adding a column on the end that showed the Week Number in it, you could then perform your calculations on the week number instead of the date itself
    D

    Better a bad day on the water than a good day in the office

  3. #3
    Join Date
    4th September 2005
    Posts
    9

    Re: extracting weekly data from daily data

    Hi Dave,

    I've tried using the weeknum function in XL 2003 but I get what I think are incorrect results. Either I'm using it incorrectly or there is a problem in XL

    I have dates loaded in celll A3 as 1/3/2000, for example, for the first monday of the year and the datenum function (=DATENUM(A3,1)) says it is week 2 rather than week 1.

    I'll keep playing with the weeknum function and see if there is a way around this.

    Thanks for the suggestion.

    Cheers,

    Delberte

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th March 2005
    Location
    California
    Posts
    200

    Re: extracting weekly data from daily data

    I am a bit confused when you mentioned DATENUM function since I dont seem to know it to work that way.
    Establishes the date number for a date, when that date can be specified as either a date or a decimal year.
    from this website

    but know the WEEKNUM function. my guess is they both work the same way, but you check on it. Doing a search on the net here is a quote from a website:
    For the year 2000, WEEKNUM will compute week 1 as containing either only 1 day, Saturday, 1-Jan, if the return-type setting is 1 (weeks begin on Sunday), or as containing 2 days, Saturday, 1-Jan, and Sunday, 2-Jan, if the return-type setting is 2 (weeks begin on Monday).
    and this is the problem I think why you end up with a 2 instead of a 1. you can read more on that website

    Sorry could not help more.
    Last edited by laythss; September 6th, 2005 at 03:34. Reason: more explination and fix link
    Layth

    ____________________
    If you are looking for coffee to go with your cigs get an Always Coffee
    It is nice to read a Thank You

  5. #5
    Join Date
    4th September 2005
    Posts
    9

    Re: extracting weekly data from daily data

    Thanks for the suggestion.

    While I work on this, do you have any thoughts on extracting 1 quarter at a time. I need to pull out the data for Jan, Feb, and Mar and do analyses on them, then do April, May June and so on for about 20 years of data.

    Cheers,

    Delberte

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,539

    Re: extracting weekly data from daily data

    Delberte

    Could we see some sample data?

    What analysis are you doing on it?

    Can you not use any of the built in methods such as a pivot table or grouping/sunb totalling?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    4th September 2005
    Posts
    9

    Re: extracting weekly data from daily data

    Hi norie,

    I am new to using forums. I don't know how to paste my spreadsheet, but I can describe it. In col A I have daily dates for many years. In col B I have corresponding prices. I copy various time windows to column C and do statistical analyses such as regression, box-jenkins, spectral and so on. I want to copy the data on prices for the first quarter's data, do the various analyses, then copy the second quarter's data to the same location and repeat and so on. I need to set up a loop that copies the data, does the analyses, stores them, copies the next set of data, and so on.

    Because a "quarter" is the first 3 months of the year and is not always the same number of rows (due to holidays, leap-years, missing data), I need to somehow specify 3 months at a time as opposed to 365/4 days at a time and continue this through as much as 20 years of daily data.

    Thanks for any suggestions you may have.

    Cheers,

    Delberte

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    30th March 2005
    Location
    California
    Posts
    200

    Re: extracting weekly data from daily data

    sorry read the next post this was a duplicate
    Last edited by laythss; September 6th, 2005 at 06:16. Reason: duplicate
    Layth

    ____________________
    If you are looking for coffee to go with your cigs get an Always Coffee
    It is nice to read a Thank You

  9. #9
    Join Date
    30th March 2005
    Location
    California
    Posts
    200

    Re: extracting weekly data from daily data

    as a formula in the cell you would use something like this:
    VB:
    =If(And(MONTH(A1)<=3,DAY(A1)>0),"first quarter",If(And(MONTH(A1)<=6,DAY(A1)>0),"2nd quarter",If(And(MONTH(A1)<=9,DAY(A1)>0),"3ed quarter",If(And(MONTH(A1)<=12,DAY(A1)>0),"4th quarter","ERROR")))) 
    
    
    but I am not sure if you are going to be using a formula in the cells, it seems 9since you said loop) you will be using VBA code, then it would be the same idea and you would still use
    VB:
    If (MONTH(cells(1,1) < 3 And Day(Cells(1,1))>0) Then 
        msgbox (" This would be the first quarter") 
    Else 
        If ' and continue the nested if's
    
    
    better yet you would use a select case:
    VB:
     'assuming your dates are in column 1 (or A) then we will loop through all these cells
    Dim i As Integer 
    i = 1 
    Do Until cells(i,1) = "" ' be aware that this will not allow a row gap in the
         'dates, use a FOR NEXT if you have a gap
        Select Case MONTH(cells(i,1)) 
        Case 1,2,3 
            If Day(cells(i,1))>0 Then 
                 ' enter what you would like to do with the first quarter
            End If 
             
        Case 4,5,6 
             'same thing
        Case 7,8,9 
             'same
        Case 10,11,12 
             'same
        End Select 
        i = i+1 
    Loop 
    
    
    Hope this helps
    Layth

    ____________________
    If you are looking for coffee to go with your cigs get an Always Coffee
    It is nice to read a Thank You

  10. #10
    Join Date
    4th September 2005
    Posts
    9

    Re: extracting weekly data from daily data

    Hi Layth,

    Thanks for the really helpful suggestions. The "do case" looks like what I need. Will try to incorporate it into my code.

    Thanks again,

    Cheers,

    Delberte

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Staff Duty Rota - Daily And Weekly
    By Whiplash450 in forum EXCEL HELP
    Replies: 1
    Last Post: March 29th, 2008, 11:54
  2. Extracting Data Based On List Of Data
    By brooza in forum EXCEL HELP
    Replies: 1
    Last Post: February 7th, 2007, 19:49
  3. Converting Daily data into Weekly data
    By ADE in forum EXCEL HELP
    Replies: 3
    Last Post: October 8th, 2006, 03:56
  4. Calculating Daily Hours/Weekly/By Job Num
    By Tredin Water in forum EXCEL HELP
    Replies: 7
    Last Post: September 8th, 2006, 00:50
  5. Weekly Data Entry Form
    By ruva98 in forum Excel and/or Access Help
    Replies: 1
    Last Post: April 29th, 2003, 17:22

Bookmarks

Posting Permissions

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