How To Make A Dividend Income Tracker In Google Sheets

what is going on guys in this video i'm going to break down how to make this awesome dividend income tracker in google sheets everything you see here is completely dynamic probably the best way in my opinion to track all of your dividend growth stocks you can see here's my portfolio breakdown all the different stocks i have and what my annual dividend yield is per stock 3m being the most almost 300 then down here this is tracking your monthly dividend income and you can see right here with this portfolio it is in a beautiful upward trend uh making between around 75 and at a high 150 a month and then to throw in we got a nice little industry breakdown which i believe is always good to look at if you're tracking dividend stocks let's jump over we have we have four different uh tabs down here we'll jump over the portfolio breakdown so you can see here's all the stocks in this portfolio no no this isn't my actual portfolio i just did this for an example but i do own a handful of these stocks um and by a handful i mean just one which is apple but uh that's not here nor there let's jump over to my positions so this is where we're getting most of our data from and this is what's creating the other four tabs so you can see right here in this tracker i have 17 different stocks here's all the shares it's industry breakdown price market value dividend yield annual payout annual ratio all that good stuff then we jump over to data and this is where the magic happens the data actually then is helping us create this beautiful dashboard so if we go over here and if we were to change something so let's say december december it says i made 161 dividend income and you can see right here that's where this is december is 161. let's jump over to data and if we drop this down to say only 100 you can see it'll update see that now it is down to 100. well let's uh let's put that back to 167. and you can see that changed it right here if we go into our portfolio i'm sorry my positions and let's say apple let's say we put a bunch of money into apple right so let's take a look at our portfolio breakdown apple's only 10 percent our dashboard apple is only bringing in 41 a year but if we go back to my positions and instead of 50 let's make it 500 something crazy like that now you can see this has gone up significantly and if we go to our portfolio breakdown 52 would be apple uh data you can see apple market value everything updates here so everything is connected to each other let's go back to my positions change that back to 50 uh and we i'm gonna break down how to make this awesome dividend tracker and all i ask of you guys is to smash that like button and subscribe if you haven't already i got tons of google sheets trackers like this and i will be coming out with a bunch of other content all related to the stock market so let's dive right in we're going to start with a brand new slate and right here we're going to create our four tabs so go to this plus and add three more so you can see sheet one two three and four and the very first tab uh let's rename is going to be my positions this is the first one that we have to do we'll rename all these right now uh this one will be data and then this one will be portfolio breakdown and the last one arguably the coolest dividend dividend making sure everything spelt right dashboard all right great so we could jump over to my positions and here we go so the first couple things we're going to need are tickers and shares that's easy then we want industry then price market value dividend yield if anything's spelt wrong here i'm just trying to do this as quickly and efficiently as possible annual payout whoops get rid of that equals payout ratio x dividend date and then our annual dividend div for short income all right so again this is the building block for everything else so once we have all this laid out you could go and you know you can make it whatever color you want i don't want to spend too much time on that i want to get into the raw data but uh there we have that we're going to have our stocks here what i'm going to do is actually take this thing right here and we're going to move that 1 2 over so then that stays there as we go through this that's if you then want to expand on it um and then you could go through that whatever it might be uh so let's bring that back down to 100. now at this point we could start with some easy stuff you start adding in the stocks that you have so let's just add in what i had so i had apple i had adp [Music] clorox and last 150 all right great now that you have all that stuff in if you want to make it maybe center i always like doing that we can now move on to you know one of the very first ones probably the easiest one that you probably already know how to do and that is going to be our price so if you've watched any of my videos you already know how to do price price is very easy it's equals google finance uh what you want is then the ticker which is in cell a2 for me and then you type out price and close it it's loading and there's apple's price now if you drag this all the way down right right now we have 17 stocks what if you want to add some stocks you want to keep pulling this down obviously like let's say we want to go down to i don't know 30. you see there's a bunch of n a's and to me that just looks ugly so what we're going to do is we're actually going to go back up here in between the g and the equals i'm going to do if is blank a2 so if a2 is blank comma two quotations comma so what this means is if a2 is blank leave it blank if not give it this formula so that's all that's saying and now if you see if we take this and drag it down the nas are gone and that's because this is blank all of these are blank so that's why this is remaining blank nice little trick that i do in just about all my videos uh you could add price here great we have price now we want to move on to our market value so for our market value what we actually need to do it's very simple all we're taking is our price and multiplying it by the amount of shares we have and that's our market value and just like price we want to do the equals if is blank so if is blank and you'll see i have that in just about every single cell and then what we want is d2 times b2 hit enter autofill great we want to make sure this drops all the way down you see how in this view autofill uh e19 has no code in it right now so you want to make sure you bring this down to we brought it down to whoops 30 i believe just bring them all down to 30 or again maybe you have a hundred dividend stocks bring it all the way down to 100 maybe you want to buy 100 more bring it down to 200. so now after that uh we're going to move on to our dividend yield so for many of you you already know where i'm going to be pulling this from it's going to be from finviz and one of the tables so let's jump over to finviz so here's finviz and what we want to do is go up here you can type in any ticker we'll just type in apple um and we're going to be pulling from this table down here now the thing with finviz if you've watched my other videos finviz sometimes likes to change around its code uh one of my first videos i made we were pulling from table 11 then it changed to table nine now currently it's table eight and you'll see what i mean in a second but uh sometimes the tables may change and you might have to update it it's been table 8 for a while now so hopefully they keep it at that but we're going to go over here we're actually going to copy the if is blank because we're adding that to the beginning of everything so you can do that and i'm going to do if is blank then what we want to do is substitute so let's substitute index okay so we got substitute index import html and then what we want here is we're pulling the html so you want https finviz.com quote all the way up to the equals you want to stop you don't want to take the apple and you'll see why in a second so once we have all of that you want to put it into quotations that's making it a string for all my coders out there and then you want to hit end and then we want a2 or whatever cell you're pulling from and then you want to put in quotations table so we're pulling from a table and what table are we pulling from well right now like i said before this is table 8. it may change in the future if it does then you might have to change this but right now it's table eight close that and now where is dividend yield well if we look right here we got dividend yield is right here so that point sixty percent that's what we want so what is that well that is going to be eight down and two over so that's what we're plugging in we're gonna plug in eight comma two close that now you might think we're done here but we're not what we then want to do and that's why we put in the substitute we want to then do another comma and you want to do asterisks in quotations because if we were to hit enter and if we didn't have substitute there's going to be an asterisk around the actual number i don't like that so i like to get rid of that and we want to substitute that for nothing or blank close those then you want to multiply it by one just so you have no problem with you adding it or creating it with other functions uh hit enter load there it is and all you have to do is then make it a percentage boom point sixty percent drag this all the way down to thirty all of them are loading and now all of them are in there's our dividend yield for everything now payout is going to be virtually the exact same code except instead of 8 this 8 needs to be a 7. so if you click on this highlight all of that control copy go to your annual payout paste and change this 8 to a 7 and there you go that's everything we need if we jump over to here just to double check dividend annual is 0.82 that's our annual payout all right so i'm just going to make this the same 10 center that make it a dollar sign so there we go so for every share of apple that i own i'm getting 82 cents pretty straightforward now we move on to the payout ratio again it's going to be finvis again you can see this is pretty easy now uh instead this 7 is going to be an 11 and this 2 is going to be an 8. so copy all of that again paste it into there and then make this 7 and 11 and make this two and eight hit okay there's our payout ratio could highlight all of that center it make the same text and the same size add a little ratio boom there we go payout ratio i personally like anything that's at 60 or less when you start getting up to like 83 that's a little high in my books but uh let's move on i'm not a financial advisor so why listen to me so now our x dividend date so you can see here where is it it is not here there's no x dividend date so now we need to pull it from somewhere else and where we're pulling it from is yahoo finance so let's type in a symbol we'll use apple again and you can see apple has its x dividend date so for this one it's november 6 2020. so how are we pulling that well if we jump back we're going to start with the if is blank like we always have i'm just going to copy this from this cell it's going to be a whoops it's going to be a little different in ex dividend date with yahoo finance so what we want to do is index import html which is right there don't do hxml and then you want to do concatenate so where's concatenate right here this appends strings to one another so very important we have concatenate we want we want to do yahoo finance so in quotations you can then copy our yahoo finance all the way up to the equals not even the equals i'm sorry we don't want to have apple all the way up to that forward slash so right after quote include the forward slash hit copy and we go back to our chart we paste that in we close the quotation do a comma and then we put in our cell which would be a2 close that comma now where are we pulling from let's go back to yahoo finance this is table one this is table two so we're pulling from table two and we wanna go one two three four five six seven and then one two to pull that okay so if we go back we're gonna do seven comma two close out our parentheses hit enter and we have a value editor and the reason why is you can see right here i actually left out something i need to add in table we need to say what we're pulling from so we're pulling from a table hit enter now we got n a and that's because right here we need that 2. so 2 close that get rid of that let me just double check add a comma in here you can copy all of the code down below everything will be right uh let's see if that's right it's loading there we go now you may be thinking that's not a date well you go over to here and just change it down to date boom 11 6 2020 that's what we have 11 6 2020 so now that we know that is right drag that all the way down to 30. all of them are now going to load and we have all of our x dividend dates all right we're almost done with our positions and this will probably be the hardest one um once we do this doing the data and the portfolio breakdown and all the rest of the tabs are much easier so just hang in with uh with me we're now going to do our annual dividend income this is an easy code gonna do if is blank a2 because that's what we're pulling from right now okay we already know that but now what we're gonna do is we're gonna take g2 i'm going to multiply it by b2 so our annual payout multiplied by the amount of shares we own hit enter and then hit ok and then here's how much money we're expected to make per share so now that we have that we could move on to the last one that is industry and i saved this for last because it is probably the longest code and it's kind of weird uh this is where we're pulling it from we're not pulling it from finvez or yahoo finance we are pulling it from fidelity.com so we go up to search quote let's do apple let that load and then if you scroll down it's somewhere here let's see where is it i always miss it right here industry well actually we're going to be pulling the sector information technology this is the data we are grabbing right here from fidelity.com it took a long time for me to find this uh function uh but i did it and again with this alone you guys should be smashing that like button because this is awesome i was able to find this but just like every other one i'm going to start off with and if is blank a2 great so now just like the other ones we're going to be doing index and this time we're going to be doing import xtml so we don't want to do html because we're not pulling from a table or a list pulling from structured data online all right so it's a little different but what we want is the rest is going to be pretty similar we're going to be pulling the https all of this all the way up to before apple okay so all the way up to the equals hit control c i'm going to go back control v now what we want to do is close this off with our quotation hit the end then do a2 hit end again close those quota well not close those quotations we want to then make a quotation close quotation do a comma close those quotations and then we're going to be pulling from if we go back to fidelity uh it we would have to right click go into our inspect uh page source we're gonna be pulling from there i don't want to do that right now just so i'm not digging through it and to make this video quick because this video is only going to be long enough uh so this is the code it's going to be 2 forward slash it's going to be d i v square bracket and class or not end at class equals and you could copy this code from below one quotation not two sub dash heading close that out two more forward slash span square bracket at class again equals open single quotation right close single quotation close square bracket close double quotation close parentheses comma one that's really important two more close let's see if i got this right maybe i have a spelling mistake floating and boom there we go information technology that was the hardest one guys if you did that then congratulations it is a cake walk from here on out now that we have this the hard part is done now we can get into the fun stuff so the very last thing we're going to do on this page is go down to however many stocks right so i go all the way down to 30. if yours is down to 100 then go for it we're going to go over to conditional formatting and if it's not empty we want this to be maybe a dark green not the text the text is none we want this one to be a dark green something like that i'm going to hit done and then we're going to go over here highlight all this down to 30 and we will make this conditional formatting a lighter green something like that hit done x out of that all right great so now at this point our positions are done if you want to buy another position last one let's actually just make this all bold there we go so if you want to add another position so one i don't have let's see do i have walmart it doesn't look like i have walmart so let's say i want to add walmart to my portfolio 10 there you go everything gets loaded oh we can see i forgot to drop this down so let's drop this down to 30. drop this one down to 30 as well and then this one down to 30. there we go so everything gets updated if i add more in then the green color goes down the industry all the beautiful stuff goes down but let's get rid of walmart because i didn't have that included uh so now that's it our positions are done congratulations with that we're gonna move on to data this one's gonna be a lot easier now and things should start to pick up all right so for data what we're going to have here and i'm actually going to jump over and we're going to cheat a little i'm going to go over to data i'm just going to copy all of my headers to speed things up and be a little bit more time efficient i'm going to paste that into place okay great so now that we have all of that we'll widen these a little bit as well and meanwhile i'm writing these you could be plugging this data into your sheets and then the blank ones i will bring in like this okay great now that we have that uh for the date that's gonna be easy we're gonna do jan feb if you have two you could probably autofill the rest if i highlight them go all the way down there we go get rid of january so i do it on a yearly basis um if you want to make that a color go for it it's going to make whoops wrong one i'm gonna make that a color i'm gonna make income gray and the reason why income is gray is because there's no code for this this is gonna be manual uh it's gonna really be the only thing that's manual other than uh putting your positions in so if you want to maybe just put a border around it or something i made it great to know like okay i'm gonna have to go in and each month i'm gonna have to put in how much income i've made just because things can happen even though we have our annual income here anything can happen any of them could cut dividends just because they have dividends doesn't mean they necessarily need to pay you dividends so that's why we need to manually put in our income here and just kind of stay on top of that but the rest will be uh pretty automated so for this we're going to be pulling from my positions so to do that it's really simple we're going to do equals and type in my positions or whatever you have this tab as if this is just positions you're not going to type my positions you're going to type positions and then do an exclamation point and then a2 so what's in a2 a2 is where all of our symbols are right so if we just jump back over here a2 is apple so what we then do is we will take this and drag it all the way down to 30 then all our positions get filled and we could do the conditional formatting i'll save that for later but let's make that center market value is going to be pretty much the same exact thing except instead of a2 which i'm actually going to copy this paste it into market value and change it to e2 because that's where we're pulling from we're pulling from e2 if we jump over them here e2 is our market value see that let's go back to data and again don't forget to drag this down to 30 and then we can make that center you can see this is a lot easier now we the hard part is over this is now making it fun so this is going to be the same exact one as this so we go back to tickers put that in and we drag this all the way down to 30 make it center annual dividend so our annual dividend is in j2 so we can jump over here's j so we're pulling from the j column let's go back to data same deal we'll just copy and pasting and instead of a2 this will now be j2 hit enter autofill great drag this down to 30 like we do with everything else center it and now we get to industry so industry is going to be a little different and that's because we don't want to have it all the way down we want to then concentrate it um and then do a little bit of math where if apple here this is what i mean we'll look so apple and adp are both information technology so how do i add these two together i want to add both of these together so it only shows information technology once because i have information technology let's see one two three and four i want these four stocks to kind of combine together and combine their market value together so how do i do that we'll add the 6700 this 2600 this 5300 so it's this neat little trick called equals unique so unique rows in the provided source range so this isn't going to create any repeats which is really cool so if we do unique and then what we want to do is my positions so i'm actually going to paste it in but instead what we want to do is c2 we're pulling from the c column now so c2 down to c30 or however far down you go and then we hit enter i have an equals here by accident there we go look at that it just pulls the industries that i have with no repeats so it kind of combines it together and then for the market value this is the only quote-unquote complex code on this sheet so we're going to do the equals if is blank so if is blank j3 so this is j3 if this is blank we don't want anything to show comma quotation quotation comma then we want to sum we're doing a sum if function so sum if my positions so we're going to do my positions don't forget the exclamation point and i'm going to do money sign c money sign so money sign c money sign two quote um semicolon then we want to do money sign c money sign 30. whoops c yeah we just want to do c money sign 30. comma j3 comma i'm doing capitalize i'm not meaning to do that when i sign 30 there we go j3 one quotation because we're going to do my portfolio my positions my positions and by now you guys should know it's very important to make sure you have every last thing spelt right or it will not work it will break the function so now we're going to do money sign e money sign two semicolon money sign e money sign 30. close both of whoops only one i messed up somewhere i know i messed up somewhere it's not gonna work where did i mess up right here we don't need that to be closed out there we go see that the price is showing the price is right there are all of them if you want to do some quick math just to make sure it's right just sum all of them so that's 67 470 we go back to my positions go over to market value hit equals sum all of them there we go 67 470 so you know that this is correct right that's a quick and easy way just to make sure that your math is right and everything is going in but that's it that was the hard part it's now done so what we could do is center this and we can make this money and then you could highlight from here down to 30 and then add in that conditional formatting if it's not blank if it's not empty make it that green hit done x out boom this one is now done now you might be thinking hold on income is blank that's because remember that is where we're going to be putting in so say okay january happens and he made 88 and 32 cents great february happens and you make 73 72.41 so you slowly got to go back and put it in it's really one of the only manual parts of this portfolio tracker um and what i'm going to do is i'm actually just going to copy and paste from the original one i had just so everything stays the same so paste them all in and it doesn't just have to be an annual one if you want to keep on going all the way down uh so you could see instead of one year maybe three years whatever it might be you just keep adding to it but this section is now done we can move on to the next tab portfolio breakdown this is a really simple one so to do portfolio breakdown we are actually going to jump back to positions and what we're going to do here is go to insert chart so we want to insert a chart and we want it to be a nice pie chart i like the doughnut chart and for our label what we want is going to be our tickers so you just kind of highlight that row hit enter whoops click on that select range there we go we want that hit ok and then our values is going to be our market value so we want to select the range market value hit okay and here is our lovely chart that is now created um if you want to make it 3d go and hit 3d and i'll make it 3d if you want that uh go to customize and if we go to titles you can then add a title so portfolio break down spell breakdown right there we go make it a little bit bigger make the text black and bold and center it and then the last thing i think i had the background was like a light gray something like that and then if you want to you could also change see if you click on these little guys there we go so you can change this as well maybe you want it bold or actually i think i changed it to wide there we go beautiful so once that is done obviously this isn't in the right tab so just hit ctrl c go to data not data go to portfolio breakdown control v and it copies it into the proper tab so we can put it there we can widen it like that widen that like that so it fits your screen and then the very last thing it's weird how they're all the way out we don't need them all the way out so if we double click we can then bring it in so let's bring it in like that bring this one in as well that looks pretty even there we go so there's my portfolio breakdown if you want it 3d or not i honestly i don't like it 3d i like it this one 2d but we'll leave it 3d for now that's your portfolio breakdown you can then go back to my positions just kind of delete that because we don't need that so we have my positions we have data and we have portfolio breakdown done in the books last one is our dividend dashboard so at this point if you want to move your tabs around i like the dashboard first then maybe the portfolio breakdown then my positions then the data in the back i mean the data is not that important it's just so we could create everything else so i just have that as the last one but now we can move on to the dividend dashboard all right so very similar to how we did the portfolio breakdown to do the different dividend dashboard we're going to be pulling um from our positions and even data now to make our other charts so first we're going to start with the industry breakdown and we're actually going to go to our data tab and we're going to go to insert chart and what we want we can move over a little bit we want to make sure it's a pie chart or the doughnut chart that i like and for our label i'm going to click on this and for our data range we're going to take from right here so we go from here you want to go all the way you know down to maybe 25 or something you don't need to go down to 30 for this one actually because you're probably not going to have that many uh industries i doubt you have over 30 industries i don't even know if there's over 30 industries and then for value it's going to be the market value so we scroll all the way down to whatever it was 25 hit okay and then you can see here's our industry breakdown um so last what i want to do is go to titles and we want to make it industry breakdown and we want to make that black bold center 24 and then what i did here because everything looks you know it's all about visual appeal to me um you know i gotta make those thumbnails look nice for you guys to click on the videos i changed all these to those colors just a bunch of green because that's the theme i have going on here and while i'm at this if you haven't already smash that like button i know you guys are probably annoyed by now listening to me always say it uh let's see am i missing one i'll make that a custom whatever that's good enough that's close enough uh and then i just have the background is like a light gray something like that so that's for our industries you can then go and change this make that bold that's good i like that um so we'll make this smaller something like that at this point we could click on this hit control copy go to our different end dashboard paste it in and then bring that to the top right there so that's done go back to your data we don't need that anymore so you could delete that because it's saved here and now we can move on to our dividend income breakdown so to do that what we are pulling from is my positions and we are pulling from our tickers and our annual dividend payout so this one right here so to do that i'm sorry annual dividend income right here so to do that same deal we're going to do a chart although this one is going to be a column chart our x-axis is going to be all the way down to 30 hit okay and then our y-axis we're going to go over to here j's right j2 all the way down to j30 hit okay now that's up we're going to copy it we're going to paste it into here move that up we'll bring this one up as well and then widen it like that bring this up right about there i actually think i had no maybe i had it there so at this point find the title dividend income breakdown keep forgetting the b for some reason there we go make it 24 again you don't have to follow exactly what i'm doing here i just think it looks nice it's like this and these little guys i want nice dark green ah that's too dark let's make it that green and for the background the same gray that we're using okay good and actually why isn't that 24 there we go that is two charts done we got one last chart and that is going to be the bottom one our monthly dividend income so we want to go back to our data one last time guys we are in the home stretch here we're gonna go to charts this time we want it to be a line chart or i have an area chart i think that one looks the nicest and what we're going to do here for our data range is going to be our dates hit ok and then for our series is going to be our income so select all of them b3 to b14 hit ok there it is we can then go to customize add that title dividend not dividend monthly dividend income make that black bold 24 center it control copy go to dividend dashboard control paste bring it up here stretch that out to there stretch it all the way out to there i want to then bring it up and then the last thing is obviously make it the color that we want which is this green that we're going for and the very last thing background that nice gray and we are done as your dividend income tracker the entire chart is dynamic every single part of this is dynamic we go over to positions oh let's delete that so just just so you could see we then go let's add walmart again uh before that let's actually go over here so you can see walmart isn't here you could check out all of that my positions the data as well let's delete that we go to portfolio now portfolio positions let's add walmart and we'll make it a sizable amount we'll say 200 shares just show it really pops up uh did we forgot to drop that down we did let's drop that down so there's walmart you can see portfolio walmart is now added dividend dashboard there's walmart right there consumer staples which walmart is made that one a lot bigger as well and you can even see where is it walmart is added to the bottom here and consumer staples right there forty three thousand so everything's dynamic here the only thing that you need to do is just stay on top of the dividends that you are making each month so that's the only thing you need to plug in right here so let's say we're not in december let's say it's august so let's just delete all of those the only thing that's going to change is right here so you could see okay we're in the month of july say and we can just kind of track how we're doing with our monthly dividend income but that is it guys let's let's add that back really quick there we go we added that back that is it guys again smash that like button if you like this drop any comments if you have any questions check out my other stuff i hope you guys like what i was able to create for you and as always i will see you guys in the next one

Channel: Think Stocks

Views: 40,369

Keywords: Think Stocks, dividend income tracker, dividend income tracker excel, how to track dividend income, How to make a dividend income tracker in google sheets, dividend income tracker spreadsheet, how to make a dividend spreadsheet, how to make a dividend spreadsheet excel, track your dividend income in google sheets, google sheets dividend, google sheets dividend tracker, google finance google sheets dividend, google sheets dividend yield, Dividend portfolio tracker in google sheets

Id: m1IvRbj5CzA

Length: 46min 35sec (2795 seconds)

Published: Fri Jan 08 2021