Scrape from webpages without table and classes

[Bruzio]

Hi to everyone, surfing I found this website BsportsFan It has some interesting data.
The landing pages haven't tables or classes so I wasn't able to get that data. A few months ago @smozgur who I greet, made a vba code for me, I'm still using that sheet, maybe also using this website.
Inspecting the site I found this info:

The destination url is https://app.bsportsfan.com/events/inplay, by clicking on an event i found:

The destination url is https://app.bsportsfan.com/event/odds?id=3225440.
By changing the id I can find all the data for each event.

My goal is to have this data in a sheet.
I would like to know what tools I need to use.
I apologize if mine are trivial requests.

Thank you for your attention and have a nice day

 

[Bruzio]

Check the responseText. Is it invalid or indicate an error?

{"success":0,"error":"INVALID_ID"}

I edited

For r = 1 To 6  '.Cells(.Rows.Count, "A").End(xlUp).Row  'test on id rows 2-6 to For r = 4 To 6  '.Cells(.Rows.Count, "A").End(xlUp).Row  'test on id rows 2-6, and works.

Here

times = Split("start,kickoff,end", ",")

I added "end"

 

[Bruzio]

If I try a company doesn't exist

 

[John_w]

{"success":0,"error":"INVALID_ID"}

The code below handles invalid IDs.

Here

times = Split("start,kickoff,end", ",")

I added "end"

I didn't include "end" because your sheet in post 7 shows only start and kickoff.

If I try a company doesn't exist

The code below also handles a company which doesn't exist.

Public Sub Get_Odds()

    #If VBA7 Then
        Dim httpReq As XMLHTTP60
        Set httpReq = New XMLHTTP60
    #Else
        Dim httpReq As XMLHTTP
        Set httpReq = New XMLHTTP
    #End If
  
    Dim json As Object
    Dim odds As Object
    Dim r As Long, c As Long, i As Long, j As Long, k As Long
    Dim times As Variant, divs As Variant, oddsTypes As Variant
    Dim id As String, company As String
  
    times = Split("start,kickoff,end", ",")
    divs = Split("1_1,1_2,1_3", ",")
    oddsTypes = Split("home_od,draw_od,away_od,home_od,handicap,away_od,over_od,handicap,under_od", ",")
    company = "Bet365"
    company = "PinnacleSports"
  
    With Worksheets("Home")
  
        For r = 4 To 10  '.Cells(.Rows.Count, "A").End(xlUp).Row  'test on id rows 4-10
      
            id = .Cells(r, "A").Value
            Application.StatusBar = .Cells(r, "A").Address(False, False) & ": " & id
      
            With httpReq
                .Open "GET", "https://app.bsportsfan.com/event/odds?id=" & id, False
                .setRequestHeader "Content-Type", "application/json"
                .send
                Set json = JsonConverter.ParseJson(.responseText)
                'Debug.Print .responseText
            End With
          
'            With ThisWorkbook.Worksheets("JSON")
'                .Cells.Clear
'                JSONToCells json, .Range("A1")
'            End With

            'Start output in column G
            c = 7
          
            .Cells(r, c).Resize(, 3 * 3 * 3).Clear
            DoEvents
  
            If json("success") = 1 Then
          
                Set odds = json("odds")
      
                If odds.Exists(company) Then
              
                    For i = 0 To UBound(times)
                        For j = 0 To UBound(divs)
                            For k = 0 To 2
                                Debug.Print .Cells(r, c + i * 9 + j * 3 + k).Address, company, times(i), divs(j), oddsTypes(k + j * 3)
                                .Cells(r, c + i * 9 + j * 3 + k).Value = "NULL"
                                If Not IsNull(odds(company)(times(i))) Then
                                    If Not IsNull(odds(company)(times(i))(divs(j))) Then
                                        If Not IsEmpty(odds(company)(times(i))(divs(j))) Then
                                            .Cells(r, c + i * 9 + j * 3 + k).Value = odds(company)(times(i))(divs(j))(oddsTypes(k + j * 3))
                                        End If
                                    End If
                                End If
                            Next
                        Next
                    Next
                  
                Else
              
                    .Cells(r, c).Value = company & " doesn't exist"
              
                End If
              
            Else
          
                .Cells(r, c).Value = "Error: " & json("error")
          
            End If
          
        Next
      
        Application.StatusBar = ""

    End With
      
End Sub

 

[Bruzio]

I didn't include "end" because your sheet in post 7 shows only start and kickoff.

Yes, you right, it was a trunked screenshot.

Now the code is perfect, thanks a lot.

I just have two more things to fix for the first code:

1) I would like to scrape soccer only, I tried with " sport_id": "1" ", ......

if  item("sport_id") = "1" then

but I don't know if that's the correct procedure, and it also returns blank rows.

2) I would like to scrape the time, if possible, "time"

item("time")

but the value isn't a date or time, I don't know the format 1613243400

 

[John_w]

I just have two more things to fix for the first code:

1) I would like to scrape soccer only, I tried with " sport_id": "1" ", ......

if item("sport_id") = "1" then

but I don't know if that's the correct procedure, and it also returns blank rows.

2) I would like to scrape the time, if possible, "time"

item("time")

but the value isn't a date or time, I don't know the format 1613243400

Your sport_id line should work, if 1 is indeed soccer. Blank rows - where have you incremented the row number? Inside or outside the If ... End If block?

The time is a Unix timestamp (see Epoch Converter) which we can convert to an Excel date-time, which I've put in column G.

Change your first code to:

    i = 4
    For Each item In json("results")
         With Worksheets("home")
            If item("sport_id") = "1" Then
                .Range("A" & i).Value = item("id")
                .Range("B" & i).Value = item("league")("name")
                .Range("C" & i).Value = item("home")("name")
                .Range("D" & i).Value = item("away")("name")
                .Range("E" & i).Value = "'" & item("ss")
                If item.Exists("timer") Then
                    .Range("F" & i).Value = item("timer")("tm")
                End If
                .Range("G" & i).Value = CvtTimestamp(CDbl(item("time")))
                i = i + 1
            End If
        End With
    Next

and add this function:

'Convert Unix timestamp (number of seconds since 1-Jan-1970 to an Excel Date time
Private Function CvtTimestamp(timestamp As Double) As Date
    CvtTimestamp = DateAdd("s", timestamp, DateSerial(1970, 1, 1))
End Function

 

[Bruzio]

Inside or outside the If ... End If block?

It was outside ?


It's perfect. thank you so much, you have been very patient and very kind.

I leave you in peace.

Have nice day John

 

[Bruzio]

Hi, I'm here again because I'm trying to filter the leagues.

For example:

Esoccer Liga Pro - 12 mins play	Saturn Rama (SAT) Esports	Im Not Over (PNZ) Esports
Esoccer Live Arena - 10 mins play	Atletico Madrid (Iceman) Esports	Sevilla (Oolancer) Esports
Esoccer Live Arena - 10 mins play	Barcelona (Kit) Esports	Real Madrid (Weeman) Esports

I'm not interested in these and other leagues, so I tried with

If (item("league")("name")) <> "esoccer" Then

But with no success.
I would like to be able to decide which innertext to exclude.

John, when it's possible.

Thank you

 

[John_w]

Use Instr:

If InStr(1, item("league")("name"), "esoccer", vbTextCompare) = 0 Then

 

[Bruzio]

Thank you John, obviously it works.

 

[Bruzio]

Hi, John excuse me but I have a problem with "oddsTypes".

Some companies use delimiters, others not.

+0.5,+1.0 -1.0,-1.5 -0,75 -1,5 0 -2.0,-2.5 0.0,-0.5 0.0,-0.5

-1/1.5 -2.5/3 -0/0.5

The delimiters are different too, comma or dash or slash.
When there's the delimiter I would need to have the average of the two values, I tried with Average(Split.... with no success.

oddsTypes = Split("home_od,draw_od,away_od,home_od,handicap,away_od,over_od,handicap,under_od", ",")

Is this the string to edit?