CODEWITHSUNDEEP
htmlexcelvbaweb-scraping
clach12
clach12

Reputation: 3

HTML Scrape is getting different number now

I built the below code to pull the price of a mutual fund into excel via VBA. It worked up until last night. It just started pulling a different number (the % return on the dow. Top of page.). I looked to see if the website layout changed, but can't figure it out.

I believe the code is getting confused between the "value" I am trying to pull and the "value positive" class for the dow.

Is there a way for the code to focus on "value" not "value positive"?

    Sub ExtractLastValue()

    Set objIE = CreateObject("InternetExplorer.Application")

    objIE.Top = 0
    objIE.Left = 0
    objIE.Width = 800
    objIE.Height = 800

    objIE.Visible = True

    objIE.Navigate ("https://www.marketwatch.com/investing/fund/lfmix")

    Do
    DoEvents
    Loop Until objIE.readystate = 4

    Dim myValue As String: myValue = objIE.document.getElementsByClassName("value")(0).innerText

    Range("C3").Value = myValue

   End Sub

Upvotes: 0

Views: 83

Answers (2)

SIM
SIM

Reputation: 22440

You can scrape that value in several ways. Here is one of the faster methods. When the execution is done, you should get the value in Range("C3").

Sub FetchValue()
    Const Url$ = "https://www.marketwatch.com/investing/fund/lfmix"
    Dim Html As New HTMLDocument, S$, elem As Object

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", Url, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; ) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.61 Safari/537.36"
        .send
        Html.body.innerHTML = .responseText
        [C3] = Html.querySelector("h3.intraday__price  > .value").innerText
    End With
End Sub

Make sure you don't execute the script incessantly as the site is very good at banning ips temporarily.

Upvotes: 0

Zwenn
Zwenn

Reputation: 2267

Always declare all variables. The best way to do this is to write Option Explicit at the top of each module. The declarations also belong at the top of the macro.

First fence the desired value before you get it via the CSS class "value".

Option Explicit

Sub ExtractLastValue()

Dim objIE As Object
Dim url As String
Dim myValue As String

  url = "https://www.marketwatch.com/investing/fund/lfmix"

  Set objIE = CreateObject("InternetExplorer.Application")
  objIE.Top = 0
  objIE.Left = 0
  objIE.Width = 800
  objIE.Height = 800
  objIE.Visible = True
  objIE.navigate url
  Do: DoEvents: Loop Until objIE.readyState = 4

  myValue = objIE.document.getElementsByClassName("intraday__price")(0).getElementsByClassName("value")(0).innerText
  Range("C3").Value = myValue
End Sub

Upvotes: 1

Related Questions