How to Create a Day Trading Tracker in Google Sheets (Step by Step)
HTML-код
- Опубликовано: 14 авг 2024
- Download my Spreadsheets: tickerdata.com/
Get 50% off of Seeking Alpha Premium!
www.sahg6dtr.c...
In this video, I take you step by step on how to make an extremely useful interactive day trading tracker. This tracker can be use to track the successfulness of your trading, as well as provide some valuable insight to analytics that may help your future trading.
Formulas Below:
Trades Placed Profit
=if(E8=0,"",(D8-C8)*E8)
Trades Placed W/L (*Note: youtube does not allow the greater than or less than sign in the description, so replace the word greater than in the formula below with the greater than sign.)
=if(E8=0,"",IF(F8 greater than 0,"W","L"))
Days Traded Date:
=unique(A8:A)
Days Traded Profit:
=if(I8=0,"",SUMIF(A$8:A,I8,F$8:F))
Days of the Month values:
=SUMIF($I$8:$I,date($N$7,$L8,O$7),$J$8:$J)
Win Amount Total:
=sumif(G8:G500,"W",F8:F500)
Loss Amount Total:
=sumif(G8:G500,"L",F8:F500)
Dashboard Wins:
=COUNTIF(Recording!G8:G500,"W")
Dashboard Losses:
=COUNTIF(Recording!G8:G500,"L")
Note - These formulas are based off of where my prior data is located. You may have to change what cells the data is pulling from based off of where you put your prior data.
If you are having trouble, be sure to ask a question in the comments or head to my patreon page and download this spreadsheet for your own use!
Thanks for watching and don't forget to like and subscribe!
The microphone I use: amzn.to/3ngqbVz
#DayTrading #DayTradingTracker #GoogleSheets
I'm full of admiration for the way you seem to have mastered Excel. I don't think there's anything like this on YT. Very original content.
Thank you so much!
the ability to add long or short position would be the finishing touch
Just put L or S beside
I was just about to say that, this can only record going long…..WHAT ABOUT GOING SHORT
How to record for short trade
For those who are having an issue with the Days of the Month function where it is displaying a "0" value you need to ensure that your columns which holds the date values (In his example it's Columns A and I) are set to the date format. In addition, you have to ensure that the way you are writing the date matches the format set. Hope this helps.
HI im having trouble fixing this, it is all displaying "0" and I don't know what the issue is, my columns are the same but I still get 0?
@@zanderrichens4467 Change the way you write the date in the cells. It has to be the same as the standard format of your settings. If the format is set to USA (month/date/year) and you write it in a UK format (date/month/year) it will give this "0" issue. This is what worked for me.
@@CodConquest sorry to bother you but try that and doesn't work any ideas thank you
@@jorgedias563 Go to file then settings and change it to your country (make sure you delete the functions and dates before you exit the format). Now open it and start to add the dates and the =SUMIF($I$8:$I,date($N$7,$L8,O$7),$J$8:$J)
Hi CodConquest, I am still unable to fix the error. Can I trouble you to send a working copy of the spreadsheet?
Ur vid was fire bro! It took me from zero to hero in about an hour. I hacked it up a little to suite my needs but seriously could not have gotten the results I got without your vid. Thanks much!! Cheers!!
Awesome!!! I’m glad it helped!!
Thank you a lot, we do appreciate the effort and the time put on this awesome work
You are welcome!
The most utilitarian and simple (in a good way) methodology for tracking trades I’ve seen. You also have a excellent way of leading through your process. Well put together. I’ll definitely sign up for your patreon.
Thank you! I’m glad it helped. I believe you’ll find many other useful tools on my patreon as well. Enjoy!
Wonderful video, very enjoyable to follow along. Great tracker and perfect instruction. Thank you good sir.
This video was extremely helpful and a great way for me to orgnize all the information. Massive thank you!
Glad it was helpful!
Please can u help me out, mine only adds the price I traded on January first alone, other days other months only says 0, what's the cause plzz?
This is a super good video man, very simple for a beginner at excel/spreadsheets to understand. One thing that could help is explaining to users what combinations of CTRL+SHIFT you're using to highlight the columns. Other than that, great job mang thanks!
Thanks for the kind words and thanks for the tips!
Amazing video and super helpful.
I still got a question though. If you bough 4 shares of AAPL at a price of 114.98 as shown in the video but then you add 3 more shares at 115.2 and sold everything at 137.42, what formula can be used in this situation?
Did you find out?
How can I update the formula to work in mine it keeps on saying #REF! error
hands down the best video, not only was I beginner and you helped me understand so much you made it really easy to understand. You deserve WAYY more views and likes
Thank you! I'm glad it helped!!
can someone explain the IF(C8=0,"") part of the first two formulas? its like 'if this cell isnt empty, then display the result' but what in that formula says that, i would've thought it would be something more like IF(C8>0, xxx, xxx) i dont understand the empty quotation marks or why its =0
Do you have a spreadsheet for FOREX traders? Or how can I adapt your spreadsheet for the Forex market?
Great video. but what if i take short positions it consider it as a loss ???
In the case of the formula =SUMIF($I$4:$I,date($N$4,$L4,O$4),$J$4:$J), the date function is referring to the cell $N$4, which contains the formula itself. This creates a circular dependency, and the spreadsheet cannot calculate the formula.
To fix this error, you can either:
Move the formula to a cell that is not contained within the range of cells that it refers to.
Change the reference in the formula so that it does not refer to a range that contains the formula itself.
If you are using the iterative calculation setting, you can also ignore the error message and allow the spreadsheet to calculate the formula. However, this may result in inaccurate results.
To enable iterative calculation, go to File - Settings - Calculation and select "Enable iterative calculation".
But why did it work in the video if that is the issue...I'm still having trouble fixing it and I've followed your suggestions. I've changed the time format in my settings and the spreadsheet. No luck.
yeah I have the same problem and it won't allow me to fix it
So simple and easy to understand. Well done!!
Thanks!
It is amazing having a people like you, giving us the best information for making our workflow more efficient enough! Can't thank you enough mate! Well done!!!😊🙏🏻
Happy to help!
What an amazing video, it is not only about a world class tracking trades sheet but also an awesome educational presentation about Google Sheets. Great job! Thank you so much 👍
You’re welcome!
Hi for some reason on the days of the month section from February 28-31 it wont write P/L even though it has the formula everywhere else works, does someone know why this is happening
how do i set up those formulas for excel
This was brilliant. Thank you for the time and effort you put into this. Very much appreciated!
You are welcome!
I love the spreadsheet I am trying to make it myself but I am stuck, need your help. I am stuck the longest formula under the calendar month it is not filling out correctly. I tried several ways to fixed but still cannot get it the right way. The valves for the days of the week/month are filling out in column in block instead of the cell across. How do I fixed that?
Can you please share back testing template for forex and crypto with automation.?
It would be great help.! Thanks.
Absolutely love this video! this is why I love RUclips, because of such incredible content by people like you! thank you so much, man! Earned a Sub!
Wow, thank you!
Hello, love your tracker. Could you maybe also make a video for trading Futures?
I’ll have to make add that to my list of trackers to make!
hey buddy every time i get back on my trcker codes are there but it dosent anmatticly put data in. its done right was well please help me fix this
19:20 Formulae not working,please help me on this
Would I need to make a separate column for bearish/ short trades? This spread sheet only calculates Long positions
This is great! How can we adjust this for forex traders who can profit off of sell positions? Is there a way to have the chart calculate sells as positive profit too?
What is the equivalent of =unique in apple numbers?
Great video, thank you so much.... I take it now that since I don't do the shares stuff I can omit that column and adjust the formula to suit me
19:26, idk what u hv done here, it confused me, can u re explain it in a short video plz?
hh
Hello, thanks for sharing this. How do I get the average price if I bought same stock on 3 different time at varying prices then selling half now and half later. It works when if I bought 10 shares then sell same 10 shares later.
i usually just put in the average price
can't thank you enough!!! ❤❤
how do you record short trades?
I'm trying to create your Daily Profit area, where you have Days traded and profit. The version of excel I have doesn't have the UNIQUE feature what can I use to do the same thing on mine?
do you have a tracker that allows for options as well as stocks
The unique function is for Daily Traded is giving a different value than the Date in trades placed
Thank you for this! Now I just need to figure out how to work in buy and sell commission 😯
hello, for some reason when i type 3/1 it adds it on march first and feb 29th, a couple other dates do this also?? any ideas?
Hey I have the same issue. Any fix?
I haven't watched until the end, but how do you deal with trades that are shorts, and incorporating this data?
Best way ive found is to just flip the bought and sold prices around and it works
this is extremely helpful, i would like to know if you have one for swing traders? days held column and then adjust some rules for long vs short
Putting those Days of the month has been challenging,
Plzz how did u do it that u didn't type in formula, u just clicked on the box and it worked.
Enjoyed your Day Trading Tracker. Is there anyway to add a swing trade tracker to this. If anyone wanted to hold a trade for a week, month, 6 months or longer?
I have plans to release a swing trade tracker! That is on my list of videos to make!
Awesome video this has changed my trading so much ! Keep up the great videos maybe do an updated version for 2024 :D
Thanks for the idea!
Hi ty for the video. do you know if there is a way to get Live data feed for stocks for pre-market and after hour trades? Most scrubbers' sources doesn't have a live pre-Market or after hour data.
Thank you so much for the video really easy to understand!
Is there a formula to also add weekly gains with a chart?
I'm sure there are ways to do that. I'll look into adding that feature in the future!
this was areally good video the only problem was you didn't show us how to actual write some of the formulars like the sumif formular and i can't copy the one you wrote in the description. i really like your video but help us atleast how to write the formulars.
This vid was awesome . Well done sir, quick question tho my “days of the month” values are a little bit different they read : =Sumif($I$7:$I,Date($Q$6,$O7,$R$7),$N$7:$N)
But the profit doesn’t reflect… any idea what I may be missing ?
I cant get it to show the pnl on the days of month, have you figured it out?
same here. anyone figured this part? BuMP!!!
@@MC-ki2jr it has to do with how you write dates in different parts of the world.
@@Martin-vw8bw : thank yiu, i ll try it
@@Martin-vw8bw : i tried it but i am coming to the conclusion that it doesn't matter as long as all the date cells have the same date format. i got stuck in that 89.76 Sumif
How can we add a leverage tab? Apologies if you’ve already mentioned it, haven’t seen it.
Hello, how can we calculate weekly PNL from monday to Friday just you did for monthly day of the month
Superb Guidance.
Thanks alot.
Thanks and welcome
hello, new suscriber, im only facing issue in 19.22min. plz help me why not working this? plz answer me what should I do
Me aswell
Bro i Figured ot out bro
You have!! To add the $ just like he did and do the months seperately bro
I can help you .. was having the same problem but i just solved it bro
Hi thank you for your video. What mean the Column "share" what is this number please?
You are a legend. Thank you.
You’re welcome!
AnyWay yo can do this for a forex currency pairs sheet too which has columns pair, long/short, size, price, stoploss, Take profit, Risk to reward then profit or loss which you automated as I've tried always to figure how to do this and I cant
I Know this is a very late comment. But I just used your spreadsheet and I love it! I have just one question, how do I create a formula that changes the profit if I trade a different symbol. For example, the profit gained from 5 handles using 1 contract on the NQ will be different from trading ES using 1 contract. Does anyone know how I can edit this formula? I have been fighting with it for a while
This is for Futures Specific. however I believe it's formatted for Shares.
I trade options and I have to put in the $ amount rather than my contract price bought & sold.
Thank you. Very nice tutorial. Just one question.
This spreadsheet is built up with an assumption that all the trades I take are long. However, if I want to go short, this formula wont work because it will calculate a short win as a loss. Is there a way around it? I think I need to introduce new column called Trade Type and modify the formula somehow right?
For the past hour, I have been trying to figure it out and haven't come up with it yet. There has to be a solution
im having issues with dates and monthly :( i think because im australian the formats different? i dont know. literally only this part im stuck. my entire spreadsheet i created on my own is working. its literally just the daily and monthly formula of yours stops working =/
same issue here but no help we got so far. hope they answer us
Mi too
One of the formulas is incomplete, take a look at the first variables "$I$8:$I" and last variables it should mark a range, you need to specify the last cell of those columns, for example, i have a column of 100 cells, so i put =SUMIF($I$8:$I$100,date($N$7,$L8,O$7),$J$8:$J$100). If the problem is with that formula you only need to fix this and put the number of your last cell.
Excel formulas changed with updates.. so that changed too
The link in the description is not working, please upload it, thank you very much for your effort
Just checked and it seems to be working on my end!
Please sir, I don't understand
The entry price is 1.7598, the stop loss is 1.7580, the profit is 1.7630, and the lot is 0.05
And I put the same formula and it doesn't give me the result
Please master help me🙏
How I write the correct formula to automatically show the profit
Works great 💪🏻 do you Maybe have a formula to calculate the amount, win/loss % of long and short positions? It would make the journal even better!
Link not opening cN I have it to take record on indices and pls add picture colum were I can addd my trading view chart 😊
I badly need this but I need a column were to put my chart in each trade
At 15:20 when you add in the examples, it doesn't compute the same as mine does when adding the same examples. What could be causing this can you give me a better method for solving this issue. Also, at 19:20 the formula is not working at all.
Thank you for such great videos. Any help is grately appreciated!
Ok, figured out the issue at 15:20. Still no luck on the issue at 19:20 for the monthly issue.
Amazing stuff! Thanks a bunch for sharing such niche knowledge+skills. Where 2 find this on your Patreon?
All my spreadsheets are linked on my patreon posts. Check my earlier posts there.
Awesome video. I can download your spreadsheet. Do you have another link?
You can download it on tickerdata.com!
hi there, thanks for the video.
Do you know a formular for the average number of trades per day? thanks.
It helped me a lot brother. Keep up the good work
That’s great!
Hello
Could you tell me the formulas for the Best Trade and Worst Trade?
Thank you
would this track the difference between long/short sales?
Hi @Dividendology, is there a working copy of this spreadsheet that I am able to download?
really good video man thanks!
im having trouble with the formula for days of the months value, its not shoowing me the correct numbers its all 0.00
if u can help me that will be great
thanks!
Excellent Video. Super informative
Glad you think so!
can you use this when you go long and short or is it just for longs?
also which patreon package do i need to get for this google sheet?
thanks
You should be able to use it for short or long. Just adjust your input to a negative number. And it’s available on all patreon packages!
Thank you, very much appreciated. You are AWESOME!!!!
Thank you!!
great video!! a quick question how can i get this spread sheet? thank you in advance! keep up the good job
On my patreon page! Link in description!
Thanks for your useful sharing!
Something no one seems to have asked about are exchange fees. also what if you wanted to short stocks. other then that its aweome, hope you can answer
Exchange fees would be relatively easy to add in. It would depend on how the exchange charged you. For example, if they charge you a flat fee of $5 per transaction, simply add a transaction fee cell to your dashboard and make the formula the count of your number of transactions multiplied by 5. You would then need to subtract that number from your total profit.
As for shorting a stock, that is possible to show, but maybe I’ll just have to make a video at some point showing how to do that.
Hope that helps some!
Where can I download your spreadsheet for day trading?
On tickerdata.com!
This is only for stocks and the formula only works if you are buying the stock right? What if you are selling the stock like futures?
you can also do negative values for short selling.
Tons of value, thank you so much!!
You are welcome!
You're savage dude thank you!
Thanks!
BRO WHATS YOU MEAN NUMBER OF SHARES!!!!!!
do you have this as a sample or template ?
You can download this template on tickerdata.com!
I'm getting problem in the formula for days of the month can anyone help me please
THANK YOU VERY MUCH I WISH YOU THE BEST I REALLY LEARNED HOW TO MAKE ONE AND I COSTMISED ONE FOR ME BECUASE I WORK FOREX
Glad it helped!
Hey mman, thank you for a brilliant video. Would this sheet also work for a p2p crypto trader? I only sell 1 currency on a p2p exchange, and was wondering if this sheet would work if id just skip to ever add anything in the "Bought" section. Thing is just i would need a place to were i could add in the tax, so it automatically calulated my taxes for each trade, like profit after tax. I would love to have a look at a video for a p2p crypto trader that only sells 1 coin and everytime i enter anything its always a win, but i need to have a section for how much profit it is after taxes, and we have 30% taxes in my country regardless of how big the profit is in crypto and regardless if its mining, daily trading, p2p trading etc. Thanks in advance
I believe it should work for that!
Hi There,
thakn you for the tutorial. i am kind of stuck trying to add sumif into days of the month daily profit. it keeps giving me 0 when I try it with whatever date that has already profit. what can I possibly be doing wrong?
2 - is it possible to formulate this spread sheet for options
thank you in advance
1 - make sure your cell format is in date format.
2 - yes. I believe that would be possible. I have an options spreadsheet on my list of videos to make in the future.
@@Dividendology Would you be explain which cell please. thank you
@@Dividendology i went ahead and bout the spreadsheet from patreon today. i was still faced with the same issue. then I realized I left the year as 2021 rather than change it to my trading of 2022. let me know of any modification I can add to this for options please. thank you
does this also calculate shorts aswell
Broo thankyou for this one ❤
What about if you use leverage?
Is it the same calcul for forex pairs?
What tier on the Patreon is this spread sheet included in?
The 'Official Patron' Tier!
Great, and very informative video. Do you have a spreadsheet for forex or indices
it should work with this spreadsheet.
what if youre selling shorts?
Can you please share this sheet as google sheet?
Yes! You can download it on my patreon page. Link in description.
Very good! Thanks 📈 new follower here
Thanks!