Visual Basic | The Overround

Excel Macro for NFL Data

Posted by Rufio Magillicutty in Excel, Featurific, NFL, Visual Basic on August 9, 2010

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.

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 a macro and see the result, and use google

code, database, development, Excel, maco, national football league, NFL, programming, vb, visual basic

No Comments

MVP and Cy Young Odds Evaluation Process

Posted by Rufio Magillicutty in Featurific, MLB, Visual Basic on July 28, 2010

I decided to take a look at the MVP and Cy Young odds. So in doing so, I felt it appropriate to utilize the excel macro framework uncovered in my college football and subsequently line movement survey posts. Both required some form of data extraction process, using different sites but implementing similar programming methodology.

The imminent site for baseball awards and statistics is, as one knows, baseball-reference.

I haven’t analyzed the numbers yet, but thought it beneficial to share the extraction code for those inclined to undergo some form of related odds evaluation process.

Sub MVP_CyYoung_BRef()
'
' This Macro extracts the descriptive info of each specified awards year for Major League Baseball
'

'
On Error Resume Next
Dim awardyear As Integer
Dim sht As String
Dim i As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
'Enter the most recent year and earliest year for which to extract information next to the respective
' assigned variables

awardyear = 2009
awardyearend = 2000
'Beginning Extraction Loop
For i = 0 To (awardyear - awardyearend)
 
'Create the new sheets named by the appropriate year

sht = Str(awardyear)
Worksheets.Add().Name = sht
Sheets(sht).Select
 
'Data extraction cheat
'The excel browser points to the awards page on <a href="http://Baseball-reference.com">Baseball-reference.com</a> and isolates the statistic tables

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;<a href="http://www.baseball-reference.com/awards/awards">http://www.baseball-reference.com/awards/awards</a>_" &amp; awardyear &amp; ".shtml", Destination _
        :=Range("$A$1"))
        .Name = "awards_" &amp; awardyear &amp; ".shtml"
        .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 = _
        """AL_MVP_voting"",""NL_MVP_voting"",""AL_Cy_Young_voting"",""NL_Cy_Young_voting"",""AL_Rookie_of_the_Year_voting"",""NL_Rookie_of_the_Year_voting"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
 
'End Data Extraction Process

    End With
 
'Subtract one year from the most recent to redefine the value of the variable

awardyear = awardyear - 1
 
'End Loop

Next i
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
End Sub

Here is the excel workbook with 2000-2009 data already in place with sheets labeled according to the date. Ten years of data seems optimal enough for me. From my standpoint, further experiments in finding fair-value odds on these MVP and Cy Young futures would require including the future odds themselves.

MVP+CyYoung

At length I’ll post the results.

baseball, baseball-ref, baseball-reference, bet, code, code highlighter, cy young, data, data extraction, development, Excel, extracting, future, Futures, macro, MLB, MVP, Odds, scraping, sports, sports betting, Statistics, syntax, visual basic, web

No Comments

The Overround

Archive for category Visual Basic

Excel Macro for NFL Data

MVP and Cy Young Odds Evaluation Process

Record

Recent Posts

Follow us on Twitter

Recent Comments

Meta

Categories

Blogroll

Contact us