1

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
study1234
  • 13
  • 3
  • The URL redirects to https://in.search.yahoo.com/?fr2=inr, showing "As of August 26th, 2021 Yahoo India will no longer be publishing content. " Removing `in.` the url works, but nowadays it looks that the contents is loaded by JavaScript. – Rubén Jan 06 '23 at 15:44

1 Answers1

1

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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Yes that's exactly what I want but is there a work around wherein instead of link In column A1 i can just have the name of the stocks because in links I will have to manually add name of stocks. But I do have the name of the stocks list. There are many stocks and inserting name in links would take lot of time. Or is there a way where in the link adds names of the stocks after /quote/ instead of doing manually. Many thanks for help – study1234 Aug 16 '18 at 07:23
  • @study1234 Thank you for your additional information. I could study from your question and comment. Thank you, too. – Tanaike Aug 16 '18 at 07:47