Import the Market Data and Charting techniques
Sub RetrieveDataFromYahoo_and_Charting()
'Find the max value to run the loop based on Number of tickers
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Yahoo_Data")
maxrow = sh.Range("A" & Rows.Count).End(xlUp).Row
maxcolumn = sh.Range("C1").End(xlToRight).Column
sh.Range(Cells(2, 2), Cells(maxrow, maxcolumn)).Clear
'Creating Object for Internet Explorer
Dim IE As New InternetExplorer
r = 2 ' As ticker symbols starts from 2nd row
Set IE = New InternetExplorer
IE.Visible = True
'Run the Loop until the blank cell hits in COLUMN "A"
Do Until sh.Range("A" & r).Value = ""
'Display the Internet Explorer and navigate to website
Ticker = sh.Range("A" & r).Value
WebLink = "https://finance.yahoo.com/quote/" & Ticker & "?p=" & Ticker & "&.tsrc=fin-srch"
IE.navigate WebLink
Application.Wait (Now + TimeValue("00.00.05"))
'Retrieve all the elements based on CLASS NAME
Set Cnames = IE.document.getElementsByClassName("D(ib) Fz(18px)")
For Each Cname In Cnames
sh.Cells(r, 2).Value = Cname.innerText
sh.Columns(2).AutoFit
Next
TotalTableTags = IE.document.getElementsByTagName("table")
For ElementNumber = 0 To Len(IE.document.getElementsByTagName("tr")) - 1
If IE.document.getElementsByTagName("tr")(ElementNumber).Children(0).innerText = sh.Cells(1, 3).Value Then
sh.Cells(r, 3).Value = IE.document.getElementsByTagName("tr")(ElementNumber).Children(1).innerText
End If
If IE.document.getElementsByTagName("tr")(ElementNumber).Children(0).innerText = sh.Cells(1, 4).Value Then
sh.Cells(r, 4).Value = IE.document.getElementsByTagName("tr")(ElementNumber).Children(1).innerText
End If
If InStr(IE.document.getElementsByTagName("tr")(ElementNumber).Children(0).innerText, sh.Cells(1, 5).Value) > 0 Then
sh.Cells(r, 5).Value = IE.document.getElementsByTagName("tr")(ElementNumber).Children(1).innerText
End If
If IE.document.getElementsByTagName("tr")(ElementNumber).Children(0).innerText = sh.Cells(1, 6).Value Then
sh.Cells(r, 6).Value = IE.document.getElementsByTagName("tr")(ElementNumber).Children(1).innerText
End If
If IE.document.getElementsByTagName("tr")(ElementNumber).Children(0).innerText = sh.Cells(1, 7).Value Then
sh.Cells(r, 7).Value = IE.document.getElementsByTagName("tr")(ElementNumber).Children(1).innerText
End If
If IE.document.getElementsByTagName("tr")(ElementNumber).Children(0).innerText = sh.Cells(1, 8).Value Then
sh.Cells(r, 8).Value = IE.document.getElementsByTagName("tr")(ElementNumber).Children(1).innerText
End If
If IE.document.getElementsByTagName("tr")(ElementNumber).Children(0).innerText = "EPS (TTM)" Or IE.document.getElementsByTagName("tr")(ElementNumber).Children(0).innerText = "Ex-Dividend Date" Then
Exit For
End If
Next
Application.Wait (Now + TimeValue("00.00.02"))
r = r + 1
Loop
IE.Quit
Set IE = Nothing
FormattingSheet
MsgBox "Hi Process Completed"
End Sub