CODEWITHSUNDEEP
excelvbaweb-scraping
bibsn
bibsn

Reputation: 23

How to extract the name of the stock in yahoo finance

I have found a code that extracts all data from the tables in the "financial" tab in yahoo finance. Now I'll want to have the possibility to extract also the name of the stock to insert the cell A3, near to the stock symbol.

'this is the code to exctact the data
 Dim htm, Tr, Td, Tab1, Tab2, HTML_Content As Object
 Dim Web_URL As String
 Dim Column_Num_To_Start, iRow, iCol, eCol, iTable As Integer
 Dim Val, varVal1, varVal2, varVal3 As Variant
 Dim chtObj As ChartObject

 Column_Num_To_Start = 1
 iRow = 7
 iCol = Column_Num_To_Start
 iTable = 2

 Web_URL = "https://finance.yahoo.com/quote/" & Cells.Range("A2").Value & "/financials?p=" & Cells.Range("A2").Value

 Set HTML_Content = CreateObject("htmlfile")

 With CreateObject("msxml2.xmlhttp")
   .Open "GET", Web_URL, False
   .send
   HTML_Content.body.innerHTML = .responseText
 End With

 For Each Tab1 In HTML_Content.getElementsByTagName("table")
     With HTML_Content.getElementsByTagName("table")(iTable)
         For Each Tr In .Rows
             For Each Td In Tr.Cells
             ActiveSheet.Cells(iRow, iCol).Select
             ActiveSheet.Cells(iRow, iCol) = Td.innerText
             ActiveCell.WrapText = False
             iCol = iCol + 1
             Next Td
          eCol = iCol - 1
          iCol = Column_Num_To_Start
          iRow = iRow + 1
          Next Tr
        End With
    Exit For
Next Tab1

Upvotes: 2

Views: 273

Answers (2)

QHarr
QHarr

Reputation: 84465

Across the stocks I looked at briefly it seems it is always the first h1 so you can literally use a fast css selector of 

HTML_Content.querySelector("h1").innerText

You can also use an attribute = value css selector

HTML_Content.querySelector("[data-reactid='7']").innerText

As a side note of interest to some: you can use the complex class as follows:

HTML_Content.querySelector(".D\28 ib\29 > h1").innerText

Modern browsers and many libraries are optimized for css so apart from older IE versions this is a fast selection method.

I looked at S&P 500 (^GSPC), BP p.l.c. (BP.L), Banco Santander, S.A. (SAN), Apple Inc. (AAPL), General Electric Company (GE), Alcoa Corporation (AA), KOSPI Composite Index (^KS11)

Upvotes: 2

Matteo NNZ
Matteo NNZ

Reputation: 12665

Right after executing your XMLHTTP request of the page:

With CreateObject("msxml2.xmlhttp")
   .Open "GET", Web_URL, False
   .send
   HTML_Content.body.innerHTML = .responseText
End With

...you can get the stock name from the header h1:

stockName = HTML_Content.getElementById("quote-header-info").getElementsByTagName("h1")(0).innerText

... and then you can put it where you want (e.g. Range("A3").Value = stockName).

Note: I've made my test on https://finance.yahoo.com/quote/SAN/financials?p=SAN, guessing that was what your URL would have looked like.

Upvotes: 2

Related Questions