1

Is there an API call to use in a Google Spreadsheet to get a stock's Dividend Yield (% annual yield)

1
  • Share a brief description of you search/research efforts. Reference How to Ask.
    – Rubén
    Oct 24 '17 at 16:38
4

Try this: =INDEX(SPLIT(INDEX(IMPORTHTML(CONCATENATE("https://finance.google.com/finance?q=", "JNJ"),"table",3),1,2), "/"),1, 2) * 0.01

3

To further help with this answer. I tried tb11's answer and did not work for me June 2019. It was the URL that was the issue. I picked another site with a simpler URL and it worked for me. Sample below.

=SPLIT(INDEX(SPLIT(INDEX(IMPORTHTML("https://finance.yahoo.com/quote/MITT?p=MITT&.tsrc=fin-srch","table",2),6,2),"("),1,2),")")

Google, if you could please add an API to allow us to get the divided yield easier, Thanks!!!

2
  • 1
    This allows you to select another cell using the above, so you can populate a portfolio. Ticker is B3 in this example:=SPLIT(INDEX(SPLIT(INDEX(IMPORTHTML(concatenate("finance.yahoo.com/quote", A3,"?p=",A3,"&.tsrc=fin-srch"),"table",2),6,2),"("),1,2),")") May 1 '20 at 10:04
  • Can you please explain how this works? Why the duplicated MITT? Why those numbers? What if you want to get (or if not available per year, sum), for example, all dividends distributed in a specific year?
    – Ivan Perez
    Jan 15 at 1:02
1

I used to be able to pull it from google finance, but that stopped working for me a little while back. I found another one. It's not quite as clean, but it is working for me as of March 2019.

  1. In column B, put your ticker symbol

  2. In column C: =IF(NOT(ISBLANK(B12)),GoogleFinance(B12,"Price"),"") This will populate the current price, which is used in the next step to calculate the yield

  3. In the column where you want the dividend yield,

=IF(NOT(ISBLANK($B2)),iferror(index(index(importhtml(concat(concat("[https://www.nasdaq.com/symbol/",$B2),"/dividend-history"),"table",3),2,3))\*4/$C2,IFERROR(index(index(importhtml(concat(concat("https://www.nasdaq.com/symbol/",$B2),"/dividend-history"),"table",0),2,3))\*4/$C2,"N/A")),"")

1
  • In Item 3 (hard coded to 4 payments) this will not be correct unless there are always 4 dividend payments: Its possible to have more or less than 4 dividend payments.
    – rcasady616
    May 18 '19 at 3:33
-1

Yes, you can use:

=GOOGLEFINANCE("FCNTX","yieldpct")

This will display the distribution yield for Fidelity Contrafund Fund (aka. FCNTX). According to their docs:

"yieldpct" - The distribution yield, the sum of the prior 12 months' income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month's net asset value number.

Full docs here: https://support.google.com/docs/answer/3093281?hl=en

3
  • 3
    Doesn't work for a single stock, like "GOOG". Returns ERROR. =GOOGLEFINANCE("GOOG","yieldpct") Function GOOGLEFINANCE parameter 2 value is invalid for the symbol specified. Nov 6 '17 at 15:55
  • Doesn't work, even though their documentation says it will. Mar 18 '18 at 17:30
  • 1
    It works for FCNTX as that's specifically Yield (ttm) as opposed to Div yield. If you click built in help for the function it says that attribute is for mutual funds. Jul 27 '18 at 4:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.