Excel Macro for NFL Data

[uva3021]

Thought I posted this but obviously I haven't.

Web developers and programming veterans often consider visual basic and excel to be amateur. But I find it to be extremely powerful, and those that are inclined to deviate from what is used by the consensus for spite deprive themselves of one of the more considerably tractable programs available.

Here is the excel macro VB code to get NFL data from Statfox

Spoiler:

Sub NFLfromStatfox()
'
' Get NFL Date from Statfox -- Enter specified years in the appropriate variable definitions
'

'
On Error Resume Next
Dim Datenum As Integer
Dim Datestart As Integer
Dim sht As String
Dim i As Integer
Dim n As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Datenum = 2009 'This corresponds to the desired starting data that fits the StatFox linking structure
Datestart = 2000
For n = 0 To (Datenum - Datestart)

For i = 1 To Range("NFLteams").Rows.Count 'NFLteams refers to the Range of teams labeled in the workbook
sht = Range("NFLteams").Cells(i, 1).Value
Worksheets.Add().Name = sht & Datenum
Sheets(sht).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.statfox.com/nfl/gamelog~season~" & Datenum & "~teamid~" & sht & ".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 = "4,7,8"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With Range("A2", Cells(Rows.Count, 1).End(xlUp))
.SpecialCells(xlCellTypeBlanks, xlTextValues).EntireRow.Delete
End With
Next i
Datenum = Datenum - 1 'Count down or Count up from a given date
Next n
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

What you need to do before running is create a table with the names of each team according to the parameters that correspond to the web address, i.e. San Diego = San+Diego, Giants = NY+Giants

You can speed up the process by importing a team report or statistics table from the website and find all '" "' and replace with '+' (Find spaces and replace with +), go here and copy and paste or the more efficient way is to import through excel web service browser

NFL Preseason - Team Report

Then select all the teams in the table and define a name to the range, a brief survey of the code and one can see I named the range "NFLTeams"

That's it, run the code. Takes a while but gets the job done. The workbook will contain however many years worth of sheets for each team, all named with the same format, team and year (Arizona2009, Washington2004, NY+Jets2007, etc...)

All impertinent and superfluous data will be stripped, and if you scan the code you can probably figure out which command is used to strip the data.

Each sheet will have the date, opponent, score, line, total, ats results, total results, offense/defense - rushing yds, rypa, passing yards, pypc

A great way to learn VB is to record or macro and see the result, and use google

[FederlineYo]

WoW I just got major wood!!! Where Brit????? Great macro UVA!! Thanks !!!

[Seanie Mac]

UVA always giving guys wood

[Gfym_3kDeep]

I like to consider myself geeky, but UVA you take the cake. Take that as a compliment please.

[Night_Owl_135]

I have been looking for a long time for this kind of spreadsheet. Has anyone created such sheet that cares to share

Thanks