Using Excel to import and filter all Pinnacle lines by sport/league

[thom321]

uva's workbook 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. 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.

[uva3021]

This is very nice. I highly suggest everybody download this. Its understandable as well why you want to import the entire XML sheet first, otherwise you would just have to find the node properties for each sport individually. I agree no need to burden yourself with such an exhaustive task.

BTW this is far more organized than what I throw together, though I'm usually only concerned with functionality and not the aesthetic qualities of the coding.

[mvalentin]

K this is awesome thanks

best line "If your computer catches fire after running this workbook, it is not my fault, it is not my fault, in any such event, i suggest getting a new computer"

[subs]

Hi Thom321,

i tried to run this on my mac, guessed it wouldn't work... trouble is it's screwed up my excel. any ideas about how to restore everything?

sorry for the dumb question.

[thom321]

Sorry, I have never used a Mac so I have no idea what works/doesn't work on it.

I know that the workbook does NOT make any changes to any Excel settings. In Windows, if Excel crashed while running this workbook, which can happen with any workbook, when you restart Excel everything would look the same.

If you want a solution that works for Mac, I advice you to read uva's thread "Grabbing Pinnacle Lines with Excel and DOM" where he shows a php script he created to get odds from Pinnacle.

[subs]

thanks

excel freakin out on my mac lol. not sure wht's done it but it might also have been a site i tried to scrape just B4.

going to run ur scraper on windows <::> yea been checking UVA's thread too.

thanks to both of u

good luck

[marmon4270]

New to this site, been on other forums a while. Think I'm going to like it here. That spreadsheet is fantastic. Thanks a bunch.

[aa441995]

This is exactly what I needed, Thank You! I do have one question though, I open the worksheet, import the odds and everythign works fine. I then wait 5 minutes re-import the odds create the new table, but the new odds arent in. When I watch the status in the bottom left hand corner it flys past importing odds. Is there something I am doing wrong, or a settign I need to change? For now I just need to close the file and reopen.

[uva3021]

Your cache is interfering with the new data, in other words you are drawing data from the exact same source and to save time the excel procedures default to older data in recent memory. What you need to do is add the Pinnacle Feed Time to the end of the URL with "&last=XXXXXXXXXXX" where "X" is a sequence of numbers.

You can grab it with

xmlLoad.DocumentElement.ChildNodes(0).FirstChild.T ext

Where "xmlLoad" is the name of the defined Dom Document

[aa441995]

Thank you for the reply, I am novice at best what you stated makes sense its just a matter of my ability to do it that is killing me. I also only need one sport NCAA Basketball would that make matters easier?

[aa441995]

Originally Posted by uva3021

Your cache is interfering with the new data, in other words you are drawing data from the exact same source and to save time the excel procedures default to older data in recent memory. What you need to do is add the Pinnacle Feed Time to the end of the URL with "&last=XXXXXXXXXXX" where "X" is a sequence of numbers.

You can grab it with



Where "xmlLoad" is the name of the defined Dom Document

I'm really hitting a wall with this one, not sure if this is even appropriate or if the rate is even worth your time, but I would be willing to pay you say $30 to fix up my version of the above file. Let me know if this is something you would be interested in taking on.

[thom321]

aa441995,

Glad to hear someone is getting some benefit from my sheet and sorry for the late response. I haven't been active here in a while.

Do you still need it fixed? If you do, I can take a look at it. If uva's suggestion solves the issue (which past history tells me it does) it should be pretty easy for me to change it and upload an updated version here.

[thom321]

I checked my workbook and it seems the Pinny's xml URL had changed slightly so I updated it. 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 using "&last=xxxxxxxxxx" the way uva described it. 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 after 5 min.

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.