How To Make A Dividend Income Tracker In Google Sheets

Подписаться 35 тыс.

Просмотров 99 тыс.

50% 1

How To Make A Dividend Income Tracker In Google Sheets
**Finviz Code UPDATE**
You need to pull from table 7 now instead of table 8
📈 Link to download all my Portfolio Trackers: / thinkstocks
☑️ Follow me on Twitter: / keithfrislid
🎥 Here is the camera I use for my videos: amzn.to/2PB865Q
🔶Get 50% off Seeking Alpha Premium with my Promo Link!!!🔶
www.sahg6dtr.com/28KL61/R74QP/
In This Video, I will show you how to make a dividend income tracker in google sheets. I think it is very important to track your portfolio's dividend growth if you are someone who favors dividend-paying stocks or would one day like to live off your dividends. The google sheets tracker will be your best friend tracking your monthly dividend income and stay nice and organized as you grow your dividend portfolio over the years!
If you like this tutorial make sure to smash the like button and subscribe to my channel!
🔷Google Sheets Functions:
🔸Price: =if(isblank(A2),"",GOOGLEFINANCE(A2,"Price"))
🔸Market Value: =if(isblank(A2),"",D2*B2)
🔸Dividend Yield: =if(isblank(A2),"",(SUBSTITUTE(INDEX(IMPORTHTML("www.finviz.com/quote.ashx?t="&A2,"Table",7),8,2),"*",""))*1)
🔸Dividend Payout: =if(isblank(A2),"",(SUBSTITUTE(INDEX(IMPORTHTML("www.finviz.com/quote.ashx?t="&A2,"Table",7),7,2),"*",""))*1)
🔸Payout Ratio: =if(isblank(A2),"",(SUBSTITUTE(INDEX(IMPORTHTML("www.finviz.com/quote.ashx?t="&A2,"Table",7),11,8),"*",""))*1)
🔸Ex-Dividend Date: =if(isblank(A2),"",index(IMPORTHTML(CONCATENATE("finance.yahoo.com/quote/",A2),"table",2),7,2))
🔸Annual Div. Income: =if(isblank(A2),"",G2*B2)
🔸Industry: if(isblank(A2),"",index(IMPORTXML("eresearch.fidelity.com/eresea..."&A2&"","//div[@class='sub-heading']//span[@class='right']"),1))
AFFILIATE DISCLOSURE: Some of the links on this channel and in video descriptions are affiliate links. At no additional cost to you, we receive a commission if a purchase is made after clicking the link.
#Dividendincometraker #dividendportfoliotraker #dividendstocks #googlesheets

Опубликовано:

24 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 337

@ThinkStocks 2 года назад

***FINVIZ UPDATE*** Hey Everyone, Finviz updated their code so now you need to pull from table 7 instead of table 8 like in the video!!!

@loustj7800 2 года назад

Is there a way to change the code so that if there is an error a “-“ will appear instead of n/a or ref error? Thanks.

@kfordham281 2 года назад

Thanks for this update! I was lookking into what happened and tried both 9 and 10 but not 7. Moving forward, I'm substituting that table number value for a cell value from another sheet so that I can update one cell and all my formulas that call that table number will be updated with a single update.

@Specv1228 2 года назад

You are the man!!!!!

@VanZam 2 года назад

I having trouble in Industry column :(

@davidsiminoff7940 2 года назад

can someone paste the formula here so we can just copy and paste? I keep getting errors

@mittjoshi6011 3 года назад

Beautiful. I loved it. I was looking for something like this exactly. Thank You!

@lukeyoung9839 2 года назад

Just stumbled across this and it has already been a lifesaver! Still trying to learn all the ropes. Thanks!

@charalambosmichael4678 3 года назад

Thanks again! You have a gift in teaching! Also, *1 works to destring the copied information so I can conditionally format them later.

@mikenone590 2 года назад

Excellent tutorial. Exactly what I've been looking for. Thank you.

@mangos2125 3 года назад

Thank you so much for sharing! this content is amazing, I created my own spreadsheet for tracking stocks and options, your video helped a lot!!

@ThinkStocks 3 года назад

Glad to hear it!

@envylime 2 года назад

I was looking for how to create tracking sheets like yours. Your video is super great 👍🏻 Thank you for so much.

@susanvelez5167 2 года назад

Awesome, thanks for taking the time to put this together. I've been looking for something like this to document my dividend growth. My question is, if I wanted to keep track of the income the following year, do I just add new months in the data tab and label them 2022 and then just create another monthly dividend income chart for the new year? Thanks again!

@barisciloglu6903 3 года назад

Great video especially thanks to types of functions

@nataliepope3286 3 года назад

If I could like this video a thousand times I would. I sat and created this whole spreadsheet with you. Thank you so much. It’s nice to actually have numbers to see how my investment are doing😊

@ThinkStocks 3 года назад

You're welcome and glad you liked it!!

@christophermichael236 2 года назад

I keep getting an error in column E, could you send me a copy of yours so I can see where I am going wrong?

@nataliepope3286 2 года назад

@@christophermichael236 Hi sorry my computer crashed and I haven’t been able to get another one as yet. Are you sure you are following the step in the video accurately? Sometimes you just need to pause the video and look at the function to make sure it’s exactly the same. Spacing and everything. It also helps to write it on a piece of paper first then do it on your computer.

@pal8h 3 года назад

Awesome find with the sector info!

@joementoro6456 2 года назад

Thank you so much, I was able to follow your instructions and create my own spreadsheets.

@ThinkStocks 2 года назад

Nice!

@aarthiarun9440 2 года назад

works perfect! thank u so so much.

@sydneymady8415 9 месяцев назад

I watch some of video not understand but your video is very clear & spot on thanks dude

@soniaehivet9850 3 года назад

Thank you for these amazing videos!

@ThinkStocks 3 года назад

Glad you liked it!

@roeylevi5416 3 года назад

Your videos and this whole channel are amazing !! If I want to check the dividends let's say 5 years back how do you do that?

@demman8081 2 года назад

Great tutorial. Thanks.

@mikewestern9925 2 года назад

Great video can't wait to make my own

@verywai 3 года назад

Great video, thank you so much

@andrewdavis6917 Год назад

Thanks for the great content. Hi from Texas

@huethan3495 2 года назад

Thank You you save me two dayss

@guille7007 2 года назад

I made all spreedsheet too following all your steps that mention!. Excelent video!! Thanks for that!. Do you have something similar for options? Thanks!

@aplacadorfamily2417 3 года назад

I was able to create a Canadian version with your overall template! Thank you!

@ThinkStocks 3 года назад

Thats awesome!

@SkyWolf520 3 года назад

Hi im trying to make a canadian version as well, can you send me the formula that you used for Annual payout, payout ratio, Annual div income? Just a little stuck just because finviz doesnt support canadian stocks. Thanks!

@aplacadorfamily2417 3 года назад

Annual Div Income is just where you add your # of shares and annual payout so you can just manually add those 2 up.

3 года назад

trying to make a Canadian version as well, I haven't been able to make the formulas work for PE and yield. Changed ticker to multiple versions, tried yahoo, Gfinance, and other formulas, no luck. If you could provide the formulas you used that would be much, much appreciated!

@kmichelevarcoe 2 года назад

Would you be willing to share your Canadian version with us, please? I haven't been able to get the one I am trying to build work. Thanks in advance!

@241Topic 3 года назад

great content!!

@haleythelibrarian3688 2 года назад

OH MY GOD I made an excel spreadsheet. Thanks!

@DowsStockTalk 3 года назад

Your customize excel spreadsheet are amazing

@ThinkStocks 3 года назад

Gotta keep track of those gainz somehow📈💪

@ilyarique 10 месяцев назад

Thank you so much!

@Pharaoo2012 2 года назад

Thank you, excellent.

@idofelix5707 3 года назад

thanks this helped a looooooottt

@Senior-de-la-Vega 2 года назад

Thank you for helping! I have a question regarding the annual payout. This information is formated as a text and cannot be used in a formular in my sheet. Is there a trick to change that? Thank you!

@TheJamieParisShow1 2 года назад

Just found your channel and love your spreadsheets. Question couldn’t you use your transactions sheet from your dark themed portfolio tracker to make this more automated? Just thinking it would be cool to enter your buys, sells, dividend income etc to have it update your positions and monthly income. Just a thought and would be awesome to see an updated dividend tracker spreadsheet. Thanks for the videos.

@sebastiankongstad6403 3 года назад

Thanks for the vid

@jackduffard2269 Год назад

Is there an update on Fidelity industry code? Having trouble getting this to work.

@DrunknOdin 2 года назад

Your sheet tutorials are the best. I have learned lots but still struggle with some things. Finviz doesn't seem to provide dividend data on preferred stock that I'm aware of. Any recommendation on how to pull that data if finviz doesn't have it? The stocks in particular I'm asking about is HT-d, rpt-d & rlj-a. Again thanks for you help and keep up the good work!

@usman.ghani162 Год назад

Woww . That's superb.

@robmorrison228 2 года назад

Great Video!

@staycalmcollectdividend9104 2 года назад

Thank you.. Dividend Yield is Table 9 today.

@ninaa5984 3 года назад

Great video. Could you please tell me how can I calculate dividends for the next 5-10 years? Thank you.

@rccabral 3 года назад

Hi! Im trying to automate a dayly save of a cell to do a grafh, do you have a video about that?

@yana7071 3 года назад

Thank you for all information and work hard to update them. Would you do the "Keep Days" column and "Shares Outstanding" column? want to know how many days I keep the stock on my portfolio and How many Shares the company has. very appreciate your time.!

@ajducey1868 3 года назад

Thank you!!! You're AWESOME, I've subscribed. Can you do an options income tracker?

@ThinkStocks 3 года назад

Thanks for the sub man! and that's on my list! I got a few more trackers coming out before that one!

@sebastiaanhazelzet8342 3 года назад

Hey, I tried to put in AT&T but it does not show the "pay-out ratio", could you maybe help?

@k2774 3 года назад

This is great! Thanks. Any advice on easily finding the table number? I'm looking through the html and only counting 4 previous tables. Assuming they are buried, but wondering if you have a better trick?

@ThinkStocks 3 года назад

Thanks! And nope, it's just trial and error unfortunately! Lol

@WinterKing2811 2 года назад

Have the same question..really trying and error?

@erikbaghdasarian4091 Год назад

If you want to know what the current table number is. 1) Once your on the webpage. Press f12 on your keyboard. 2) On the menu that pops up the right hand side of ur browser. Click on console. 3) Paste the following line into the console. var i = 1; [].forEach.call(document.querySelectorAll('table'), function(x) { console.log(i++, x); }); 4) All the tables on the webpage will come up and hover your mouse over them until the one you want is highlighted. 5) the number of the table will be next to it. :)

@MrTheelicitor 3 года назад

Thanks for sharing much appreciated. How does one deal with some UK stocks e.g. BT.A as a ticker symbol. In some case there is need to place the name of the exchange so one can get the required information. How do you do this?

@nathanielmason6266 3 года назад

You need to add an exchange reference to every ticker (or you could add the exchange reference in another cell as part of the same row). This data can then be used as needed to adjust the importhtml function based upon search requirements - for example yahoo finance uses .L at the end of a ticker reference for UK stocks. With BT-A and ex-dividend date, your ticker would be LON:BT-A (in cell C4 - for the example below). To pull other data for BT-A all you need to do is adjust the table (4), row (8), and column (2) values. =iferror(if(isblank(C4),"",index(importhtml("uk.finance.yahoo.com/quote/"&REGEXEXTRACT(C4,".*:(.*)")&if(regexmatch(C4,"LON:"),".L","")&"/key-statistics?p="&REGEXEXTRACT(C4,".*:(.*)")&if(regexmatch(C4,"LON:"),".L",""),"table",4),8,2)),"-")

@wesgoebel2293 2 года назад

Love the Video, I am excited to learn more now that I subscribed. I am from Canada and many of my Div funds are Canadian . The Finviz tables do not seem to work for Canadian stuff. How can we pull that info?

@jordanmorgan-saxby1804 3 года назад

hi, trying to do this from australia, is there a way to make google finance pull data from Specific exchanges? its getting confused and pulling date from US companys with the same ticker as the Aus ones im trying to use]

@CSchaef02 3 года назад

Great video! What if I'm tracking things like SPHD and VDIGX in the same spreadsheet?

@EspyJW 3 года назад

resource at url not found. :( did the table move or is my CTRL C, CTRL V skills lacking?

@anddocster 3 года назад

Had the same issue. Check to be sure you have “ “ around the URL

@MrDraidis Год назад

This is a great video ! - any idea what table and from where for the ASX ( Aussie Stock Exchange )

@elizabethsanchez3351 3 года назад

First of all, thank you for taking the time to teach us how to create this tracker. I have a question regarding the PAYOUT RATIO, the formula only worked on the 1st cell. On the remaining cells, I am getting #VALUE!. When I click on the cell with the error the following message pops up: Function MULTIPLY parameter 1 expects number values. But "-" is a text and cannot be coerced to a number. UPDATE: I just realized that all the tickers on my spreadsheet do not have any data for the Payout Ratio on the finviz table. I looked up each any every one and the Payout Ratio on the finviz table only shows a hyphen for all my tickers. Is there a way to hide the #VALUE! error message on all my cells? Thanks in advance.

@chemist67 3 года назад

how can you copy these functions Like dividend ex date and such) or do we type it using your video?

@MrChingChingPow 3 года назад

Hi, thanks for sharing. Awesome formula. Somehow when I get to the Industry Breakdown, for the Market Value, it is only showing value on the 1st row. the rest is blank. And another thing is when you finished putting down the formula, it showed the autofill, mine did not. Any idea why it did not? Thanks!

@mjb7469 2 года назад

Having same issue

@MattLunser 3 года назад

Thanks for all the videos! Question from a newbie regarding Div. Yield. Shouldn't we calculate the yield base on our purchase price rather than the current price? Meaning the yield on our investment will stay the same regardless if the stocks current price. (assuming the dividend is the same). i.e. I buy a stock for $50 w/ $2 dividend. I get a 4% div yield. If the stock doubles, an you bought the stock your div yield would be 2% but I'm still at 4%. Or am I thinking of this wrong...? Thanks again!

@ThinkStocks 3 года назад

That is a great question! And what you are thinking of is yield on cost! I posted a video a few months back regarding yield on cost and the difference between the two. So by all means you can have both yield on cost and dividend yield to see the growth

@lestancy 2 года назад

You can compute on historical cost yield or current price yield. It really depends on your preference but current price yield is usually more relevant since you can add or sell at current price.

@jimmysanders1375 3 года назад

Is there any way to do this with excel? Thanks for the video!

@nunoduarte8984 3 года назад

Hi! Nice work, thanks very much for this sharing. You've got another sub. If I just use "," in the formulas, nothing works. I had to replace it with ";". However, when I import data, it appears to be in text format and I cannot change it to currency. This situation does not allow me to multiply values such as Payout Racio by the number of shares. Can you help?

@lukesaan 3 года назад

I got the same...any solutions?

@nunoduarte8984 3 года назад

@@lukesaan you have to use a substitute indication. Show me the line code you got pls.

@kinbg 3 года назад

Where did you plug in the numbers for the income on the data sheet?

@Tommybotham 3 года назад

My data takes long to load now. What methods can I use to speed it up?

@strangergranger10 3 года назад

33:52 I'm not quite sure how can I put my annual income monthly since it's since I haven't getting paid for the rest of the year. The Ex-dividend and Market Value are not working. This is getting frustrating.

@DestroyYesterday 3 года назад

Bro this is awesome. Is there any way to link data to see my P/L for the day?

@NikiinCanada Год назад

Thank you

@Nadia72639 2 года назад

The tracker stopped reading the finviz table recently. It seemed to work again when I changed column F, G, and H in My Positions from _"Table", 8_ to _"Table", 7_ Keep the rest the same.

@juniortj9428 3 года назад

Great video man. Do you know how I can create projected income month by month or quarter by quarter based on DRIP in Google sheets? Do you know how I can set up a live sparkline that shows the performance of the entire portfolio? Thanks

@ThinkStocks 3 года назад

Thanks! And so far no, I've been doing a lot of research on how to do that but so far no luck. When I figure out how ill be sure to post a video!

@daveh7235 3 года назад

How does this work for canadian stocks on the Toronto Stock exchange? FinViz doesn't have some of my stocks I own.

@aplacadorfamily2417 3 года назад

You have to use Yahoo Finance

@ramazanbelgirat4562 2 года назад

Thank y.ou wery much

@Chubacca838 3 года назад

Sure wish there was a Canadian version of this. Oh well, it looked nice and promising.

@5Rodneyterr 2 года назад

I fully agree, tried recreating my Canadian portfolio and realized it just wont work out.

@kiwionarope 2 года назад

How do you specify between stocks that are in USD or CAD? Also, when I add the formula to update the Market Price, I don't know where its getting the price but they are not what is showing on Google finance. Update - I notice the Google finance site, when I post Canadian tickers, it doesn't recognize that they are Canadian and changes to the American ticker and price, how can I fix this?

@amittekchandani Год назад

Hello, thnKs for such informative video. looking for such content from months Finally found here. btw Finviz doens't have Indian stocks. do you recommend any particular website similar to finviz for indian stocks??

@mangekyousharingan7335 2 года назад

hey still have 1 -2 questions, how does it go over several years and how can you add dividends already received?

@rahuls7498 2 года назад

Awesome

@kelle_li Год назад

Hi, great tracker, currently building something similar following your awesome design. Just wondering how do you know it is table 8? Where does it specify it's table 8? For example if I wanted to pull data from another site, how do I know which table # to pull from? Many thanks

@fuccckckkkkckkck Год назад

Its table 9

@myrjavi Год назад

@@fuccckckkkkckkck Awesome sauce. How do you know which table it is?

@Kodykenway 2 года назад

How do you find out what table number a table is? If you see data you want to import, how do you find the path to import it? I am struggling to pull data from yahoo. Finviz does not cover a lot of canadian stocks.

@Riot_Man 2 года назад

The XML you're importing from Fidelity for Industry / Sector info doesnt work on all stocks because not all stocks show that info on the stock page.

@arturosanji Год назад

great info, I'm getting an error in the dividend yeild part I can't get the data from finviz, it gives me an error, any guidance will be welcome

@huethan3495 2 года назад

you are greatest

@chavezsv Год назад

Hi, what do I do if one of my stocks just did a reverse split? Thank you.

@flipSSide97 3 года назад

This is great thank you! How do we know what "table" a table is if it changes?

@sanketchafle4018 3 года назад

hey @flipSSide97 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Q5yVoTqD06M.html watch this video it will show how you can find what is the table number of table you want to find out. It works. Hope it helps.

@DLCShark 3 года назад

I want so badly to make this sheet but the ex dividend date won’t work. Nor will the industry formula.

@CasualFlyer 3 года назад

@DLCShark The links in the discription for ex-dividend and industry dindn't work for some reason. I went back to the video to copy them individually and it all worked!

@aaronrd6136 3 года назад

@@DLCShark Maybe it's because you use comma to delimit decimals. If it's true you need to substitute dot by comma. Like this =IF(ISBLANK(A2);"";SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTHTML("www.finviz.com/quote.ashx?t="&A2;"Table";8);8;2);"*";"");".";",")*1)

@fuccckckkkkckkck Год назад

@@CasualFlyer How did you input it for Industry because its the only part that doesn't work

@thinkitthendoit3224 2 года назад

I am having a hell of a time with the formula working, I have changed out the numbers you spoke about but I keep getting errors, or ref.

@DiscJockeyWafer 2 года назад

Awesome torturials, i cant get the dividend to load from the HTML :-( is it because they change the table number? can´t seem to guess the right number if thats the case :-(

@C_m0nkey 3 года назад

Great video! How would I get a Sector name for a ETF like VTI or ARKK?

@ThinkStocks 3 года назад

Thanks! And thats a good question. I'll have to do some research and get back to you on that one!

@C_m0nkey 3 года назад

@@ThinkStocks No problem and alright let me know when you find something on that! I'm in your discord too!

@Perfected69 2 года назад

Where can the dividend income be pulled from for London FTSE stocks?

@alexg8479 3 года назад

Can you track stock performance 5,10 years. And divident cagr 5 and 10 years? Do you know were to pull this data from?

@ThinkStocks 3 года назад

That I have not figured out yet!

@alexg8479 3 года назад

@@ThinkStocks Thanks for reply

@kennethrovtar7692 3 года назад

EVERYTHING PASSED market value says "Resource at url not found." what do i do

@michaeldunning5910 3 года назад

If you used copy paste try typing it in by hand. Copy paste does not always work for some reason.

@anddocster 3 года назад

Even that didn’t work. Bummer

@Mcgurkyyy101 3 года назад

Does anyone know why when I drag the stuff down it gives me a “N/A” on the empty boxes. Like I know why it’s there because there is no stock to get info from but on the video it doesn’t do that

@ricardooliveira522 3 года назад

Great work. For me, in the formula of the Ex-Dividend date, some appear like yours (ex: 44285) and others appear ex: Apr 30, 2021 ... Strange Greetings from Portugal!👍

@inesschu574 2 года назад

Thanks for the amazing work 😍. But how do you know, what table it is? Where do you see that?

@elenaavtaykina2639 10 месяцев назад

I have the same question and am stuck with the Dividend Yield formula not working because the table number changed probably but I don't know how he knows this table number. Have you figured that out?

@osg1409 2 года назад

Hi, how do you find out which table is?

@conradragsdale2989 2 года назад

What do you do when you put an ETF into your portfolio because the industry sector would be N/A

@trevorrobison6806 Год назад

I am getting an error with the formulas that the, "Valid Values are from 0-3 inclusive". Can someone assist me with this?

@cestupcycling9025 2 года назад

I am a Canadian investor and canadians stocks are not on Finviz (unless dual listed on American exchanges), would you know how to pull the date from another source?

@professorgamebum 2 года назад

I am having the same problem :(

@PeteBirdsong 3 года назад

This is pretty amazing. BRAVO! Quick question, maybe someone can answer. Hypothetical... what if a company stopped paying dividends? Then the Div Yield box would ERROR. How can that update to a 0.00% instead? And so forth? F (FORD) in my example gives the following error under Dividend Yield “ERROR - Function MULTIPLY parameter 1 expects number values. But “-“ is a text and cannot be coerced to a number.”

@ThinkStocks 3 года назад

Yes! Add an iferror statement to say zero for companies who stock paying dividends!

@ASMRNatureViews 3 года назад

Can you index funds instead of using only companies. - Thanks I would love that

@ThinkStocks 3 года назад

Sure I can add that to the list!

3 года назад

@@ThinkStocks that would be awesome!

@magicsinmyfingers 3 года назад

When i type in the formula to get a price based off the ticker it gives me the USD price, is there anyway I can get the Canadian Price to show up? If i own the company in the toronto stock exchange it usially ends in .TO but if i add that to the ticker it says N/A. Any help would be greatly appreciated. Thanks

@ThinkStocks 3 года назад

Try putting TSE: in front of the ticker. So for example TSE:T

@magicsinmyfingers 3 года назад

@@ThinkStocks Thank you

@viewjamaica 3 года назад

Can you do a video on how to pull dividend info from Fidelity website page.

@Rsimmons777 3 года назад

I've looked at 2 vids of yours and the ex dividend just doesnt work. All it says it resource at url is not found

@VanZam 3 года назад

same here :(

@LightlessDimension 3 года назад

I did exactly as instructed, but it didn't work... :(

@3pilot 8 месяцев назад

Ex-Div date isn't in same spot for all my tickers i guess..doesn't work.. on SCHD it's the expense ratio whereas on your example it's the right spot for aapl..any thoughts?