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)']")

Matthew · Answer 1 · 2020-09-08T19:44:57.943

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")))

score 2 · Answer 2 · edited Jan 04 '23 at 18:58

2

Currently using googlefinance but find it does not update often enough even when updates set to every minute so currently testing if below will allow updates at least with an F5 press within the sheet

This brings in the price and other information (dated 2022/09/27)

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

edited Jan 04 '23 at 18:58

Rubén

34,714
9
70
166

answered Sep 28 '22 at 03:53

Glabella Streamline

21
2

As of January 4th, 2022 refreshing the browser tab dowesn't updates the imported value, either clearing and ading the formula again in the same spreadsheet, only adding it on a new spreadsheet. – Rubén Jan 04 '23 at 19:42

score 0 · Answer 3 · answered Jan 11 '17 at 06:48

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]")

the HTML/XML structure of that page has changed. Would you be kind to update your answer? — Foad S. Farimani, May 03 '20 at 10:14

score 0 · Answer 4 · edited Oct 14 '21 at 17:24

0

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

edited Oct 14 '21 at 17:24

derloopkat

6,232
16
38
45

answered Jan 25 '21 at 18:22

Onson

9
2

DUPLICATE of an existing post. https://stackoverflow.com/a/63800561/14678267 – Mr Shane Mar 18 '23 at 05:02

Foad S. Farimani · Answer 5 · 2021-07-21T09:52:32.443

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.

Google Sheet: IMPORTXML from Yahoo Finance

5 Answers5

Linked