Scraping data with vba from Yahoo finance

Question

I need to read the closing price of a stock from the Yahoo Finance page. I had this answered before using Google Finance page, but the page is no longer available and I believe Google has completely changed its Finance page. I believe I can apply the same on Yahoo Finance with little modification.

Let's say Yahoo Finance has the following code for the stock symbol AAPL (Apple):

I need to only extract the value 172.77.

This was working perfectly with Google Finance page. In my code below.

The line:

    "https://finance.google.com/finance?q="

is replaced by:

    "https://finance.yahoo.com/quote/"

The code loops in a range of cells and reads the stock symbols. We need to get the same results but from the Yahoo page instead.

    Sub ImportCurrentPriceNEW()
    Dim appIE As New InternetExplorer, html As HTMLDocument
    Dim item_data As Object    

    For k = 6 To 26 Step 1
     s = 1
     H = 1
     L = 1
     StopLoop = 0
     q = Format(k, "0")
     If IsEmpty(ActiveSheet.Range("$E$" & q).Value) = True Then  

     With appIE
    .Visible = False
    .navigate "https://finance.google.com/finance?q=" & Sheets("Up Trend 
     Stocks").Range("$A$" & q).Value
    Do Until .readyState = 4: DoEvents: Loop
    Set html = .document
    End With

   Set item_data = html.querySelector(".pr span")
   Range("$B$" & q).Value = item_data.innerText               
   End If    
Next    
appIE.Quit
Range("D1").Select

End Sub

Please let me know how I can modify code above to read data from Yahoo Finance page.

Answer 1

Maybe you have an aversion to google, but I think you should consider using this small utility.

http://investexcel.net/multiple-stock-quote-downloader-for-excel/

That should do everything you want and a while lot more!!

Otherwise, in Excel, under the Data tab, click Existing Connections and then click MSN Money Central Investor Stock Quotes. See the image below. Enter your tickers/symbols, and click Open.

If the app is not installed already, click the link below, and follow the steps to get everything setup and working on your machine.

https://appsource.microsoft.com/en-us/product/office/WA104379220?tab=Overview