Returned values from ImportXML in Google Sheet is different than the actual values on Yahoo Finance

Question

I am trying to import option price from Yahoo Finance into my Google sheet using ImportXML but the actual values for some of the prices received in Google Sheet is very different than what I can see on Yahoo Finance website. I even tried ImportHTML and the result is the same.

Formula used in Google Sheet:

=TRANSPOSE(IMPORTXML( "https://finance.yahoo.com/quote/KL220121C00045000?p=KL220121C00045000" ,"//tr"))

Here's the result in Google Sheet (all red cells are the values that are different):

Actual values on Yahoo Finance page:

I am totally clueless why this is happening and how to solve it.

question from:https://stackoverflow.com/questions/65651295/returned-values-from-importxml-in-google-sheet-is-different-than-the-actual-valu

Answer 1

@Tanaike's link to ImportXML not producing correct values answers how to workaround the issue using App Scripts.

---

To answer the question of "why", I believe Yahoo Finance has implemented some sort of user agent detection, such that requests from Google Spreadsheets, or more specifically requests with the user agent Mozilla/5.0 (compatible; GoogleDocs; apps-spreadsheets; +http://docs.google.com) will be served a different (I believe older) version of the data.

When I visit the link https://finance.yahoo.com/quote/KL220121C00045000?p=KL220121C00045000 in the browser, it currently shows

As of 3:17PM EST. Market open

But when I change my user agent to mimic Google sheets, I get

As of 10:43AM EST. Market open.

Which is the same result as IMPORTXML.

I am guessing they implemented this either to reduce fetching from automated spreadsheets, or to discourage people from scraping their sites using Google Sheets.