Create Account

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

Excel Q&A Forum

Failure to import data from Yahoo site

I created a code which is based on a tutorial that i view in this site about importing data from Yahoo site.

The code imports the stock price and the forecast price .

The code import the current stock price but failed to bring the price forecast.

I get the next messege:

run time error "91"

object variable or with block variable not set

I will truly appreciate your help

This is the code:

Sub YAHOO_DATA2()


Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
'Dim price1 As Variant 
Dim price2 As Variant 





stock = "WFC"


website = "https://finance.yahoo.com/quote/" & stock & "/analysis?p=" & stock

' 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




' price1- current price,  price2- forecast  low price

price1 = html.getElementsByClassName("Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)")(0).innerText   
' The following command fails!
price2 = html.getElementsByClassName("Pos(r) Fl(start) Fz(xs) C($tertiaryColor)").Item(0).innerText





End Sub

excel

posted by: rafih on Sun Aug 22, 2021 at 4:30 am

Answer

Discuss

Answers

Hi Rafih and welcome to the Forum

I believe you get Error 91 because there is no longer an element as named in (and as requested by) the call for "price2" on the target webpage.

Not sure which stock price you're looking for but the macro does work for known elements on that page. For example, I I comment out your calls and end the sub as follows (changes in bold):

price1 = html.getElementsByClassName("Fz(s) Ell Fw(600) C($linkColor)")(0).innerText
price2 = html.getElementsByClassName("Trsdu(0.3s) Fz(s) Mt(4px) Mb(0px) Fw(b) D(ib)").Item(0).innerText
Debug.Print price1 & ": " & price2
End Sub

then there is no Error 91 for price2 and the result "S&P 500: 4,441.67" is printed in the Immediate window of VB Explorer (i.e. the Standard and Poors 500 index plus its value today).

You need to find out how your target value is defined in the HTML code of the webpage.

Not sure why you commented out the declaration of price1 at the start- you should restore it.

Hope this helps.

posted by: John_Ru (rep: 6142) on Sun Aug 22, 2021 at 9:42 am

Discuss

Discussion

After checking, the reason for the error is that the data I refer to appears on the web page only after scrolling the page and therefore the code does not find the data. How do I give a command to web page fully uploaded or a command to scroll the page automatically?

rafih Aug 22, '21 at 10:44 am

Rafih
Not sure (on phone now so can't test). I thought GET... "MSXML2.XMLHTTP" request loaded the whole page.

Which stock price are you seeking?

John_Ru (rep: 6142) Aug 22, '21 at 11:12 am

Thanks john
You can load the page and see that it does not fully loaded the whole page Although the order GET... "MSXML2.XMLHTTP" that i wrote.
i must scroll the page to get the all data that i'm looking for. For example, the following address:

https://finance.yahoo.com/quote/WFC/analysis?p=WFC

rafih Aug 23, '21 at 2:36 am

Rafih
Given the URL you gave above gives details of the stock price for Wells Fargo & Company, why don't you just identify the class names of the data you want from there and modify your macro to use that URL and the identified elements?

John_Ru (rep: 6142) Aug 23, '21 at 3:58 am

But this is what I tried to do and I got the error. I tried to put in the variable PRICE1 the data with the CLASSNAME "Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)" and I get the error. Apparently the data associated with this CLASSNAME does not appear immediately when the page loaded, but only when the page is scrolling

rafih Aug 23, '21 at 5:47 am

Rafih. There may be several elements with that name so you need to identify the one you want- by following it with .(n) or .Index(n) where n is the index number for the element you want (you may have to experiment to get that!)

Furthermore I believe it's not an issue relating to the field you want being visible only when you scroll down. For example. I need to scroll down to see the "Growth Estimates" data set. If however I chnge you macro to end like this:

price1 = html.getElementsByClassName("Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)").Item(0).innerText
price2 = html.getElementsByClassName("Ta(end) Py(10px)").Item(4).innerText
 
Debug.Print price1 & ": " & price2
 
End Sub

then the printout is something like: 47.66: 133.30% i.e the up-to-date price for the stock followed by the Growth Estimate for Current Qtr. (under column WFC) where index n=4 (I found by trial and error).

I still think you probably just have the wrong ClassName (or index number) for the item you want for price2.

(I checked back and Don's tutorial VBA Macro to Import Web Data into Excel remains valid since it gets only what you renamed as price1)

Given this is a Q&A forum (where the aim is not to have protracted discussions), I won't be doing any more on this and leave you to correctly identify the (unnamed) element you need from the website.

John_Ru (rep: 6142) Aug 23, '21 at 11:21 am

Add to Discussion

Excel VBA Course - From Beginner to Expert

Failure to import data from Yahoo site

Discussion

Answers

Discussion

Answer the Question

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