Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Need data along with stock price

0

Wonderful tutorial on: 

Import Web Data to Excel using VBA Macros

Worked perfectly!  I needed it to be loaded as a value in my spreadsheet so I added the line of code Range("A1").Value = price.  Now I need the date the price was from also as a piece of data.  When I inspect that element I get  <span>At close: September 9 04:15PM EDT</span>  how can I attach that date with the specific stock's price?  Would love it if it returned in the format 9/9/2022 but I'll live with it spelled out if that's the best I can get.

Didn't know how to write that code.

Thank you so much and hope this makes sense.

Nolanmb

Answer
Discuss

Discussion

Hi Nolan(?) and welcome to the Forum.  

I assume you are referring to Don's YouTube tutorial here: Import Web Data to Excel using VBA Macros but which specific website are you accessing? (Please add this to your original question) 

It saves us time (and gives you a better answer) if you give us a file to work with- please edit your question and use the Add Files... button to upload a representative Please edit your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. Then we should be able to give specific help.
John_Ru (rep: 3992) Sep 12, '22 at 10:54 am
For example, I'm able to find the element class for the price for:
https://finance.yahoo.com/quote/%5EBXM?p=^BXM

but not the "as of date".  I grab this before market opens so this example is not going to show date, just time for right now.



Just don't know how to identify Span



Nolanmb (rep: 2) Sep 12, '22 at 11:09 am
Where is the "add file" button?
Nolanmb (rep: 2) Sep 12, '22 at 4:13 pm
Go to your original question, choose to Edit it and you will find the button below the text entry box.
John_Ru (rep: 3992) Sep 12, '22 at 4:16 pm
Add to Discussion

Answers

0
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

Discuss

Discussion

Thank you for the time to answer but I'm not totally understanding.  I'm not a coder so hopefully more specific instructions?  

My ultimate quest is I have about 30 websites (not all finance.yahoo) that I need to grab close date and close price.  I typically do this ~5:00 am everyday.  The table would look something like this:

Website Link Close Date Close Price https://finance.yahoo.com/quote/%5EBXM?p=^BXM 9/9/2022 1634.83 https://finance.yahoo.com/quote/%5EXCMP?p=^XCMP 9/9/2022 14526.67 https://finance.yahoo.com/quote/%5EXNDX?p=^XNDX 9/9/2022 14910.79
Of the above my only success is getting one websites price using VBA.  I guess in addition to figuring out how to retrieve the date I need some kind of a loop where once one website has been looked at and info retrieved VBA will move on to the next until there aren't anymore.

But the element where the As of Date is doesn't have a neat little identiter like price did.  Unless I'm supposed to use the identifier for tertiary color?

<div id="quote-market-notice" class="C($tertiaryColor) D(b) Fz(12px) Fw(n) Mstart(0)--mobpsm Mt(6px)--mobpsm Whs(n)"><span>As of  03:42PM EDT. Market open.</span></div>
<span>As of  03:42PM EDT. Market open.</span>

Hope this makes sense and look forward to your help.
Nolanmb (rep: 2) Sep 12, '22 at 4:06 pm
I think when I attached file it was requireing a pw so I tried attaching again.  Hope you can open
Nolanmb (rep: 2) Sep 12, '22 at 5:43 pm
Mary Beth, please see my revised answer and file.
John_Ru (rep: 3992) Sep 12, '22 at 6:27 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login