How To Make A Day Trading Tracker In Google Sheets

what is going on guys in this video i'm gonna walk you through how to make a day treating journal so i actually had someone comment kind of requesting this so here it is if you're watching that uh you're welcome make sure to smash that like button for all you guys that haven't subscribed yet subscribe please it really helps anyways let's dive right in so this is my day trading journal so essentially everything that you see here is great way to calculate any kind of day trades i've day traded once upon a time in my life i really don't day trade that much anymore but i know the power of day trading and i know staying organized and being able to track your progress is super important so pretty much let me walk you through what's going on here and then i'm going to break down exactly how i make this step by step and reveal all of the lovely codes or functions might i say so this all works so everything's dynamic the date when i put in a ticker the date will pop up uh then you put in your entry and stop and then a target will be generated your risk will be generated the amount of shares that you need depending on your risk will generate it'll say if it's either long or short depending on your entry and stop and then once you put in your fill price so you can see right here if i was trading apple at 115 and twelve cents but my fill price uh had a little bit of slippage 115 and 17 cents then i'll calculate what my cost was so that's exactly what it would cost uh then it would be my exit when i plug in my exit it'll then generate my profit or loss the result either popping an l or w and then the status i could put in my status exactly what happened here just so i could keep track of everything then on top of that all of this will be updated here so you see my total profit total trades my win percentage and my risk factor everything's dynamic and let me show you if i go here let's pretend i'm trading another stock we'll say it's target so tgt i hit tab there you go you got our date is now in and then the result well right now the positions open let's say i don't know what target's trading at right now let's just say 200 to make matters simple so we'll say i want to enter at 200 and my goal is to exit at about 200 and my stop might i say so if i'm going long we'll say my exit is a dollar right if it drops uh down to 199 i want out so you can see here's my target which i was able to choose what i want my target to be so i want it to be double my risk if i'm risking 250 dollars it shows how many shares i'm going to need so i'm going to need exactly 250 shares and my position is long now let's switch the two let's say i wanted to go short so instead my stop would be about 201 so if my entry is 200 i want the stock to go down so my stop loss is going to be 201 i hit enter you can see that updates now my target is 198 dollars and i am shorting the stock right and if we you know mess around with this let's say instead it's um about only 50 cents now you see the shares updates accordingly to however i want it now let's say i get filled at 251 okay a little bit of slippage it will show what my costs will be hundred thousand dollars um and then exit so let's say i won and let's say it hit my target which was 99.50 i'm sorry 199.50 so if we do 199.50 there you go it shows what my profit would be 500 and my result would be a win and then i could go here and say my target was hit now let's say that didn't happen and instead the stock went up and then i got stopped out at a hundred uh 201 dollars you can see there's my loss of 245 dollars and now it is an l for loss and then i could change that stopped out now it's even cooler as you saw me going through all this this was updated up here as well now my total trades are 29 my win percentage went down a little bit and my profit went down let's switch this back and watch up here let's switch this back to let's say 199. you could see this went up to three 3442 and my win percentage went up to 48 so everything in this is dynamic enough showing off let's jump right in clean slate we are going to create this uh let's start with all the pretty graphics that i have here so it looks like i go four down and then all the way over to j so four down all the way over to j we're going to merge those cells so merge all type in whatever you want i'm just going to copy and paste this copy and paste okay we're going to keep my color scheme going here we're not going to bother with this yet okay we'll leave it there but we're not going to bother with it next we want to plug in all of this so i'm just going to copy all of this just to save time in the video and paste all of that and i'm just going to kind of uh make these a little bit smaller so everything fits on the screen okay great now we have that uh so if you want you can center all of these make that the same color to keep everything all in sync or whatever you want to call it what color do i have there we go okay and we will make that white bold good we are looking good right here let me just make this the same color because that will drive me crazy all video okay great now we have that we have this set up we could start laying out the framework for what we need so if we jump back to what i already have here you can see right here we have this is yellow all of these are yellow over here and these are yellow over here the reason why i did that is because these are stuff that you're not really plugging information into it's only the blue fields that you're plugging information into so your ticker entry your stop your fill and exit everything else gets generated so everything else will need a function so let's just kind of do that i don't know how many day trades you make in a month but i think if you're doing over 20 or 30 uh well props to you and i hope you're winning so let's just go down to whatever you think is reasonable i'd say uh what is that 65 trades if i'm going down down to cell 70 and we're starting at cell six what's that like 65 trades uh that is a lot if you're doing that you could go down as much as you want if you're doing a hundred trades a month by all means go all the way down to two or three hundred so you never run out of space but i'm just going to fill down to 70. so these are blue because we're going to be uh changing those around and then right here all of this is going to be yellow so i'm going to do that the fill and exit are blue and whoops that is the wrong blue and profit result and status is yellow okay so now that we have all of that something that you might want to do which i really like i like that this is at the top it's frozen so i could go all the way down and this stays here the way i do that because you see right here it doesn't stay as i go down you just take this up here and you drag it down to five there you go now it stays up top it's frozen so if you want to do that by all means okay so we're gonna move on to the first function i am actually gonna skip date because it took me a while to make so i'm gonna have to build some suspense for it we will get to it a little later we're gonna start with target okay so we're gonna do an if function all right and it is going to be if is blank we're going to have a lot of is blank functions and the reason being is because you don't want to see your entire portfolio or your entire journal littered with a whole bunch of n a error codes i don't like to see that i like everything to be nice and blank when there's no information so that's why we're doing that so what is going to be blank well if our stop is blank then what we want we're going to do comma quotation quotation so what this is saying is if this cell is blank then we want this cell to be blank that's what your quotation quotation is okay then we want to do comma again and here's where the math comes into play if it's not blank we want it to be c6 subtracted by d6 that's your entry minus your stop close parentheses multiplied by now this is your target this is your risk to reward ratio for me it is a two to one ratio so that's why i have times two if your risk to reward is three to one then by all means put in a three if it's a 1.5 then put in a 1.5 if it's a one you don't know how to day trade and you really shouldn't be watching this video i'm gonna keep it at a two because that's what i always did when i day traded and lastly we want to then add back to our entry and then we want to close the parentheses and hit enter there you go it's blank um and again the is blank just so you guys understand exactly what this is doing if i add a zero here and hit enter it shows a zero because i'm saying if d6 is blank which is this cell then produces zero so there's the zero if we got rid of that if we got rid of the is blank function then i'll just say n a i don't like seeing that so there we go this is blank now so if we let's say we add in apple right we're trading apple it's at say 200 and our stop is 199 our target should be 202 because it well here you'll see 199 enter 202 because what it is doing here it is taking c6 minus d6 so that's 1 times 2 which is 2 and then adding it back to c6 which is 202 now the really cool part about this is when you are um shorting the stock it will be correct it'll update it the right way so if we do 201 it updates it so it's actually correct so it will go in reverse down to negative two points right so from 200 down to 198. so now that we have our target done we get to move on to our risk so risk is going to be very similar and actually we gotta make the risk so if you see here see i have risk up here risk 250 and see how 250 is right here so if i change this to 500 all this gets updated now the risk is 500 my shares were doubled my cost was doubled my profit or loss was doubled so if i change it back to 250 everything goes down by half so we need to have that so that's going to be where do we have it here kj m k l m k l n i'm going to merge these two cells right here i'm going to type in risk and then right here we'll keep it at 250. so whatever your risk is whether it's 50 100 250 you know make it whatever you want and i'm just going to format it so it looks nice and pretty there we go there's my risk i want it exactly how i had it on my other one put a line there and a line right there we want those lines to be white i like that and white oops okay there we go so there's our risk right we're going to have the other ones in here if you want to add them now we'll get to the functions but we have risk what else do we have when percentage total trades total profit all right there we go so that's all set we're going to get to these three in a little bit but let's jump back to risk so pretty much what we want to do is it's another equals if function if is blank okay and what we want is d6 again so if our stop is blank we don't want anything showing up comma open quotation close quotation another comma and then what we're going to do is money sign m money sign 4 so where's m4 it's right here and the reason why we're doing money sign is so it stays stagnant and it doesn't change we're going to close those parentheses and do that and what do i mean by stay stagnant and doesn't change well if i take this and if i didn't have those money signs and i dropped this down by one this cell would no longer be m4 this cell would actually pop up for result watch if you don't believe me if i get rid of those money signs up here get rid of that get rid of that hit enter and then if we drag this down let's add something in there see it says result so now it's pulling from that cell and we do not want that m5 so what we need to do is we need to put those money signs in place and then it will keep it it'll freeze that so all of these will be m4 all right so now that we have that and what we could do for target we could drag our target all the way down okay so now the functions in every single cell and then we do the same for risk we drag those all the way down and there we go all right we're going to move along to shares now so shares is going to be another if function okay we're going to open the parentheses it's not an if uh is blank it's just an if open parenthesis d6 pulling from d6 a lot we're going to make that d6 equal zero then we want close quotation so saying if this is it's essentially an if function but it's just easier to write it this way uh and that's because it's going to be multiple ifs so then we're going to have to do another if and then what we need to do is we're going to do c6 so if c6 is greater than d6 comma now here's the math we want the math that we're going to want is f6 divided by open quotations c6 minus d6 close quotations comma and now it's saying if it's not greater right so now what we're going to want is f6 out of 5 f6 divided by now we're just going to switch the two so instead we're going to want to do open quotation open parentheses d6 minus c6 close close and close and hit enter so you're probably wondering what just happened there what are we doing let's not make that a um dollar sign instead we just want numbers not even well yeah we'll do numbers i want to get rid of the decimals though unless you're trading on like robin hood or something and you can have fractional shares then maybe you do want that but i do not so pretty much what this is saying right here and you just gotta have to take it uh take my word for it with the math but it's just doing the math for you if you want your entry at 200 and your stop at 201 and you're risking 250 you're gonna need 250 shares again i really don't want to get into the math of everything but you're just going to take my word for it so if we were to raise this stop to say 203 this then adjust accordingly and then if it was actually a long position so say 197 you see it stays at 83 uh even though we did that and that's just because of some simple algebra again take my word for it if you want to uh review it by all means but now we're going to move on to type so now this is cool this will say if it's long or short so it's going to be another if function and we're going to do d6 equals zero so it's going to be similar to the shares function right comma quotation quotation comma another if function so we got an if inside an if and then what we want is c6 if c6 is greater than whoops i missed a comma here comma always make sure you uh don't miss anything so where were we if open parenthesis c6 is greater than d6 then we want it to say long comma now this is saying if it's not if it's false then we want it to say short close those quotations close the two parentheses hit enter there you go it says long because right now we are in a long position what if we change it to say 202 now it says short and our shares update everything updates so far we are moving along uh let's just drop these down so our shares could drop all the way down and i mean if you are still watching this at this point we're about in a halfway point and if you haven't liked this video yet what are you doing come on please like my video really appreciate it it really helps out my algorithm let's move on to cost it is going to be another if is blank whoops we do not want that so if is blank and if you haven't already figured out all of these uh functions are in my description so you could take a look at that and that could help you out a lot so it could save you some time but if you want to learn that's the point of this video so if j6 is blank so if the fill is blank then we want to keep it blank right but then if it's not we're going to want to do j6 times g6 all right so we're doing the fill times the shares to get us what our cost is says n a all right here's where i messed up i actually missed a quotation i mean a parenthesis right here so if j6 close parenthesis then we have the rest of the formula there we go our cost is that so now our fill let's say our fill was exactly 200 because that was our planned entry shows what my cost is my cost is going to be two thousand uh twenty-five thousand dollars so we can drag our costs down there we go so our costs just checking make sure all of these are down here good okay so now exit well we don't even need to worry about exit because it's not a function we have to put in our next one is going to be profit so we're coming down to the final amount that we have two long codes our profit and result are going to be our last two hard codes then the rest is cakewalk so just stick with me guys we're going to do another if is blank so if is blank k6 so if k6 is blank our exit obviously we can't have a profit yet because we haven't exited the position yet so if it is blank comma we want it blank and then another if statement inside an if if h6 is equals so now we're taking our long or short so if it equals long so equals long then here's the formula we're going to need so open open parentheses we want to be k6 minus j6 close parentheses times g6 close parenthesis comma now this is saying if it's not long so if it's short then we want j6 minus k6 which those flipped again right so if it's short we just need to flip that uh multiplied whoops close those parentheses multiplied by g6 close and close hit enter if i didn't screw up so let's say our exit our target was 196 let's say we hit that 196. there you go our profit would be 500 which is in line because that is times two ratio our risk is 250 so our profit would be 500 so that means if we were stopped out at 202 then our loss should be negative 250. so now that we know that's all right we can move on to result this is going to be the last long piece of code here we go another if is blank b6 so if b6 is blank comma we want it to be blank okay now we're going to do our second if so if another is blank then we want it to say open okay close that comma right and then we have a third if so if open parentheses l6 is greater than zero comma then we want w for win if not then we want it an l for loss three close parentheses we hit enter and that is it so you can see our profit is negative so our result is loss if we then change this to that 196 because we are shorting it and we made 500 boom it's a win if we exit that it says open because that trade has not closed yet that was probably the hardest code of it all let's make it nice and easy now with our total profit so our total profit is going to be sum equals sum sorry about that equals sum l l close hit enter there you go it is a zero we're gonna make that white now we're gonna jump around here we're gonna get back to our total trades our win percentage and our status but we're going to jump over to date now so we're going to hit equals our date is going to be an ifs statement we're going to open parentheses so if b6 equals blank then we want date to be blank if it's not then we want a6 because that's the cell we're in to equal blank comma now open parenthesis close parenthesis comma true comma a6 and now when you hit enter well first you can get a number it may not even work for you and i'm going to explain why it's not working for you it may say ref and that's because look if we're pulling from a6 and we want a6 to equal something you can't add it together now this is coding language this is iterations we don't want that it doesn't make sense so pretty much it's saying to add a6 and then if a6 then equals something well then now it needs to update it so it's a never ending loop so how do we fix this what do we do well you need to go to file okay go all the way down to spreadsheet settings go to calculations go to iterations calculation turn it on maximum number of iterations you probably have 50 change it to one hit save settings boom there you go now you're probably thinking hold on the date is not zero there's still something wrong well that's because it's set to automatic if we set it to date you may still think all right well it's not 1899 and that is very correct it is not but let's clear that let's re-edit there you go there's the proper date i guess 1899 is just where it or whatever it was is where it starts for whatever crazy reason but now everything should be good if you drop this all the way down there you go let's say you make two trades in a day you traded apple in the morning and then square in the afternoon boom your date gets updated you can even go as far as if you want to know exactly when you entered you could do that as well uh you could just mess around with right here so instead of date maybe you want date and time so but i mean i think just the date is really all you need just make that capitalized okay so now we're almost done here the iteration is also needed for this over here and what i mean is for our i believe it's the total trades no it's not it's the win percentage so the win percentage we're actually going to be pulling from m so we can't have um you know that cell updating with it just wouldn't work the same way so the iterations actually solves two problems for us but let's jump to total trades actually yeah it's total trades but let's do count so if we type in count we're gonna do equals equals count l l hit enter saying zero because well there's no profit yet so there's no trades that have been closed yet um let's just make this bold white and center it okay so now if we you know our exit let's say was that 196 we're talking about there we go see total trades one profit win good square so whatever squares out right now let's pretend it's at 150 our stop is 149 so we're going along on it we want to get to 152. um and we get in say 150.08 a little bit of slippage there's our cost our exit let's say we lose on this one um and let's say we're out at i don't know zero 149.03 okay all right we didn't bring this down so we're gonna bring all of this down there we go so there's our profit our total trades went up and let's drag this all the way down there we go whoops that is not a win oh wait now you can see here clearly i screwed up because if i was exiting here's where i screwed up right here i spelt long wrong so that's why it is very important to get everything right now that long's right there you could go it's a negative it's in for a loss so just make sure you gotta make sure everything is perfect again you can just copy and paste in the description and everything will be right but i had that as an m instead an n right so you got to be very careful c l o m g did not mean for that so let me fix that by going like that and dragging this all the way down okay now that that's updated and that's correct that's good we have that going all the way down we are almost done here just a couple more things so we're going to move on to status status is very simple right click and you go to data validation sorry not dial validation uh no yeah i was right data validation and do list of items and then you type in whatever you want so if you want to do what i had which was target hit comma stopped out what else i had comma timed out comma manual exit hit save then there you go it turns into a drop down with those four so we could take that and drag it all the way down almost there okay there we go now all of them are there last thing is our win percentage another really easy one we're gonna do equals countif comma m semicolon m uh quotation not quotation comma open quotation w close quotation close parentheses hit enter bold white center and then right here what we actually have to do the very last part of this is we need to do divided by m2 so we need to divide that by right up here m2 our total trades so you can see right there our win is 0.5 or if we want to make that a parenthesis 50 percent and then for our profit we can make that a dollar sign cost we can make a dollar sign our fill and exit dollar signs our target and risk more dollar signs entry and stop highlight all of this make everything centered because that's what i like and then right here let's say uh let's see so that was target hit that was we'll say it stopped out we can now widen that a little bit so that fits so let's say then our next trade was visa and whatever visa is um i know all these prices are wrong but we'll just save these as a hundred dollars and we'll go short on that so how about 150 we want out so there we go we have all of that and we'll say we got filled at 99.99 and our exit will say it was another win so we'll say we got out at how about 99.30 so there you go another win so our total trades updated to three our wins updated to 70 uh 67 we'll say that that was a manual exit but this is it right here i mean you could add some other stuff like if you highlight all of this and go up here the filter you can filter through them if you want so if we go back to the big one that i had maybe i want to filter through all of my profit and loss so here's all my loss all my profit i then want to go back to my date so there we go oh you see even right here a couple of the dates were wrong but that's because i was making this and i was just uh haphazardly putting it together you shouldn't have those problems everything should be in line uh the very last things that i have here you could see i have some color coordination right here that's conditional formatting a very simple thing so if you take all of this highlight it all right click go to conditional formatting and then if we do greater than zero then we want keep that as none keep this a nice green like that we'll make it bold so we can see it ah say we want the green a little bit more neon about like that there we go hit done add another rule and then go is less than zero then we want it red so i have oh we don't want that red maybe you want that red i don't i want just the text red make that bold and hit done x out of that and there we go i think that is everything let me just double check here if i miss anything it really does not look like i missed a single thing oh you know right here we can make that that percent make that money yeah that is everything so here we go let's just so you can see let's say then i traded target um i don't know we'll say it's 150 dollars my stop was 148. so there's my target 154. um we'll say i got in for 150 and i got out at 151 and we could then put that in as how about we were timed out but you could see everything updates here you can then make this maybe you want to have it each month so you can make this right click and change the name you could rename it so maybe this is the month of january and you could have february march april may june all the way to december so you could track every single month how you're doing compare your stats but that is it guys i really hope you like this video i hope you're able to now use this for day trading and track your progress and stay more organized smash that like button if you haven't already thank you for watching all the way to the end you guys are my true fans love you all like and subscribe one last time and as always guys i will see you in the next one

Channel: Think Stocks

Views: 27,970

Keywords: think stocks, keith frislid, how to make a day trading journal in google sheets, day trading journal excel, day trading journal google sheets, day trading journal spreadsheet, day trading journal template, day trading spreadsheet, google sheets day trading journal, day trading tracker, day trading tracking spreadsheet, how to make a day trading tracker, track your day trades in google sheets, spreadsheet of your day trades, stock tracker google sheets, google sheets stocks

Id: OIta3q1YrgA

Length: 36min 12sec (2172 seconds)

Published: Fri Dec 11 2020