1

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

  • 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

| improve this answer | |
2

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!!!

| improve this answer | |
  • 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),")") – Neuraleptic May 1 at 10:04
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")),"")

| improve this answer | |
  • 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

| improve this answer | |
  • 2
    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. – Clay Nichols Nov 6 '17 at 15:55
  • Doesn't work, even though their documentation says it will. – Jefferey Cave 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. – user3015682 Jul 27 '18 at 4:27

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