SBR Top-Rated Sportsbooks
Using Excel to import and filter all Pinnacle lines by sport/league
uva3021's work on grabbing Pinnacle's XML feed with Excel gave me an idea to take this a step further by building a workbook that allows the user to create a table with odds for any sport/league that Pinnacle offers odds for and displaying it in a customized format e.g. depending on the sport or event. Since Pinnacle explicitly allows anyone to access their XML feed once every 60 seconds, I figured too much harm can't be done by sharing this. Also, if you were to change the settings to import it more often, I am pretty sure your IP would eventually get blocked by Pinny. Obviously there are ways around that too but my point is that as long as everything in the workbook is left the way I set it up, no rules (explicit or implicit) are likely to be broken.
My goal was to get a working solution, not the prettiest, and in its current version it works for me at least and maybe it can be useful for someone else.
I wouldn't advice using the code I created as a proper way of writing VBA since my focus was on getting something working quickly. When that failed and the task got infinitely more complicated than I envisioned initially, instead of re-writing much of the code, I added some patches here and there to make it work. Hence, the code is very likely not the most efficient way (I know it isn't) but it works fast enough and is flexible enough for me.
I hadn't worked with XML before and I quickly got tired of trying to parse XML the way I assume it was intended. Instead I am simply looping through all the nodes and dumping the resulting columns of data into an Excel sheet and take if from there since I know I can get the data to dance pretty well once I have it in Excel.
If you decide to have a look at it, I suggest that you first read the sheet called "Instructions" (go figure) since it should tell you most of what you need to know to use the workbook. Actually, it tells you a whole lot more than you need to know but I included the extra info in case some of you felt like actually creating your own customized formatting for how the odds are displayed in the workbook.
Definitely works in Excel 2010, works in my version of Excel 2007 and probably works in Excel 2003. If you encounter any problems in running the macros (after having checked the references I list in the Instructions sheet) please let me know. Also, if it seems to run ok but the data looks incorrect I would definitely like to know. I have tested it a fair bit myself but not on all sports or all leagues so there might be some league specific issues that would prevent the data from displaying properly.
Pinny says in the xml feed instructions that one can either load the entire xml or only the odds that have changed since last time. The main xml feed is only updated once every 10 mins, which is why one might not see any updated odds if re-importing the odds more frequently than that.
I added the feature of importing only the changed odds. In the sheet called "User input", select "Full" to import all odds or "Incremental" to only import the odds that were changed since last time. Keep in mind that if you choose "Incremental", odds that were not updated will not show at all so it will appear that some available odds are missing.
JohnAnthony gave thom321 2 SBR Point(s) for this post.
Reply With Quote
Originally Posted by Greenei
