new data query question excel

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Formulawiz
    Restricted User
    • 01-12-09
    • 1589

    #1
    new data query question excel
    After downloading stats from a website using the external data, new data query, everything gets saved into cells. How can I maintain those values in the existing cells if I download the next weeks stats. This causes the values to change in the existing cell to what you download.
  • djiddish98
    SBR Sharp
    • 11-13-09
    • 345

    #2
    I think the data query's function by design is to refresh the data, but keep the cell references the same.

    Are you looking to keep some cells constant from prior weeks but update others? Or keep the original set in one place, and then grab that updated number?

    Your best bet might be a macro that runs through, grabs the data you want to keep, puts it in a separate sheet, and then runs the refresh.
    Comment
    • Formulawiz
      Restricted User
      • 01-12-09
      • 1589

      #3
      I would like to keep the cells with last weeks stats the same and then use the new query for the upcoming week.
      Comment
      • djiddish98
        SBR Sharp
        • 11-13-09
        • 345

        #4
        I would recommend going the macro route, instead of loading up a query for each week. I don't know how queries affect excel file size (probably minimal), but it seems unnecessary.

        The process would look like this.

        1)Query week 1
        2) Copy week 1 stats into a separate sheet, marking them as week 1 stats
        3) modify your query to grab week 2
        4) copy week 2
        5) rinse and repeat

        If you're un-familiar with macros and programming, start with the macro recorder, create a query, and see what it's doing. Then,try replacing the hard-coded macro-recorder code with variables and go from there.
        Comment
        • TomG
          SBR Wise Guy
          • 10-29-07
          • 500

          #5
          If it's just a once a week thing, don't bother with a macro. Just copy the results -> Paste Special Values into a separate tab. Now you have that week hard coded and saved while the web query still set to update with new data.
          Comment
          • Formulawiz
            Restricted User
            • 01-12-09
            • 1589

            #6
            Originally posted by djiddish98
            I would recommend going the macro route, instead of loading up a query for each week. I don't know how queries affect excel file size (probably minimal), but it seems unnecessary.

            The process would look like this.

            1)Query week 1
            2) Copy week 1 stats into a separate sheet, marking them as week 1 stats
            3) modify your query to grab week 2
            4) copy week 2
            5) rinse and repeat

            If you're un-familiar with macros and programming, start with the macro recorder, create a query, and see what it's doing. Then,try replacing the hard-coded macro-recorder code with variables and go from there.
            I do this for football but you cant do it for basketball there are too many games. I still would like to know how to do it if you look at my last post.
            Comment
            • djiddish98
              SBR Sharp
              • 11-13-09
              • 345

              #7
              Originally posted by TomG
              If it's just a once a week thing, don't bother with a macro. Just copy the results -> Paste Special Values into a separate tab. Now you have that week hard coded and saved while the web query still set to update with new data.
              Fair point - I hate manually doing anything, so I'd rather waste an hour setting something up to save 5 seconds.

              Originally posted by FormulaWhiz
              I do this for football but you cant do it for basketball there are too many games. I still would like to know how to do it if you look at my last post.
              I started this setup for basketball, so I'm not sure why you're saying there are too many games. Care to elaborate?

              When I setup my macro, I would have the links to the data, query each link, and then pull the data into a separate sheet for each query. Probably took about 30 seconds on a crap computer for a full night of NBA games.
              Comment
              • Formulawiz
                Restricted User
                • 01-12-09
                • 1589

                #8
                Originally posted by djiddish98
                Fair point - I hate manually doing anything, so I'd rather waste an hour setting something up to save 5 seconds.



                I started this setup for basketball, so I'm not sure why you're saying there are too many games. Care to elaborate?

                When I setup my macro, I would have the links to the data, query each link, and then pull the data into a separate sheet for each query. Probably took about 30 seconds on a crap computer for a full night of NBA games.
                Ill try to setup an example under excel
                Comment
                • Formulawiz
                  Restricted User
                  • 01-12-09
                  • 1589

                  #9
                  here is an example spreadsheet. As you can see the the cfb lines are taken from wagertracker for week 14. For example if we look at column F under sheet 1, we have all the lines. If we now change the lines page to week 15, all the values will change in shhet 1, cloumn F. How can we keep the lines in week 14 the same even if we change to week 15 without doing any programming, just by formula.
                  Attached Files
                  Comment
                  • djiddish98
                    SBR Sharp
                    • 11-13-09
                    • 345

                    #10
                    You'll have to copy and paste if you don't want to do anything programming based.

                    I believe you can't reference two different URLs using 1 query, so you can't plug in partial week 15 info with some week 14 info staying.

                    If you start stacking queries, you'll have to adjust your formula for the team spread every time to reference the new cells.

                    I slapped some code together to make the copying and pasting easier, but SBR doesn't allow for .xlsm attachment uploads. Here's the code:

                    Code:
                    Sub getSpread()
                    
                    Set Fill = Sheets("Sheet1").Range("a2")
                    
                    While IsEmpty(Fill) = False
                    
                    Set Fill = Fill.Offset(1, 0)
                    
                    Wend
                    
                    
                    i = 0
                    
                    While i < 2
                    Set check = Sheets("Lines").Range("f9")
                    While IsEmpty(check) = False
                    
                    Fill.Value = check.Value
                    Fill.Offset(0, 1).Value = check.Offset(1, 0).Value
                    Fill.Offset(0, 3).Value = check.Offset(2, 0).Value
                    Fill.Offset(0, 5).Value = check.Offset(2, 2).Value
                    
                    Set check = check.Offset(4, 0)
                    Set Fill = Fill.Offset(1, 0)
                    
                    Wend
                    
                    Set check = Sheets("Lines").Range("k9")
                    i = i + 1
                    
                    Wend
                    
                    End Sub
                    You only need to select one of the boxes in the query page that wraps around all the spreads and scores. No need to check every single box.

                    Good luck!
                    Comment
                    • djiddish98
                      SBR Sharp
                      • 11-13-09
                      • 345

                      #11
                      As a note: all this macro does is start with the first game (presumed to be date in Lines tab cell f9), and cycle through till there's no game.

                      Then it moves over to the second column of games, since wagertracker doesn't present their data in a straight line, starting at cell k9.

                      It copies the date, teams and spread for the home team over to sheet1.

                      FYI - it starts with the first empty cell on Sheet1, so you don't over-write any old spreads - if you ran the macro a lot for one week, you'd end up duplicating your spreads every time.
                      Comment
                      SBR Contests
                      Collapse
                      Top-Rated US Sportsbooks
                      Collapse
                      Working...