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.
@@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.
@@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)
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!!
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.
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
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.
Oh my, this was amazing, thank you so much, I wanted to improve my spreadsheet journal and this blew mine away! Really appreciate you sharing your expertise!
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!!!😊🙏🏻
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!
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?
I really do like your trading journal and the way it is laid out. I just wish it was also setup for options trading and tracking. That would be fantastic. Other than that, it is a really great trading journal.
Do you have a formula for the technical side of trading? Trying to figure out my win % for certain setups and what days theyre most successful on along with timeframes, etc. Would be much appreciated!
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 👍
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?
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?
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 ?
@@yoel420-z7c : 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
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.
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?
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?
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
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!
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.
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.
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 =/
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.
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
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!
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?
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!
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
What if I short a stock? Short bias traders are getting no love here lol. You also mention that your spreadsheets are on your Patron page but you don't list a Patreon page under your vid's description.
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!
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!!
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
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
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!!
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!
Oh my, this was amazing, thank you so much, I wanted to improve my spreadsheet journal and this blew mine away! Really appreciate you sharing your expertise!
You are so welcome!
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!
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!
Thank you a lot, we do appreciate the effort and the time put on this awesome work
You are welcome!
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?
This video was extremely helpful and a great way for me to orgnize all the information. Massive thank you!
Glad it was helpful!
I really do like your trading journal and the way it is laid out. I just wish it was also setup for options trading and tracking. That would be fantastic. Other than that, it is a really great trading journal.
Great video. but what if i take short positions it consider it as a loss ???
Do you have a spreadsheet for FOREX traders? Or how can I adapt your spreadsheet for the Forex market?
Do you have a formula for the technical side of trading? Trying to figure out my win % for certain setups and what days theyre most successful on along with timeframes, etc. Would be much appreciated!
The unique function is for Daily Traded is giving a different value than the Date in trades placed
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!
How can I update the formula to work in mine it keeps on saying #REF! error
Wonderful video, very enjoyable to follow along. Great tracker and perfect instruction. Thank you good sir.
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
So simple and easy to understand. Well done!!
Thanks!
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
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?
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
This was brilliant. Thank you for the time and effort you put into this. Very much appreciated!
You are welcome!
Absolutely love this video! this is why I love UA-cam, because of such incredible content by people like you! thank you so much, man! Earned a Sub!
Wow, thank you!
Thank you for this! Now I just need to figure out how to work in buy and sell commission 😯
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!
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?
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!
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.
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!
19:26, idk what u hv done here, it confused me, can u re explain it in a short video plz?
hh
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.
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!
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!
Would I need to make a separate column for bearish/ short trades? This spread sheet only calculates Long positions
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?
Superb Guidance.
Thanks alot.
Thanks and welcome
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.
@@yoel420-z7c : thank yiu, i ll try it
@@yoel420-z7c : 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
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.
What is the equivalent of =unique in apple numbers?
You are a legend. Thank you.
You’re welcome!
Awesome video. I can download your spreadsheet. Do you have another link?
You can download it on tickerdata.com!
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.
19:20 Formulae not working,please help me on this
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
Can you please share back testing template for forex and crypto with automation.?
It would be great help.! Thanks.
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!
Hi thank you for your video. What mean the Column "share" what is this number please?
how do i set up those formulas for excel
In his messup for the days/months. Just highlight those cells, right click, shift cells right.
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
It helped me a lot brother. Keep up the good work
That’s great!
Thanks for your useful sharing!
can't thank you enough!!! ❤❤
How can we add a leverage tab? Apologies if you’ve already mentioned it, haven’t seen it.
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!
would this track the difference between long/short sales?
Great, and very informative video. Do you have a spreadsheet for forex or indices
it should work with this spreadsheet.
hi there, thanks for the video.
Do you know a formular for the average number of trades per day? thanks.
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
Excellent Video. Super informative
Glad you think so!
Hello
Could you tell me the formulas for the Best Trade and Worst Trade?
Thank you
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!
Hi @Dividendology, is there a working copy of this spreadsheet that I am able to download?
how do you record short trades?
Thank you, very much appreciated. You are AWESOME!!!!
Thank you!!
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
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.
You're savage dude thank you!
Thanks!
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
Tons of value, thank you so much!!
You are welcome!
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!
Hello, how can we calculate weekly PNL from monday to Friday just you did for monthly day of the month
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!
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!
Broo thankyou for this one ❤
do you have a tracker that allows for options as well as stocks
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.
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!
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?
Thank you so much for this information 🙏
Glad it was helpful!
@@Dividendology it was very helpful. Thank you again.
So nice! thanks🙏
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.
Thanks a lot
I badly need this but I need a column were to put my chart in each trade
Thanks broo
great info bro!
Glad it was helpful!
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
What tier on the Patreon is this spread sheet included in?
The 'Official Patron' Tier!
phenomenal!
What if I short a stock? Short bias traders are getting no love here lol. You also mention that your spreadsheets are on your Patron page but you don't list a Patreon page under your vid's description.
The spreadsheet is now on Tickerdata.com! You can also adjust for shorts by making the numbers negative.
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 😊
BRO WHATS YOU MEAN NUMBER OF SHARES!!!!!!
Very good! Thanks 📈 new follower here
Thanks!
THANKS BOSS
Brilliant!