+ Reply to Thread
Results 1 to 12 of 12

Journal for trading stocks help

  1. #1
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    8

    Journal for trading stocks help

    Hi guys,

    I need some help with my spreadsheet that I working on. I have been trading for a while, and looking to improve my results. I thought a trading journal would help me, showing me what my strengths and weaknesses are. I created the attached journal in excel.

    I have managed to muddle though a good amount but having trouble with the rest. I would appreciate some help.
    The spreadsheet has three sheets, ‘Journal’ ‘Description’ and ‘Mock Up’

    Journal – A part-working journal, which I need help with
    Description – Describing the data/information used and how things should work.
    Mock Up – A quick mock-up of how the spreadsheet should look like when working correctly.

    Hope someone can help me out. TIA EFW

  2. #2
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Journal for trading stocks help

    Try this and see if it works for you. I edited the Description sheet to reflect what I changed.
    I also changed your date and time entries to eliminate adding a bunch of extra cells to calculate.
    This can be changed back if it don't work for you.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    8

    Re: Journal for trading stocks help

    Hi jamfz , First of all, Thankyou. The work you have done is excellent.

    On the first trade, which is a ‘Short’. The table shows -0.57 risk per share, -1.75 Profit per share, Return $ (1,750.00) and Return % -18%. Would it be possible to have both Long and Short trades to be to be shown without brackets or ‘-‘if they are winning trades. If the trade is a Loss, then can we have the brackets and ‘-‘shown on that that trade to show that it was a loss? I have changed the mock up sheet to reflect this.

    Not sure if I am doing something wrong, but the Win / Loss does not seem to work for me. The idea is that if the trade is a winner, the cell would change to Green with the text ‘Win’, but if the trade was a loser, the cell would change to Red with the text ‘Loss’. I like the look of it.

    The change to the date/Time looks grand and makes things easier to read. Thanks

    TIA EFW
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Journal for trading stocks help

    Pasting the following formula into cell R6 and dragging down to R12 on the journal sheet yields the same results as those shown in the 'Return $' and 'Return %' columns on the mock up sheet:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Journal for trading stocks help

    Sorry it's took me so long to reply. I have been blocked by the firewall and couldn't get to the forum for some reason. Can you tell me what the cell is that determines the win/loss? If i know that, I can make the win /loss show up automatically. I left it as manual entry last time. Also if you make the return column not show negative numbers that will affect the summary table at the bottom of the sheet. The numbers there will not be accurate. You need the negative numbers to show the loss of money. If not everything titled win is a gain even though the math says it's not. But using JeteMc's suggestion will fix the negative numbers on the rows that show win.
    Last edited by jamfz; 07-29-2018 at 08:58 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Journal for trading stocks help

    Looking at the values on the Journal sheet it appears that the following could be used to populate the Win/Loss column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Journal for trading stocks help

    That would work. Does the exit price ever not equal the target or the stop loss? I was thinking using the risk to return ratio because the negative values on EFW's sheet are the losses. But I wasn't sure what they are considering for win/loss.

  8. #8
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    8

    Re: Journal for trading stocks help

    Hi guys,

    Many thanks for your help and replies. This week has been a good week as I got most of the spreadsheet working, just got a couple of things to get working/complete. They are 'Risk to Return Ratio (L)' and 'Drawdown', but other than that everything seems to be working as I wanted. I tried to attach the sheet, but was not working, so I have uploaded a copy to my google drive, if you spot any errors etc, please let me know. Thanks

    I've had to remove the first part from the url as I don't have enough posts to use links :/

    drive.google.com/file/d/18InvgunyS7DjgPDoqBLav3m-9_DiwuY_/view

  9. #9
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Journal for trading stocks help

    I would add in IFERROR to the table at the bottom to prevent the errors you have now. not sure about the other cells. I know nothing about stocks. a new spread sheet upload would be helpful.

  10. #10
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    8

    Re: Journal for trading stocks help

    What error are you refering too? If it the #DIV/0! errors on the 'Average loss Per Trade' and 'Average Holdint Time (L)' cells, I was assuming that normal. If you change 'Type' in the dropdown list from Long to Short, then the error goes away

  11. #11
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    8

    Re: Journal for trading stocks help

    Quote Originally Posted by EFW View Post
    What error are you refering too? If it the #DIV/0! errors on the 'Average loss Per Trade' and 'Average Holdint Time (L)' cells, I was assuming that normal. If you change 'Type' in the dropdown list from Long to Short, then the error goes away
    I've added these to those cells, now the cell is blank rather the the #DIV/0! error.
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Journal for trading stocks help

    Yes the #DIV/0 errors were what I was referring to. You will also get those errors in the same cells for the wins if you don't have any wins on the sheet because excel is trying to divide by 0. I just recommended it to make things look cleaner.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel vba for trading stocks (interactive brokers)
    By union in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2014, 11:48 PM
  2. Replies: 4
    Last Post: 02-13-2014, 09:16 AM
  3. Need help with an "if function" to improve a Forex trading journal
    By baelar in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-18-2013, 03:09 AM
  4. How do i calculate this stocks trading excel?
    By nederay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2013, 03:25 AM
  5. Journal
    By bongmendoza in forum Excel General
    Replies: 2
    Last Post: 02-06-2012, 05:59 AM
  6. Excel 2007 : Charting for stocks
    By King_Junior in forum Excel General
    Replies: 1
    Last Post: 09-28-2009, 04:34 AM
  7. Stocks
    By cheryl church in forum Excel General
    Replies: 1
    Last Post: 08-11-2006, 01:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1