Finding data in HTML code

Yahoo has a CSV file ready to download with all the data, so you can use Inet Control to get the information (Project->Components->Microsoft Internet Transfer control)

In a new form include a new Inet control and paste this code:

Private Sub Form_Load()

    Dim Info As Variant
    Dim Values As Variant
    Dim PrevClose As Double
    Dim NasdaqName As String

    NasdaqName = "YHOO"
   
    Info = Inet1.OpenURL("http://finance.yahoo.com/d/quotes.csv?s=" + NasdaqName + "&f=sl1d1t1c1ohgv&e=.csv")
   
    Values = Split(Replace(Info, """", ""), ",")
   
    PrevClose = Values(1) + Values(4) * -1
   
    MsgBox PrevClose, , "Prev close for " + NasdaqName
   
End Sub

Hi, pcpaquete.

You can do what you wish with the WebBrowser control as follows:

1) Add a textbox "txtPrevClose" for the stock's closing price.

I put the following in a command button's Click() event--this should be clicked after the Web page is loaded, or more coding is required(e.g. command button #1 loads Web page, and the code below goes in command button #2:

    Dim strPageContent, strPrevClose, strPrevCloseChunk As String
    Dim intPrevClose, intPrevCloseChunk As Integer
   
    strPageContent = WebBrowser1.Document.documentElement.innerHTML 'grabs all HTML content
   
    intPrevClose = InStr(1, strPageContent, ">Prev Close:</TD>", 1)            'finds "Prev Close" between <td> tags
    strPrevCloseChunk = Mid(strPageContent, (intPrevClose + 44), 6)            'creates data chunk spanning 44 characters
    If InStr(1, strPrevCloseChunk, ">") > 0 Then                                          'if price is over $100, good, if not, kills ">"
        strPrevCloseChunk = Replace(strPrevCloseChunk, ">", "")                
    End If
   
    txtPrevClose.Text = Format(strPrevCloseChunk, "currency")

Sorry--please delete the "strPrevClose" and "intPrevCloseChunk" variables, they were used for testing in VB 6 on Win2k Pro machine.

hi
Can u give some example of the pattern u want to search for
for example
   href="data1"
   value="data2"
   name="data3"
etc...

;-)
Shiju

edwardiii,

would it be possible to do it only in one procedure, rather than clicking on 2 buttons?

and the HTML code for the prev close is:

<tr><td class="yfnc_tablehead1" width="48%">Prev Close:</td><td class="yfnc_tabledata1">31.61</td></tr>

so could you rewrite the full procedure (sub) I should use

Thanks
pcpaquete

Hi, shijusn.

Nice touch with the mousepointer.  To get the hourglass to go away after the Yahoo page is done loading, I put a "vbnormal" mouse pointer command after the DoEvents loop:

     Screen.MousePointer = vbHourglass

     WebBrowser1.Navigate ("http://finance.yahoo.com/q?s=yhoo")
     Do While WebBrowser1.Busy
         DoEvents
     Loop

    Screen.MousePointer = vbNormal

Here's another approach using the DOM which will be a lot less strict on how the HTML is formatted but may be slightly more overhead. If you only want a "visual" search (similar to IE's "Find"), the DOM has built-in support for that as well.

Form1:
----------------
'Include reference to: Microsoft HTML Object Library
Option Explicit
Private Sub Command1_Click()
    'wait for browser to finish loading before clicking!
    Call MsgBox(MyFind("Prev Close:"))
    Call MsgBox(MyFind("Volume:"))
End Sub
Private Sub Form_Load()
    Call WebBrowser1.Navigate("http://finance.yahoo.com/q?s=yhoo")
End Sub
Private Function MyFind(ByVal strFind As String) As String
    Dim oDoc As HTMLDocument
    Dim oCol As IHTMLElementCollection
    Dim oTCl As HTMLTableCell
    Dim bYes As Boolean
    Set oDoc = WebBrowser1.Document
    If Not (oDoc Is Nothing) Then
        Set oCol = oDoc.All.tags("TD")
        For Each oTCl In oCol
            If bYes = False Then
                If StrComp(oTCl.innerText, strFind, vbTextCompare) = 0 Then
                    bYes = True
                End If
            Else
                MyFind = oTCl.innerText
                Exit Function
            End If
        Next
    End If
End Function

>>there's some dodgy code in this thread
You use complete late-binding for accessing the DOM interfaces which is slower because with early-binding (my method), the interfaces are already available whereas yours have to be queried. And if the query fails, it'll throw an error. Also, splitting each element's innerText property and then comparing it seems a bit more slower than just comparing it. Albeit you may loop through less elements, a For/Next statement would still be a lot faster I believe.

Hi, DanRollins.

I haven't had time to test the other Experts' solutions, but I do know my code works (tested it again right now and it returned a closing price of $37.14, which can be found on the Web page in question following the "Prev Close:" label, and it was the first solution posted that used a WebBrowser control as pcpaquete asked.