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[wsS]+?</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:
![enter image description here](https://i.stack.imgur.com/wCDcZ.png)
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:
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…