An introduction to research

Originally Posted by CrimsonQueen

Ok... now I've tried to do this with Statfox's CBB ... but I can't get anything to show up in my tables. How do you determine "WebTables = "4,7,8""
I feel like this is where the major problem is going to come into play as not all the teams have the same format in data... but even still, I can't get any numbers to show up, but the headers of rows will show up " #G SU W-L ATS W-L O/U/P Over % Under % PF PA Margin "

But no numbers. Any help?

For each sport you should record a macro importing the data then run a loop around it. To help you out though here is the table extracting code for Statfox NCAAB

...
Sheets(sht).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.statfox.com/cbb/cbbteam~teamid~" & sht & "~season~" & Datenum & "~log~1.htm", _
Destination:=Range("$A$1"))
.Name = sht & Datenum
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6,7,9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With Range("A6", Cells(Rows.Count, 1).End(xlUp))
.SpecialCells(xlCellTypeBlanks, xlTextValues).EntireRow.Delete
End With
...

I put in bold the snippets that were changed. The previous NFL macro accidentally stripped the headers from the columns in each sheet (to fix for NFL change 'With Range("A2")'... to 'With Range("A3")') this will keep the headers.

Sir, you are my hero.
If you have any other wisdom to share on building databases and models, PLEASE share it!

Can you explain how you determine what webtables to use? Because the 2008-2009 seasons don't work right due to the table being in a different spot.
Thanks again!
EDIT: I guessed a number of times and eventually figured out it was table 13,14 that I needed. But there has to be a way you can figure it out without all the guesswork, no?

There certainly are ways to isolate the table you want without trial and error, I suggest to google the DOM method of website data extraction, which essentially allows you to look for text inside the websites html code itself and select the respective table for which it lies within. Then you can run a loop around the code like was done previous

Mine worked fine, but I built the tables a few weeks ago and each sub-site of statfox used the exact same table format for each sport. Maybe it was changed for whatever reason

Like I said import a table through the Import web table service in excel and find the correct table numbers that way, then run the loop

This is some cool information. Thanks a lot.

id have to say its all about the WNBA

gotta love python! i've been using a script i wrote for odds and live game scores with BeautifulSoup. Well done on posting yours up here.

What about that model? Since the OP decided not to pursue this thread lets see if we can move it forward a bit. This is after all the Think Tank so lets think about it. I'm currently building an NBA model. I have built two other successful models in other fields of endeavor so I have a little experience that I can share. I built my first model over 20+ years ago using Lotus 123. If you know what that is your getting old like me. Today I use Perl and Mysql.
OK, lets assume everyone now knows how to get the data they want and load it into Excel or Mysql or whatever. What do we do now?
First, a caveat. Information that is readily available and fairly easy to obtain is NOT profitable. If you think that running a few macros or Excel functions in your favorite sport will give you long term profit your going to be disappointed. If it were that easy we would all be wealthy. The program I have been writing for the last 2 months is already over 1500 lines of code and its not finished. It also is currently not profitable returning only about 53% win percentage ATS. Of course, I have just recently brought it to the point where I can backtest it.
It really is about re-inventing the wheel to a large extent. You have to build the databases and write the programs so you can discover those "gems" that the masses do not have. You will spend as much time thinking about your model as building it. You have to look at the information that everyone else has in a new light. It is doable, but it ain't easy.
It is commonly understood that only 2 - 3% of gamblers are successful long term. Now you know why. If any of you are interested in pursuing this further, lets do it. If not have a nice day.

Originally Posted by neila

You have to build the databases and write the programs so you can discover those "gems" that the masses do not have. You will spend as much time thinking about your model as building it. You have to look at the information that everyone else has in a new light. It is doable, but it ain't easy.
It is commonly understood that only 2 - 3% of gamblers are successful long term. Now you know why. If any of you are interested in pursuing this further, lets do it. If not have a nice day.

OK, whats keeping the masses from reading this post?

2-3% is currently too high a number, pros are leaving the biz because it has become much less profitable due to lack of square money to inflate the market.

Originally Posted by Wrecktangle

OK, whats keeping the masses from reading this post?.

Absolutely nothing. But, when you consider the difficulty and time involved to gain a long term edge, I think you can safely eliminate the vast majority.

2-3% is currently too high a number, pros are leaving the biz because it has become much less profitable due to lack of square money to inflate the market.

Good. That should increase the ratio of squares to sharps. By the way where are all the pros going? Are they becoming plumbers?

Originally Posted by neila

Good. That should increase the ratio of squares to sharps. By the way where are all the pros going? Are they becoming plumbers?

Those fvkkers are becoming the homeless on the streets of San Diego. Can't go downtown without falling over these worthless shiits hitting you up for money/drugs/women.

Great info thx a loot

I tried running the visual basic/excel macro code above but it didn't work for me, it created all the sheets with the teams and the years but they are all empty. Maybe this weekend I will try again -- I have order the Visual Basic for Dummies book so maybe that will help too! I didn't realize you could scrape with Excel, thanks for the info UVA -- now I just hope I can get it to work

Originally Posted by pedro803

I tried running the visual basic/excel macro code above but it didn't work for me, it created all the sheets with the teams and the years but they are all empty. Maybe this weekend I will try again -- I have order the Visual Basic for Dummies book so maybe that will help too! I didn't realize you could scrape with Excel, thanks for the info UVA -- now I just hope I can get it to work

stick a comment,', before the "On Error Resume Next" sequence, then post the error message, if any

it could be merely statfox being offline, or a bad internet connection

I got blank tabs the first time too, I think cuz I named the column the wrong thing. double check everything, that's what I did and it worked the second time