VBA Scraping from dynamic web page

[Bruzio]

Hi everybody. I'm trying to download soccer results from Football results 21 august 2020, I can't as I'd like.
My question was posted on a italian forum, but we were unable to resolve.
I trust in your knowledge.


What do i need?

6 columns:

LEAGUE - START - HOME - AWAY - SCORE- STATUS

What are the problems?

1) Macro doesn't download all scheduled events.
2) The content of the rows isn't correct
2) The web page has a more results button that is always visible and clickable, and i need to load all hidden pages.

I expect this, see screen:

This is my current code:

Sub fromweb()
    Dim IE As Object
    Dim Doc As Object
    Dim x As Object
    Dim i As Long
    Dim myArray As Variant
    Set IE = CreateObject("InternetExplorer.Application")
    Application.ScreenUpdating = False
    Cells.Clear
    Const myURL As String = "fctables.com/livescore/21_08_2020/"
    With IE
        .navigate myURL
        .Visible = True
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
        Application.Wait Now + TimeValue("0:00:10")
    End With
    myArray = Array("LEAGUE", "START", "HOME", "AWAY", "SCORE", "STATUS")
    With Range("A1:F1")
        .Value = myArray
    End With
    Set Doc = IE.Document
    For Each x In Doc.getElementsByClassName("league")
        i = i + 1: j = 0
        Cells(i + 1, 1) = Replace(Replace(x.innerText, "Table", ""), Chr(10), "")
        For Each y In Doc.getElementsByClassName("col-xs-8 col-sm-9 col-lg-10 truncate")
            Cells(i + 1, 1) = y.innerText
            Cells(i + 1, 2) = Doc.getElementsByClassName("date")(j).innerText
            Cells(i + 1, 3) = Doc.getElementsByClassName("home")(j).innerText
            Cells(i + 1, 4) = Doc.getElementsByClassName("away")(j).innerText
            Cells(i + 1, 5) = Doc.getElementsByClassName("score")(j).innerText
            Cells(i + 1, 6) = Doc.getElementsByClassName("status_name")(j).innerText
            j = j + 1: i = i + 1
        Next
    Next
    IE.Quit
    Set IE = Nothing
    Set Doc = Nothing
    Application.ScreenUpdating = True
End Sub

How to proceed correctly?
Thanks for the attention

 

[smozgur]

In this new page there are all available odds, you can tell me how i should do to download all odds for every event?

@Bruzio - In your latest mini-sheet, you list the URLs that you extracted. Does it mean you solved it? Is it all you need to get the hyperlinks? In this case, you can use HYPERLINK() function to convert column K links to actual hyperlinks that you can click.

If not, then I see the following content in the game's odds page.

• This pages should be parsed individually for each game, so it doesn't make sense to do this during the actual games download solved in the previous step. I recommend doing this step on demand by using a trigger like clicking to a hyperlink saying "Odds" that will be placed in a new column in the big table for each game. So, you click on it, a new macro parses that game's odds page, and create a new table in a new sheet that shows the data above. Is this something that will help your project?
• Otherwise please explain how you need it to work.
• In either case, please explain how you need this data to be placed in the worksheet. I have zero experience and interest about these games/odds, so I can't visualize how this data should be listed in a worksheet.

 

[Bruzio]

I'm proceeding step by step.
In the column K i used a formula becouse using

rng.Cells(, 10).Value = game.getElementsByTagName("a")

doesn't download the hyperlink. Is it correct this procedure?

This is my idea:
1) I use your powerfull macro (thanks again) to download scheduled events.
2) I should to use a temporary sheet where to download all odds individually for each event, paste to main sheet, clean temp sheet and repeat for all events.

I need to learn to download directly hyerlinks, then simulate click and lastly to download individually odds for events.
I'm good to copy cells and to clean a sheet

What do you think?

 

[smozgur]

2) I should to use a temporary sheet where to download all odds individually for each event, paste to main sheet, clean temp sheet and repeat for all events.

There are many odds for each game. What is your plan to locate them in the main sheet for each game?
Again - please show us how you want to write the game odds in the corresponding column(s).

Edit: Look at the following sample: How do you want to place the odds starting from column J? I am trying to understand the final table structure that you need.

1143549-2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	LEAGUE	START	HOME	AWAY	SCORE	STATUS	WIN HOME	DRAW	WIN AWAY	ODDS
2	England - Premier League	20:00	Crystal Palace	Newcastle United	-	Not started	2.13	3.22	3.83	????
3	Spain - La Liga	20:00	Valladolid	Levante	-	Not started	2.54	3.27	2.85	????
4	Germany - 1. Bundesliga	19:30	Wolfsburg	Werder Bremen	-	Not started	1.9	3.65	4	????
5	France - Ligue 1	20:00	Strasbourg	Rennes	-	Not started	2.89	3.2	2.55	????
Sheet1

There are hundreds of games, and if you need to get all odds instead of getting them individually on demand, then it means hundreds of other web calls that will slow your macro dramatically.
However, if that's what you need, then you should be ok about the slowness.

I need to learn to download directly hyerlinks, then simulate click and lastly to download individually odds for events.

I mostly write comments in my codes to explain what that section is actually doing like many other coders. because we want you to learn instead of just copy and paste action. So, you can follow the comments to learn, and ask questions if you don't understand something.

Side note: I don't know about this particular football website's request monitoring policy, and if they would block your access by suspecting that you are trying to make a DDOS attack. It won't be a massive request in this scenario, but if you try to access thousands of pages of a website in a short amount of time, there is almost a possibility that the responding server would think that it is an automated attack, and try to stop your access. Just as a note for automated access to the website.

 

[Bruzio]

Hi, my target is to have an archive, for this reason i need to download automatically.

Cartel1 (1).xlsm
	J	K	L	M	N	O	P	Q	R	S	T	U	V
4		HT			FT			OVER/UNDER 1,5		OVER/UNDER 2,5		OVER/UNDER 3,5
5	LINK	1	X	2	1	X	2	OVER 1,5	UNDER 1,5	OVER 2,5	UNDER 2,5	OVER 3,5	UNDER 3,5
6	about:/livescore/monagas-sc-caracas-g1862174/
7	about:/livescore/alianza-atletico-union-huaral-g1864016/
8	about:/livescore/figueirense-botafogo-sp-g1779723/
9	about:/livescore/guarani1_2-gremio-g1863581/
10	about:/livescore/atletico-junior-union-la-calera-g1866559/
11	about:/livescore/defensa-y-justicia-vasco-da-gama1-g1866556/
12	about:/livescore/bucaramanga-alianza-petrolera-g1871896/
13	about:/livescore/zhetysu-taldykorgan-fc-astana-g1765294/
14	about:/livescore/tianjin-teda-guizhou-hengfeng-zhicheng-g1867215/
15	about:/livescore/kaisar-kyzylorda-fc-kyzylzhar-petropavlovsk-g1765308/
Foglio6

I've to study how to replace "about" via vba and how to have hyperlinks without formula.
I need to proceed step by step.

Note: odds are only available when the match hasn't started

 

[smozgur]

Just like before, the following approach might fail if the source document structure is changed.
And there is no error handling for missing elements. However, it worked without problems for many days I tested. So, the source looks to be consistent.

Please apply the steps carefully below.

• Create a new workbook.
• Go to VBE, and insert a new Class Module. Name this new class module as clsOddGroup.
• Copy and paste the following code into the empty class module you just created.

Option Explicit
' Class module for Odd Group object
' Odds are grouped as 1X2, HT, Under/Over 1.5, etc.
' And each group has odds for a various number of different results.
' Therefore, I am using a class object to store the group name
' and also the odds for different results in an encapsulated object.
' We can also use the Dictionary object instead of the Collection object
' to avoid using a class object in this project.
' However, Collection is a native object type
' that doesn't require an additional library.

' Odd group name, 1X2, HT, Under/Over 1.5, etc.
Private m_name As String
' Collection object to store the odds for different results, 1, X, 2, etc.
Private m_items As New Collection

' Assign the Name property of the group
Property Let Name(strName As String)
    m_name = strName
End Property

' Retrieve the Name property of the group
Property Get Name() As String
    Name = m_name
End Property

' Function to add an odd result name
Public Sub addItem(itemName As String)
    m_items.add itemName
End Sub

' Retrieve the odd result names collection of the odd group
Property Get items() As Collection
    Set items = m_items
End Property

• Insert a new Standard Module, and name it as however you like.
• Copy and paste the following code into the empty standard module you just created.

Option Explicit

Private Function createOddGroup(strName As String, oddItems As Variant) As clsOddGroup
' An odd group is defined with a name,
' and odds for different results
Dim i As Integer
    ' Create a new odd group class object
    Set createOddGroup = New clsOddGroup
    With createOddGroup
        ' Define the name of the odd group
        .Name = strName
        ' Define the odds for different results
        For i = 0 To UBound(oddItems)
            .addItem CStr(oddItems(i))
        Next i
    End With
End Function

Public Sub crawlForMe()
Dim sht As Worksheet
Dim rng As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim strDate As String

Dim http As MSXML2.XMLHTTP60
Dim html As HTMLDocument
Dim htmlOdds As HTMLDocument
Dim league As HTMLDivElement
Dim game As HTMLDivElement
Dim status As HTMLDivElement
Dim teams As HTMLDivElement
Dim odds As HTMLDivElement
Dim gameOddGroups As HTMLDivElement
Dim gameOdd As HTMLDivElement

Dim collOddGroups As Collection
Dim clsOddGroupItem As clsOddGroup
Dim oddsUrl As String

    strDate = "29-11-2020"
    
    Set sht = ActiveSheet
    sht.Columns.Delete

    Set rng = sht.Range("A1")
    
    ' Create a collection for odd groups
    ' and add each group by providing the group name,
    ' and an array containing the result names.
    ' The beauty is working with objects,
    ' new group names can be added easily.
    Set collOddGroups = New Collection
    With collOddGroups
        .add createOddGroup("1X2", Array("1", "X", "2"))
        .add createOddGroup("HT", Array("1", "X", "2"))
        .add createOddGroup("Under/Over 1.5", Array("Under 1.5", "Over 1.5"))
        .add createOddGroup("Under/Over 2.5", Array("Under 2.5", "Over 2.5"))
    End With
    
    ' The rest is basically the same with the previous code
    ' except parsing the odd group pages
    With rng
        With .Resize(, 9)
            .Merge
            .Value = "GAMES"
            .HorizontalAlignment = xlHAlignCenter
        End With
        .Offset(1).Resize(, 9) = Array("LEAGUE", "START", "HOME", "AWAY", "SCORE", "STATUS", "WIN HOME", "DRAW", "WIN AWAY")
        
        j = 1
        For Each clsOddGroupItem In collOddGroups
            With .Offset(, j)
                With .Resize(, clsOddGroupItem.items.Count)
                    .Merge
                    .Value = clsOddGroupItem.Name
                    For k = 1 To clsOddGroupItem.items.Count
                        .Offset(1).Cells(, k) = clsOddGroupItem.items.Item(k)
                    Next k
                    .Resize(2).HorizontalAlignment = xlHAlignCenter
                End With
            End With
            j = j + clsOddGroupItem.items.Count
        Next clsOddGroupItem
        
    End With
    
    Set rng = rng.Offset(1)
    
    
    Set http = New MSXML2.XMLHTTP60
    Set html = New HTMLDocument
    Set htmlOdds = New HTMLDocument
    
    Do
        DoEvents
        i = i + 1
        
        http.Open "POST", "https://www.fctables.com/xml/livescore/", True
        http.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
        http.send "page=" & i & "&date=" & strDate
        
        Do Until http.readyState = 4
            DoEvents
        Loop
        html.body.innerHTML = http.responseText
        
        If Len(http.responseText) = 0 Then Exit Do
        
        For Each league In html.getElementsByClassName("league")
            For Each game In league.getElementsByClassName("games").Item(0).Children
                Set rng = rng.Offset(1)
                Set status = game.getElementsByClassName("status").Item(0)
                Set teams = game.getElementsByClassName("name game_hover_info").Item(0)
                Set odds = game.getElementsByClassName("godds").Item(0)
                
                
                
                rng.Cells(, 1).Value = league.getElementsByClassName("panel-title row").Item(0).getElementsByTagName("a").Item(0).innerText
                rng.Cells(, 2).Value = status.getElementsByClassName("date").Item(0).innerText
                If teams.getElementsByClassName("home").Item(0).ChildNodes.Length > 0 Then
                    rng.Cells(, 3).Value = teams.getElementsByClassName("home").Item(0).ChildNodes(teams.getElementsByClassName("home").Item(0).ChildNodes.Length - 1).NodeValue
                End If
                If teams.getElementsByClassName("away").Item(0).ChildNodes.Length > 0 Then
                    rng.Cells(, 4).Value = teams.getElementsByClassName("away").Item(0).ChildNodes(0).NodeValue
                End If
                rng.Cells(, 5).Value = "'" & teams.getElementsByClassName("score").Item(0).innerText
                rng.Cells(, 6).Value = status.getElementsByClassName("status_name").Item(0).innerText
                
                If odds.ChildNodes.Length = 3 Then
                    rng.Cells(, 7).Value = odds.ChildNodes(0).innerText
                    rng.Cells(, 8).Value = odds.ChildNodes(1).innerText
                    rng.Cells(, 9).Value = odds.ChildNodes(2).innerText
                End If
                
                ' This is where we start processing the odd results page.
                ' Open the odd results page only if the game is not started yet.
                ' We do this by checking a class name defined in the game element accordingly.
                If InStr(game.className, "status_not_started") Then
                    ' URL for the odds page
                    oddsUrl = Replace(teams.getElementsByTagName("a").Item(0).href, "about:", "https://www.fctables.com")
                    http.Open "GET", oddsUrl, True
                    http.send
                    Do Until http.readyState = 4
                        DoEvents
                    Loop
                    htmlOdds.body.innerHTML = http.responseText
                    
                    'Self-descriptive, althought it requires understanding
                    ' the cell references, html document model objects, etc.
                    j = 0
                    For Each clsOddGroupItem In collOddGroups
                        For Each gameOddGroups In htmlOdds.getElementsByClassName("odds_type")
                            If gameOddGroups.getElementsByClassName("type_header").Item(0).innerText = clsOddGroupItem.Name Then
                                For k = 1 To clsOddGroupItem.items.Count
                                    For Each gameOdd In gameOddGroups.getElementsByClassName("odd")
                                        If gameOdd.getElementsByClassName("name").Item(0).innerText = clsOddGroupItem.items.Item(k) Then
                                            rng.Cells(, 9).Offset(, j).Offset(, k) = gameOdd.getElementsByClassName("value").Item(0).innerText
                                            Exit For
                                        End If
                                    Next gameOdd
                                Next k
                                Exit For
                            End If
                        Next gameOddGroups
                        j = j + clsOddGroupItem.items.Count
                    Next clsOddGroupItem
                End If
            Next game
        Next league
    Loop
End Sub

Execute crawlForMe sub procedure. The following shows the sample result.

1143549-2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	GAMES									1X2			HT			Under/Over 1.5		Under/Over 2.5
2	LEAGUE	START	HOME	AWAY	SCORE	STATUS	WIN HOME	DRAW	WIN AWAY	1	X	2	1	X	2	Under 1.5	Over 1.5	Under 2.5	Over 2.5
3	England - Premier League	14:00	Southampton	Manchester United	-	Not started	3.7	3.65	2.01	3.85	3.6	2.04	4.1	2.23	2.55	3.95	1.25	2.02	1.8
4	England - Premier League	16:30	Chelsea	Tottenham	-	Not started	2.11	3.6	3.47	2.12	3.6	3.6	2.63	2.23	3.9	4.1	1.24	2.06	1.76
5	England - Premier League	19:15	Arsenal	Wolverhampton Wanderers	-	Not started	2.2	3.27	3.58	2.18	3.35	3.75	2.8	2.02	4.2	2.95	1.4	1.64	2.28
6	Spain - La Liga	13:00	Barcelona	Osasuna	-	Not started	1.29	5.95	9.2	1.26	6.4	11	1.58	2.95	7.5	6.1	1.12	2.8	1.44
Sheet1

I am not telling that this is an easy code to understand, however, it is also not difficult if you carefully analyze it "step by step". In fact, the relatively more difficult part is building the underlying logic. I consider this project is completed here. However, if you have questions about the code or the logic, then let me know.

 

[Bruzio]

Wow, you did it again ?
It's perfect, now i have to understand what you did, i've to add over/under 3.5 column.
Thank you so much.

 

[Bruzio]

I added array

.add createOddGroup("Under/Over 3.5", Array("Under 3.5", "Over 3.5"))

and i changed position

rng.Cells(, 11).Offset(, j).Offset(, k) = gameOdd.getElementsByClassName("value").Item(0).innerText

It's ok. Now i've to download HT results when match has finished, it's only visible when has finished.

 

[Bruzio]

Hi @smozgur , sorry to bother you again, I've a request.
I realized that when the events are too many macro works slowly and I don't need to download events that don't have odds, so i added <> "" here

If gameOdd.getElementsByClassName("name").Item(0).innerText = clsOddGroupItem.items.Item(k) Then

but doesn't work. I tried > 0 with the same result.

May it be because it's a class group?

 

[smozgur]

As I mentioned, I have no idea about how the betting logic works, and even the terms on this website. So, if you could explain how you determine if an event doesn't have odds, then I would try to help.

 

[Bruzio]

Hi, the website doesn't provide all odds for all matches, I'm referring to the ht and over odds, for this reason there are empty records in my file.
I don't need to download events that haven't these odds, download time are long when there are too many events.
For example here, Atletico Guemes - Sarmiento de Resistencia livescores result Primera B Metropolitana 21 dec 2020
For this event there aren't ht and over odds, it can skip download?

"If clsOddGroupItem.items.Item is empty go next game" a thing like that. As I said i tried with <>"" and > 0 but doesn't work

Ecuador - Ecuadorian Serie A	21-12-2020		01:00	LDU de Quito	Tecnico Universitario	Not started	2,08	2,32	5,30		1,21
Ecuador - Ecuadorian Serie A	21-12-2020		01:00	CSD Macara	Independiente del Valle	Not started	3,00	2,25	3,05		1,18
Chile - Primera Division	21-12-2020		01:30	Coquimbo Unido	O'Higgins	Not started	3,40	2,10	3,00		1,32
Costa Rica - Primera Division	21-12-2020		03:00	LD Alajuelense	Club Sport Herediano	Not started	2,43	2,20	4,40		1,25
Mexico - Ascenso MX	21-12-2020		03:00	Atlante	Jaibos Tampico Madero	Not started	3,10	1,84	4,10		1,55
Saudi Arabia - 1. Division	21-12-2020		13:10	Al Batin	Al Nassr FC	Not started
Egypt - 1. Division	21-12-2020		13:30	National Bank	El Entag El Harby	Not started
England - Premier Reserve League	21-12-2020		14:00	Crystal Palace U23	Newcastle U21	Not started
Kuwait - 1. Division	21-12-2020		14:00	Al-Salibikhaet	Al Yarmouk Kuwait	Not started
Turkey - 1. Lig	21-12-2020		14:00	Samsunspor	Boluspor	Not started	2,02	2,23	6,25		1,3
England - Premier Reserve League	21-12-2020		14:30	Stoke U21	West Bromwich Albion U21	Not started
England - Premier Reserve League	21-12-2020		15:00	Leeds U23	Fulham U21	Not started
India - ISL	21-12-2020		15:00	Atletico de Kolkata	Bengaluru FC	Not started
DR Congo - Super League	21-12-2020		15:30	Sanga Balende	Maniema Union	Not started
Saudi Arabia - 1. Division	21-12-2020		15:55	Al-Taawon	Al Shabab	Not started	4,40	2,23	2,33		1,25
Egypt - 1. Division	21-12-2020		16:00	El Gounah	Ismaily SC	Not started
Ghana - 1. Division	21-12-2020		16:00	Berekum Chelsea	Aduana Stars	Not started
Turkey - 1. Lig	21-12-2020		16:00	Adana Demirspor	Tuzlaspor	Not started	2,55	2,18	4,10		1,22
Greece - Super League	21-12-2020		16:15	PAS Giannina	Asteras Tripolis	Not started	3,20	1,85	3,95		1,41
Romania - Liga I	21-12-2020		16:30	UTA Batrana Doamna	Astra Giurgiu	Not started	3,55	1,91	3,45		1,45