Web query basics

[touque]

I am trying to create a web query of a site showing Masters scorecards.
I have only created one query some years ago which was also for the Masters tourney but nothing since. Tried a couple times without success.
So I found a good site showing all players scores for every hole but when I copy the url and try to paste it in the box (after hitting "Get Data", "Other Sources", "Web site") it just keeps spinning with "Connecting" message then eventually timing out.
How do I get around this? I am retrieving from Edge.

 

[touque]

Anyone have any tips on how to connect?
I have found a good web page for the table I need.

 

[Edgar_]

What is the website? I might be able to help if you discard the tool you're referring to.

 

[touque]

Edgar, here is the url,

https://www.masters.com/en_US/scores/index.html

 

[Edgar_]

OK. That website is loading this URL with the data every few seconds:
https://www.masters.com/en_US/scores/feeds/2024/scores.json

If you copy paste that URL into any browser, you'll get the most recent data. However, I'm not sure which data you need from there, there are a lot of numbers and I'm not a Golf person, so I don't know what data you really need. If you can explain with some pictures, I'll tell you how to proceed.

 

[Edgar_]

 

[touque]

Thanks for the quick replies Edgar! I got pulled away yesterday afternoon but now am back attempting this project.
I tried entering that in the URL box of the Basic tab with no luck. So I am trying the Advanced tab. Is there anything else I should be entering?

 

[Edgar_]

I do not have that tool, sorry. I can do that using VBA. The procedure is as follows:
1. Get the VBA-JSON parser here
2. Drag it/import it to your VBA window
3. Add this code to a module

Option Explicit

Sub test()
    Dim url As String
    url = "https://www.masters.com/en_US/scores/feeds/2024/scores.json"
    
    Dim game2024 As Object
    Set game2024 = GetJsonData(url)

    'iterators
    Dim rw As Long
    Dim col As Long
    
    'add names
    rw = 4
    Dim member As Variant
    For Each member In game2024("data")("player")
        Cells(rw, 1) = member("display_name")
        rw = rw + 5
    Next member
    
    'add pars (assuming it's the same for all rounds)
    col = 3
    For Each member In game2024("data")("pars")("round1")
        Cells(3, col) = member
        col = col + 1
    Next member
    
    'add round 1 scores
    rw = 4
    col = 3
    Dim player As Long
    For player = 1 To game2024("data")("player").Count
        For Each member In game2024("data")("player")(player)("round1")("scores")
            Cells(rw, col) = member
            col = col + 1
        Next member
        col = 3
        rw = rw + 5
    Next player
    
    'add round 2 scores
    rw = 5
    col = 3
    For player = 1 To game2024("data")("player").Count
        For Each member In game2024("data")("player")(player)("round2")("scores")
            Cells(rw, col) = member
            col = col + 1
        Next member
        col = 3
        rw = rw + 5
    Next player
    
    'add round 3 scores
    rw = 6
    col = 3
    For player = 1 To game2024("data")("player").Count
        For Each member In game2024("data")("player")(player)("round3")("scores")
            Cells(rw, col) = member
            col = col + 1
        Next member
        col = 3
        rw = rw + 5
    Next player

    'add round 4 scores
    rw = 7
    col = 3
    For player = 1 To game2024("data")("player").Count
        For Each member In game2024("data")("player")(player)("round4")("scores")
            Cells(rw, col) = member
            col = col + 1
        Next member
        col = 3
        rw = rw + 5
    Next player
    
End Sub

Function GetJsonData(url As String) As Object
    Dim req As Object
    Set req = CreateObject("msxml2.xmlhttp")
    
    Dim json As Object
    Set json = CreateObject("scripting.dictionary")
    
    With req
        .Open "get", url, False
        .send
        Set json = ParseJson(.responsetext)
    End With
    
    Set GetJsonData = json
End Function

 

[Edgar_]

You can do a few things more with the code (anything really), however, I certainly do not understand how the scoring system works. The calculations can be done directly from VBA. In the website, the JSON data that is retrieved every few seconds is utilized by the client to make the calculations necessary to show you a score. You would need to directly do the calculations with the data. Again, I do not understand Golf scores, so I can only show you how to extract the data that they use.

There's an alternative that is feasible as well, which is utilizing a tool like puppeteer, which works with JavaScript to extract the data. Browser automation tools of that kind open an browser that can be manipulated from outside -- typically Chromium-- this browser then goes to the website, waits until the content is loaded and then it extracts it any way you want. It can be triggered from Excel. IEXPLORE could probably do that as well, and using VBA, depends on whether the website can be run from that old browser. If you can open Edge from your version of Excel and automate it, you could utilize that approach as well.

Additionally, a bookmarklet could take care of all that. A bookmarklet is a bookmark that can run JavaScript code from your browser. With it, you can extract the data similar to a browser automation tool, but this one is triggered from the browser, not from Excel (well, you could, but that is a little more trouble that involves sockets or other tech).

Or find another source for this data.

 

[Domenic]

If you want to use Power Query, you'll need to set a couple of request headers, Accept and User-Agent.

The value for Accept would be...

application/json, text/plain, */*

The value for User-Agent would be something like this...

Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36

To find your User-Agent, have a look at the following link...

User Agent: Definition and Examples. Most Common User Agents List.

A user agent is a string of text that is sent by a web browser to a web server to identify itself and provide information about the browser's capabilities.

www.link-assistant.com

Hope this helps!