Using Excel VBA to scrape HTML

Question

I have been trying to scrape and parse a website for some financial data so that I can add the data to an Excel spreadsheet using VBA. I have found several possible solutions, but I cannot seem to get them to fit my parameters. My problem is that I only need one variable (Average Target Price) from a table. I have not been able to figure out what I am doing wrong. I will also be using a similar VBA format to check several hundred companies at a time so if there is a more efficient way to code what I have please let me know.

Here is what I have so far:

Sub ImportAnalystEst()

Dim oHtml       As HTMLDocument
Dim oElement    As IHTMLElement

Set oHtml = New HTMLDocument

With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "http://www.marketwatch.com/investing/stock/aapl/analystestimates", False
    .send
    oHtml.body.innerHTML = .responseText
End With

Dim wsTarget As Worksheet
Dim i As Integer
i = 1
Set wsTarget = ActiveWorkbook.Worksheets("Sheet1")

For Each oElement In oHtml.getElementsByClassName("snapshot")
  wsTarget.Range("A" & i) = Split(oElement.Children(0).innerText, "<TD>")
  i = i + 1
Next

End Sub

Here is the HTML I am trying to pull from. Can someone please give an example of how I could extract the average target price of 146.52?

<div class="analystEstimates">

<div class="block">
    <h2>Snapshot</h2>
</div>
<table class="snapshot">
    <tbody>
        <tr>
            <td class="first">Average Recommendation:</td>
            <td class="recommendation">
                Overweight
            </td>
            <td class="first column2">Average Target Price:</td>
            <td>146.52</td>
        </tr>
        <tr>
            <td class="first">Number of Ratings:</td>
            <td>

Why not just look at innerText and use a Regular Expression to get at the value you want? — Ron Rosenfeld
– Ron Rosenfeld, Commented Dec 5, 2015 at 2:12
If you can rely on the layout, and don't have go searching for "Average Target Price", then Split(oHtml.getElementsByClassName("snapshot").item(0).firstchild.firstchild.innerhtml,"TD")(7) would return: >146.52</ which you could then clean up. — Ron Rosenfeld
– Ron Rosenfeld, Commented Dec 5, 2015 at 2:34

Part_Time_Nerd · Accepted Answer · 2015-12-05 06:12:30Z

1

I was able to solve my issue with the following:

Sub ImportAnalystEst()
Dim oHtml       As HTMLDocument
Dim oElement    As IHTMLElement

Set oHtml = New HTMLDocument


With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "http://www.marketwatch.com/investing/stock/aapl/analystestimates", False
    .send
    oHtml.body.innerHTML = .responseText
End With

Dim wsTarget As Worksheet
Dim i As Integer
i = 1
Set wsTarget = ActiveWorkbook.Worksheets("Sheet1")


For Each oElement In oHtml.getElementsByClassName("snapshot")
  wsTarget.Range("A" & i) = Split(oHtml.getElementsByClassName("snapshot").Item(0).FirstChild.FirstChild.innerHTML, "TD")(7)
  wsTarget.Range("A" & i) = Replace(wsTarget.Range("A" & i), ">", "")
  wsTarget.Range("A" & i) = Replace(wsTarget.Range("A" & i), "</", "")
  i = i + 1
Next


End Sub

answered Dec 5, 2015 at 6:12

Part_Time_Nerd

1,0147 gold badges30 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ron Rosenfeld Over a year ago

Why do you have the For Each ... Next loop?

Part_Time_Nerd Over a year ago

I am actually going to be looping through a series of iterations. That is the next step in my process. I am creating and troubleshooting that now. My final product will actually have a couple hundred stock symbols in column A and then put the analyst estimate in column E. So the GET function as well as the For Each function will have to be some sort of loop. You may have noticed the “aapl” in the above example. That is the stock symbol that will change based on column A. Do you have any suggestions for a corrected or more efficient structure?

Ron Rosenfeld Over a year ago

I would check to see if you can obtain multiple quotes from marketwatch with a single call, for the parameters in which you are interested. I do that with Fidelity, but I am only looking for current quotes and time.

QHarr · Accepted Answer · 2018-10-10 06:32:02Z

Far easier to use a CSS selector combination to target the value by it's position as the first row table cell in the second column of the table. The CSS selector is .snapshot .first.column2 + td which uses "." class selector, " " descendant combinator, and "+" adjacent sibling combinator.

Option Explicit
Public Sub ImportAnalystEst()
    Dim oHtml       As HTMLDocument
    Dim oElement    As IHTMLElement

    Set oHtml = New HTMLDocument

    With CreateObject("WINHTTP.WinHTTPRequest.5.1")
        .Open "GET", "http://www.marketwatch.com/investing/stock/aapl/analystestimates", False
        .send
        oHtml.body.innerHTML = .responseText
    End With
    Debug.Print oHtml.querySelector(".snapshot .first.column2 + td").innertext
End Sub

QHarr · Accepted Answer · 2018-10-10 06:16:02Z

0

This will do what you want.

Sub Test() Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .Navigate "http://www.marketwatch.com/investing/stock/aapl/analystestimates" ' should work for any URL
    Do Until .ReadyState = 4: DoEvents: Loop

        x = .document.body.innertext
        y = InStr(1, x, "Average Target Price:")
        Z = Mid(x, y, 6)

        Range("A1").Value = Trim(Z)

        .Quit
    End With
End Sub

edited Oct 10, 2018 at 6:16

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

answered Dec 8, 2015 at 13:50

ASH

20.5k28 gold badges115 silver badges247 bronze badges

Collectives™ on Stack Overflow

Using Excel VBA to scrape HTML

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related