0

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 !

Community
  • 1
  • 1
Default001
  • 45
  • 6
  • [JPs IE Automation Tips](http://www.jpsoftwaretech.com/an-exploration-of-ie-browser-methods-part-ii/) 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 Oct 21 '15 at 13:46
  • @ScottHoltzman The link you provide no longer works. Is there another source for it? – Rick Henderson Feb 21 '16 at 03:25
  • @RickHenderson - Yes, that website has been taken down. There are plenty of resources on the web if you search a bit. – Scott Holtzman Feb 22 '16 at 13:43

1 Answers1

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.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72