How To Make A Stock Watchlist In Google Sheets

what is up guys we have yet another google sheets stock market tutorial this time we're gonna learn how to make a watch list in google sheets so here's what i have now this top part that you see here is your single stock analysis we will get to that in a second but first let's jump down to the master watch list so here's the master watch list it's nothing crazy um besides this setup right you can see here we have our tickers it's a buy or sell signal our price our change 52-week low 52-week high target buy and target sell for the target buy and sell i didn't do anything fancy i just did a certain percentage away from the 52 week low or high if you are someone who actually has a watch list you probably have those predetermined numbers in your head so you would plug those in manually but other than that everything is completely automatic here everything generates even up here with the single stock analysis uh but first let's uh let's see what it looks like if i add another stock so i go down here and let's see on this list it doesn't look like i have target let's add target tgt there we go target is a cell right now there's its price now here's how it did today it's 52 week low and high you know what if we want we could bring this down to 12. there we go that's just so this stays frozen up here so you can see here's target let's uh let's add walmart right below it also a cell a lot of these things are gonna be a sell because the market is overvalued right now i mean i was able to come up with kmi that is a buy right now and intel is a buy i don't even think ge is a buy right now nope can't think of another buy at the moment but that's okay you can see that the list works uh let's jump over to the top part which i think is very cool so as you add more stocks to the list like we just added target and walmart we then have this drop down of all of the stocks on our list and as you can see target is on there as well because i just oh i already had target on there so it's only generating once but there's walmart so i didn't have walmart on this before now and walmart popped up on the list watch if i get rid of it let's get rid of walmart shouldn't be on the list anymore not on the list anymore let's add how about o the realty group so if we had o let all of that load oh o is a buy and now o is on the list so if we select o everything's gonna update realty income corp that updates this chart updates this is the 12 month uh trend line uh it's a buy signal it's 52 week high low it's market cap 21 billion shows it's eps 1.21 and then it's pe 50 pe is a little high but it's a buy right now we jump in just to show you let's change how about to uh one of my favorite stocks etsy made yourselves had a major write up and you can see everything updates if i get rid of here i can go and delete say i don't want any of these anymore if i delete all of those our drop down should be shorter which it is so everything is dynamic in this we're going to break it down step by step how to make every last part starting right now so we got a clean slate to work with let me bring this down there's not too many complex codes here we're gonna be using a lot of google finance which is the equals google finance function and then you can add for example apple comma price and then it should generate apple's price see that we're going to be doing a lot of that we're not going to be using finviz or any of that stuff but there will be one special code or function and that is to get this beautiful trend line that we have this 12 month trend line but first we're going to make this part the master watch list then we'll jump to this part so this video is going to be pretty lengthy so do what you have to and get comfortable in your chair and let's see so what do we have we have master watch list on row 11 and then ticker sig i'm just going to copy and paste this so control copy on row 12 control v paste there we go there's all that let's pause the video write that down do we have to do i'm now going to highlight all of this and i am going to merge the cells and type in master watch list hit enter okay and we're just going to center that well actually we're going to center all of them whoops so here we go we're going to center all of them all right you can make uh the size and the colors we'll we'll get to that a little later let's let's jump right into some of the functions so ticker just throw in all you need is just one stock right now so we'll just throw in apple signal we're gonna hold off on signal we will get to that but we're going to jump to price price is the easy one you saw how i just did it with the google finance but before that we're going to hit equals you're going to do if is blank and you will see why in a second if you don't already know but if is blank then i'm going to select the cell that i have my stock which is apple so if it's blank a 13 comma close parenthesis comma quotation quotation saying if this cell is blank return nothing so now when we copy this down because all of these are blank nothing will show up here instead of that error and a function we're going to type in google finance so comma google finance now this is saying if it's not blank then what we're going to want is a13 comma open quotation price close close close one more time hit enter there we go so apple is now 121 dollars as i'm recording this we could add a currency as well and you can see if i take this and drag it down the code gets put into the rest of these but it's blank because there's nothing in these cells so if we start adding stuff so let's add how about i'll do target again we'll do visa about att they just generate for you just like that so now let's move on to change and even before that however far down you want to go if you want to go down you know 100 200 300 you go as far as you want i really don't think you're going to be monitoring 300 stocks at once but what do i know maybe you do typical watch list for me has maybe 10 to 20 stocks maybe 30 but uh that's not here there let's jump over to change so change is going to be another pretty simple code we're going to do the exact same thing the if is blank and guys everything is below so you could copy and paste but i always like to teach people this as well this is useful knowing what you're doing because then you could expand upon it and meanwhile you're at it make sure to hit that like button and subscribe if you haven't already i have a ton of videos like this you guys seem to be really liking all of my google sheets videos and i got plenty more coming out that i believe is even cooler than this one so you're going to want to be updated when that comes out so we're going to do change p c t so change percent you can already see it wants to put in 2.09 because apple dropped to an uh two percent today we're going to close those uh quotations close the parentheses we're not going to hit enter yet we're then going to divide that by 100 so then it turns it into a decimal and you'll see why in a second we'll hit enter it wants to autofill we're going to hit yes and what we're going to do for this column right here we're going to change it to percent now if you didn't do the divided by 100 this wouldn't be 0.75 percent this would be 75 percent and target did not jump 75 in a day apple did not go down 200 in a day that's impossible so that's why you want that divided by 100 very important but let's move on to 52 week low so all these are pretty much going to have if is blank so we already know that and it's always going to be the same cell a13 always gonna have the comma quotation quotation comma and then we jump over to the google finance so google finance very simple again if we want to pull um apple so a13 if we want to pull the 52-week low all it is is low 52 very simple and if we hit enter there you go autofill great okay 52 week high i'm pretty much just going to copy and paste this code because it is essentially the same thing so let's paste that except instead of uh low 50 uh low 52 it will be high 52. autofill great so now we're about to get into some of the fun stuff the target buy and target sell now again if you are doing this what i would suggest hold on i just want to make this dollar sign i would suggest you would do your own research you would look at apple you look at some charts you need to figure okay looks like there's a support or resistance at price a b or c and then that would really determine where your target price is or your target cell is but what i did was i just threw in a function that just kind of took i think uh it was for the target buy it's 50 off of i pretty much i take these two numbers find the differences and then if it's 50 away from the low it's it's fabricated in a sense there's really no strategy but i'm just going to show you the codes i'm actually going to copy and paste because again i really want to encourage you to uh use this as a buy there's really no strategy behind it i just did this for the video so we could have something so it's if it's blank a13 right so we're pulling that and then you can see here it's going to be f13 minus e13 so we're taking the 52-week high subtracting it from the 52-week low then multiplying by 50 so what's 50 of that and then adding in the 52-week low so essentially it's 50 between the 52-week low and 52-week high as long as it's 50 or less it's going to be a buy so that's my target buy again there's really that's that's really no strategy i'd go off maybe the rsi or something like that but again look at my other videos you could add that into this list you don't have to follow it verbatim what i'm doing here you know i think you all know this isn't financial advice here i'm just showing you how to create this really cool chart um and for the cell it's going to be almost the same thing so we're going to be subtracting the two again except it's going to be 85 so when it's 85 of the 52-week high or higher it's going to be a sell so we'll hit enter there there's our target buy and our target cell now we can get into the fun stuff which is the signal so this signal just like everything else if is blank so there's the if is blank a13 comma quotation quotation comma and then what we're going to do is we're going to do another if statement so if comma what we want here is we're going to be using our target by in cells here so what we want is if price so for me that's c13 is greater than h13 which is your target cell then we want to sell obviously close that close the uh do a comma then we're going to do a third if so if open the parentheses c13 again i can't access it because this is in the way here i could access it that way so c13 so if the current price is less than g13 my target buy then obviously we want that to be at a buy if not then we want it to be at a hold so what this is saying is and it's pretty pretty self-explanatory so if the cell in a is blank then you want everything to be blank if it's not blank the price will generate obviously and it's saying if the price is greater than the target cell then we want this cell to return c-e-l-l want to return cell s-e-l-l don't get those two confused and then the third if if the current price is less than the buy price then return buy in the cell uh if not then return hold so go through this order it'll say okay let's see is it higher than sell no okay is it lower than buy no okay we're gonna return hold that's how that works so we hit enter and that's what apple is right now 121 is in between 95 and 125 and we just drop that down like that take this drop that down take this and drop that down that's pretty much it for the master watch list at this point what i would recommend is take this go all the way down to however many say i only ever want to you know monitor how much is that around 37 stocks then there you go drop it down to that right so i'm just going to drop them all down to sell 50. okay i just dropped them all down to sell 50. you could take my word for that but now as you can see i can start plugging in a bunch of stocks so i don't know about facebook johnson and johnson mmm um verizon you know it's all there and it will all generate for me uh now we could start to add in some of the colors so with the buy hold and sell that's really the most important part uh at least in my opinion so what i'll do is i'll highlight all 50 right click we're going to go to conditional formatting and what we want is if text is exactly hold so if text returns hold then we want it to be yellow so you can see both of those just changed yellow so we could hit done add another rule if text is exactly cell then we want it red so there you go hit done add one more rule if text is exactly bi then we want green whoops not green text let's add that back green hit done all right we have our conditional formatting for that and then the last one i think i have the change conditionally formatted so go all the way down to 50 again i'm going to right click go to conditional formatting and instead i believe i have the well it's text not text is exactly it is greater than greater than or equal to zero and we don't want anything like that we want the text to be a dark green and make it bold so it's easier to see hit done we then going to add one more um i'm gonna have the text a dark red hit bold and that is if it is less than less than zero hit done x out of that all right we're looking good here if we jump back here it's pretty much everything we have for the master list the only thing left really to do for this besides the coloring is maybe highlight all of that go down to that cell 50 and slap on the filter and then maybe space these out so you could see everything whoops i went one too many we don't need a filter for you let's get rid of uh gets rid of all of it all right go down to 50 let's try that again slap on a filter there we go now we could filter through everything so what if we want to see what was the biggest drop and it puts it in order or we want all of our tickers in alphabetical order whatever it might be so that's really the last thing we need to do there again for the color you can see i just pretty much made this text a lot bigger maybe 24 bold i made the text white and i made that cell i believe that bright orange and then you can highlight all of this make it a dark blue maybe it was that blue text white all right and then i highlighted all of this go all the way down and you can make that nice light blue something like that ah maybe even lighter like that there we go so that's pretty much what we have already for this there's really nothing uh nothing else to that and then if you want to drag that down that's just so you can scroll through this and all this stays up here but now we're going to get to the last part which is the single stock analysis this part right here this i think is the really cool part so here let's look at ba nice crash because of 2020 slowly running back up everything pops up there's no pe because it is in the garbage right now what about uh southwest airlines also no pe let's go to intuit into it's doing great so this is what we're going to create and really the cool code is this one right here to create this trend line uh but let's let's jump right in so we want to make it look the same so we start on cell three we're going to build this out first so we got i'm just gonna copy and paste all of this so from three so from a3 control v there we go let's also just kind of do all this make it centered and guys if you made it this far we're like 20 minutes into the video if you haven't smashed that like button yet that's just disrespectful come on but anyways let's jump in so stock overview what do we even have here i can't remember okay that's going to be the stock so that doesn't have to change but then we have our stock price our cell signal and these are all going to be very similar from what we have here so we'll do some copy and paste that's going to be price that's going to be the same as our sell signal sell 52 week high 52 week low and then to add market cap eps and p e ratio it's all google finance again but as you can see with the market cap right here we have the dollar sign and the b for billion there's a way we could do that and you might not want this for a reason being that you may be dealing with smaller stocks that aren't over a billion dollars i typically never look at those stocks so it was okay for me to have this but you'll you'll see what i mean uh but first let's just start adding in so for the stock price is gonna be the equals google finance uh and then we're gonna be pulling from the cell right above that's where our stock's gonna go we're gonna hit comma and we're gonna do price close that off see how it says n a well that's because nothing's up here so let's actually handle that first what we want i'm going to right click go to data validation and we want to go list from range and what range do we want well we want all the way from a13 all the way down to a50 because those are all the places where we could have stocks we hit ok and hit save now you can see there's a drop down of all the stocks we have in here so let's put in apple and there you go we can make that a dollar sign and again look we have one two three four five six seven eight if we start adding in other stuff how about bank of america uh tesla uh led southwest out airlines um is that best buy or bed bath and beyond i always forget the two but you can see now all of those get added in as well okay so now our signal is going to be a little different this we're pulling from over here and everything uh but this we need to pull from data up here so we're going to do a little cheat and we're actually just going to see what we have right here and copy and paste this and i will explain it once it's in so let's paste that so what we're doing is it's an if statement for here and if b4 here let me do this there we go if b4 so this one is greater than b6 minus b7 so these two multiplied by the 0.85 it's pretty much we're putting this formula within this and then it'll be a cell and then we put the buy formula into this although we're pulling from stuff over here we don't want to pull from over here uh then it's a buy and if it's neither then it's a hold so you can pause this video and copy what i have right here if you're in the same cells as me or you just copy and paste it from the bottom but this is all correct um what you're going to want to do is it really depends with this signal depends on how you are working it if you're doing the same thing that i'm doing then great because this target buy and target cell will line up but if you are actually manually putting in your own buy and sell then this signal it may not just work out you may not uh be able to use it um so you really don't need the signal up here you just look at the signal down here um but let's hit enter so there's that we could add some conditional formatting again okay i just put in the buy cell hold with the three colors just to kind of skip past that part so you don't have to watch me redo that uh we will center this as well there we go moving on to the 52-week high so we already have that again it's going to be essentially this except instead of the a17 we're gonna want a three so google finance and we don't have to put if is blank because this technically will never be blank um so this is the high so high 52 if you want to keep everything uniformly put in the blank go for it really doesn't matter now the 52 week low will be the equals google finance pulling from that again comma low 52 hit enter so we got both of those in now our market cap it's a pretty simple formula it's google finance taking that you're gonna type in market cap and then you could see i mean apple is over two trillion dollars so that might be kind of hard to figure out exactly what number it is if you go and add a dollar look at that you gotta actually stretch out the sell because apple's such a large market cap um there we go you know that's pretty ridiculous so what a lot of people will do is they'll take this and they'll divide it by you just divide it down to let's see there we go how many zeros was that is that divided by a million or one two three four five six seven eight so i divided by a billion so it makes the market cap look much more reasonable it's 2 000 you know apple is 2 000 billion right so if we were to change this to something that's uh under a trillion about facebook there facebook 791 billion you can even get rid of the decimals if you want like that but now you might be wondering hold on how do i add the b so the b was a little bit of a cheat code here not really a cheat code i'm just calling it that but to do that what we have to do is you can't have the dollar sign unfortunately you can't make it dollar it has to be automatic because you're essentially taking this number turning it into a text and the way that we're doing that is at the end you're gonna add end b so it's saying take what we have and then add a b to it now this still isn't what i have because there's a whole bunch of decimals behind it so to fix that we got to add something to the front and essentially what we're adding is going to be the money hit end so we're saying add money sign and google finance market cap divided by a billion add the b hit enter we're almost there we got the money signed we got the b but we want to get rid of all those decimals because that's just too much it looks ugly so what do we do well it's this function called true c open parentheses and at that point we want to add our closed parenthesis right there hit enter and then there you go so now this isn't a number anymore it's actually a text that we've essentially turned it into but if we go and say go to johnson johnson see that it changes now why wouldn't you want this all right so if we take something like hol which is a uh it's a penny stock well not necessarily well i guess it kind of is penny stock it's under um it's a small cap stock it's under a billion so if we do just to show you really quick quick google finance hol comma market cap and you'll see what i'm doing in a second load there you go it's 370 million dollars but now if we jump here and go to hol see it's going to come up it's going to return zero zero billion okay so it's not zero billion so if you're dealing with stocks that are under a billion dollars then you may not want to do this or maybe you want to have million instead and you're not going to have as many zeros instead you're gonna get rid of three zeros so there's 370 million but then when you go back to uh when you go back to apple apple's gonna be what is that two million million so it doesn't look as clean but uh that is a little trick depending on what stocks you're dealing with if you're only dealing with penny stocks then that's what you could do um use millions instead of billions if you're only dealing with large cap stocks then there you go you can add the b in just so it looks cleaner that way let's get rid of that almost done here eps really easy one google finance pulling apple and we're gonna do eps boom equals google finance for pe uh which you guys probably already know what it's gonna be pe and good now that we have all of that here comes the fun part which is this which you guys oh we actually missed one right here this actually updates so this is just google finance name see that so we're actually just gonna copy that gonna copy that and that is let's see one two three what we actually have to do there we're going to create this whole top section that i have before we get to the other thing that and that and then we're going to i'm just going to plug that in so i had a google finance b3 name i'm going to bring that that way this one is going to be what i have there our 12 month trend line so again i'm just going to copy and paste this ah oh do that because we want that color so make that color the same let's add the text bold what did i do 12 maybe it looks like 12. it's 11. okay 11. that's good so now that will update so if i go to facebook it changes to facebook and common stock so it's just pulling the name you can highlight all of this merge this as well name it whatever you want think stocks watch list for me hit enter make that nice and big white and that orange that i use for everything and the very last thing okay up here we have stock overview we already have that oh single stock analysis i'm just gonna merge that single stock analysis maybe i spelt that right maybe i didn't i'm not the best with spelling make that 11 as well i'm gonna make you that blue that i like okay i just i i had to do that everything needs to look uniform before we get to this so here is the code so we are going to do equals and it's going to be s p a r k l i n e sparkline and then it's going to be google finance so you just plug that in and at this point we're going to be taking obviously b3 like we have been doing comma we want price comma today so we're taking the price of today and then subtracting negative 3 6 4 comma and we want to plug in today again so let's take in the price of today and the last 364 days before the day of today hit enter it's loading there is our chart how cool is that so that's facebook we go to johnson and johnson that's what johnson johnson looks like let's go to how about southwest airlines it's taking a while there we go that is it how cool is that that is pretty much every last part for you guys that stuck around all the way to the end you rock again smash that like button and subscribe if you haven't already if you have any questions drop them below i will try to answer but that is it i hope you guys like this and learned a lot check out my channel for other videos i do a ton of this stuff uh so make sure to look at all of that but yeah other than that i will see you guys in the next one

Channel: Think Stocks

Views: 40,705

Keywords: think stocks, keith frislid, how to make a watchlist in google sheets, stock watch list in google sheets, how to make a stock watchlist in google sheets, stock watchlist in google sheets, google sheets stock watchlist, how to make a stock watchlist, stock watchlist google sheets, google sheets stock watchlist tutorial, google sheets watchlist tutorial, create a stock watchlist in google sheets, create a watchlist in google sheets, google sheets stock tracker

Id: nn-Jv32o-XU

Length: 33min 51sec (2031 seconds)

Published: Fri Dec 18 2020