Grabbing Pinnacle Lines with Excel and DOM

[uva3021]

DOM (Document Object Model) is one of the many HTML/XML to programming language routines that use a common toolbox for which to call its properties. Fortunately for us, Visual Basic, Java, PHP, etc… all integrate these toolbox routines in order to grab and manipulate information given a particular XML/HTML source. For a general information scraper, its one of the more expansive and user-friendly, though with simplicity comes limitations. However, such limitations pose very little concern for extracting basic information from a website. DOM doesn’t necessarily promote dynamic streaming information parsing, because DOM typically requires the document source to be loaded in complete form into memory. If you think of DOM as a branching tree, with each element a branch, or “child”, or its root, or “parent”, then its obvious why the information must be in some way stored in memory before the various DOM elements can be called.

DOM can be more useful in extracting box scores or season statistics. Such data is in a complete state and does not require any other web and programming integration to grab information that is changing, as opposed to line movement or live game updates.

Below is the code for extracting NCAA lines and matchups via Pinnacle’s XML feed using Excel. Excel’s VBA Modules allows these DOM properties to be implemented, after referencing the relevant toolbox routines. So before accessing the DOM methods, create a reference to Microsoft XML, v6.0



Pinnacle’s full XML feed is located at:

http://xml.pinnaclesports.com/pinnacleFeed.aspx?

and they are kind enough to offer some assistance on how to get different lines from the different sports offered.



The URL parameters are given above, so to access solely the lines for NCAA football, use the following parameters:

http://xml.pinnaclesports.com/pinnaclefeed.aspx?sporttype=Football&sportsubtype=NCAA

XML files are configured, as mentioned before, as subsequent branches from a tree, whose root is the document source. The DOM properties to access each branch are in a rather English familial vernacular, to make it human-friendly. For example, the following XML sequence:

 <game>
<date>10/23</date>
<hometeam>USC</hometeam>
<roadeam>Oregon</roadteam>
</game>

Contains the “game” element, or node, as the parent to its three children, or child node, “date”, “hometeam”, and “roadteam”. The latter three are designated as siblings.

Each element in the document is numbered sequentially from an initial value of zero. Therefore to grab just the home team from above, its DOM properties translate to the second child node, childnode(1), of the parent node “game”.

The VB code is honestly rather self explanatory from there. Some trial and error and a google search or two is needed in order to find the right properties and naming conventions to fit the programming language. Excel is user-friendly enough to provide all the properties available for the particular function required.

Sub XML_Pinny()
    Dim xmlLoad As New MSXML2.DOMDocument
    Dim allevents As IXMLDOMNode
    Dim eventslen As Integer
    Dim events As IXMLDOMNode
    Dim XMLHttpRequest As MSXML2.xmlhttp
    Dim i As Integer
    Dim URL As String
 
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    On Error Resume Next
 
    URL = "http://xml.pinnaclesports.com/pinnaclefeed.aspx?sporttype=Football&sportsubtype=NCAA"

    Set XMLHttpRequest = New MSXML2.xmlhttp
    XMLHttpRequest.Open "GET", URL, False
    XMLHttpRequest.send
    Set xmlLoad = New MSXML2.DOMDocument

    Do Until xmlLoad.readyState = 4
    Loop
    xmlLoad.LoadXML (XMLHttpRequest.responseText)
 
    Sheets("Sheet1").Range("A1").Value = xmlLoad.DocumentElement.ChildNodes(0).FirstChild.Text
 
    Set allevents = xmlLoad.DocumentElement.ChildNodes(3)
 
    eventslen = allevents.ChildNodes.Length
    Sheets("Sheet1").Range("B1").Value = eventslen
 
    i = 0
    For i = 0 To (eventslen - 1) Step 1
        Set events = allevents.ChildNodes(i)
        Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1) = events.FirstChild.Text
        Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp)(2, 1) = events.ChildNodes(5).ChildNodes(0).FirstChild.Text
        Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp)(2, 1) = events.ChildNodes(5).ChildNodes(1).FirstChild.Text
        Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp)(2, 1) = events.LastChild.FirstChild.SelectSingleNode("spread").FirstChild.Text & " " & events.LastChild.FirstChild.SelectSingleNode("spread").ChildNodes(1).Text
        Sheets("Sheet1").Cells(Rows.Count, 5).End(xlUp)(2, 1) = events.LastChild.FirstChild.SelectSingleNode("spread").ChildNodes(2).Text & " " & events.LastChild.FirstChild.SelectSingleNode("spread").ChildNodes(3).Text
    Next i
 
Set xmlLoad = Nothing
Set allevents = Nothing
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 
'Application.OnTime Now + TimeValue("00:02:00"), "XML_Pinny"
End Sub

The lines:

 

Do Until xmlLoad.readyState = 4
    Loop

Simply means do not continue until the document is completely loaded into memory (readyState = 4).

Remove the comment from the final line to run the program every two minutes.

The final result should look something like this:

[Romanowski]

<tup>

[uva3021]

Been getting a lot of PMs over at sbr about this, bumperoo for relevance

Post here if you want the MLB version, though with the provided instructions you should be able to do it yourself

[Pancho]

I will take you up on your offer, for the MLB version.

Thank you in advance

Pancho

[uva3021]

Unzip the file, enable macros, Click the "GET" button

[Pancho]

Thank you UV, works great

[carl5793]

When I click the button it says the macro can't run. Ideas?

[uva3021]

Originally Posted by carl5793

When I click the button it says the macro can't run. Ideas?

What exactly does the error message say? It may be a security issue, check your security options and make sure macros are enabled.

[carl5793]

I checked the security, it should be fine. "Cannot run the macro 'MLB_Pinnacle.xlsm!Button_3Click'." The macro may not be available in this workbook or all macros may be disabled (which they aren't).
But when I go to Macros and run XML_Pinny it works

[carl5793]

Nevermind figured it out. Thanks!

[AuntPeg]

I'll take you up on your offer, for the MLB version.
Thanks foe advice

[except]

Originally Posted by uva3021

Been getting a lot of PMs over at sbr about this, bumperoo for relevance

Post here if you want the MLB version, though with the provided instructions you should be able to do it yourself

Any recommendations that are Mac friendly? Excel for Mac 2011 (with vba support) but no MS XML availability.

[uva3021]

Originally Posted by except

Any recommendations that are Mac friendly? Excel for Mac 2011 (with vba support) but no MS XML availability.

i'll work on one for javascript

[ichigoFAN1]

Originally Posted by uva3021

i'll work on one for javascript

hi, uva did you look into a mac friendly version? thanks

[uva3021]

Originally Posted by ichigoFAN1

hi, uva did you look into a mac friendly version? thanks

here is a php version to screen scrape MLB odds, it outputs in a plain html style page with comma delimited fields

in order to run you need to have server space, or just download xampp and run a virtual server from your computer

if you want to have server space, signing up for a quick blogspot page might actually be worth the trouble just to have the ability to run local php files

PHP Code:

<?php

$raw = file_get_contents("http://www.pinnaclesports.com/League/Baseball/MLB/1/Lines.aspx");

$newlines = array("\t","\n","\r","\x20\x20","\0","\x0B");

$content = str_replace($newlines, "", html_entity_decode($raw));

$start = strpos($content,'<table class="linesTbl" cellspacing="1" border="0">');

$end = strpos($content,'</table>',$start);

$table = substr($content,$start,$end-$start);

preg_match_all("|<tr(.*)</tr>|U",$table,$rows);
//$handle = fopen('pinnybaseballtxt.txt', 'w');
//echo "<table>";
foreach ($rows[0] as $row){

    if ((strpos($row,'<h4')===false)&&(strpos($row,'colspan')===false)){
   
        preg_match_all("|<td(.*)</td>|U",$row,$cells);
       
        $date = strip_tags($cells[0][0]);
       
        $rot = strip_tags($cells[0][1]);
       
        $team = strip_tags($cells[0][2],"<BR /><EM>");
        $teamandname = str_replace("<EM>", ", ", $team);
        
        $rl = strip_tags($cells[0][4]);
        
        $ml = strip_tags($cells[0][5]);
        
        $total = strip_tags($cells[0][6]);
        $tline = substr($total, -4);
        $tnumber = substr($total, 0, -5);
        
        echo "{$date}, {$rot}, {$teamandname}, {$rl}, {$ml}, {$total} <br>\n";
        //echo "<tr><td>{$date}</td><td>{$rot}</td><td>{$teamandname}</td><td>{$rl}</td><td>{$ml}</td><td>{$tnumber}</td><td>{$tline}</td></tr> <br>\n";
        //$pinnylines = "{$date}, {$rot}, {$teamandname}, {$rl}, {$ml}, {$total} <br>\n";
        //fwrite($handle, $pinnylines);
        //fclose ($handle);
        
    }
}
?>