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]

Thanks not needed, it was fun.

but you're welcome nonetheless.

 

[Jonmo1]

OK, somebody stop me, I'm having too much fun with this one.

now, the macro will create a new sheet with the game data and name the tab after the game's name.
you don't need the sheet named "Query" anymore

Sub Macro1()
For Each Sheet In Worksheets
If Sheet.Name = Sheets("Input").[g1].Value Then
    MsgBox ("That game has already been downloaded")
    Exit Sub
End If

Next Sheet
Sheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.nfl.com/gamecenter/gamebook/NFL_" & Sheets("Input").[g1], 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
        .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
    
    ActiveSheet.Name = Sheets("Input").[g1]
End Sub

 

[CBSVOL]

Really cool! While you are on a roll......

Could I use this to somehow pull multiple Gamebooks at once?

Before I sap the life out of you with my incessant questions, here is what I am attempting to do:

1. Be able to pull information off of the Gamebook, directly into excel. (Can now do, thanks to your help)
2. Select that player / team information which is required to score our games, and exclude everything else. (Can do this, and although it is beyond anyone's control, would like to further reduce what has to be brought in - I want to know the total yards for each team, but not first downs, etc., but it is encompassed in the same "block" of information)
3. Place that scoring information into a "smooth" format that will allow me to place the team / players respective statistics into a pre-formatted (i.e. known) cell on my spreadsheet, and listed alphabetically.
4. This would allow each member of the league would only need to look at a single worksheet to scroll down to see the statistics of all players / teams for that week - rather than having to access 16 some odd Gamebooks for every player on both their roster, as well as the oppositions.
5. Once the player / team stats are in a known field, I could then add an equation which scores each positional statistic (for example: you get one point for every ten rushing yards, 1 point for every solo tackle, etc.) as it is listed in the field.

I know that this is far beyond what a normal person should be attempting to do for a normal hobby, but the more automated the process of finding stats can be, the better.

While we are talking about the Gamebook - perhaps it really would be easier to grab the game data from the player page (the team stats are few, and wouldn't be that much trouble to manually grab from the Gamebook).

The player pages have the total player stats from each game in one linear format that could be accessed based on several factors other than opponent or date.

Here's a URL for a player page:
http://www.nfl.com/players/playerpage/553379/gamelogs/2006

In this instance, the only thing that separates one player's game logs from another is that uniquely identifying number between "playerpage" and "gamelog" - the rest of the data is static (unless you wanted to change the year at the end, but I wouldn't necessarily need to do that).

I think that this could be easier to pull multiple information en masse, simply because you could be able to list a range of player pages to pull data from (i.e. list 1-1000000, rather than a particular game on a particular date).

Everything that you have done to this point has been tremendously helpful, and has accelerated what I am attempting to figure out by several hundred years, so please only respond / offer other assistance in the event that it is no trouble or bother to do so.

I am also more than willing to send you a sample file of our scoring sheet, as well as the format that I envision having everything in, if you think that it would help you to visualize what I am rambling about.

 

[the magician]

And if you can get it to pick tonight's Powerball number, I can truly join the ranks. Otherwise, I'm back to work again tomorrow.

 

[Jonmo1]

Well, I've gotten two tabs, one for a player query, and one for a game query. I can email you the file if you like, and you can do with it as you please. Getting the data in the sheet I had fun doing, manipulating that data is gonna be left up to you. That smacks of effort.



send me a PM with your email address, I'll send you what I've got so far.

 

[CBSVOL]

Come on, Magic Man - who surfs internet message boards to basically interject unwarranted, unwelcomed and unwitty quips tinged with sarcasm? From your picture, you not only look like a nice person, but I like how your hands seem to be positioned to appear as if they are right out of an MC Escher sketch. Moreover, you show some sense of inner strength in having a notably shaved head. Of course, we all know this to be the number one maneuver used in combatting male-patterned baldness - but you pull it off nonetheless.

You've got a lot going for you, so don't be that "interjecting dim-witted comments on the message board" guy.

Unless moving your eyes while reading this thread somehow directly provides the necessary amperage to power your life support system, I welcome you to discontinue doing so at any point hereafter.

I'm just trying to get some help on some excel issues - not banter back and forth with someone who clearly exceeds my meager capacity in both good looks and common sense.

I do wish you a safe and Happy Holidays, and will be in touch if I need you for anything else.

Thanks!

 

[CBSVOL]

Jommo - you have done more than enough, and I sincerely appreciate the help. I'll send the PM with my address, and I look forward to its receipt. I couldn't have gotten 1/10th of the way in the next 20 years without your help, so thank you very much again.

 

[the magician]

"unwarranted, unwelcomed and unwitty"
"sense of inner strength"
"dim-witted comments... guy"
"someone who clearly exceeds my meager capacity in both good looks and common sense. "

Ah, the passive-aggressive type, I see. My humble apologies for the intrusion.

(as el mago bows in supplication and backs out of the door)

Happy Holidays to you as well, and good luck in your endeavor. I take my leave.

 

[CBSVOL]

No harm, no foul.

Happy Holidays.