?
Solved

Finding data in HTML code

Posted on 2005-03-18
15
Medium Priority
?
605 Views
Last Modified: 2013-12-26
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
0
Comment
Question by:pcpaquete
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +3
15 Comments
 
LVL 13

Expert Comment

by:rettiseert
ID: 13577841
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
 
LVL 10

Expert Comment

by:edwardiii
ID: 13578222
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
 
LVL 10

Expert Comment

by:edwardiii
ID: 13578248
Sorry--please delete the "strPrevClose" and "intPrevCloseChunk" variables, they were used for testing in VB 6 on Win2k Pro machine.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 13580857
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
 

Author Comment

by:pcpaquete
ID: 13583315
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
 
LVL 10

Accepted Solution

by:
edwardiii earned 600 total points
ID: 13584292
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
 
LVL 14

Assisted Solution

by:Shiju Sasidharan
Shiju Sasidharan earned 600 total points
ID: 13588752
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
 
LVL 10

Expert Comment

by:edwardiii
ID: 13592221
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
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 13603747
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
 
LVL 6

Assisted Solution

by:cjard
cjard earned 600 total points
ID: 13635950
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
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 13637648
>>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
 
LVL 10

Expert Comment

by:edwardiii
ID: 14100219
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question