Google Sheet中从ImportXML返回的值与Yahoo Finance上的实际值不同 [英] Returned values from ImportXML in Google Sheet is different than the actual values on Yahoo Finance

问题描述

我正在尝试使用ImportXML将Yahoo Finance的期权价格导入到我的Google工作表中，但是Google Sheet中收到的某些价格的实际价值与我在Yahoo Finance网站上看到的完全不同.我什至尝试了ImportHTML，结果是一样的.

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.

Google表格中使用的公式:

Formula used in Google Sheet:

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

这是Google表格中的结果(所有红色单元格都是不同的值):

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

Yahoo Finance页面上的实际值:

Actual values on Yahoo Finance page:

我完全不知道为什么会发生这种情况以及如何解决它.

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

推荐答案

@Tanaike的 ImportXML的链接不产生正确的值回答了如何使用App Script解决该问题.

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

要回答为什么"的问题，我相信Yahoo Finance已实施了某种用户代理检测，例如来自Google Spreadsheets的请求，或更具体地说是来自用户代理 Mozilla/5.0的请求(兼容； GoogleDocs； apps-spreadsheets； + http://docs.google.com)将获得不同版本(我认为是旧版本)的数据.

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.

当我访问链接 https://finance.yahoo.com/quote/KL220121C00045000?p = KL220121C00045000 ，当前显示为

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

但是当我更改我的用户代理时模仿Google表格，我得到了

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

As of 10:43AM EST. Market open.

与 IMPORTXML 的结果相同.

我猜测他们实施此操作是为了减少从自动电子表格中获取数据，或者劝阻人们不要使用Google表格抓取他们的网站.

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

这篇关于Google Sheet中从ImportXML返回的值与Yahoo Finance上的实际值不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！