ImportXML无法产生正确的值 [英] ImportXML not producing correct values
问题描述
我正在按照以下教程将股票期权数据导入Google表格.
问题和解决方法:
- 当我测试了
= iferror(TRANSPOSE(IMPORTXML("https://finance.yahoo.com/quote/NKLA220121C00002500?p=NKLA220121C00002500"，"//tr"))的示例公式时，您必须在A列中添加合同名称")，我可以确认您的问题的相同情况. - 另一方面，当我从URL检索HTML数据时，我可以确认与在浏览器中看到的页面具有相同的值.看来这与您的底部图片相同.
- 不幸的是，我无法理解这种差异的明确原因.
在这种情况下，我想在这个答案中建议使用Google Apps脚本.
示例脚本:
请将以下脚本复制并粘贴到Google Spreadsheet的容器绑定脚本中并保存.并将 = SAMPLE("https://finance.yahoo.com/quote/NKLA220121C00002500?p=NKLA220121C00002500")放到单元格中.由此，返回结果.在这种情况下，Google Apps脚本将用作自定义函数.
function SAMPLE(url){const res = UrlFetchApp.fetch(url，{muteHttpExceptions:true});const table = [... res.getContentText().matchAll(/(< table [\ w \ s \ S] +?< \/table>)/g)];如果(tables.length< 2)返回无表.请再次确认URL.const值= table.reduce((ar，[，table])=> {如果(表格){const root = XmlService.parse(table).getRootElement();const temp = root.getChild("tbody"，root.getNamespace()).getChildren().map(e => e.getChildren().map(f => isNaN(f.getValue())?f.getValue():Number(f.getValue())));ar = ar.concat(temp);}返回ar;}，[]);返回values [0] .map((_，i)=> values.map(r => r [i]));} 结果:
注意:
我测试了此示例脚本的URL，网址为 https://finance.yahoo.com/quote/NKLA220121C00002500?p=NKLA220121C00002500 .更改URL后，可能无法使用该脚本.因此，请注意这一点.那时，请分析每个HTML数据并修改脚本.
参考文献:
I am following the following tutorial to import stock options data into a Google sheet.
https://www.youtube.com/watch?v=Be7z9YeeVY0&ab_channel=daneshj
The following formula will import data from yahoo finance into the sheet:
=iferror(TRANSPOSE(IMPORTXML(CONCATENATE("https://finance.yahoo.com/quote/",A2,"?p=",A2),"//tr")),"You have to add a contract name in column A")
At first glance, everything looks fine, as it seems to be pulling data back from the webpage; however, all the values are incorrect.
The URL it is pulling data from in this example is below. Note that that the data changes frequently.
https://finance.yahoo.com/quote/NKLA220121C00002500?p=NKLA220121C00002500
The numbers are not only wrong in this specific example, they are wrong every time, and with a great enough margin of error that I don't believe it is due to IMPORTXML caching the page. I have searched through the HTML source of the webpage and can't find the values from IMPORTXML anywhere.
Issue and workaround:
- When I tested your sample formula of
=iferror(TRANSPOSE(IMPORTXML("https://finance.yahoo.com/quote/NKLA220121C00002500?p=NKLA220121C00002500","//tr")),"You have to add a contract name in column A"), I could confirm the same situation of your issue. - On the other hand, when I retrieve the HTML data from the URL, I could confirm that the same values with the page which is seen with my browser. It seems that this is the same with your bottom image.
- Unfortunately, I cannot understand about the clear reason of this difference.
From these situation, in this answer, I would like to propose to use Google Apps Script.
Sample script:
Please copy and paste the following script to the container-bound script of Google Spreadsheet and save it. And please put =SAMPLE("https://finance.yahoo.com/quote/NKLA220121C00002500?p=NKLA220121C00002500") to a cell. By this, the result is returned. In this case, the Google Apps Script is used as a custom function.
function SAMPLE(url) {
const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
const tables = [...res.getContentText().matchAll(/(<table[\w\s\S]+?<\/table>)/g)];
if (tables.length < 2) return "No tables. Please confirm URL again.";
const values = tables.reduce((ar, [,table]) => {
if (table) {
const root = XmlService.parse(table).getRootElement();
const temp = root.getChild("tbody", root.getNamespace()).getChildren().map(e => e.getChildren().map(f => isNaN(f.getValue()) ? f.getValue() : Number(f.getValue())));
ar = ar.concat(temp);
}
return ar;
}, []);
return values[0].map((_, i) => values.map(r => r[i]));
}
Result:
Note:
I tested this sample script for the URL of https://finance.yahoo.com/quote/NKLA220121C00002500?p=NKLA220121C00002500. When the URL is changed, the script might not be able to be used. So please be careful this. At that time, please analyze each HTML data and modify the script.
References:
这篇关于ImportXML无法产生正确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
