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

[thom321]

pringles,

I wasn't even aware Pinnacle had an API (e.g. to place bets) until I saw your question. I think it is a great initiative by Pinnacle and shows confidence in their ability to offer efficient odds. I don't have time to look into it further at this moment but if I do so in the future, I'll post something here.

Hey thom! I appreciate what you are doing!
Would it be possible for you to write a sample vba code to grab Pinnacles API using HTTP Basic Authentication?

[pringles]

pringles,

I wasn't even aware Pinnacle had an API (e.g. to place bets) until I saw your question. I think it is a great initiative by Pinnacle and shows confidence in their ability to offer efficient odds. I don't have time to look into it further at this moment but if I do so in the future, I'll post something here.

Sure, I hope you have time sometime.
Maybe you know where I could find some material on this (how to write VBA code to get just Pinnacles API at first ... parsing later if I succeed )

[Pondering]

I had this working for a long time but in the past few days I have gotten an error message when I click on Import Pinnacle Odds.


When I go to that web address I get the below screen. Is anyone else running into this issue? Is it just because their xml leagues is down?

[thom321]

I don't know if the league URL is down permanently. In case it is, I have updated the workbook to by default only retrieve leagues from the imported XML feed. I tested the new code and it appears to be working. However, it was so long ago since I originally built this that I don't remember if the separate import of leagues provided some necessary info. If the behavior of the updated workbook seems strange, let me know.

I had this working for a long time but in the past few days I have gotten an error message when I click on Import Pinnacle Odds.
[ATTACH]71675[/ATTACH]

When I go to that web address I get the below screen. Is anyone else running into this issue? Is it just because their xml leagues is down?

[ATTACH]71677[/ATTACH]

[Pondering]

Thanks so much that was quick!

[Vegas68]

Hi thom321,

I´m using your spreadsheet every day and it is wonderful.
But have some trouble since yesterday. Please can you have a look at it, if there are any updates necesarry.

I can also send you a few dollars via paypal for your efforts.

[Vegas68]

double posting

[thom321]

Vegas68,
sorry for the slow response but I haven't been on this forum for a while. I'll check the workbook tomorrow to see if I can fix it.

[thom321]

I just checked it and it appears to be working. Let me know if you still have any problems importing the data.

Vegas68,
sorry for the slow response but I haven't been on this forum for a while. I'll check the workbook tomorrow to see if I can fix it.

[Vegas68]

Hi thom,

yes today at the moment it comes again to this message while sorting data:
"Input array is not an array of simple data types"
So at the end the "Import Sheet" folder is empty!

This happens not every day!

Do you need more information from the debugger?

[Vegas68]

At this point comes the warning message today:

(modQSortInPlace)

'''''''''''''''''''''''''''''''''''''''' '''''''''''
' Ensure that InputArray is an array of simple data
' types, not other arrays or objects. This tests
' the data type of only the first element of
' InputArray. If InputArray is an array of Variants,
' subsequent data types may not be simple data types
' (e.g., they may be objects or other arrays), and
' this may cause QSortInPlace to fail on the StrComp
' operation.
'''''''''''''''''''''''''''''''''''''''' '''''''''''
If IsSimpleDataType(InputArray(LBound(Input Array))) = False Then
If NoAlerts = False Then
MsgBox "InputArray is not an array of simple data types."
RecursionLevel = RecursionLevel - 1
Exit Function
End If

[a4u2fear]

At this point comes the warning message today:

(modQSortInPlace)

'''''''''''''''''''''''''''''''''''''''' '''''''''''
' Ensure that InputArray is an array of simple data
' types, not other arrays or objects. This tests
' the data type of only the first element of
' InputArray. If InputArray is an array of Variants,
' subsequent data types may not be simple data types
' (e.g., they may be objects or other arrays), and
' this may cause QSortInPlace to fail on the StrComp
' operation.
'''''''''''''''''''''''''''''''''''''''' '''''''''''
If IsSimpleDataType(InputArray(LBound(Input Array))) = False Then
If NoAlerts = False Then
MsgBox "InputArray is not an array of simple data types."
RecursionLevel = RecursionLevel - 1
Exit Function
End If

spaghetti

[thom321]

Vegas68,

The error you are getting means that the imported data is not in the expected list format. I am sorry you are experiencing this but unfortunately I can't replicate it on my end. The import works when I try it which makes it hard to debug.

Could you attach a screenshot of the "User Input" page to make sure we are using the same settings when importing.

At this point comes the warning message today:

(modQSortInPlace)

'''''''''''''''''''''''''''''''''''''''' '''''''''''
' Ensure that InputArray is an array of simple data
' types, not other arrays or objects. This tests
' the data type of only the first element of
' InputArray. If InputArray is an array of Variants,
' subsequent data types may not be simple data types
' (e.g., they may be objects or other arrays), and
' this may cause QSortInPlace to fail on the StrComp
' operation.
'''''''''''''''''''''''''''''''''''''''' '''''''''''
If IsSimpleDataType(InputArray(LBound(Input Array))) = False Then
If NoAlerts = False Then
MsgBox "InputArray is not an array of simple data types."
RecursionLevel = RecursionLevel - 1
Exit Function
End If

[thom321]

a4u2fear,

Not sure what to make of that comment.

Personally I like spaghetti, especially spaghetti bolognese or spaghetti carbonara. They are equally good depending on the day.

spaghetti

[a4u2fear]

Ha, it's a reference to coding, I like spaghetti too

[Vegas68]

Ok, here is my User Input Screen:

[thom321]

Vegas68,

I should have asked earlier, but when does the error appear? Is it when you click the "Import Pinnacle Odds" button?

Also in your screenshot I see that some of the True/False settings are in German but one is in English. Did you change that or did Excel do that automatically?

Ok, here is my User Input Screen:

[ATTACH]77379[/ATTACH]
[ATTACH]77381[/ATTACH]

[Vegas68]

thom321,

yes it appears when I click the "Import Pinnacle Odds" button.
I´m from germany and so my excel is a german version but this is a good hint, I try to change my excel settings to english.

By the way, today it works again fine. I don´t understand this really. And it has always worked until 2 weeks ago.

I do not want to make you too much work. Will now switch my excel version to English and try it again. It may also be due to my PC system, perhaps bothers another application. I should try it at another computer.

Thank you so far

[thom321]

Vegas68,

The url that the odds are imported from is:

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

If you get the error again, put that url in your browser to make sure that the xml feed is actually available. If it doesn't show up in your browser, then the Excel workbook won't work either.

thom321,

yes it appears when I click the "Import Pinnacle Odds" button.
I´m from germany and so my excel is a german version but this is a good hint, I try to change my excel settings to english.

By the way, today it works again fine. I don´t understand this really. And it has always worked until 2 weeks ago.

I do not want to make you too much work. Will now switch my excel version to English and try it again. It may also be due to my PC system, perhaps bothers another application. I should try it at another computer.

Thank you so far

[jeffjam_]

Hi, thom, I just found out your spreadsheet and I have some trouble getting it to work. I have following problems:

1) If I understand it correctly I have to first Create odds table for the sport of my choosing a than Import Pinnacle odds so I get the current odds, is that correct? Well, here I am having the first problem. If I choose, say English soccer and create Odds table, the macro creates table with soccer matches dated to August of last year, which is very strange.

2) Consequently, when I click on Import Pinnacle odds an error pops up saying Compile error: User defined type not defined. I literally made no changes in the Excel other than choosing Soccer and Premier League. I am sending you the link with the error.

http://postimg.org/image/kodyyh1t9/

3) Is it possible to add sports that aren't currently in the list, if so, does it have to be done manually or automatic adding based on xml is possible?

Thanks with the help.

[thom321]

jeffjam_,

After checking my instructions page, I realize it is a bit confusing. The correct order is 1. Click "Import Pinnacle odds". 2. Click "Create odds table".

On the error, it probably appears because you don't have all the required references set for the workbook. Check the last section of the Instructions page and verify that you have selected all the references shown in the picture.

The list of sports reflect all the available sports that Pinnacle currently have odds for. So if a sport is not shown, it is because Pinnacle does not have any odds for it at the moment. It will be available if/when the imported odds have that sport included.

Hopefully this will solve your issues,

thom321

[jeffjam_]

Thank you for your response and clarification thom, however, I am still not able to solve it. I have all references as suggested in the Instrusction sheet up and running, only Object library is of 12.0 version, but that's something to do with me using Excel 2007 I suppose. Here is the link with set up references http://postimg.org/image/9mt5c7p07/

Do you suggest anything else? I'd really like to have it working.

[thom321]

What operating system are you using?

Try also setting a reference to Microsoft XML v3 or use Microsoft XML v3 instead of v6.

If that still doesn't work, you can try to uncheck all references, save and close Excel. Then open the workbook again and add back the references.

The workbook should work fine in Excel 2007.

Keep me posted on how it goes.

Thank you for your response and clarification thom, however, I am still not able to solve it. I have all references as suggested in the Instrusction sheet up and running, only Object library is of 12.0 version, but that's something to do with me using Excel 2007 I suppose. Here is the link with set up references http://postimg.org/image/9mt5c7p07/

Do you suggest anything else? I'd really like to have it working.

[jeffjam_]

I am using Windows 8.1. I've changed Microsoft XML v6 to v3 and it's done the magic, at least it seems so. Thank you for your help, appreciate it.

[thom321]

Glad that it worked. I did some research and for whatever reason XML v6 on Windows 8 does not expose DOMDocument so that is why the error happened and that is why XML v3 works.

It is an issue specific to Windows 8 so it will happen on all Windows 8 machines and only on Windows 8.

I am using Windows 8.1. I've changed Microsoft XML v6 to v3 and it's done the magic, at least it seems so. Thank you for your help, appreciate it.

[kingskib]

Thom321,

Thank you very much for this.

[thom321]

Happy to hear you find it useful.

Thom321,

Thank you very much for this.

[pringles]

Hello Thom321, i would like to scrape www.soccerstats.com , league table and form table and merge this data with your pinnacle spreadsheet so that there would be data behind every game.
In the end it would look something like this : home team/away team/1/X/2/AH odds/O/U odds from Pinnacle .. then avg goals home ... away etc. goals is last 5 matches and so on .. about 15 variables
Could you teach me how to do it in VBA rather than i would have to create a new sheet for every league and use the simple scrape that excel offers, then parse that using formulas.

Also big thank you for what youve done

[nase21]

Hi thom321 - I've been using your Pinnacle template for a while now and I've never had a problem until I recently upgraded my operating system to Windows 10. Now the workbook will not import. Not sure if it relates to the same type of issue that jeffjam had with the version of Microsoft xml 3. Any help would be much appreciated. Thanks!

[thom321]

I am happy to hear that you are thinking of ways to increase your utility of the sheet and that you want to learn about how to write code yourself. I am very busy at the moment so I can't provide much guidance myself currently. You can check out the thread started by a4u2fear about scraping using Excel. It might give you some ideas of how to automate the process.

Hello Thom321, i would like to scrape www.soccerstats.com , league table and form table and merge this data with your pinnacle spreadsheet so that there would be data behind every game.
In the end it would look something like this : home team/away team/1/X/2/AH odds/O/U odds from Pinnacle .. then avg goals home ... away etc. goals is last 5 matches and so on .. about 15 variables
Could you teach me how to do it in VBA rather than i would have to create a new sheet for every league and use the simple scrape that excel offers, then parse that using formulas.

Also big thank you for what youve done

[thom321]

Try the same solution that worked for jeffjam. Most likely it is an issue in Windows 10 as well.

Hi thom321 - I've been using your Pinnacle template for a while now and I've never had a problem until I recently upgraded my operating system to Windows 10. Now the workbook will not import. Not sure if it relates to the same type of issue that jeffjam had with the version of Microsoft xml 3. Any help would be much appreciated. Thanks!

[nase21]

I managed to figure it out. I changed the Microsoft XML back to V3.0 and I can import once again. Thanks!

On a side note, when using the workbook, do you know if it is possible to either go back and run odds for a previous day or can one automate the workbook to run on it's own?

Thanks again!

[kingskib]

I managed to figure it out. I changed the Microsoft XML back to V3.0 and I can import once again. Thanks!

On a side note, when using the workbook, do you know if it is possible to either go back and run odds for a previous day or can one automate the workbook to run on it's own?

Thanks again!

this would be excellent, if we could grab lines and results from a date range.

Again, I appreciate this tool you developed, Thom321! THANK YOU.

[statnerds]

Wow, fantastic tool Thom, thanks.

[thom321]

nase21,

Sorry for the late reply. I am not that active here, since the Think Tank don't get that many updates. As far as running it to get past odds, unless Pinnacle starts storing their odds, it is not possible. With some programming, you could get this to run on its own but it would not be a very good solution. You are better off trying to get historical odds from a site like OddsPortal, although they probably don't want you to scrape their site (but that is up to you)

I managed to figure it out. I changed the Microsoft XML back to V3.0 and I can import once again. Thanks!

On a side note, when using the workbook, do you know if it is possible to either go back and run odds for a previous day or can one automate the workbook to run on it's own?

Thanks again!