Read Table from HTML in Excel with vba

Question

I would like to get the top Gainer of the S&P500 (The Company that registered the highest stock surge in percentage) and put it on an excel cell.

I use the data from this website :

http://www.quotenet.com/index/market-movers/S&P_500

Unfortunately when I analyse the source code with my Browser the first entry of the table does not have an id so I don't know how to recognize it in my code.

Here is what I have written so far :

Dim ie As InternetExplorer, doc As HTMLDocument,, topmover As String
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate "http://www.quotenet.com/index/market-movers/S&P_500"


       Do
       DoEvents
       Loop Until ie.readyState = READYSTATE_COMPLETE

       Set doc = ie.document
'topmover= doc.getElementBy???

edit :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)



Dim ie As InternetExplorer, doc As HTMLDocument, rng As Range, ticker As String, topmover As String
Dim TopMoverTable As Object

Set rng = Sheets("Sheet1").Range("A1")
ticker = rng.Value

Set ie = CreateObject("InternetExplorer.Application")

If Target.Row = rng.Row And Target.Column = rng.Column Then

ie.navigate "http://www.quotenet.com/index/market-movers/S&P_500"



       Do
       DoEvents
       Loop Until ie.readyState = READYSTATE_COMPLETE

       Set doc = ie.document



      Set TopMoverTable = doc.getElementsByTagName("TABLE")(2)

Cells(1, 2) = Split(TopMoverTable.getElementsByTagName("TD")(0).innerText, vbCrLf)(0)
Cells(1, 3) = Split(TopMoverTable.getElementsByTagName("TD")(4).innerText, vbCrLf)(1)

Cells(2, 2) = Split(TopMoverTable.getElementsByTagName("TD")(1).innerText, vbCrLf)(0)
Cells(2, 3) = Split(TopMoverTable.getElementsByTagName("TD")(4).innerText, vbCrLf)(1)

End If

ie.Application.Quit


End Sub

Do you Know can I get the first entry of the first Table ? Thank you

Excel Hero · Accepted Answer · 2015-09-17 23:29:51Z

1

Create a variable to hold the table object. You'll need these two lines additional to your code:

Dim TopMoverTable As Object

Set TopMoverTable = doc.getElementsByTagName("TABLE")(2)

Cells(1, 2) = Split(TopMoverTable.getElementsByTagName("TD")(0).innerText, vbCrLf)(0)
Cells(1, 3) = Split(TopMoverTable.getElementsByTagName("TD")(1).innerText, vbCrLf)(0)

To get another record from the table you do this:

Cells(2, 2) = Split(TopMoverTable.getElementsByTagName("TD")(11).innerText, vbCrLf)(0)
Cells(2, 3) = Split(TopMoverTable.getElementsByTagName("TD")(12).innerText, vbCrLf)(0)

edited Sep 17, 2015 at 23:29

answered Sep 17, 2015 at 22:10

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2505650 Over a year ago

Then when I put : Cells(1, 2).Value = TopMoverTable I get [object HTMLTableElement] as an answer on Excel, do I have to convert it as String ?

user2505650 Over a year ago

sorry for my late answer,it is working for the first stock but how can I fetch the second mover in the table ? (I updated my answer with your code)

user2505650 Over a year ago

Thank you for your patience, o you know where I can document myself more in detail about this subject (Web Scrapping) ? I still do not understand for example where this 'vbCrLf' comes from

Excel Hero Over a year ago

vbCrLf is one of the constants built into VBA. It stands for Carriage Return and Line Feed. What my code does is split the values in each table cell on the line break between the two values. This is only required because that website decided to stuff two values into each cell. As far as learning more about scraping, just google it. Tons of blogs about it. Search for website scraping with VBA.

Simon White · Accepted Answer · 2015-09-17 22:22:46Z

1

You could search the text of the HTML to get the number you want:

I looked at the HTML you linked to, and it looks like your code first needs to search for: class="main". It appears 3 times, and the third appearance is your table of top gainers.

Once you have found this point in the file, then search for the following <tr> (which is the row of headers), and then the next <tr> after that, which is your first row.

From here, you need to count the <td>s: immediately after the first is the name of the stock, and it looks like the number you want is after the fifth one. There are two, and they are in <span>s, so you can search for that to finally get the actual numbers.

Note: be careful, as the source HTML has occurrences of <td... instead, which you would also need to be search for when you are searching for your <td>s!

answered Sep 17, 2015 at 22:22

Simon White

1558 bronze badges

1 Comment

Simon White Over a year ago

Although the answer from Excel Hero would be easier and more reliable!

Tim Williams · Accepted Answer · 2015-09-17 23:42:04Z

0

Tables have Rows and Cells collections:

Debug.Print TopMoverTable.Rows(1).Cells(0).innerText 'row 2 cell 1

Note they are zero-based indexes, not 1-based

Cells(3, 2).Value = Split(TopMoverTable.Rows(1).Cells(0).innerText, vbCrLf)(0)

edited Sep 17, 2015 at 23:42

answered Sep 17, 2015 at 23:21

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

user2505650 Over a year ago

Ho can I print this in a Cell ? This does not work : Cells(3, 2) = Split(TopMoverTable.getElementsByTagName("TD")TopMoverTable.Rows(1).Cells(0).innerText, vbCrLf)

Collectives™ on Stack Overflow

Read Table from HTML in Excel with vba

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related