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]

Good morning, good news. I understand that it is json script, so I imported VBA-tools/VBA-JSON.
Then I did as written in this post Parse JSON into Excel.

This is the code:

Sub GET_HOME()

    Dim Request As Object
    Dim url As String
    Dim Response As String
    Dim RunAsync As Boolean
    Dim json As Object
    Dim item As Variant
    Dim i As Long
   
    Set Request = CreateObject("MSXML2.serverXMLHTTP")
 
    url = "https://app.bsportsfan.com/events/inplay"
    RunAsync = True
   
    With Request
        .Open "GET", url, RunAsync 
        .setRequestHeader "Cache-Control", "no-cache"
        .setRequestHeader "Pragma", "no-cache"
        .setRequestHeader "Content-Type", "application/json"
        .send
             
        While Request.readyState <> 4
            DoEvents
        Wend
       
       Response = .responseText
     
    End With
   
    Set json = ParseJson(Response)
 
    i = 2

    For Each item In json("results")

         With Worksheets("home")
            .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
        End With
   
    i = i + 1
       
    Next item

    Set Request = Nothing
       
End Sub

This is the result in Excel

ID	LEAGUE	HOME	AWAY	SS	TIMER
3232181	3x3 Liga Pro	Amur-pro 3x3	Zenit-pro 3x3	3-1	8
3226516	Australian Open	Cameron Norrie	Roman Safiullin	3-6,7-5,6-3,1-1
3223746	Australian Open MD	Brkic/Qureshi	Andujar/Martinez	6-3,6-7,1-0
3230047	ITF M15 St Petersburg	Artem Dubrivnyy	Martin Krumich	4-6,6-1,4-4
3229169	ITF W15 Antalya	Gergana Topalova	Polina Kudermetova	6-4,6-7,1-0
3230229	ITF W15 Villena	Matilda Mutavdzic	Nicole Fossa Huergo	7-5,2-1
3231067	DOTA2 - Snow Sweet Snow	Spider Pigzs	Creepwave	1-0
3229604	Russia Youth League	Lokomotiv TSIVS Youth	Universitet Nizhnevartovsk Youth	23-18
3230721	ITF M15 Sharm El Sheikh	Shintaro Mochizuki	Jiri Lehecka	2-6,7-6,0-0
2650881	Russia MHL	Amurskie Tigry U20	MHC Spartak U20	2-3	14
3230230	ITF W15 Villena	Rosa Vicens Mas	Leyre R Gormaz	6-4,1-4	19
3228560	ITF W25 Grenoble	Cristina Bucsa	Alexandra Eala	6-2,3-6,2-2
3230173	ITF M15 Antalya	Pietro Rondoni	Nerman Fatic	6-3,2-6,0-3
3231030	ITF W15 Antalya	Irina Khromacheva	Marta Custic	2-6,4-5
3231984	Nepal National League Women	Biratnagar Metropolitan City Women	Tribhuvan Army Club Women	0-7	72
3229171	ITF W15 Antalya	Matilde Paoletti	Vlada Koval	6-4,3-4
3230065	ITF M15 Antalya	Khumoyun Sultanov	Alvaro Lopez San Martin	6-2,5-3	65
3230778	ITF M15 Grenoble	Mirko Martinez	Lucas Poullain	3-6,1-3
3230339	Challenger Potchefstroom	Ulises Blanch	Chun Hsin Tseng	7-6,1-2
3229538	ITF W25 Grenoble	Tereza Martincova	Pemra Ozgen	5-6
3227917	ITF W25 Potchefstroom	Gabriela Ce	Anna Bondar	4-6,2-1
3230302	ITF M15 Monastir	Benjamin Pietri	Oliver Crawford	6-4,2-0
3230470	ITF W25 Potchefstroom	Jesika Maleckova	Eva Guerrero Alvarez	5-6
3230889	ITF W25 Potchefstroom	Anastasia Gasanova	Carolina Meligeni Rodrigues Alves	6-3,1-3
3231215	ITF W15 Sharm El Sheikh	Fanny Ostlund	Anna Siskova	6-7,0-1
3232076	Iran U23 League	Mes Kerman U23	Zob Ahan U23		0
3230308	ITF W15 Monastir	Melis Yasar	Linda Fruhvirtova	2-6,2-3
3230221	TT Star Series	Jiri Vrablik	Tomas Koldas	0-0	0

I can't do the same thing with https://app.bsportsfan.com/event/odds?id=3172226

Runtime error 13

Please, I'd like to know your opinion about first code and help me for the second link.

Regards

 

[Bruzio]

This is the result I would like

BET365
start									kickoff									end
1_1			1_2			1_3			1_1			1_2			1_3			1_1			1_2			1_3
home_od	draw_od	away_od	home_od	handicap	away_od	over_od	handicap	under_od	home_od	draw_od	away_od	home_od	handicap	away_od	over_od	handicap	under_od	home_od	draw_od	away_od	home_od	handicap	away_od	over_od	handicap	under_od
3.200	3.300	2.100	1.950	0.0,+0.5	1.850	1.975	2.5	1.825	3.100	3.100	2.300	1.875	+0.25	1.925	1.850	2.25	1.950	301.000	51.000	1.002	2.425	0	1.525	5.900	4.5	1.130

I'm spending too many hours and I haven't found the solution yet

 

[John_w]

item is not a dictionary, so you can't put item("Bet365"). For Each item In json("odds") are the keys inside the "odds" dictionary, which are the betting company names.

Extract the JSON data like this:

    i = 2
    For Each item In json("odds")
        Debug.Print item
        With Worksheets("odds")
            .Range("A" & i).Value = json("odds")(item)("start")("1_1")("home_od")
            .Range("B" & i).Value = json("odds")(item)("start")("1_1")("draw_od")
            .Range("C" & i).Value = json("odds")(item)("start")("1_1")("away_od")
            i = i + 1
        End With
    Next

 

[Bruzio]

Thank you John for your reply. I need to parse just one company name, it's possible?

 

[John_w]

Don't loop through the company keys then:

    With Worksheets("odds")
        .Range("A1").Value = json("odds")("Bet365")("start")("1_1")("home_od")
        .Range("B1").Value = json("odds")("Bet365")("start")("1_1")("draw_od")
        .Range("C1").Value = json("odds")("Bet365")("start")("1_1")("away_od")
    End With

 

[Bruzio]

Thank you again.
Now, I should parse odds for each id in A column, so this https://app.bsportsfan.com/event/odds?id=3172226 should become https://app.bsportsfan.com/event/odds?id="A4" but how can I set the loop by taking the id column?

Partial final goal

						start									kickoff
						1_1			1_2			1_3			1_1			1_2			1_3
						home_od	draw_od	away_od	home_od	handicap	away_od	over_od	handicap	under_od	home_od	draw_od	away_od	home_od	handicap	away_od	over_od	handicap	under_od
3238324	Ebasketball Battle - 4x5mins	HOU Rockets (Andrik) Esports	UTA Jazz (blaylockk) Esports	13-17	1	2,80	3,06	2,14	2	0,25	2
3238326	Ebasketball Battle - 4x5mins	LA Lakers (Steel) Esports	BOS Celtics (Arthas) Esports	8-8	2
3194358	Turkey Efeler League	Solhan Metem Spor	Sorgun Bld	7-4	109
3238373	Iceland League 1 Women	KA B Women	Ymir Women	14-8	92
3239532	Esoccer Battle - 8 mins play	Belgium (dm1trena) Esports	Brazil (Foggy) Esports		0
3239533	Esoccer Battle - 8 mins play	Portugal (slezaintima) Esports	France (LaikingDast) Esports		2
3235859	Kenya Super League	Soy United	APS Bomet	0-0	88
3222097	Romania Liga I	Chindia Targoviste	ACS UTA Batrana Doamna	1-0	86

 

[John_w]

Try this - tests first few IDs. Puts NULL in the cell if an odds value doesn't exist. Add reference to MS XML v6.0.

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, oddTypes As Variant
    Dim id As String, company As String
   
    times = Split("start,kickoff", ",")
    divs = Split("1_1,1_2,1_3", ",")
    oddTypes = Split("home_od,draw_od,away_od,home_od,handicap,away_od,over_od,handicap,under_od", ",")
    company = "Bet365"
   
    With Worksheets("Home")
   
        For r = 5 To 10  '.Cells(.Rows.Count, "A").End(xlUp).Row  'test on id rows 5-10
       
            id = .Cells(r, "A").Value
       
            With httpReq
                .Open "GET", "https://app.bsportsfan.com/event/odds?id=" & id, False
                .setRequestHeader "Content-Type", "application/json"
                .send
                Set json = JsonConverter.ParseJson(.responseText)
            End With
           
'            With ThisWorkbook.Worksheets("JSON")
'                .Cells.Clear
'                JSONToCells json, .Range("A1")
'            End With
   
            Set odds = json("odds")
   
            'Start output in column G
            c = 7
                       
            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), oddTypes(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))(oddTypes(k + j * 3))
                                End If
                            End If
                        End If
                    Next
                Next
            Next
           
            DoEvents
           
        Next

    End With
       
End Sub

 

[Bruzio]

Hi John, thank you, the last code returns an error:

Runtime error 424 object required

JsonConverter is imported

 

[John_w]

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