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

Xpath data extraction from yahoo finance table

Want to get yahoo data in google spreadsheet. I don;t want google data from google finance so please don't suggest that. I want yahoo finance data in spreadsheet.

I want data in the following spreadsheet which is editable:

https://docs.google.com/spreadsheets/d/1_MQyyaeEtGD0p9l7ncFqyLDWDKsnLNh2XBcH5CiHkF8/edit?usp=sharing

I want only specific column data for just first five days. the link for yahoo data extraction is:

https://in.finance.yahoo.com/quote/abb.BO/history/?guccounter=1

I have attached image for reference.

enter image description here

Please help how can I extract data specifically from one column.

I know I will have to use =transpose(importxml("url,xpath) but I am not sure about xpath.

Solution

  • You want to retrieve the latest 5 values of "close" which is yellow range. If my understanding is correct, how about this modification?

    Modification points :

    • In your xpath, //[@class="Pb(10px) Ovx(a) W(100%)" cannot be used. If you want to retrieve values from class Pb(10px) Ovx(a) W(100%) of div, please modify "//div[@class='Pb(10px) Ovx(a) W(100%)']". This xpath includes the values you want. But it cannot retrieve only the latest 5 values of "close".
      • So I would like to propose "//tr[position()<6]/td[5]" as the xpath.

    Modified formula :

    =TRANSPOSE(IMPORTXML(A1,"//tr[position()<6]/td[5]"))
    • https://in.finance.yahoo.com/quote/abb.BO/history/?guccounter=1 is put in "A1".

    Result :

    enter image description here

    If this was not what you want, I'm sorry.