Google Sheet: IMPORTXML from Yahoo Finance

Question

I'm trying to import current stock price from yahoo finance. I used a formula from some website and it partially work. I only know how to tell it to look for a specific query and it worked fine for some other data point I need but the price change query changes from

"Fw(500) Pstart(10px) Fz(24px) C($dataRed)" 

to

"Fw(500) Pstart(10px) Fz(24px) C($dataGreen)" 

depending if the price is up or down for the day.

How do I modify the formula I'm using below to use the "or" operator in this case? so that it will pull the price down whether the stock is up or down for the day. Thanks!

Formula I'm using: =IMPORTXML("https://finance.yahoo.com/quote/IBM","//span[@class='Fw(500) Pstart(10px) Fz(24px) C($dataRed)']")

Solution 1

I noticed the other answers did not work for me (they may have worked in the past), so I decided to post this solution. Just put the ticker in cell A1 and one or both of the below formulas somewhere else.

Price:

=IFNA(VALUE(IMPORTXML("https://finance.yahoo.com/quote/" & A1, "//*[@class=""D(ib) Mend(20px)""]/span[1]")))

Change:

=IFNA(VALUE(REGEXEXTRACT(IMPORTXML("https://finance.yahoo.com/quote/" & A1,"//*[@class=""D(ib) Mend(20px)""]/span[2]"), "^.*?\s")))

Solution 2

If you just want the price: =IFNA(VALUE(IMPORTXML("https://finance.yahoo.com/quote/" & $A1, "//*[@class=""D(ib) Mend(20px)""]/span[1]")))

Solution 3

Sadly Yahoo Finance changes the XML/HTML structure of its website quite often. The one that works for now is:

=IMPORTXML("https://finance.yahoo.com/quote/IBM/", "//*[@id=""quote-header-info""]/div[3]/div[1]/div/span[1]")

You may always open the HTML structure and use the developer tools to find and copy the X-path.

P.S.1. Though there seem to be a bug and the function can't retrieve data from URLs where there is a dot/point/period "." in the name.

P.S.2. The IMPORTHTML() function can't also fetch the latest price from Yahoo Finance because the information is neither in a table nor a list. You can try the scripts from this page and this page to list all the tables and lists.

Solution 4

You could use a more dynamic/generic xpath that doesnt require such specific paths such as this:

This one pulls in both the price and the change:

=ARRAY_CONSTRAIN(transpose(IMPORTXML("https://finance.yahoo.com/quote/IBM:,"//*[@class='Mt(6px)']//span")),1,2)

If you just want the price:

=trim(IMPORTXML("https://finance.yahoo.com/quote/IBM","//*[@class='Mt(6px)']//span"))

If you just want the change:

=IMPORTXML("https://finance.yahoo.com/quote/IBM","//*[@class='Mt(6px)']//span[2]")

View more solutions

Comments

[Foad S. Farimani]

the HTML/XML structure of that page has changed. Would you be kind to update your answer?