1

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

1

4 Answers 4

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, 2020 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, 2021 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, 2019 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, 2017 at 15:55
  • Doesn't work, even though their documentation says it will. Mar 18, 2018 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, 2018 at 4:27

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