How to Create A Simple Beginner Investment Tracker Using Google Sheets (Built From Scratch)

so over the past few days I was actually scrolling through my youtube comment section and I stumbled across a comment that I realized that never responded to and that was asking how I actually was structuring my investment trackers how I keep my investments in hand and know exactly what's going on within all of my accounts so what I wanted to do was actually sorry little pet peeve of mine there what I wanted to do was actually show you guys how to set up an Excel spreadsheet or a Google sheet from scratch to track all of your investments it's going to have a lot of great features it's gonna be pretty automated for the most part there'll be a little bit of manual input that you need to do but there are some great functionalities right within these programs that are automated that are going to allow you to keep track of all your investments to get full insight onto what's going on and you can look at specific things that you tailor towards your investment style so it's a really great idea to have a tracker in place have a place that you can go to to see all of your investments and for me what I do is a little bit old-fashioned some might say there's probably some resources online that you can have it a little bit more automated but I like to have that personal tracking I know my informations right as I input it myself so I'm gonna act like I'm starting from scratch here and build this spreadsheet out right in front of your very eyes so you can follow along and if you want to build a spreadsheet along with me as well you can go ahead and do that so first thing you're gonna need to do is jump into Google sheets alright so now that you are within the Google sheets application there's a lot of great features that come along with this for tracking your investments tracking your portfolio's I'm just gonna start off by renaming the spreadsheet here so I'm gonna do investment tracker I would recommend that you have open your portfolio or portfolios that you want to track right now I'm doing my Robin Hood portfolio I'll start off with the top three names of the companies here for you guys the top three holdings that I have just to give you a breakdown I'll skip ahead and fill in the rest off-screen and then I'll give you an entire break down after I fill them all in so let me start off with the ticker symbol and this is going to be manual there's a couple different manual things you'll need to enter but for the most part this will be pretty hands-off you just to put in a few different things and it will auto-populate the rest that you need so I'm gonna do s PhD oh and K oh so SP b o KO next little column here I'm gonna have be the name of the company so company aim and then this is where we start to see some of that automation so if you do a formula so if you do equals and then you start typing Google right there it auto populates Google Finance so what you're gonna want to do is click on Google Finance you're gonna select the ticker symbol and then from there just hit comma and then in quotation marks you write name and then hit enter and you can see that pulled in the company name right there so you can literally just drag this down and it will Auto populate the rest of them for you right there because it's just bringing the formula down and then what you're gonna want to do you probably have a couple of the companies that have long names like this just so you can get the perfect size you you're gonna want to go in between these two right here double click and that will automatically drag it out to be the proper length for you so that's just a neat little trick right there the next thing that I'm going to be populating is the number of shares that I have and then on top of that I will be doing my average my average cost so let me jump back in here 32 shares and nine point five four eight point two so 32 nine point five four and eight point two just a little bit more of the manual stuff and then unfortunately I also have to get the average cost by going in here and you see my average cost is $37 71 cents for this one thirty seven point seven one hop and back we've got let's see our Realty income we're looking at $69 and 30 cents sixteen point three zero and then we are looking right at for coca-cola our average is going to be 49 of 41 so 49 for one okay and then from there the next thing that I want to cover is the actual share price that it is today and that is pretty easy that's where another one of those automations come in so it equals Google and then click on the Google Finance again you click on the ticker there you do a comma and then now you're typing in the word price and price and I didn't put the quotation mark so you put the quotation marks you do price and you hit enter and then it loads in the exact share price right now for you so you just drag down on down to and all these are pretty accurate the only thing that you need to keep in mind is these aren't the most updated you can see it updates about every 20 minutes if you read this down here so just keep that in mind it's not something that you should necessarily be a living off of but pretty much after hours it's a pretty solid way to be looking at your investments so that's how you get the share price right there the next thing that I want to go over is total equity and this is a pretty simple formula in itself all you do is the shares that you have so equals the shares and then you multiply it by the share price and enter so there we go those are my total equities right there and if you guys want these to look a little bit better in terms of formatting so first off obviously this is monetary value so I'm gonna go ahead and select this will do monetary column and then you can obviously if you want to do more decimal as you can you can do less decimals by clicking this here but I'm gonna go ahead I'm just gonna format a few of these real quick so so they're the right order and after you do total equity the next thing that I want to go into is total cost so when looking at the total cost you are going to be taking the average cost and then you're going to go ahead and multiply that by the number of shares you have so you can see the total cost will populate there next column I want to cover is going to be the total game and loss that we have here and this isn't gonna look as great as I would like it to look because of the market conditions right now but nonetheless this is just tracking a portfolio this is just teaching you how you can get some of that information in there so don't get too caught up in these numbers what we're gonna be doing for the total gain / loss is taking the total equity and then you're just going to be subtracting the total costs that you have so pretty simple equation right there drag that on down and you can see that that's what it's looking like for my top three holdings in my portfolio in terms of equity that's my total gain and loss on them so from there what I'm gonna go into is the percent gain loss just two so it's basically the same thing but I want to see it in terms of a percentage just to make it a little bit easier to read for myself so equals the share price and then you're gonna divide it by the average cost right there and then after that just subtract one to get it to be the correct decimal formula there so that is that right there I'm gonna go ahead and make that a percent so obviously some pretty big percentages there but like I said not too worried right now so after that what I'm gonna want to look at is my allocation for the portfolio to know how heavily weighted I am in each one of these so if I were to do this right now you're gonna want to do first you're gonna need to do some totaling of these so let's just imagine right now that this row is going to be your totals so when I come down here I'm gonna want to do equals sum and then I will drag this here hit enter then for total cost we're gonna do the same thing equals sum and then drag this down right here and then might as well go ahead and do it to total equity so equals sum and it suggests what to do right there so I'm just gonna go ahead hit enter so those are some of the totals right there for that so to get your portfolio allocation what you're going to want to do is click on equals go over to your total equity and you're going to divide that by the actual total column in that total equity so that's how you get that and you're gonna first think that hey you're ready to go you can just drag that down but you can see that you're gonna get an error from that so what you actually need to do is when you do this equation you're gonna want to do an absolute reference so to do that just go ahead start the equation off and then when you divide it to absolute reference this meaning that all of the cells that are going to be you know linking to it are gonna strictly use this value what you're gonna want to do is click it and you can either click on f4 on your keyboard you can see that put in some dollar values there or you can manually enter those dollar values and that's how you set up an absolute reference so when you hit enter and then you just read rag this on down you can see that the portfolio allocation is just going to appear for you there so as a dividend investor what I'm gonna be really focused on for some of these is the dividend aspect of it as well so what you're gonna want to do is make one of the title rows here for annual dividend and then the next one that I'm going to do is the dividend yield and then after that I'm gonna do the annual return for that so this is the unfortunate part year as well there's no real easy way to get some of these data values so I'd recommend heading over to dividend comm so the first one I'm obviously gonna look up is s PhD just to figure out what their their annual payout is so if I go ahead and click on them you can see the annualized payout is one dollar and eighty nine cents so through 1.89 I'm gonna make this here dollars so it's gonna come out to you got to redo that one point eight nine and then we got Realty income so heading into that it's a looking like two dollars and eighty cents eight and then one more we're gonna do coca-cola and coca-cola is one dollar and sixty four cents so with that information we can now get the dividend yield which is another equation we'll do rate within the spreadsheet so you'll do equals the annual dividend and then you can take that annual dividend and divide it by the share price so that's pretty simple go ahead drag that down and then change this right over to percent pretty good percentage for some of those dividends and then your annual return here is going to be another equation so you're gonna go ahead and do your dividend and then you will multiply that dividend by the share price that it's currently at and when you do that that is all you need to do for that information so let me go ahead and drag that down and that is some pretty great stuff right there so that's kind of the basic set up of all the different categories that I want to be doing for this particular spreadsheet let me go ahead and just do some maintenance so what I want to do is make all of these bold I'm gonna go ahead and Center these I'm gonna make sure that they're there right the right sizes here just so everything looks nice and tidy and from there I'm also going to go ahead and you know what I'll add some borders I'll do all borders around those and then these ones I'll do go ahead and go with one of these so I have a nice clean total column here and I'm gonna go ahead I'm gonna actually fill in the rest if you guys were wondering how to add more on to this you want to keep these here you go ahead you can just right click over here insert one above and then you can start reading more of those ticker symbols and getting more and more into it so what I'm gonna do is fill in the rest of mine and then I'll get back to you and I'll there's a couple more things I want to show you how to visualize some of this how to get some nice charts and graphs in there so let's just speed ahead to go along and do something like that all right so now that everything's been updated what I'm gonna do now is show you guys some visualizations of the breakdown of my portfolio and this is stuff that I really like to look at stuff that I like sitting at the bottom so I can just scroll down at any point in time and realize exactly what's going on within my portfolio so I'm gonna go ahead is highlight these right here all the tickers and then I like looking at the allocation for the breakdown so go ahead and highlight those as well you can do that by holding ctrl and dragging down every single one that you have in each of them so next thing you want to do go ahead and click on insert and go to chart the way that I like to look at this breakdown here is what I'm gonna do is a pie chart here three-d pie chart and you can see this is just the visual representation of everything broken down so it's pretty easy to create some of this stuff there's a lot of different things that you can do I'm just gonna drag it down here a lot of great things you can do there and yeah I know my portfolio isn't doing the best rate and how down around 600 bucks but we're looking to bounce back from that relatively soon so so yeah there's really a lot of different things you can do for those visual representations this is just one of them here but the one that I like to look at the most just to understand my diversification where I'm at other things you might want to add to something like this is maybe adding another another section here for the industry that would be another manual thing so as you go down you could just do you know food and beverage for coca-cola and you can figure out all these and then you can have a similar breakdown and create visual representations of that just helps you understand some of your diversification a little bit more there's a lot of different ways that you can go about creating some of these trackers but I think this is a really basic one that you can set up fairly easily just by following this tutorial it's not too hard to do so if you're looking to track any of your investments all of your investments this is a valuable option that pretty much anybody out there should be able to do with the tools the reason why I went with excel is because you can use it whether you're on a Mac or you're on a PC it's very versatile and where I think I'm going to be switching computers back and forth I didn't necessarily want it to be locked up in Excel you can do a lot of the same things in Excel so don't let that hinder you if you don't want to be stuck to Google sheets but there's a lot of great things out there and then guys keep your eye out for some other things that I'm gonna be doing building out a nicer spreadsheet that is gonna be downloadable for you guys that you can just enter in some of your new positions and it's gonna auto populate all of this stuff for you there's a lot of really really great resources out there that you can do some of this stuff with and it's just a little bit above my pay grade to be talking about in a video like this where I'm just giving a pretty easy break down towards structuring this and building out you know however you want to show your portfolio so that's all I got for you guys today thanks for checking it out I hope it did help if you guys have any suggestions things that you added to this or things that you want to see in the future definitely let me know in the comment section down below other than that thank you so much you

Channel: DLITZTV

Views: 26,169

Keywords: how to create a stock spreadsheet, how to create an investment spreadsheet, how to create a portfolio spreadsheet, stock tracking spreadsheet, how to track your investments, dividend investing, robinhood investing, stock spreadsheet, dividend spreadsheet, stock tracker, google sheets stock tracker, how to track stocks using google

Id: ZVZFON5RBQU

Length: 16min 17sec (977 seconds)

Published: Fri Apr 10 2020