Idiotic Fantasy Football Question

[CBSVOL]

I thought about making an attempt at veiling or hiding the true intention of this question with some business or industry example - just to make it at least appear to be less trivial, but I will stick with honesty as to why this question is being raised. Simply, I do not have a big presentation next week that involves delving into 28 years of agricultural soybean production numbers, there is no national sales meeting that I "have" to get some help from the gurus of the board in order to be prepared to present information, etc. etc. etc. - this question arises from my desire to better enjoy my hobby. No more, no less. Just wanted to be forthright at the outset.

I, like millions of other people across the country obviously possess far too much leisure time, compete in a fantasy football league.

Each week, every member of our league goes through the tedious task of accessing both the individual and team statistics for each member of their fantasy team through a process of reviewing the Gamebook for every NFL game for that particular week.(the "Gamebook" is a statistical summary page that accompanies the conclusion of each NFL game). Once the appropriate statstics are found, they are then manually entered into a spreadsheet which automatically calculates the points awarded to each player / team, as well as the overall points won by the team as a whole.

My basic question is this: Can some portion of accessing and recording this information be automated? Is there a way to simply run a macro that would search out a pre-determined website, and grab some or all of the data that was needed, while excluding other information that was not?

For example:

I would want the macro to automatically go to the NFL's website, and specifically to those/that page(s) containing statistical game information (i.e. Gamebook) from one week to the next. An example of a Gamebook can be found at:

http://www.nfl.com/gamecenter/gameb...n example of a player page can be found at: http://www.nfl.com/players/playerpage/12531/gamelogs/2006

I know that this is not the atypical question for the message board, but any ideas would be most appreciated in the matter of this small and seemingly inconsequential area of our fantasy lives.

 

[Jonmo1]

I thought this was going to be difficult, but it was actually pretty easy.

run macro on a blank sheet for testing purposes first.

Sub Macro1() 

ActiveSheet.Cells.ClearContents 
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "URL;http://www.nfl.com/gamecenter/gamebook/NFL_" & InputBox("date formatted yyyymmdd") & "_" & InputBox("opponents formatted SF@SEA"), Destination _ 
        :=Range("A1")) 
        .Name = "NFL_20061214_SF@SEA_1" 
        .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

 

[the magician]

Quick follow up- if I want to join the millions of other people across the country who obviously possess far too much leisure time, do I have to compete in a fantasy football league?

Thanks in advance.

 

[Jonmo1]

I think being called a Board Regular on MrExcel.com qualifies you by default.

 

[the magician]

PLAY BALL!!!

 

[CBSVOL]

Response to Follow-Up

Nope, an excess of leisure time doesn't positively correlate to participation in a fantasy football league. Like many others, you could use that time to consume an even greater quantity of both Funyuns and Yoo-Hoo, or simply continue to concentrate on breathing through your mouth.

Either / or, whatever seems to fill the void.

 

[CBSVOL]

The macro worked beautifully, and pulled the data in just as it appeared on the site.

If too much time and energy has not been expended on this nonsense, at least to this point, is there a way of adapting the macro to selectively pull certain information from the site, while excluding other things?

Could it be adapted to pull multiple pages of information at once?

Could it be adapted to pull information from other site(s) as well?

What you have already provided has helped immensely, while no further replies are expected for such a trivial thing as this, they would be very appreciated.

Thanks for taking the time to help solve this for me!

 

[Jonmo1]

what you can do is just play around with it.

after running macro first time
right click on the sheet anywhere and click "Edit Query"

It should show the web page in a new smaller window with a bunch of small CheckBoxes...

When I recorded the macro, I checked the very top left one that imported the whole page.

you can play around with it by checking/unchecking whatever boxes you want until you get the info you need.

Once you figure out which ones you need, record yourself doing it using the macro recorder - tools - macros - record new macro.
look at the new macro
you should end up with a line in the new macro that looks something like

.WebTables = "4,7,8,9,10,11,16,17"

copy that line into the original macro in the same location it was in the new macro you recorded.
AND remove the line that says

.WebSelectionType = xlEntirePage

 

[CBSVOL]

Easily, one of the coolest things I have ever seen Excel perform. Thanks for not only taking the time to help me with this, but for the equally excellent step-by-step instructions as to how to implement it.

Many, many thanks to each of you for helping out with this!

 

[Jonmo1]

I was having fun with this one, you can make it so that it will pull specific games based on info you enter on another sheet.


I made a table with some abbreviations for a few teams. Both Home and Away.

name your sheets "Query" and "Input"

Leave the Query sheet blank, that's where the data will go

in the "Input" sheet

Collumn A is blank, for user input
Collumn B is for the Home Team (list all teams in this column)
collumn c is blank for user input
collumn d is for the AWAY Team (list all teams in this column).

in E2 User can enter the date of the game (in the required format 20061214)

in G1 entered this formula

=E2&"_"&VLOOKUP("X",C:D,2,FALSE)&"@"&VLOOKUP("X",A:B,2,FALSE)

now put an X to the left of the HOME team in collumn A
and put an X to the left of the AWAY team in collumn C
enter the date of the game in E2

now you can use this macro to retrieve that game to another sheet based on that info.

again, the line that says
.WebTables = "4,7,8,9,10,11,16,17"
is the line for you to adjust to get specific parts of the web page.

Sub Macro1()
Sheets("Query").Cells.ClearContents
    With Sheets("Query").QueryTables.Add(Connection:= _
        "URL;http://www.nfl.com/gamecenter/gamebook/NFL_" & Sheets("Input").[g1], Destination _
        :=Sheets("Query").Range("A1"))
        .Name = "NFL_20061214_SF@SEA_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4,7,8,9,10,11,16,17"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub