Anyone interested in pulling historical results/odds from SBR

[a4u2fear]

If so, I can help if enough people are interested. I have pulled all odds/results off SBR's data from 07/08 till today. While I have decided I do not want to share my Excel data, I decided I will help those who help themselves by showing you how in Microsoft Excel/Visual Basic.

Post if you'd like to start this and I will help you through it best I can.

[a4u2fear]

For starters. Open up Excel. Recent versions like 2007 and 2010 will have a tab called "Developer". If you don't see it, click the Microsoft logo at the top far left, then click Excel options at the bottom.

Once in Excel options, click the popular tab on the left, and check the box next to show developer tab.

Once the developer tab is visible, click the visual basic button

[a4u2fear]

Once in visual basic, click the insert tab and insert a module.

Now to insert your first webpage into excel with the data from SBR's site (i.e for 10/03/2007)
(paste code below into the module:


Public NHLyear As String
Public NHLmonth As String
Public NHLday 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 = "NHLData"

'http://www.sbrforum.com/betting-odds/nhl-hockey/pointspread/?date=20071003
NHLyear="07"
NHLmonth="10"
NHLday="03"


With Sheets("NHLData").QueryTables.Add(Connec tion:= _
"URL;http://www.sbrforum.com/betting-odds/nhl-hockey/pointspread/?date=" & "20" & NHLyear & NHLmonth & NHLday, Destination:= _
Range("$A$1"))
.Name = "20" & NHLyear & NHLmonth & NHLday
.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

End Sub

[a4u2fear]

Click the run tab at the top and click run sub/userform and you will have the website imported into an excel sheet called NHL Data.

I will continue on and go further if interest is there.

[PaperTrail07]

Love and hate excel sometimes ....excellent program in reality....

[GunnShow]

I'd be interested in learning more. What kind of odds/results are you able to pull using this method?

[Canukc]

I'm following too. Just updating an older version of Excel first.

[a4u2fear]

Ok will add more soon, out of town this weekend getting wasted.

You can pull pick lines, money lines, totals by game and period

[a4u2fear]

Now that you've loaded your first game, you need to find the data you want to place into another sheet where you will store all of your data.

First you need to determine whether or not there is data on the day you have imported. There are some days where there aren't any game and there are others that SBR did not record any data for, it happens.

I found that every (or almost every) imported day has the word "Options" right before each game. In the first game we loaded, you can see in row 97, Options. By finding this word, you can find the two teams playing and their puck line odds.

You will need to add the following code after the code "End With" above but before "End Sub" (do not add the End With or End Sub here, just showing it as where to put your code:

End With

'this is a comment, add the line below to find the starting point
CodeStart=Application.Match("Options", Sheets("NHLData").Range("A1:A100"), 0)

End Sub

[a4u2fear]

CodeStart will be returned with the value of 97.

Now you will want to place your first game's data and line into another sheet, so create another sheet called "NHLGames", and say in cell A1 you put Home team, B1 will be home score, C1 will be home puck line, D1 will be away team, E1 will be away score, and F1 will be away puck line

now, we can place the following code after the "CodeStart" line we just added:
'Comment, Cells(row,column) is how it works. We know CodeStart is where options is, and the home team is one row below it in row 96 and column A, Column A is written with its numeric code, or 1 here

Sheets("NHLGames").Cells(2,1)=Sheets("NH LData").Cells(CodeStart-1,1) 'Home team name
Sheets("NHLGames").Cells(2,2)=Sheets("NH LData").Cells(CodeStart-4,1) 'Home team score
Sheets("NHLGames").Cells(2,3)=Sheets("NH LData").Cells(CodeStart+2,1) 'Home team puck line

Sheets("NHLGames").Cells(2,4)=Sheets("NH LData").Cells(CodeStart-2,1) 'Home team name
Sheets("NHLGames").Cells(2,5)=Sheets("NH LData").Cells(CodeStart-5,1) 'Home team score
Sheets("NHLGames").Cells(2,6)=Sheets("NH LData").Cells(CodeStart+1,1) 'Home team puck line

[a4u2fear]

Your code should now look like:

Public NHLyear As String
Public NHLmonth As String
Public NHLday 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 = "NHLData"

'http://www.sbrforum.com/betting-odds/nhl-hockey/pointspread/?date=20071003
NHLyear = "07"
NHLmonth = "10"
NHLday = "03"


With Sheets("NHLData").QueryTables.Add(Connec tion:= _
"URL;http://www.sbrforum.com/betting-odds/nhl-hockey/pointspread/?date=" & "20" & NHLyear & NHLmonth & NHLday, Destination:= _
Range("$A$1"))
.Name = "20" & NHLyear & NHLmonth & NHLday
.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("NHLData").Range("A1:A100"), 0)

Sheets("NHLGames").Cells(2, 1) = Sheets("NHLData").Cells(CodeStart - 1, 1) 'Home team name
Sheets("NHLGames").Cells(2, 2) = Sheets("NHLData").Cells(CodeStart - 4, 1) 'Home team score
Sheets("NHLGames").Cells(2, 3) = Sheets("NHLData").Cells(CodeStart + 2, 1) 'Home team puck line

Sheets("NHLGames").Cells(2, 4) = Sheets("NHLData").Cells(CodeStart - 2, 1) 'Home team name
Sheets("NHLGames").Cells(2, 5) = Sheets("NHLData").Cells(CodeStart - 5, 1) 'Home team score
Sheets("NHLGames").Cells(2, 6) = Sheets("NHLData").Cells(CodeStart + 1, 1) 'Home team puck line

End Sub

[a4u2fear]

I can continue if anyone is working on it or following. Obviously, there is a lot of work to do. We still have to create loops to go through all games in a day into our NHLGames sheet, and then we have to create a loop for progressing through each day through an entire season, as no one wants to manually type in each games date and wait.

[a4u2fear]

If you work hard enough, you can come up with something like I have here over 6 NHL seasons.

[bateeeman22]

nhl system

Have you created a system what's hanging up together with your nhl data? Would be good to know which system that is and if it works.

Bateman

[dshawn]

holy crap you made that !!!
wow !!!!
much respect

[a4u2fear]

Have you created a system what's hanging up together with your nhl data? Would be good to know which system that is and if it works.

Bateman

It's difficult to find systems that work. What it has been great is debunking myths such as teams on B2B doing very poorly or the team with a lot of rest being highly profitable.

[a4u2fear]

holy crap you made that !!!
wow !!!!

much respect

Thanks

[a4u2fear]

Added more code (below). In this updated code, you'll see I've added the code to loop through the day of games just imported to find all games and lines.

While it may look a bit weird as there is a counter called "bb" in there, it is there because sometimes the line was not available from a sportsbook. The page we are importing from SBR contains all lines from all sportsbooks and there are sometimes errors. If there is an error, the "bb" counter eventually finds where the relevant data is.

I also had a hard time finding the end of the days data as it was not always consistent. But the while loop I have in there works to find this information.

Post any questions.

[a4u2fear]

Public NHLyear As String
Public NHLmonth As String
Public NHLday As String

Public Sub importdata()

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

'http://www.sbrforum.com/betting-odds/nhl-hockey/pointspread/?date=20071003
NHLyear = "07"
NHLmonth = "10"
NHLday = "03"


With Sheets("NHLData").QueryTables.Add(Connec tion:= _
"URL;http://www.sbrforum.com/betting-odds/nhl-hockey/pointspread/?date=" & "20" & NHLyear & NHLmonth & NHLday, Destination:= _
Range("$A$1"))
.Name = "20" & NHLyear & NHLmonth & NHLday
.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("NHLData").Range("A1:A100"), 0)

bb = 0 'counter
LastRowNHLData = Sheets("NHLData").UsedRange.Rows.Count
LastGameNHLData = 0
'find last game row
Set NHLDataRange = Sheets("NHLData").Range(Sheets("NHLData" ).Cells(LastRowNHLData - 75, 1), Sheets("NHLData").Cells(LastRowNHLData, 1))
LastGameNHLData = LastRowNHLData - 75 + Application.Match("Options", NHLDataRange, 0)

'Need to find counter where data is not corrupt
Do While CodeStart < LastGameNHLData
If Mid(Sheets("NHLData").Cells(CodeStart + 1, 1), 1, 2) = "+1" Or Mid(Sheets("NHLData").Cells(CodeStart + 1, 1), 1, 2) = "-1" And Len(Sheets("NHLData").Cells(CodeStart + 1, 1)) > 4 Then
bb = 0
ElseIf Mid(Sheets("NHLData").Cells(CodeStart + 2, 1), 1, 2) = "+1" Or Mid(Sheets("NHLData").Cells(CodeStart + 2, 1), 1, 2) = "-1" And Len(Sheets("NHLData").Cells(CodeStart + 2, 1)) > 4 Then
bb = 1
ElseIf Mid(Sheets("NHLData").Cells(CodeStart + 3, 1), 1, 2) = "+1" Or Mid(Sheets("NHLData").Cells(CodeStart + 3, 1), 1, 2) = "-1" And Len(Sheets("NHLData").Cells(CodeStart + 3, 1)) > 4 Then
bb = 2
ElseIf Mid(Sheets("NHLData").Cells(CodeStart + 4, 1), 1, 2) = "+1" Or Mid(Sheets("NHLData").Cells(CodeStart + 4, 1), 1, 2) = "-1" And Len(Sheets("NHLData").Cells(CodeStart + 4, 1)) > 4 Then
bb = 3
ElseIf Mid(Sheets("NHLData").Cells(CodeStart + 5, 1), 1, 2) = "+1" Or Mid(Sheets("NHLData").Cells(CodeStart + 5, 1), 1, 2) = "-1" And Len(Sheets("NHLData").Cells(CodeStart + 5, 1)) > 4 Then
bb = 4
ElseIf Mid(Sheets("NHLData").Cells(CodeStart + 6, 1), 1, 2) = "+1" Or Mid(Sheets("NHLData").Cells(CodeStart + 6, 1), 1, 2) = "-1" And Len(Sheets("NHLData").Cells(CodeStart + 6, 1)) > 4 Then
bb = 5
ElseIf Mid(Sheets("NHLData").Cells(CodeStart + 7, 1), 1, 2) = "+1" Or Mid(Sheets("NHLData").Cells(CodeStart + 7, 1), 1, 2) = "-1" Then
bb = 6
End If

LastRowNHLGames = Sheets("NHLGames").UsedRange.Rows.Count
Sheets("NHLGames").Cells(LastRowNHLGames + 1, 1) = Sheets("NHLData").Cells(CodeStart - 1 + bb, 1) 'Home team name
Sheets("NHLGames").Cells(LastRowNHLGames + 1, 2) = Sheets("NHLData").Cells(CodeStart - 4 + bb, 1) 'Home team score
Sheets("NHLGames").Cells(LastRowNHLGames + 1, 3) = Sheets("NHLData").Cells(CodeStart + 2 + bb, 1) 'Home team puck line

Sheets("NHLGames").Cells(LastRowNHLGames + 1, 4) = Sheets("NHLData").Cells(CodeStart - 2 + bb, 1) 'Away team name
Sheets("NHLGames").Cells(LastRowNHLGames + 1, 5) = Sheets("NHLData").Cells(CodeStart - 5 + bb, 1) 'Away team score
Sheets("NHLGames").Cells(LastRowNHLGames + 1, 6) = Sheets("NHLData").Cells(CodeStart + 1 + bb, 1) 'Away team puck line

If (CodeStart + 1) < LastGameNHLData Then
Set NHLDataRange = Sheets("NHLData").Range(Sheets("NHLData" ).Cells(CodeStart + 1, 1), Sheets("NHLData").Cells(CodeStart + 40, 1))
foundteam = Application.Match("Options", NHLDataRange, 0)
CodeStart = CodeStart + foundteam
Else
CodeStart = CodeStart + 20
End If
Loop

End Sub

[a4u2fear]

mods can we get this moved to handicapper think tank? I think it's better suited there.

[ppfreitas]

wow, it looks awesome!

will try it to compile a nba database...

great job!

[a4u2fear]

wow, it looks awesome!

will try it to compile a nba database...

great job!

let me know if you need help.

[hockey216]

Love and hate excel sometimes ....excellent program in reality....

lots of functions. can do lot of mathematics in it. Excel can do so many things that you have no idea it can do. so many programs. its great resource. Matlab is the best for optimization and numeric programing. i have some financial mathematics/actuarial science programs if anyone wants some investing tools. i have actuarial science programs simulating investment returns with either fixed returns and fixed life periods (basic annuities). also have programs written to simulate that based on randomness in yearly portfolio return (iid normal random variable with whatever mean and std dev you want- i use .08 and .15 as those are accurate figures for market as whole). i can also simulate models incorporating randomness in how long you live using GOMA distribution. lets say you set aside x per year for a set amount of years during accumulation period... and want to consume y per year during retirement. i can simulate probability you run out of $ in retirement based on randomness of portfolio return and also randomness in how long you live. if you want to see what you need to retire, with randomness in portfolio return plus randomness in your life... i have some great Matlab functions. run 1,000 simulations and take the average. Let me know if you guys are interested. i'll help you out for free.

if you buy longevity insurance (very cheap) or life annuity, your probability of running out of $ is 0 obviously. but i have some great programs if anyone wants some investing advice.

[Zesty41]

WOW I love this it works way better then what I had

I am still working on it hope to have it fully dome soon.


thankz for this

[HelloSbrLosers]

So all this comes up with stats from the past. But you cannot possibly create a system based on past results and trends, its a recipe for failure. Stats/trends don't make the game. The game makes stats and trends. It is unbelievable how many cappers and bettors use trends and stats to decide a pick!

[a4u2fear]

So all this comes up with stats from the past. But you cannot possibly create a system based on past results and trends, its a recipe for failure. Stats/trends don't make the game. The game makes stats and trends. It is unbelievable how many cappers and bettors use trends and stats to decide a pick!

Actually it has nothing to do with developing trends. What I did is create a way to estimate a teams points per game not using historical data, then see how it tested in prior years using all of these old games and results. Otherwise you're just aimlessly attempting to cap a game which it appears is your motto.

[nash13]

a4u2fear i send you a message about data scrapping.

[vitruvian_nhlmlb]

I know this is an old thread, but I wanted to bump it real quick. I can't send a pm to you until 20 posts and I don' tknow how long that'll take for me. So, I will just put the pm here that I had until it told me I couldn't send one........

Hello a4u2fear,

I am new to the sbrforum, but I have been looking at the website for a long time now. I came across something you did a year ago:

http://forum.sbrforum.com/hockey-betting/2207848-anyone-interested-pulling-historical-results-odds-sbr.html

I am really interested in this. I have no excel knowledge whatsover and when I tried doing your step by step, I kept getting an error. I was wondering if you would be able to send me anything that I could use of yours?

To be more specific: The image you posted is almost exactly what I want for this years NHL (2013-2014 season) and I want to use the general concept for the upcoming MLB season as well. Would you be able to send me an excel spreadsheet that you did from this day that is in the picture?

If not and you would be able to make one quickly for me, then I would greatly appreciate it. This is all I want in it...

For Hockey
Date, Away team, Away ML odds, Away PL odds, over streak, last 10 games, avg. g for, avg. g ag, days rest, 1p, 2p, 3p, ot, total, Home team, Home ML odds, Home PL odds, over streak, last 10 games, avg. g for, avg. g ag, days rest, 1p, 2p, 3p, ot, total

For Baseball
Date, Away team, Away ML odds, Away RL odds, over streak, last 10 games, avg. r for, avg. r ag, days rest, 1inn, 2inn, 3inn, 4inn, 5inn, 6inn, 7inn, 8inn, 9, inn, 10inn...., total, home team, Home ML odds, Home RL odds, over streak, last 10 games, avg. r for, avg. r ag, days rest, 1inn, 2inn, 3inn, 4inn, 5inn, 6inn, 7inn, 8inn, 9, inn, 10inn...., total,

I know it is probably a lot to ask for, but I literally have no idea how to do some of this stuff and I was hoping that maybe you already had a spreadsheet that you were able to work off of real quick and may be able to spread it around.

I would greatly appreciate it and if you cannot help me then no worries. Just thought I'd ask.

Thanks

[statnerds]

mods can we get this moved to handicapper think tank? I think it's better suited there.

Great work. I applaud your effort. HTT has been lacking for some time, but hopefully this serves as an indication that better days are ahead. also don't know i missed this thread for an entire year!

[statnerds]

Stats/trends don't make the game. The game makes stats and trends.

ummm, what makes the line then?

[a4u2fear]

Vit, are you looking for just a template or everything filled in? I've never wanted to really share my whole data package as it took me years to create.

i will start a new excel thread this weekend. What error are you getting?

i will have some time this weekend

[b_rad_1983]

I'm very interested in this, but am unable to send you a pm with my post count!

[vitruvian_nhlmlb]

Vit, are you looking for just a template or everything filled in? I've never wanted to really share my whole data package as it took me years to create.

i will start a new excel thread this weekend. What error are you getting?

i will have some time this weekend

I guess what I am looking for is a sheet that looks something like yours posted here with some of my alterations that I already posted before. From there, I need to figure out how to have everything automatically filled in from a day to day basis. I don't know enough commands to be able to do that and I am sure that its possible.

For instance, if I want a simple sheet of Team A, Team B, Box score, O/U9, O/Ufirst5. Then, in the rows below it have it automatically filled for each team or I could do each day and then have those drop down menus to select certain teams. if that doesn't make sense let me know. Thank you for responding so quickly though and showing interest. I appreciate it.

[b_rad_1983]

Iv seem to run into an error...


Run time error '13":
Type mismatch

when I click debug it brings me to this line

'Need to find counter where data is not corrupt
Do While CodeStart < LastGameNHLData
If Mid(Sheets("NHLData").Cells(CodeStart + 1, 1), 1, 2) = "+1" Or Mid(Sheets("NHLData").Cells(CodeStart + 1, 1), 1, 2) = "-1" And Len(Sheets("NHLData").Cells(CodeStart + 1, 1)) > 4 Then
bb = 0

[a4u2fear]

b_rad. The type mismatch occurs for example when you are using a variable as an integer, but it is say a type string or empty.

What I "think" happened, is after you imported your webpage, either CodeStart or LastGameNHLData is not the value it should be (integer). If CodeStart is not an integer and you use it inside of Cells(), you will get an error because Cells() is looking for (integer,integer). One you click debug when this error comes up, place your cursor over CodeStart, LastGameNHLData and see what their values are. My guess is at least one is not an integer.