CODEWITHSUNDEEP
htmlvbaexcel
user2505650
user2505650

Reputation: 1381

Read Table from HTML in Excel with vba

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

Upvotes: 1

Views: 4166

Answers (3)

Tim Williams
Tim Williams

Reputation: 166146

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)

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

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)

Upvotes: 1

Simon White
Simon White

Reputation: 146

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!

Upvotes: 1

Related Questions