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

Scrape Stock Price from Yahoo Finance with VBA - stopped working

0

Hello, this code was working fine but some months ago stopped working.

It used to get me the stock price from yahoo finance.

- The problem is located in the last line of the macro code, which is the following line:

 c.Offset(0, 1).Value = html.getElementsByClassName("Fw(b) Fz(36px) Mb(-4px) D(ib)").Item(0).innerText

- Then I changed it for this one (which extract the price, but it comes along with other data I dont need):

c.Offset(0, 1).Value = html.getElementsByClassName("D(ib) Mend(20px)").Item(0).innerText

I'll be very thanksfull if someone knows how to fix it.

THE FULL ORIGINAL CODE IS THE FOLLOWING:

Sub Get_Web_Data()
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant


Set tickerrange = Range("D2:D24")

For Each c In tickerrange

If c.Value = "" Then
c.Offset(0, 1).Value = ""

Else

website = "https://finance.yahoo.com/quote/" & c.Value & ".L"

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

c.Offset(0, 1).Value = html.getElementsByClassName("Fw(b) Fz(36px) Mb(-4px) D(ib)").Item(0).innerText


End If
Next c
End Sub
Answer
Discuss

Answers

0

Hi Santyago and welcome to the Forum

Not sure what is happening for you but if I pick two stocks (DIS and AAPL) and run the code for them, I get this result:

DIS 1.45

AAPL 489.27

for the original line of code:

c.Offset(0, 1).Value = html.getElementsByClassName("Fw(b) Fz(36px) Mb(-4px) D(ib)").Item(0).innerText

and this result:

DIS "1.4500+0.0500 (+3.57%) As of 02:38PM BST. Market open."

AAPL "489.27-19.73 (-3.88%) As of 06:16AM BST. Market open."

if that is replaced by your line:

c.Offset(0, 1).Value = html.getElementsByClassName("D(ib) Mend(20px)").Item(0).innerText

I.e. both lines work for me. Do you have the MicrosoftHTML Object Library enabled?

In the attached file, I used both lines so you should get results in columns E and F:

c.Offset(0, 1).Value = html.getElementsByClassName("Fw(b) Fz(36px) Mb(-4px) D(ib)").Item(0).innerText
c.Offset(0, 2).Value = html.getElementsByClassName("D(ib) Mend(20px)").Item(0).innerText
Discuss

Discussion

Hi John!
Thanks for your reply.

I do not understand what is wrong.. didn't you change absolutely anothing?
I am really begginer on this, a minor obvious change could be the issue : )

For me the macro works with the "second option of codes". But when I use the 1st (original) line of codes the result is "empty".
I see the macro running, but it doesn't bring me the price from the website.

Could it be that is not showing the numbers, but it works with text formats (as the second coding has some text on it)?

This is the code that doesn't bring me the price:

c.Offset(0, 1).Value = html.getElementsByClassName("Fw(b) Fz(36px) Mb(-4px) D(ib)").Item(0).innerText


I have the following boxes enabled:

- Visual Basic for Applications
- Microsoft Excel 12.0 Object Library
- OLE Automotion
- Microsoft Office 12.0 Object Library
- Microsoft HTML Object Library
- Microsoft XML, v6.0

I appreciate any suggestion.

Thanks a lot!
Santyago May 19, '22 at 12:57 am
Santyago. You're right- I changed nothing (or rather I jsut tried each option, one at a time).

I don't think the problem relates to formatting but in the file soon to be attached to my Answer, I've used both lines but started the second 
c.Offset(0,2)=....
.
for the second line so that it puts the second option in the next column.
John_Ru (rep: 3222) May 19, '22 at 9:33 am
Add to Discussion


Answer the Question

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