Search code examples
google-sheetsgoogle-sheets-formulayahoo-finance

Google sheet transpose yahoo finance data and ignore multiple rows

gurus,

I am trying to get yahoo finance quote page info into google sheet. What bothers me is the earning date, which could be a range:

enter image description here

The following code works, but the earning date will return into 4 lines with the React notes

=transpose(IMPORTXML(CONCATENATE("https://finance.yahoo.com/quote/",B7,"?p=",B7),"//tr//td[last()]"))

Is there anyway I can consolidate the data from O8-O12 into O7? Or just simply ignore the O8-O12 data in this example. Thank you! enter image description here

Solution

  • Use a query formula as well and limit results to 1: 

    =QUERY(transpose(IMPORTXML(CONCATENATE("https://finance.yahoo.com/quote/",B7,"?p=",B7),"//tr//td[last()]")),"LIMIT 1")