VBA web scraping

Question

I am trying to get a row of data from this table on this website: http://www.nasdaq.com/symbol/neog/financials?query=balance-sheet

Now I can manage to get the "total liabilities" row using the

doc.getelementsbyclassname("net")(3).innertext

but I cannot figure out how to get any other rows of data such as common stock.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("bscode").Row And _
Target.Column = Range("bscode").Column Then
Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate "http://www.nasdaq.com/symbol/" & Range("bscode").Value & "/financials?query=balance-sheet&data=quarterly"

Do
 DoEvents

Loop Until IE.readyState = READYSTATE_COMPLETE

Dim Doc As HTMLDocument
Set Doc = IE.document
Dim sD As String
sD = Doc.getElementsByTagName("tr")(8).innerText

MsgBox sD

Dim aD As Variant
aD = Split(sD, "$")
Range("bs").Value = aD(1)
Range("ba").Value = aD(2)
Range("bb").Value = aD(3)
Range("bc").Value = aD(4)

End If
End Sub

If it helps, I have the HTML source and the tr highlighted that I want to grab.

screenshot of HTML code

The issue is the method of finding the table row data. Could someone please explain to me how to get other rows of data? It would be much appreciated !

JPs IE Automation Tips have helped tremendously in exploring how to scrape data from webpages in VBA. There is code in there to loop through elements which you can then use debug.print to figure out which one you need. — Scott Holtzman
– Scott Holtzman, Commented Oct 21, 2015 at 13:46
@ScottHoltzman The link you provide no longer works. Is there another source for it? — Rick Henderson
– Rick Henderson, Commented Feb 21, 2016 at 3:25
@RickHenderson - Yes, that website has been taken down. There are plenty of resources on the web if you search a bit. — Scott Holtzman
– Scott Holtzman, Commented Feb 22, 2016 at 13:43

Scott Holtzman · Accepted Answer · 2015-10-21 14:05:43Z

0

I was able to do some trial and error and to get the correct reference this way:

Dim eTR As Object, cTR As Object, I as Integer 'I used object, because I did late binding
Set cTR = Doc.getElementsByTagName("tr")

i = 0

For Each eTR In cTR

    If Left(eTR.innerText, 3) = "Com" Then

        Debug.Print "(" & i; "): " & eTR.innerText

    End If

    i = i + 1

Next

The immediate window then displayed

(308): Common Stocks ... (a bunch of space) ... $5,941$5,877$5,773$3,779

I then tested this statement:

sd = Doc.getElementsByTagName("tr")(308).innerText
Debug.Print sd

And got the same result.

answered Oct 21, 2015 at 14:05

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

VBA web scraping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related