Google ImportHTML:Yahoo Finance找不到资源URl

问题描述

我希望将Yahoo Finance的历史股价记录到Google Sheet中，并收到此错误.请协助.如果使用导入xml，效果如何?

注意:

• 此示例脚本用于 https://au.finance.yahoo.com/quote/ASX.AX/history?p=ASX.AX 的URL.因此，当您更改URL时，该脚本可能无法使用.请注意这一点.

参考文献:

• UrlFetchApp类
• 方法:sheetssheets.batchUpdate

I wish to grab the historical stock price from Yahoo Finance into Google Sheet and received this error. Please assist. If using import xml, how will it be?

https://au.finance.yahoo.com/quote/ASX.AX/history?p=ASX.AX

=IMPORTHTML(D7,"table",1)

Import HTML

解决方案

I believe your goal as follows.

• You want to retrieve the table from the URL of https://au.finance.yahoo.com/quote/ASX.AX/history?p=ASX.AX and put it to the Spreadsheet.

Issue and workaround:

Unfortunately, it seems that the table cannot be retrieved using IMPORTHTML and IMPORTXML from the URL. This has already been mentioned in Jason E.'s answer.

But, fortunately, when I tested to retrieve the table using UrlFetchApp of Google Apps Script, I confirmed that the table can be retrieved. So, in this answer, as a workaround, I would like to propose to achieve your goal using Google Apps Script. The sample script is as follows.

Sample script:

Please copy and paste the following sample script to the script editor of Spreadsheet. And, before you use this script, please enable Sheets API at Advanced Google services. And, run the function of myFunction and please authorize the scopes. By this flow, the table is retrieved from the URL and put it to the active sheet.

function myFunction() {
  const url = "https://au.finance.yahoo.com/quote/ASX.AX/history?p=ASX.AX";
  const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  const tables = res.getContentText().match(/(<table[\w\s\S]+?<\/table>)/g);
  if (!tables || tables.length == 0) throw new Error("No tables. Please confirm URL again.");
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet();
  const resource = {requests: [{pasteData: {html: true, data: tables[0], coordinate: {sheetId: sheet.getSheetId()}}}]};
  Sheets.Spreadsheets.batchUpdate(resource, spreadsheet.getId());
}

Result:

When above script is run, the following result is obtained.

Note:

• This sample script is for the URL of https://au.finance.yahoo.com/quote/ASX.AX/history?p=ASX.AX. So when you changed the URL, the script might not be able to be used. Please be careful this.

References:

• Class UrlFetchApp
• Method: spreadsheets.batchUpdate

这篇关于Google ImportHTML:Yahoo Finance找不到资源URl的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！