Consulting

Results 1 to 5 of 5

Thread: How to distinguish data from html

  1. #1
    VBAX Regular
    Joined
    Mar 2024
    Posts
    21
    Location

    How to distinguish data from html

    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
    Attached Files Attached Files

  2. #2
    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

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,911
    Quote Originally Posted by jdelano View Post
    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
    Last edited by p45cal; 04-16-2024 at 03:44 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Joined
    Mar 2024
    Posts
    21
    Location
    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

  5. #5
    VBAX Regular
    Joined
    Mar 2024
    Posts
    21
    Location

    2 method vba to solve

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •