Search code examples
google-sheetsweb-scrapingxpathgoogle-sheets-formulayahoo-finance

Yahoo finance historical close price to google sheets returns n/a for close later than 100 days

I try to print historic adjusted close prices from Yahoo finance to Google Sheets.

=ImportXML("https://sg.finance.yahoo.com/quote/"&B57&"/history?p="&B57, "//tbody/tr[21]/td[6]")

Cell B57 is for example "SPY".

This works fine for historic prices up to 100 days. (it is adjusted here: tr[100])

When I try to get prices later 100 days it returns "N/A". These prices are visible on yahoo finance.

It there a way to adjust XPATH that it works?

I noticed, that in the html code of yahoo pices about 100 days don't have this "data-reactid=1520" in the tr tag.

Solution

  • Answer:

    IMPORTXML can not retrieve data which is populated by a script, and so using this formula to retrieve data from this table is not possible to do.

    More Information:

    As the first 100 values are loaded into the page without the use of JavaScript (as you can see by disabling JavaScript for https://sg.finance.yahoo.com/quote/SPY/history?p=SPY and reloading the page), the information can be retrieved by IMPORTXML.

    As the data after the first 100 results is generated on-the-fly after scrolling down the page, the newly available data is not retrievable by IMPORTXML - as far as the formula sees, there is no 101st <tr> element and so it displays N/A: Imported content is empty .

    References:

    Related Questions: