英文:
Not able to get data from yahoo finance using IMPORTXML in Google Sheets
问题
我正在尝试获取此股票的“Price/Sales (time-to-market)”数据。我在Google Sheets中使用了IMPORTXML函数以及如下所示的XPath值：
=IMPORTXML(CONCATENATE("https://finance.yahoo.com/quote/",C4,".NS/key-statistics?p=",C4,".NS"),"/html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/section/div[2]/div[1]/div/div/div/div/table/tbody/tr[6]/td[2]")
我的表格中的C4是股票代码ABB。然而，它显示“URL上找不到资源”。我漏掉了什么？
英文:
I'm trying to get the Price/Sales (time-to-market) data for this stock. I used the IMPORTXML function in Google Sheets along with the XPath of the value as shown below,
=IMPORTXML(CONCATENATE("https://finance.yahoo.com/quote/",C4,".NS/key-statistics?p=",C4,".NS"),"/html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/section/div[2]/div[1]/div/div/div/div/table/tbody/tr[6]/td[2]")
C4 in my sheet is the stock code ABB. However, it says, Resource not found at URL. What am I missing?
答案1
得分: 1
从“URL未找到资源”这一点来看，我担心在这种情况下，IMPORTXML无法从URL请求数据。而在“finance.yahoo.com”上，这些值包含在HTML主体中。但不幸的是，在当前阶段，这些值似乎是加密的。而且，直接检索解密密钥似乎很困难。但在您的情况下，通过使用Google Apps Script检索，似乎可以在HTML主体中找到您期望的值，而不进行加密。
示例脚本
请将以下脚本复制并粘贴到Google电子表格的脚本编辑器中，并保存脚本。
当您使用此脚本时，请在单元格中输入自定义函数=SAMPLE(CONCATENATE("https://finance.yahoo.com/quote/",C4,".NS/key-statistics?p=",C4,".NS"))。通过这样做，脚本将运行。
function SAMPLE(url) {
const search = "Price/Sales"; // 这是来自您的问题。
const r1 = [...UrlFetchApp.fetch(url).getContentText().matchAll(/<td.+?<\/td>/g)];
const idx = r1.findIndex(([e]) => e.includes(search));
if (idx == -1) return null;
const r2 = r1[idx + 1][0].match(/>(.+)<\/td>/);
return r2 ? Number(r2[1]) : null;
}
测试
当使用此脚本时，将获得以下结果。
注意
当我发布这个答案时，我确认该脚本有效，可以获取期望的值。但在服务器端，HTML有时会更改。因此，当服务器端的HTML更改时，可能无法使用此脚本。请将其视为当前的示例脚本。请注意这一点。
参考：
英文:
From Resource not found at URL, I'm worried that in this case, the URL cannot be requested from IMPORTXML. And, at finance.yahoo.com, the values are included in the HTML body. But, unfortunately, in the current stage, it seems that the values are encrypted. And, it seems that it is difficult to directly retrieve the key for decrypting. But, in your situation, it seems that your expected value is included in the HTML body without encrypting by retrieving it with Google Apps Script.
Sample script
Please copy and paste the following script to the script editor of Google Spreadsheet and save the script.
When you use this script, please put a custom function of =SAMPLE(CONCATENATE("https://finance.yahoo.com/quote/",C4,".NS/key-statistics?p=",C4,".NS")) into a cell. By this, the script is run.
function SAMPLE(url) {
const search = "Price/Sales"; // This is from your question.
const r1 = [...UrlFetchApp.fetch(url).getContentText().matchAll(/<td.+?<\/td>/g)];
const idx = r1.findIndex(([e]) => e.includes(search));
if (idx == -1) return null;
const r2 = r1[idx + 1][0].match(/>(.+)<\/td>/);
return r2 ? Number(r2[1]) : null;
}
Testing
When this script is used, the following result is obtained.
Note
When I posted this answer, I confirmed that the script works and the expected value can be obtained. But, on the server side, the HTML is sometimes changed. So, when the HTML is changed on the server side, this script might not be able to be used. Please think of this as the current sample script. Please be careful about this.
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论