Oct 17 2020 05:20 PM
Hello - I have an Excel spread that pulls in the latest silver prices from https://www.kitco.com/Silver-price-today-USA/ but I need to extract the date that the price was last updated like "Oct 16, 2020 16:59 NY Time" into my spread. Is that possible? The connection and refresh of the table was easy but don't see a way of extracting that important piece of info. Thanks for any help!
Oct 18 2020 12:22 AM
Solution
You may use this User Defined Function to get the desired info from the web page.
Place this UDF on a Standard Module like Module1.
Function getDate(url As String)
Dim ie As Object
Dim doc As Object
Dim eDate As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate url
.Visible = False
Do While .readyState <> 4
DoEvents
Loop
End With
Set doc = ie.document
Set eDate = doc.getElementsByClassName("date")(0)
getDate = eDate.innerText
ie.Quit
Set ie = Nothing
End Function
And then you may use the above UDF like a regular Excel function on the worksheet like this...
In a blank cell, place the following function...
=getDate("https://www.kitco.com/Silver-price-today-USA/")
and this will return Oct 16, 2020 16:59 NY Time in the formula cell.
For more details, refer to the attached.
Oct 18 2020 06:21 AM
@Subodh_Tiwari_sktneer Thank you for the detailed info...it worked great!!
Oct 18 2020 08:20 AM
You're welcome @JoanneS! Glad it worked as desired.
Oct 18 2020 12:22 AM
Solution
You may use this User Defined Function to get the desired info from the web page.
Place this UDF on a Standard Module like Module1.
Function getDate(url As String)
Dim ie As Object
Dim doc As Object
Dim eDate As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate url
.Visible = False
Do While .readyState <> 4
DoEvents
Loop
End With
Set doc = ie.document
Set eDate = doc.getElementsByClassName("date")(0)
getDate = eDate.innerText
ie.Quit
Set ie = Nothing
End Function
And then you may use the above UDF like a regular Excel function on the worksheet like this...
In a blank cell, place the following function...
=getDate("https://www.kitco.com/Silver-price-today-USA/")
and this will return Oct 16, 2020 16:59 NY Time in the formula cell.
For more details, refer to the attached.