Finding data in HTML code

Hi,

With a webbrowser control, I'd like to navigate to Yahoo Finance, and get the previous closing price (Prev Close: )

e.g.   webbrowser1.Navigate ("http://finance.yahoo.com/q?s=yhoo")

But what's the code that I need to look through the page to search for the quote I'm looking for...

Thanks

pcpaquete
pcpaqueteAsked:
Who is Participating?
 
edwardiiiConnect With a Mentor Commented:
Sorry for the late reply, pcpaquete.  Had to do some Motocrossing in the rain:)

Yes, the entire procedure can be done with a single click by following your "WebBrowser1.Navigate ("http://finance.yahoo.com/q?s=yhoo")" command with the following code:

     Do While WebBrowser1.Busy
          DoEvents
     Loop

This makes sure the page is fully loaded before attempting the "...innerHTML" instruction.  Also, I didn't include the full <tr> code for our Instr command; I just included that code that distinguishes the desired <td> (e.g. ">Prev Close:</TD>"). The revised code (that works perfectly for me--let me know if it doesn't work for you) that could be dropped in your command button's Click() event is:

Private Sub Command1_Click()

    Dim strPageContent, strPrevCloseChunk As String
    Dim intPrevClose As Integer
   
    WebBrowser1.Navigate ("http://finance.yahoo.com/q?s=yhoo")
   
    Do While WebBrowser1.Busy
        DoEvents
    Loop

   
    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")

End Sub
0
 
rettiseertCommented:
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
0
 
edwardiiiCommented:
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")
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
edwardiiiCommented:
Sorry--please delete the "strPrevClose" and "intPrevCloseChunk" variables, they were used for testing in VB 6 on Win2k Pro machine.
0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi
Can u give some example of the pattern u want to search for
for example
   href="data1"
   value="data2"
   name="data3"
etc...

;-)
Shiju
0
 
pcpaqueteAuthor Commented:
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
0
 
Shiju SasidharanConnect With a Mentor Assoc Project ManagerCommented:
Hi
i am appending little bit code to edwardiii's code
(using regular expressions,we can extract exact pattern we need to extraxct)

'goto
'                    Project Menu ->  References
' add the referece
'                    Microsoft VBScript Regular Expressions 5.5
'-------------------------------------------------------------------
Private Sub Command1_Click()
Dim strPageContent, strPrevCloseChunk As String
Dim sPrevClose As String
Dim objRegExp As New RegExp
Dim objMatchCol As MatchCollection
    Screen.MousePointer = vbHourglass
    WebBrowser1.Navigate ("http://finance.yahoo.com/q?s=yhoo")
    Do While WebBrowser1.Busy
        DoEvents
    Loop
    strPageContent = WebBrowser1.Document.documentElement.innerHTML 'grabs all HTML content
    With objRegExp
        .Global = True
        .IgnoreCase = True
        .Pattern = "<tr>\s*(<td class=""?yfnc_table)head1""? width=""?\d+%""?>Prev Close\:<\/td>\s*\1data1""?>(.*?)<\/td><\/tr>"
    End With
    Set objMatchCol = objRegExp.Execute(strPageContent)
    txtPrevClose.Text = "Not Found"
    If objMatchCol.Count > 0 Then
        txtPrevClose.Text = objRegExp.Replace(objMatchCol.Item(0), "$2")
    End If
    Set objRegExp = Nothing
    Screen.MousePointer = Default
End Sub
'-----------------------------------------------------
;-)
Shiju
0
 
edwardiiiCommented:
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
0
 
zzzzzoocCommented:
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
0
 
cjardConnect With a Mentor Commented:
there's some dodgy code in this thread.. tight looping on doevents just to make the machine wait? euww...

Option Explicit
Private sURL As String

Private Sub Form_Load()
  sURL = "http://finance.yahoo.com/q?s=yhoo"
  WebBrowser1.Navigate2 sURL
End Sub

Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
  If URL <> sURL Then Exit Sub 'cater for inner frames firing the event
 
  Dim trCollection As Object
  Dim i As Integer
 
  Set trCollection = WebBrowser1.Document.All.Tags("TR")
  For i = 0 To trCollection.length - 1
    If LCase$(Left$(trCollection(i).innertext, 10)) = "prev close" Then
      Dim sArray() As String
      sArray = Split(trCollection(i).innertext, ":")
      MsgBox "The closing price is: " & sArray(1)
    End If
  Next i
End Sub



similar to the solution above, though the one above has a slightly confusing way of working.. :)
0
 
zzzzzoocCommented:
>>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.
0
 
edwardiiiCommented:
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.
0
All Courses

From novice to tech pro — start learning today.