1

Can anyone help me I am trying to extract the Total Assets figure for 29/09/2020 (8,473,000) from a Yahoo Finance page into Google Sheets. I have tried a couple of formulas but keep getting an 'imported content is empty' error.

I've tried the following -

FIRST using Xpath and formula :

=IMPORTXML("http://finance.yahoo.com/quote/EZJ.L/balance-sheet", "//*[@id='Col1-3-Financials-Proxy']/section/div[3]/div[1]/div/div[2]/div[1]/div[2]/div[3]/div[1]/div[2]/span")

THEN (having read that the Xpath might be failing because it contains a reference to the /section/ ) the element and formula:

=INDEX(IMPORTXML("http://finance.yahoo.com/quote/EZJ.L/balance-sheet","//div[@class='Ta(c) Py(6px) Bxz(bb) BdB Bdc($seperatorColor) Miw(120px) Miw(140px)--pnclg Bgc($lv1BgColor) fi-row:h_Bgc($hoverBgColor) D(tbc)'"),1,1)

Grateful for any guidance on how to pull this figure!

Rubén
  • 34,714
  • 9
  • 70
  • 166
William Verrill
  • 11
  • 1

1 Answers1

0

It seems that the data in the link you provided is dynamically generated thus it can't be fetched by IMPORTXML (same goes for IMPORTHTML, and similar functions).

If you are really determined to just use basic functions in sheets and not do web scraping in apps script, you can do this by finding an alternative site that presents the data statically.

I already did it for you and found this for the same company:

I checked the data and found out that assets which is what you are trying to fetch in your link is also present here but in millions (your old link is in thousands), so we need to multiply the millions by 1000 to get the same data you want in your link.

You can find it in Total Assets (Millions) for the similar date:

sample data

I fetched the data using a combination of INDEX and IMPORTHTML.

Since the data is BOLD, returned data is * 8,473 *, thus I used SUBSTITUTE to remove the * and then multiply it by 1000.

Here is the formula:

=SUBSTITUTE(INDEX(IMPORTHTML("https://www.hl.co.uk/shares/shares-search-results/e/easyjet-plc-ordinary-27-27p/financial-statements-and-reports","table",0),41,2), "*", "") * 1000

Here is the output:

sample output

For more details regarding the issue, please see this post.

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • 1
    Thanks for this. About to have a play. I know HL well so this solution works well :-) – William Verrill Dec 31 '20 at 16:53
  • Hi @WilliamVerrill, glad to be of help. If your question was solved, please push the accept button. Other people who have the same problem with you can also base your question as a question which can be solved. If you don't find the button, feel free to tell me. stackoverflow.com/help/accepted-answer – NightEye Dec 31 '20 at 17:03