Retrieving prices from web (javascript table)

[forum1]

Is it possible to populate certain cells of my excel triggering sheet with values that are continuously updated and can be found at a javascript table of a certain webpage (such as current bookmaker's odds of an event in-play)?

If feeding my sheet with these prices is possible, is VBA and the "WinHttp.WinHttpRequest.5.1" the means to attempt it? (as discussed in some other topics - RacingPost tissue prices and tennis live scores)?

[forum1]

Does anyone who read this post and understood what I'm asking happen to have an idea if this is possible or not? (so as I have an idea if asking for programming help makes sense)

[dgs2001]

Yes it's possible, however for in running I imagine the time to gather and process the information would make it "old", unless you are looking at sports which tend to have fairly static in play odds.

Duncan

[forum1]

Thx for your answer.

Any idea of how old? more than 10sec? 30sec?

Is VBA and the "WinHttp.WinHttpRequest.5.1" the means to attempt it if I am willing to give such an assignment to a developer?

[negapo]

I'm also very interested in doing that.
Currently im using Internet Explorer and VB to get the html of some pages but a section of it, that outputs a table, is written in java.
I don't know if this is possible thru Internet Explorer or some add-in.
If anyone knows how please give us some directions. Thanks

[negapo]

Here is a good guide, but it doesn't cover what we are looking for:
http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/

[negapo]

Just to add an example, inside the html that im analyzing i have this code:

Code: Select all

<script type="text/javascript">
<!--
   page = new PageEvent();
   page.setLive(false);
   globals.setUserLogged(true);
   page.setParticipants('Dundalk', 'Derry City');
       globals.initPrefferedOdds(0);
   page.tabs.setScopes({});
   page.setBettingType(1, 2);
       globals.initBetTypeScopeDefault({"7":1});
   globals.setDefaultOddsFormat(1);
   globals.setExchangeDisplayOptions(false);
   globals.initBetCommission({"7":0.96,"39":0.95,"42":0.95,"44":0.95,"113":0.95,"127":0.99}, true);globals.initBookies([1,8,9,10,12,17,19,21,23,26,27,30,31,32,34,38,40,43,53,55,57,68,70,71,73,74,78,80,81,88,90,92,93,99,101,103,105,106,107,111,112,113,114,115,119,120,121,124,125,129,140,141,147,154,156,157]);globals.initUserData({"email":"MYEMAILHERE"});globals.initOddsWatchData([]);globals.initTimeZone(1, 2);
   page.tabs.displayPage();
   //-->
</script>

This will create a table with odds, i dont know if its possible to retrieve it.
Thanks again for any help.[/url]

[alrodopial]

You can treat the above html source code as a text and retrieve the info you want, I suggest using regex , it will make your life easier.

[negapo]

alrodopial,
Thanks for your help.
I googled regex and read about it but I'm still a little bit confused on how to use it. Did you recommended it for comparing the scrapped data with the betfair data?
I'm still confused on how to retrieve the info with that bit of javascript. I have that code available through the innertext property but i don't know how to get the data that the javascript produces.
Thanks

[negapo]

alrodopial,
I found this bit of code using regex and it seems very interesting although i have to research its uses a little bit more (specially the patterns).
Could you please advice on how to run the javascript in VB to retrieve the info? that would be great

Code: Select all

Sub Test()
    Const strTest As String = "qwerty123456uiops"
    MsgBox RE6(strTest)
End Sub
 
Function RE6(strData As String) As String
    Dim RE As Object, REMatches As Object
     
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "[0-9][0-9][0-9][0-9][0-9][0-9]"
    End With
     
    Set REMatches = RE.Execute(strData)
    RE6 = REMatches(0)
     
End Function

[negapo]

Is the

Code: Select all

execScript("CodeHere", "JavaScript")

the way to go?

[alrodopial]

If I understand your question correctly
you must find a way to recognize the start of the data you want to retrieve (the start of the table inside the page's source code ).
Ideally this should be the same every time you run the macro.

[forum1]

I'm personally not able to follow technically your discussion since I'm not a programmer.

Is it safe to conclude that retrieving these prices and updating is possible with the right programming? Can this become an assignment for Excel development ?

I've sent a personal message to alrodopial who apeears to be quite positive.

[negapo]

forum1: It seems to me that it is possible, just don't know how yet
alrodopial:
Thanks for helping.
I believe i haven't explained very well.
I'm retrieving a web page to get some data. For example:

Code: Select all

 Dim InternetExplorer As Object, HTMLObject As Object, Table As Object

    Set InternetExplorer = CreateObject("InternetExplorer.Application")
    With InternetExplorer
        .Navigate "Web Site Address"
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
        Set HTMLObject = .Document
        Set Table = HTMLObject.getElementById("TableName")
        '...
    End With
    InternetExplorer.Quit
    Set InternetExplorer = Nothing

This is ok for me but unfortunately not all data is in the html source. Some of it its generated by this piece of javascript that is inside the html:

Code: Select all

<script type="text/javascript">
<!--
   page = new PageEvent();
   page.setLive(false);
   globals.setUserLogged(true);
   page.setParticipants('Dundalk', 'Derry City');
       globals.initPrefferedOdds(0);
   page.tabs.setScopes({});
   page.setBettingType(1, 2);
       globals.initBetTypeScopeDefault({"7":1});
   globals.setDefaultOddsFormat(1);
   globals.setExchangeDisplayOptions(false);
   globals.initBetCommission({"7":0.96,"39":0.95,"42":0.95,"44":0.95,"113":0.95,"127":0.99}, true);globals.initBookies([1,8,9,10,12,17,19,21,23,26,27,30,31,32,34,38,40,43,53,55,57,68,70,71,73,74,78,80,81,88,90,92,93,99,101,103,105,106,107,111,112,113,114,115,119,120,121,124,125,129,140,141,147,154,156,157]);globals.initUserData({"email":"MYEMAILHERE"});globals.initOddsWatchData([]);globals.initTimeZone(1, 2);
   page.tabs.displayPage();
   //-->
</script>

This piece of code generates some data that is displayed by the browser in a table that i wanted to retrieve. Not sure how to do that.
Thanks for any help

[negapo]

Anyone? Thanks