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.)
Fetching Online Price (Yahoo Finance), VBA Error 91 "Object variable or with block variable not set"
0
Context: This code fetches share prices on Yahoo Finance for a list of stocks. It used to work fine, but now it indicates this Error 91, but only for a few of them. I checked, and the code was not mistakenly changed.
Error is indicated at the line: "price = html.getElementsByClassName ("Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)") .Item(0) .innerText
The full code is: (example: LVMH)
Sub LVMH
Dim request As Object
Dim response as String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant
website = "https://finance.yahoo.com/quote/MC.PA"
Set request = CreateObject ("MSXML2.XMLHTTP")
request .Open "GET", website, False
request .setRequestHeader "if-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
request .send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response
price = html.getElementsByClassName ("Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)") .Item(0) .innerText
Sheets("Sheet1") .Range("H30") .Value = price
End Sub
If it helps, the creator of the video, and this website (I believe) replied it might be because the Yahoo finance website changed a bit.
I attach a sample file.
Thank you very much!
Discussion
I just finished programming a similar task and I gave up on Yahoo in disgust. There is a lot of help on the web about getting quotes from Yahoo but Yahoo stopped supplying data in 1918. You can still scrape individual quotes from Yahoo in the same way you can get the quotes from other sites. The difference is that Yahoo makes it much more difficult. If you want American quotes, use "Stock Connector". That's a free add-in you can install and forget. If it's quotes from another exchange around the world take the quotes from a local broker's website. Their sites are less sophisticated, meaning easier to scrape, and less likely to be interrupted by policy decisions.
Variatus (rep: 4889) Aug 11, '20 at 8:46 pm
Thanks for your input! Do you know of a website similar to yahoo that has quotes from most exchanges? I indeed have stocks from various exhanges.
PPal Aug 12, '20 at 5:53 am
There's MarketWatch in the US but I prefer "Stock Connector" who also have quotes from 11 other exchanges. All American sources are really good only for American exchanges, and that basically includes Yahoo, too. I get my quotes from national financial portals now. They are better, in their sphere, than Yahoo but they require local language. You need to appproach the problem individually, for each exchange. If you want all information from one source you won't be able to do better than Yahoo.
Variatus (rep: 4889) Aug 12, '20 at 8:26 am
For those interested, I'm now asking excel to fetch prices on Investment.com instead of Yahoo Finance. For now it works. If anybody has recommendations, please add them to this thread. Thank you,
PPal Aug 18, '20 at 9:54 am
I think there is a lot of interest but the vital information here is which exchanges your experience refers to. Interest comes from people in Asia, mostly, who have plenty of sources for Apple and Tesla quotes but also need access to data from Bombay, Bangkok, Hongkong, Jakarta and Singapore, London, Paris and Frankfurt.
Variatus (rep: 4889) Aug 18, '20 at 8:52 pm