CODEWITHSUNDEEP
htmlstringvbaexcel
user1575474
user1575474

Reputation: 1

Getting data from HTML source in VBA (excel)

I'm trying to collect data from a website, which should be manageable once the source is in string form. Looking around I've assembled some possible solutions but have run into problems with all of them:

  1. Use InternetExplorer.Application to open the url and then access the inner HTML
  2. Inet
  3. use Shell command to run wget

Here are the problems I'm having:

  1. When I store the innerHTML into a string, it's not the entire source, only a fraction
  2. ActiveX does not allow the creation of the Inet object (error 429)
  3. I've got the htm into a folder on my computer, how do I get it into a string in VBA?

Code for 1:

Sub getData() Dim url As String, ie As Object, state As Integer Dim text As Variant, startS As Integer, endS As Integer

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = 0

url = "http://www.eoddata.com/stockquote/NASDAQ/AAPL.htm"
ie.Navigate url


state = 0
Do Until state = 4
    DoEvents
    state = ie.readyState
Loop


text = ie.Document.Body.innerHTML
startS = InStr(ie.Document.Body.innerHTML, "7/26/2012")
endS = InStr(ie.Document.Body.innerHTML, "7/25/2012")


text = Mid(ie.Document.Body.innerHTML, startS, endS - startS)

MsgBox text

Upvotes: 0

Views: 71398

Answers (1)

jokker
jokker

Reputation: 128

If I were trying to pull the opening price off from 08/10/12 off of that page, which is similar to what I assume you are doing, I'd do something like this:

    Set ie = New InternetExplorer
    With ie
        .navigate "http://eoddata.com/stockquote/NASDAQ/AAPL.htm"
        .Visible = False
        While .Busy Or .readyState <> READYSTATE_COMPLETE
           DoEvents
        Wend
        Set objHTML = .document
        DoEvents
    End With
    Set elementONE = objHTML.getElementsByTagName("TD")
    For i = 1 To elementONE.Length
        elementTWO = elementONE.Item(i).innerText           
        If elementTWO = "08/10/12" Then
            MsgBox (elementONE.Item(i + 1).innerText)
            Exit For
        End If
    Next i
    DoEvents
    ie.Quit
    DoEvents
    Set ie = Nothing

You can modify this to run through the HTML and pull whatever data you want. Iteration +2 would return the high price, etc.

Since there are a lot of dates on that page you might also want to make it check that it is between the Recent End of Day Prices and the Company profile.

Upvotes: 1

Related Questions