accessing html elements of ASX website dividend table using excel vba

Question

I am trying to download divident data from ASX!

I need to know how to loop through each row in the table and each td (data element) in that table to print the innertext to the excel worksheet.

Here is my code.... using this as a guide

VBA excel For each row in table match cell in spreadsheet with cell in webpage table

Sub WebTable()

Dim ie As New InternetExplorer
Set ie = Nothing
ie.Visible = False
Dim url As String
Dim doc As HTMLDocument
Dim items As Variant
Dim tr As String
Dim tbody As String
Dim td As String
Dim r As Double
Dim i As Double

Dim tbObj As Variant
Dim trObj As Variant
Dim tdObj As Variant
Dim tdItem As Variant
Dim element

    tbody = "tbody"
    tr = "tr"
    td = "td"
    url = "http://www.asx.com.au/asx/markets/dividends.do?by=asxCodes&asxCodes=ont&view=latest"

    ie.navigate url
    Debug.Print url

    'loop until complete so program doesn't freeze
    Do
    DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

'get everything in the dividends table using its id
    Set tbObj = ie.document.getElementById("dividends") 'all table elements including headers

    'get the four rows for the ONT stock (headings and data elements
    Set trObj = tbObj.getElementsByTagName(tr)


    'get the eight data items for each row of the ONT stock e.g stock price date etc.
    For Each trObj In tbObj

'print each data element one by one for each of the four rows
        Set tdObj = tbObj.items(0).getelementbytagname("td")
        Set Count = tdObj.length
        Debug.Print tdObj(0).innerText
    Next trObj

End Sub

santosh your weebsite is full of spam its only sponsored links. what are you trying to pull? — yoshiserry, Dec 13 '13 at 09:59
i just noticed it only shows two rows when you use a web query but if you goto the site: http://www.asx.com.au/asx/markets/dividends.do?by=asxCodes&asxCodes=ont&view=latest its shows all four rows (multiple years of dividends) — yoshiserry, Dec 13 '13 at 10:05
You could use the ASX API, which returns JSON, eg https://www.asx.com.au/asx/1/company/ONT/dividends/history?years=10 — InnocentBystander, Aug 15 '21 at 09:47

Santosh · Accepted Answer · 2013-12-13T10:41:20.683

0

Try this

Sub WebTable()

    Dim ie As New InternetExplorer
    Dim url As String

    Dim trCollection As Object
    Dim tdCollection As Object

    Dim tdObj As Object
    Dim trObj As Object


    url = "http://www.asx.com.au/asx/markets/dividends.do?by=asxCodes&asxCodes=ont&view=latest"

    ie.navigate url
    Debug.Print url


'loop until complete so program doesn't freeze
    Do
        DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

'get everything in the dividends table using its id
    Set tbObj = ie.document.getElementById("dividends") 'all table elements including headers

'get the four rows for the ONT stock (headings and data elements
    Set trCollection = tbObj.getElementsByTagName("tr")

Dim i As Integer
 i = 1
'get the eight data items for each row of the ONT stock e.g stock price date etc.
    For Each trObj In trCollection

    If i = 1 Then
'print each data element one by one for each of the four rows
        Set tdCollection = trObj.getElementsByTagName("th")
        Else
          Set tdCollection = trObj.getElementsByTagName("td")

      End If

      i = i + 1

        For Each tdObj In tdCollection

            Debug.Print tdObj.innerText
        Next

    Next

End Sub

enter image description here

edited Dec 13 '13 at 10:41

answered Dec 13 '13 at 10:05

Santosh

12,175
4
41
72

@user3098818 i have removed unwanted variables. – Santosh Dec 13 '13 at 10:14
trying now. only prints data in last row of table see link. only prints bottom line in green. https://drive.google.com/file/d/0B9QWdVxiXWaRbnBpTGp1S2ZFU0U/edit?usp=sharing – yoshiserry Dec 13 '13 at 10:24
@user3098818 The first row is a header row with TH(Table Header) tag / Second row onwards it will be TD – Santosh Dec 13 '13 at 10:26
@user3098818 If you want header to be printed change this `Set tdCollection = trObj.getElementsByTagName("th")` – Santosh Dec 13 '13 at 10:28
think it has something to do with the following command only returning two instead of four rows??? no idea why it does this? Debug.Print trCollection.length – yoshiserry Dec 13 '13 at 10:30
@user3098818 I have updated to code to print entire table including header. – Santosh Dec 13 '13 at 10:31
yes santosh but there are four rows when you view the dividend website in a browser one of those contains TH but 3 rows contain TD elements don't they when you INSPECT the ELEMENTS FF press F12 – yoshiserry Dec 13 '13 at 10:32
hence why I still dont understand it only printing one? I understand 3/4 since 3/4 are TD and one like you said is TH but why is it only printing one row? – yoshiserry Dec 13 '13 at 10:32
@user3098818 I see only 2 rows(including header) for table with id dividends. – Santosh Dec 13 '13 at 10:33
yes you will see that if you look at DATA TAB web query and paste the URL there, but look at the data in any normal web browser, look at my link its a screenshot of the four table rows. – yoshiserry Dec 13 '13 at 10:34
i am not able to access the link ..but i can see in the url two rows and the code is working as expected. – Santosh Dec 13 '13 at 10:35
@user3098818 See the image in my answer. – Santosh Dec 13 '13 at 10:41
this picture shows everything i said https://drive.google.com/file/d/0B9QWdVxiXWaReEdiSFdaT3NJdms/edit?usp=sharing – yoshiserry Dec 13 '13 at 10:41
SANTOSH? no images in your answers? put the link directly in your web browser url bar. there are clearly four rows? http://www.asx.com.au/asx/markets/dividends.do?by=asxCodes&asxCodes=ont&view=latest – yoshiserry Dec 13 '13 at 10:43
try dropbox link, shows pictures, and the difference between two and four rows. clearly there are four rows. in the web browser. https://www.dropbox.com/sh/wcqkbh2mu5fkudm/6SPj7xMEA_ – yoshiserry Dec 13 '13 at 10:45
@user3098818 Can you see the image in answer ? May be when you had taken a screenshot it had 4 rows but now it has 2 rows. – Santosh Dec 13 '13 at 10:47
what tool did you use to analyse HTML? – yoshiserry Dec 13 '13 at 10:48
@user3098818 The one you suggested press F12 – Santosh Dec 13 '13 at 10:50
in firefox, try firebug extension in chrome try right click inspect element. firefox right click inspectwill work too – yoshiserry Dec 13 '13 at 10:50
i added another photo to my dropbox link showing 4 table rows after using inspect elements in firefox F12 like I suggested. – yoshiserry Dec 13 '13 at 10:56
that dropbox link again is – yoshiserry Dec 13 '13 at 10:56
I've just tested all my browsers FF chrome and IE and IE is the only one which cannot see four table rows? Any way of looping thorugh the table rows after opening a firefox connection to the webpage? – yoshiserry Dec 13 '13 at 11:01
@user3098818 When automation is done using IE object does it make sense to see FF or Chrome ? – Santosh Dec 13 '13 at 11:06
Ive no idea why all browsers are now suddenly showing one header row and one table row? WTF Confused! – yoshiserry Dec 13 '13 at 11:12
1

automation can be done in FF too. ok I can access websites with it, but not sure how to loop through elements... maybe I will have to use IE. in which case given all browsers are now only showing one year worth of dividends you're code works exceptionally well. Thanks for your patience. – yoshiserry Dec 13 '13 at 11:17
@user3098818 No probs...automation can be done in FF via vba ? how ? can you share some code snippets or links for it. – Santosh Dec 13 '13 at 11:21

accessing html elements of ASX website dividend table using excel vba

1 Answers1