Anyone interested in pulling historical results/odds from SBR

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • b_rad_1983
    SBR High Roller
    • 01-07-13
    • 127

    #36
    Debugging brings me to this line

    Do While CodeStart < LastGameNHLData

    When I bring my cursor over CodeStart I get Error2042 and LastGameNHLData is 149

    So you are correct, But Im not sure how to make codestart an integer..

    Thanks in advanced!
    Comment
    • a4u2fear
      SBR Hall of Famer
      • 01-29-10
      • 8147

      #37
      b_rad, please look for CodeStart = and post what it equals to (or what you set it to). This is the line in the code you need to investigate. CodeStart is not getting set properly. Maybe the webpage you loaded in is a smidge different than the others or the information for that day's import is different.
      Comment
      • a4u2fear
        SBR Hall of Famer
        • 01-29-10
        • 8147

        #38
        nevermind i found it, you are using my code directly:

        CodeStart=Application.Match("Options", Sheets("NHLData").Range("A1:A100"), 0)

        Look at the sheet "NHLData" or the sheet if you have renamed in. If you do not see the word "Options" in column A, rows 1 through 100, that is what is causing the error. I'm not sure how much you have used this code, it could just be one day that this is happening for, or did you just start? Sometimes SBR or whatever site your loading does have bad data some days and you cannot import that day.

        By the way, I started a tutorial thread, if interested in handicapper think tank.
        Comment
        • b_rad_1983
          SBR High Roller
          • 01-07-13
          • 127

          #39
          I just used it based on what you described in here. I found the other thread and will jump over there once I get through this error.
          So far I have only copied what you putin here so iit's the data from the day in 2007.
          I won't have time to try anything until late tomorrow. Have a great weekend!
          Comment
          • aramakilx
            SBR High Roller
            • 01-18-13
            • 195

            #40
            thx alot, very useful data!
            Comment
            • b_rad_1983
              SBR High Roller
              • 01-07-13
              • 127

              #41
              It seems every time I run the macro it over writes everything in my "nhldata" sheet. So if I add it options in 1-100 anywhere it erases it.
              Comment
              • a4u2fear
                SBR Hall of Famer
                • 01-29-10
                • 8147

                #42
                Originally posted by b_rad_1983
                It seems every time I run the macro it over writes everything in my "nhldata" sheet. So if I add it options in 1-100 anywhere it erases it.
                Yes that is correct. NHLdata is the sheet where each game is loaded, then you take the data and put it into another sheet, then the NHLdata is erased and then populated with the next imported game. Again the information is moved to another sheet and this repeats. NHLdata is just the imported webpage.
                Comment
                • b_rad_1983
                  SBR High Roller
                  • 01-07-13
                  • 127

                  #43
                  Lets switch over to the think tank!
                  Comment
                  • A4K
                    SBR Hall of Famer
                    • 10-08-12
                    • 5243

                    #44
                    Okay, I got the data I needed, but the game score is off. When I look to see which cell the score is in, it appears the score is recorded in double a few cells up from where the module was told to look. I adjusted which cells to extract data from, but the score is still listed in double.

                    Example: April 5th 2009.. Braves @ Phillies, the final score was 4-1 Atlanta. The data in the cell is 44 for the Braves and 11 for the Phillies. How do I get Excel to split the data in those cells?
                    Comment
                    • a4u2fear
                      SBR Hall of Famer
                      • 01-29-10
                      • 8147

                      #45
                      A4K, can you post your code? This thread is older than another I have posted and I can't tell exactly which code you are using and why it is happening.
                      Comment
                      • A4K
                        SBR Hall of Famer
                        • 10-08-12
                        • 5243

                        #46
                        Here is the code, after I modified it from the beginning of this thread....

                        Public MLByear As String
                        Public MLBmonth As String
                        Public MLBday As String


                        Public Sub importdata()


                        'add a new sheet to put the data in
                        Application.DisplayAlerts = False
                        Application.DisplayAlerts = True
                        Sheets.Add After:=Sheets(Sheets.Count)
                        Sheets(Sheets.Count).Name = "MLBData"


                        'http://www.sbrforum.com/betting-odds/mlb-baseball/pointspread/?date=20090405
                        MLByear = "09"
                        MLBmonth = "04"
                        MLBday = "05"




                        With Sheets("MLBData").QueryTables.Add(Connec tion:= _
                        "URL;http://www.sbrforum.com/betting-odds/mlb-baseball/pointspread/?date=" & "20" & MLByear & MLBmonth & MLBday, Destination:= _
                        Range("$A$1"))
                        .Name = "20" & MLByear & MLBmonth & MLBday
                        .FieldNames = True
                        .RowNumbers = False
                        .FillAdjacentFormulas = False
                        .PreserveFormatting = True
                        .RefreshOnFileOpen = False
                        .BackgroundQuery = True
                        .RefreshStyle = xlInsertDeleteCells
                        .SavePassword = False
                        .SaveData = True
                        .AdjustColumnWidth = True
                        .RefreshPeriod = 0
                        .WebSelectionType = xlEntirePage
                        .WebFormatting = xlWebFormattingNone
                        .WebPreFormattedTextToColumns = True
                        .WebConsecutiveDelimitersAsOne = True
                        .WebSingleBlockTextImport = False
                        .WebDisableDateRecognition = False
                        .WebDisableRedirections = False
                        .Refresh BackgroundQuery:=False
                        End With


                        CodeStart = Application.Match("Options", Sheets("MLBData").Range("A1:A100"), 0)


                        Sheets("MLBGames").Cells(2, 1) = Sheets("MLBData").Cells(CodeStart - 1, 1) 'Home team name
                        Sheets("MLBGames").Cells(2, 2) = Sheets("MLBData").Cells(CodeStart - 8, 1) 'Home team score
                        Sheets("MLBGames").Cells(2, 3) = Sheets("MLBData").Cells(CodeStart + 2, 1) 'Home team run line


                        Sheets("MLBGames").Cells(2, 4) = Sheets("MLBData").Cells(CodeStart - 2, 1) 'Away team name
                        Sheets("MLBGames").Cells(2, 5) = Sheets("MLBData").Cells(CodeStart - 7, 1) 'Away team score
                        Sheets("MLBGames").Cells(2, 6) = Sheets("MLBData").Cells(CodeStart + 1, 1) 'Away team run line


                        End Sub


                        When looking at the Data sheet, it appears the score comes out funny from SBR. The score comes in double. Look at cells 91 and 92 on the data sheet. The score should be 4 and a 1. Instead it got pulled as 44 and 11. How do I trim this?

                        Side note: This tutorial is great. I plan on setting up an Excel sheet and writing a loop to pull all of the run line data that I need for the last 5 seasons. I have everything else that I need. Thanks.
                        Comment
                        • a4u2fear
                          SBR Hall of Famer
                          • 01-29-10
                          • 8147

                          #47
                          A4K. Few things:

                          The display alerts command helps speed up the code. You have =false then =true right after. You want to put the =false at the beginning of code (below Public sub....) then =true at the very end of the code (above end sub). This will speed up code.

                          For the import, I don't have a good answer for you and I can't figure it out at the moment. I did however import a few pages and it looks to be very repetitive.

                          If the score is 8-4 it will be
                          88
                          44

                          if the score is 10-2 it will be
                          1010
                          22

                          if the score is 0-1 it will be
                          0
                          1

                          You can use a function called len(variable) where variable is the cell (i.e. Cells(44,1) = row 44 column 1)

                          if cells(44,1)=0, len (cells(44,1))=1
                          if cells(44,1)=44, len (cells(44,1))=2
                          if cells(44,1)=1010, len (cells(44,1))=4

                          so, if you know the length =1, you know that team scored 0,

                          if the length is 2, i.e. cells(44,1)=44, you know the team scored:
                          mid(cells(44,1),1,1)=4.
                          The first 1 here is the starting integer, or the first digit of cells(44,1), the second one is the length of the score we want or 1 (its 2 digits in length with the score doubled), we know the score =4

                          if the length is 4, i.e. cells(44,1)=1010, you know the team scored double digits:
                          mid(cells(44,1),1,2)=10.
                          The first 1 here is the starting integer, or the first digit of cells(44,1), the second one is the length of the score we want or 2 (its4 digits in length with the score doubled, so we want half), we know the score=10

                          Also, when you import a date with two games, you will need to find multiple duplicates of "options", which can be tricky, please post all answers in the newest Excel Tutorial in handicapper think tank
                          Comment
                          • chrismunney
                            SBR Rookie
                            • 04-13-14
                            • 17

                            #48
                            awesome stuff
                            Comment
                            SBR Contests
                            Collapse
                            Top-Rated US Sportsbooks
                            Collapse
                            Working...