How to distinguish data from html

[xyz987]

Hi sirs
i want get date from this website "https://tw.stock.yahoo.com/quote/1101.TW/dividend"，but it too many branch at frame.
i try to change low & column, it not correct put in excel table
how to analyze it and if there are three same name table like "table-body-wrapper", if i want to get no.3 how to use getElementsByClassName to get
attach file is all code, thanks


Snap1.jpgSnap2.jpg

Sub Hi_stock()
    Workbooks("test1.xlsm").Activate
    'Sheets.Add(After:=ActiveSheet).Name = "Dividend"

    'Dim HTTPRequest As MSXML2.XMLHTTP
    Dim httpRequest As Object
    Dim htmlDoc As Object
    Dim tables As Variant
    Dim name_get As Variant
    Dim dividend As Variant
    Dim dividend_1 As Variant
    Dim colNum As Integer
    Dim rowNum As Integer
    
 
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    Set htmlDoc = CreateObject("htmlfile")
           
    url_1 = "https://tw.stock.yahoo.com/quote/1101.TW/dividend"
    httpRequest.Open "GET", url_1, False
    httpRequest.send

    'Check if the request was successful (status code 200)
    If httpRequest.Status = 200 Then
        'Create a new HTML document object
        htmlDoc.body.innerHTML = httpRequest.responseText
        'get stock name
        Set name_get = htmlDoc.getElementsByTagName("div")
        For Each tbl In name_get
            If tbl.className = "D(f) Ai(c) Mb(6px)" Then
                For Each tblCol In tbl.getElementsByTagName("h1")
                    Cells(1, 1).Value = tblCol.innerText
                    Debug.Print (tblCol.innerText)
                Next tblCol
            End If
        Next tbl
        
        'get dividend title
        Set dividend = htmlDoc.getElementsByClassName("table-header Ovx(s) Ovy(h) W(100%)")
        For Each tb_1 In dividend
            rowNum = 2
            For Each tb_1_1 In tb_1.getElementsByTagName("div")
                colNum = 1
                For Each tb_1_2 In tb_1_1.getElementsByTagName("div")
                    Cells(rowNum, colNum).Value = tb_1_2.innerText
                    colNum = colNum + 1
                Next tb_1_2
            Next tb_1_1
            Exit For
        Next tb_1
        

        'get dividend data
        Set dividend = htmlDoc.getElementsByClassName("table-body-wrapper")
        For Each tb_2 In dividend
            'rowNum = 3
            For Each tb_2_1 In tb_2.getElementsByTagName("ul")
                rowNum = 3
                'colNum = 1
                For Each tb_2_2 In tb_2_1.getElementsByTagName("li")
                    colNum = 1
                    For Each tb_2_3 In tb_2_2.getElementsByTagName("div")
                        Cells(rowNum, colNum).Value = tb_2_3.innerText
                        colNum = colNum + 1
                    Next tb_2_3
                    rowNum = rowNum + 1
                Next tb_2_2
                
            Next tb_2_1
            Exit For
        Next tb_2
    End If
End Sub

[jdelano]

Maybe using Power Query and the query.yahoo.com URL would be a better option https://howtoexcel.net/2021/03/how-t...wer-query.html

[p45cal]

Maybe using Power Query and the query.yahoo.com URL would be a better option https://howtoexcel.net/2021/03/how-t...wer-query.html

I think so too; I got this into Excel in under 10 minutes with Power Query with NO coding, no writing of M-code or anything, just using the user interface:

2024-04-16_112417.jpg

xyz987 seems to be hellbent on using other means and/or has an aversion to Power Query as he has turned down similar suggestions in the past:
http://www.vbaexpress.com/forum/show...l=1#post422911
http://www.vbaexpress.com/forum/show...l=1#post423174
http://www.vbaexpress.com/forum/show...l=1#post423156

[xyz987]

Hi p45cal ,
thanks for your help, and sorry maybe let you feel i don`t like your answer, any tooling is okay, i just like to practice code skill, so i try to use vba to slove. even sometimes no body answer me,
practisepractisepractise

[xyz987]

Hi, body: already to solve 1. yahoo support api ( need change web view form history to dividend will show at develop mode )
2. html mode, and calculate the formula
api_1.jpgapi_2.jpgapi_3.jpg



html_1.jpghtml_2.jpghtml_3.jpg