How to calculate a P&L in Excel

Discussion in 'Trading Software' started by hans123, Mar 2, 2007.

  1. hans123


    I have an Excel sheet with O/H/L/C data. I am buying at a close and want to sell only when the close of the next day is higher then the previous close. Otherwise I keep the trade open until there is a higher close coming up. This means I can have a trade open for at least one day, but also for example for five days.

    How can I calculate the P&L of every trade?


  2. hans123


    Anyone who can help?

  3. amace840


  4. hans123


    Thanks, but it's not what I mean. I have attached an example.

  5. not sure whether this is what you need. Try this

    Insert three columns between G and H in your sheet (i.e. before the P&L Column)

    Add these formulae and copy it down

    On cell H2: =IF(G3<>"",0,IF(F3<>"",H3+1,H3))
    On cell I2: =E2-E3
    On cell J2: =H2*I2
    On cell k2: =J2+K3
  6. hans123


    Thanks for your reaction. I have tried it , but is not correct.

    Anyone else an idea?

  7. One simple solution attached, using the old reliable Vlookup function.

    Since you can have multiple trades exited at the close of a single day, yet want to track each trade's P&L separately, I'd recommend having as many Exit and P&L columns as the largest possible number of simultaneous positions you'll ever expect to hold (here 3).

    Also, having your Exits (columns G-I) entered manually, rather than calculated, generally isn't the optimal or fastest way to handle it, for larger data sets... but if it works for you, sure, why not. I preserved as much of your original spreadsheet as possible. (Exiting Trade 2, but not Trade 1, on Jan. 8th implies that you're using other rules besides a higher close.)
  8. hans123


    The columns F and G were only to explain what I mean. All columns should be calculated in Excel. Column F is no problem. Main problem is to calculate the exit and the P&L.

    Any idea?

  9. Hans, example1.xls already calculates P&L automatically.

    To calculate exits (yes, a good idea), you'd still need to specify what the exit rules are, consistent with your actual exits. See my earlier comment regarding trades 1 and 2.