-1

I would like to extract the Forward Dividend from a site such as Yahoo Finance and insert it into Google Sheets.

Example 1: For ticker symbol AAPL: https://finance.yahoo.com/quote/AAPL?p=AAPL I would like the the Google Sheet field to return: 0.82

Example 2: For the same symbol: https://www.streetinsider.com/dividend_history.php?q=aapl I would like it to return the 0.82 amount from the table.

Is this even possible?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Tommy
  • 1
  • 1
  • "Is this even possible?" - yes, but it'd involve **a lot of steps** and this question will likely be closed for being too broad. How would you be running this script, exactly? A Chrome extension would work, I suppose. – Dai Sep 19 '20 at 11:46
  • If you want to get the numeric value of a table cell in an in-page script, that's trivial: `var value = parseFloat( document.querySelector( 'table#tableId > tbody > tr:nth-child(123) > td:nth-child(456)' ).textContent )`. – Dai Sep 19 '20 at 11:47
  • The broadness comes from the technology you'll be using, e.g. selenium (java, c#, javascript), a client e.g. new HttpClient()... or will you using as Dai said a chrome extension(javascript). It's possible, isn't that hard.. but it really comes down to what experience you have and more comfortable with. – Monofuse Sep 19 '20 at 11:50
  • I am using a Chrome web browser for the Google Sheet. I find it impossible to extract that value. The "var value" thing needs to point to one of the links in order to work. A while ago, I was using: =IMPORTXML("https://old.nasdaq.com/symbol/"&B2&"/dividend-history","//*[@id='quotes_content_left_dividendhistoryGrid_CashAmount_0']") But that site doesn't work anymore. – Tommy Sep 19 '20 at 14:25

1 Answers1

1

Since you mentioned you want to use the built-in IMPORT* functions, I will answer from that perspective.

As with most Sheets-based scraping, it depends on the site. The two sites you have given behave very differently. Let's examine them separately:

1: Yahoo Finance - Possible

We view source and see that 0.82 is located in a table. This lets us use IMPORTHTML:

=INDEX(
    SPLIT(
        VLOOKUP(
            "Forward Dividend & Yield",
            IMPORTHTML("https://finance.yahoo.com/quote/AAPL?p=AAPL", "table", 2),
            2,
        ),
        " "
    ),
    1
)
  1. Import table data
  2. Get the dividend row
  3. Extract the dividend yield value.

2: Street Insider - Not Possible

You might think that since the View Source lets us see the table in the browser, we should be able to use IMPORTHTML() again. Sadly this is not the case. The server-side seems to detect that you're not using a browser to access the .php, and it will display an empty page. Google sheets uses a different user agent from your own browser.

The only thing I can recommend if you need to use the IMPORT* functions is that you find a different site to pull the data from.

General Grievance
  • 4,555
  • 31
  • 31
  • 45
  • Now it doesn't work anymore... it displays #N/A. Do you know why that is? It worked for just 1 day... – Tommy Sep 24 '20 at 20:06
  • @Tommy No idea. Still working for me. You can mouseover the #N/A to see what's wrong. – General Grievance Sep 24 '20 at 20:10
  • Error Did not find value 'Forward Dividend & Yield' in VLOOKUP evaluation. No idea what that means – Tommy Sep 24 '20 at 20:13
  • 1
    @Tommy It means that whatever table got imported doesn't have the value in the first column. Does just the `IMPORTHTML` give you any data? It may be that you have to lookup a different value and index to get the right value. Web scraping is always shaky business. – General Grievance Sep 24 '20 at 20:18
  • Also, I am writing it like below, where B2 = ticker symbol = AAPL =INDEX( SPLIT( VLOOKUP( "Forward Dividend & Yield", IMPORTHTML("https://finance.yahoo.com/quote/"&B2, "table", 2), 2, ), " " ), 1 ) – Tommy Sep 24 '20 at 20:19
  • Error Imported content is empty. (for the IMPORTHTML) – Tommy Sep 24 '20 at 20:26
  • In another sheet it works just fine. When I do multiple (30) "scrapes", some of them get error. Nevermind, I give up, I'll do them manually. This is too much work for little gain. Thanks anyways, you've been very helpful and nice about it. – Tommy Sep 24 '20 at 20:33
  • @Tommy Yeah, sorry about that... scraping is very hacky and Sheets Formulas don't give you great tools to deal with errors. – General Grievance Sep 24 '20 at 20:49