Announcement

Collapse
No announcement yet.

extracting weekly data from daily data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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, 02: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

        Comment


        • #5
          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

          Comment


          • #6
            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?
            Boo!

            Comment


            • #7
              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

              Comment


              • #8
                Re: extracting weekly data from daily data

                sorry read the next post this was a duplicate
                Last edited by laythss; September 6th, 2005, 05: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

                Comment


                • #9
                  Re: extracting weekly data from daily data

                  as a formula in the cell you would use something like this:
                  Code:
                  =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
                  Code:
                  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:
                  Code:
                  '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

                  Comment


                  • #10


                    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

                    Comment

                    Working...
                    X