Create Your Own Stock Tracker: Beginner Google Sheets Tutorial

doesn't that just look beautiful what's up guys my name is Michael and today we're going to be doing a tutorial on how to build your own stock tracking spreadsheet personally I really enjoy using these spreadsheets because I find it helpful for me to keep track of all my investments in a single place and it provides some useful tools and analytics that can help me in my decision making processes today we're gonna start with a relatively simple spreadsheet and in the future we can build in more features to that by the end of this video you should be able to recreate a very similar spreadsheet for yourself and be able to use that for your stock investing I'm going to go at a slower pace so everyone can follow along and I'll also have timestamps in the description of the video linking to which portion of the project we're working on at a certain time alright so without further ado let's jump into the computer for this spreadsheet I'm gonna be doing it all in Google sheets which is Google's equivalent of Microsoft Excel so I'm using Google sheets because it's free with a Google account and it links up very nicely to Google Finance so in building a spreadsheet usually just start one piece at a time add the pieces that you know you're going to have so in this case let's start out with our stock tickers so these will be the actual tickers that are traded on the stock exchange and we'll use this information to link up to Google Finance so for instance I'm just going to input a few stock tickers here so we've got Microsoft Facebook Vanguard index fund Vanguard bond index fund and then Apple over here so relatively straightforward five stocks right now but in this next column we'll put our shares the number of shares we've have in each stock and the reason I'm putting these on the left hand side and we'll be adding more columns over here on the right hand side is these will be the two two of the main things that we'll be inputting into the spreadsheet the rest will be formulas that will actually calculate based on the inputs so I'm just going to make up some numbers here and one other thing that I'll add is I'll actually add a a cash position here you don't have to do this but I will for my sake if your I'll have a Robin Hood account some of your account will likely be in cash if it's not invested so you can keep track of that there okay so this is our start and it's not going to look pretty at first but that's okay we're going to worry about at the end usually when you're building things out it can take some time to get it really where you want and then once it's everything's all in the position that it should be for the spreadsheet at that point it's a lot easier to do the formatting and things like that rather than have to redo that as you're building the spreadsheet so next I'm going to add a third input which is average average cost so this is your basically your unit cost basis so your cost basis would be like the total amount that you put in for your number of shares and the unit cost basis or average cost would be over all the shares you've purchased all over that on the security what's the average cost you paid so once again I'm just gonna make up some numbers here okay so basically these three things right here are your inputs so depending on what your portfolio is you'll have different stocks you'll have different amounts of each stock and you'll have different prices paid for those stocks those are the main inputs at this point and from here we can begin building out the rest of the spreadsheet and so yeah there's no formulas in here you just input that actual value but from now on we're going to start going into some actual inputs so the first of which will be the price and this will be the live price of each of these stocks so the way that we're actually going to get this is we're going to create a formula that'll go to Google Finance and look up the stock price of each of these stocks so in excel or in Google sheets what you'll do to do a formula is you'll do equals and this indicates that the program that it's actually a formula and there's various tools and formulas you can use but won't really be using is Google Finance it'll create a drop-down list of formulas that will match what you're typing so in this case we're gonna do Google Finance when you click on that it provides some helpful tools into what are the inputs for this function so for Google Finance we have ticker and that's gonna be the main one we're gonna add so instead of you know you might be tempted to type Microsoft here in this case it need quotes quotes indicates it's a it's a text so you could do that and it gets you the stock price live which is fine but to make the spreadsheet actually dynamic and so you don't have to manually update this what we want to do is have this formula instead of typing in Microsoft we want it to reference this text here in this cell so to do that we're going to actually get rid of this and we're going to have it reference c7 so you can either type in c7 here or you can just click once the drop down goes away you can just click on the cell you want to reference so hit enter after that and then this gives you the live price so to get this formula to the other cells you could you know retype Google Finance and just do it all over again but that's rather inefficient and very tedious so there's a couple things you can do one is if you're have a formula that you're dragging down through to fill up a column there's a little button here in the corner you can just click that and drag it down and it will drop down the formula through all of those and so this one isn't actually this last cell here f12 isn't really accurate because because it's it's looking up a symbol CAS H which is just as some security I don't know which one but it's not exactly what we're looking for that was kind of small so we'll blow it up so it's a little bigger for you and there's another way you can actually do that which is instead of using this little dot and dragging it down what you can do is highlight the column that you want to fill and then you'll hit control D or I think on Mac it's like command D and it will auto go ahead and fill the rest of the formulas down for you so if you look over here it's got Google Finance C 11 which is what we want so now in this column we can do a calculation of our gain and loss so for this time actually going to do total gain and loss so of all the Microsoft shares how much are we up on all of them so to do this we'll just do another formula so it's going to be equals so give the gain or a loss we want to take our price and then subtract what we actually paid so if the price is over what we paid we've got a gain and if not we've got a loss so that'll be the total gain or loss per each share since the prices per share and the average cost is per share so want to multiply that by the number of shares that we own it's in this case $40 total gain for that so on another tip for highlighting is that if you hold shift and then use the arrow keys you can create a box that select cells just by moving the arrow keys and holding shift so by holding shift and having g7 selectively them and hitting down four times I can then create that column and then hit control D and it fills the rest of the column for me and then also double clicking these adjusts the column length so you can do that we'll do that at the end to make it look nice but for now we'll just focus on the actual insides so next we can do percent gain so this will express the gain as a percentage so we'll do this by equals our price divided by our average cost minus one and then we'll bring that down so this formula here is just the price you've used to calculate it percent and change I know it said lightly formula stuff for the end but I'm gonna change this to percent just so it's easy to see easier to read alright next we can add total equity so this is how much your current value is in each position the total equity you have in the stock so it's just going to be the number of shares you have so equals number of shares for each position times the current market price you've got that for Microsoft and then fill the rest down so I'm gonna actually just hard code one is the price for each each dollar search dollar is a $1 sounds redundant but that's just what it is for this and we'll fill that all in there Oh average cost should be average cost should be one okay now if we drag these down yeah that'll that'll look about right all right so we're game the spreadsheet coming together it doesn't have any visual tools yet but we'll be able to use this information going forward to create some of those graphics but there's a few more things like to add first so one thing that can be helpful is in the context of your entire portfolio it's nice to see just offhand what percent of your portfolio is in each position to give an idea of how diversified you are and how concentrated each of your positions actually is so for this will do a portfolio percent so for this we want to use total equity so for that that is how much your position is in that stock and we want to compare that to the total positions you have in all of your stocks so we want this as a proportion so it's going to be I seven our total equity and Microsoft divided by and now we want the one a total for everything so there's a couple ways we could do this we could actually want to delete that you create a total down here and the way we can do that is we do equals and then do s um for some and then parenthesis and then we'll just select a range so in this case we're just selecting this whole range and the parentheses hit enter and this is the total equity for our entire portfolio now so now if we want to do a percentage we can just do that I seven divided by our total portfolio value so in that case we get 16% so not too bad now if you drag this down and fill it out you'll get some errors and that's okay but there's a lesson here to be learned which is that when you drag it down these references are dragged down exactly the same as their relative position so when you drag it down this cell now references this one which is correct which is what we want but it's no longer referencing the total portfolio value so we want to lock that in so it doesn't move and the way you can actually do that is you can change what these are called relative references that are relative because their relative to the the cell that you're writing the formula in so if you drag this down they'll move but you can also create absolute references which are references to a specific cell that won't change and you can do that by adding a dollar sign directly in front of both the letter and the number of the cell so in this case we want to always reference that specific cell the total portfolio value and we don't want that to move but we do want the individual stock equity to move down so we do that we now have our percentages and you can just quickly check this by highlighting everything and then in the bottom corner where my face is covering down there you can see it's at a hundred percent so so that's a good check there so we've got four fully percent one other thing we can add is a total cost so for total costs that'll just be your unit cost times the number of units and then bring that down so for this total we can actually bring this across and just like when you dragged it down you can also drag formulas right and so these this now gives us the totals for each of these columns so there's a hundred percent there and then this is the total amount that you've actually paid into the portfolio all right so this is actually not too bad so far I'm going to clean it up a little bit I know why you usually say leave this till the end but this let us give us a little more space to work with in terms of where we're going to put our actual visuals and graphs so basically what I'm doing here is I'm just double clicking this little column and it'll auto adjust the size to fit the largest text that's in that column so this just keeps things a little more neat and tidy and reduces the whitespace within your spreadsheet and actually I'll take that back since we're almost done with what we're going to be doing for this video we can work on a little bit of the formatting to make it look a little nicer so in this case I like having the numbers formatted as numbers that's fine but for any dollar amount value I actually like using an accounting format so you can highlight those and then go to more formats and they've got a currency but they've also got an accounting and I like like the accounting format because it has negatives in parentheses rather than just having like a negative sign in front I think that's a little lease for me it's a little easier to see for these you won't really have negative prices so you don't need to worry about that but for the gains and the total loss you could so looking ahead we've got these are would all be formatted the same way so to save time you can select everything you want if you make a one selection here you can hold ctrl or the equivalent key and Mac and select other sections of the spreadsheet that you want formatted the same so that way I've got all three blocks selected I only have to format them once instead of doing it three times so for this we'll go two formats and then we'll do accounting and I actually want the negatives to appear red so what we're going to do is we're going to edit that format we're going to go to formats more formats custom number format and up here this gives you an example of what this coded format will look like and it's a little complicated here honestly more complicated than I initially expected but I think what we want is for I'm tryna this if it's red there we go basically putting the red in this section here says hey if it's a negative number apply all these play all these uh nevermind by all these formats it's really annoying by all this format and then having the red in front of me and so I'll color it red as well so we'll apply that now they're finally red that took way too long to do but we've got that sat for me I like adding a few little borders as well you can go up to here borders and have a border and then maybe a total outside border and then one here for the totals at the bottom so just do a bottom border here alright so that's starting to come together a little bit zoom out just a tad okay so this is looking not too bad so next up if this is going to be a spreadsheet that you're looking at for total sort of overview of your portfolio and you have like all your positions in here then this is a good place to kind of have a dashboard of what your current like portfolios looking like each day so for that I sometimes like having four big numbers up top that have sort of key things that I'm looking for in my portfolio one of those would be like the total value of my portfolio so what we'll do here is we'll just grab this total value right there and have that nice and big at the top since I know what that is I'm actually going to format it as just a number remove the decimal and then I'm just gonna make it nice and big that's a little too big for me but there we go so that's total portfolio value right now so total portfolio value so when you open your spreadsheet you'll know exactly what your portfolio is at that moment in time another thing I like to add is my percent so this would be what percent I'm up or down on the portfolio which would just be your total equity divided by your total cost and then again - one format that is a percent I like having only one decimal place and then what we'll do is we'll make that a little bigger as well that'll be our portfolio gain or loss and so so this will give you a good overview of your portfolio if you want to add more positions what you can do is basically click on a row and then right click and then insert a row above and you'll do that and you can add now you have room to add more positions in here so trying to think let's do Pepsi let's say you just bought H here's a Pepsi and current price is 116 so let's say you bought it for a hundred bucks so you got it on a good deal from there all this the rest once you've inputted these three things all the rest is formulas so basically just highlight this entire row and move it down one and then you can do ctrl D alternatively you can just hide this entire row and then click this little dot again pull it down it will auto-populate fill the rest notice also your portfolio total values updated portfolio total gain percents updated so the benefit about doing a spreadsheet like this is you know you only have to input three things for your portfolio if you add a new position or change the position and the entire thing updates and you know life is easy life is good okay now we're going to zoom out a little bit because what I want is for some of this white space here to be filled up with some nice or visualization tools and graphs that can help give me a quick snapshot visually at least some relatively visual learner and person so I like having those kinds of tools just easily see what's going on so what we'll do a couple of the graphs that I like to do is have just a pie chart of what my portfolio is in each percentage so we'll start with that one you can go to insert and then go to chart so right now it's got no data but we can place the chart where we want it to be in our spreadsheet so for this chart we want to do a pie chart and then we can select our data range so the first date range you can select by clicking this little grid select data range then I'll ask what data and you can just drag in this case this whole column here and then click OK and that'll be the label so that'll be the actual tickers and actually we're going to redo that we're going to add we're going to include c5 which is the it goes from ticker all the way down so that'll include this in there and that'll be actually the title for the title for that data so for the values we want the total equity so we're going to select all of that okay and poof we've got our graph right here so you want to use row 5 as headers notice that the label is updated to ticker and the values the total equity values so from this now it's a lot easier to see although you can see it in the portfolio percent here that you know looks like about quarter put portfolio is that a total stock market index almost quarter is Pepsi but it's a lot easier to see visually okay you know I've got this much in vti damage and Pepsi Wow my Apple position is much smaller than the others is that something that you intended if not now you can visually see that if so yeah you've checked that with the visual graph so this is one thing I really like at a glance plus also gives you a quick view you know Microsoft Facebook Apple you know if you add up all those percentages that's a fair amount within just like the tech space maybe that's what you want and that's you know your portfolio is heavily weighted towards tech but it could also give you sort of a heads up flag hey my portfolio is weighted a pretty significant amount towards tech know is that what you really want or is that a risk that your being now susceptible to being sort of over concentrated in a certain sector it's up for you to decide but you should at least be aware of it and this tool is a great way to sort of see that from a quick glance another thing I like is having my gains and losses in sort of a bar chart all in all together in one chart so for that will do another chart here so I've got stacked column chart here we'll want to grab some data so it's asking what data and to make this simple I'm just going to grab all this data here and we're just going to take out what we don't want or don't need so the x-axis should be the ticker so it'll be the ticker symbols so the x axis will be the ticker symbols and actually want to grab the cash so I'm going to redo the data range we're gonna grab everything in here okay so now I've got the cash so the x-axis we want to be the ticker that's fine what we really want is to have our total cost and then our gain and loss to be showing because that'll give us an idea of how much we actually put in and then how much of each position is a gain and how much of each is a loss so going back to the graph we want to get rid of that averaging that cost get rid of that price give of that we want total gain and total loss we don't want total equity doing too percent okay so now we've got total costs and total gain or loss I actually don't want the order to be that order so we're going to reorder it so we're going to take that we're going to copy that actually going to cut that and we're going to put it over here so that out we'll swap the range hit enter there we go so now I've got the total cost in blue and the total gain or loss in red so I like this graph as well because similar to the pie chart where it gives you an idea of how much is in each position here you've got an idea of not only how much is in each position but how much you put into each position and then how much of the gains are from each position so if you do this on your own portfolio you'll likely see that you know some of your positions might have a significant portion of the gains for your portfolio that's okay and some might have more of the losses from your portfolio or some might have losses spread out but this gives you a visual reference to see you know how each position is doing relative to the others one last thing I'll add up here is I'm just gonna go and do a function and I'm going to do equals today open parentheses close parentheses all right so now we've got quick dashboard to our portfolio how we're doing and a few visual tools at the bottom so hopefully this will give you guys a good start into how to build your portfolio so this is with Google sheets I definitely would encourage you to try this out if you haven't already I personally really enjoy using this and it's automatically updates anytime so I check and some even on my email every day it's a handy tool to just take a peek at every once in a while in the future if you guys like this kind of content I can do another tutorial building this spreadsheet out further and adding a few more additional features so just let me know in the comments if you're interested in that and if you've got any questions or technical difficulties if you're building this spreadsheet just let me know and I can help you out in the comments there in the future I plan to make a free spreadsheet course for you guys which won't clewd downloadable versions of all these spreadsheets so if that's something you're interested in you can sign up for my private email list and you'll be the first to know once that's available and you'll have the link for that when I get that done I hope you guys found that useful thanks for watching and I will see you in the next video [Music]

Channel: Michael Jay - Value Investing

Views: 265,010

Keywords: stock tracker, stock tracker google sheets, google sheets, google sheets tutorial, Create Your Own Stock Tracker, stock tracking google sheet, google sheets tutorial 2018, excel stock tracker, stock portfolio tracker, stock portfolio tracking spreadsheet, stock tracking in excel, stock tracker google, google sheets stock tracker, google sheet stock tracker, google sheet, stock tracking, google finance, michael jay

Id: VVAm-ldnWsw

Length: 26min 51sec (1611 seconds)

Published: Fri Aug 03 2018