Selected Answer
Mary Beth
Given you are collecting this dataeveryday (and I assume it's the current date, you simplify matters immensely by using VBA's built-in function Now (which will return both the date and time) when you get the response from the website.
Furthermore, since you want to collect many prices at the same time, I suggest that a For/Next loop be used to do that.
In the attached revised file, I've declared 2 new variables (in bold, near the start) - LstRw (which will hold the last populated row in column A) and n which will be the loop counter. In your sheet, I've added a column to hold the ClassName you're searching from a given page (without the " " quotation marks from the original code). That column can be hidden.
If you click on the button on the worksheet, the macro will run and update all the prices and add today's date and time (look in the cells to see it's there). Key changes are in bold below, along with comments for your guidance:
Sub Get_Web_Data()
'listing the variables
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant
Dim LstRw As Long, n As Long
'get last row in column A (1)
LstRw = Cells(Rows.Count, 1).End(xlUp).Row
' Loop from row 2 until LastRw
For n = 2 To LstRw
'Website to go to (from column A in sheet)
website = Cells(n, 1).Value
'create the object that will make the webpage request
Set request = CreateObject("MSXML2.XMLHTTP")
'Where to go and how to go there - probably don't need to change this
request.Open "Get", website, False
'Get fresh data
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
'send the request for the webpage
request.send
'Get the webpage response data into a variable
response = StrConv(request.responseBody, vbUnicode)
'Put the webpage into an html object to make data references easier
html.body.innerHTML = response
'Get the price for the specified element on the page from (hidden) column B
'price = html.getElementsByClassName("Fw(b) Fz(36px) Mb(-4px) D(ib)")(0).innerText
price = html.getElementsByClassName(Cells(n, 2).Value)(0).innerText
'Output the price into spreadsheet
Cells(n, 4).Value = price
' get the date and time the price was obtained
Cells(n, 3).Value = Now
Next n
End Sub
This code will work if you add more rows, so long as you add valid URLs and element names in columns A and B respectively..
Otherwise, if you really want to go to the trouble of getting the date shown on each website, Don's YouTube tutorial here: Import Web Data to Excel using VBA Macros shows you how to identify the HTML element associated with that span- look from 10 minutes 02 seconds (since you said that element isn't available on the "live" page)..
Once you have that text captured as a new variable (say ODate) and strip off the opening "At close: " bit using REPLACE. E.g if you paste:
Debug.Print Replace("At close: September 11 04:15PM EDT","At close: ","")
into the Intermediate Window (of VB Project Explorer) and press return, that will return "September 11 04:15PM EDT". You'd use it in practice like:
ODate= Replace(ODate, "At close: ","")
Converting it to mm/dd/yyyy format would need a bit more effort but could be done with following the above with this:
ODate = Left(ODate, InStr(InStr(1, ODate, " ") + 1, ODate, " ")) & Year(Date)
Range("B2").Value= CDate(ODate)
where the first line gets the text up to the second space (so "September 11 ") and adds the current year. The second line uses CDate to convert it to a date (using CDate) and puts it in B2 or wherever. It will show as 9/11/2022 if your locale uses m/d/yyyy.
Hope this helps
