How to calculate a P&L in Excel

H

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?

Thanks.

Hans
 
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
 
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.)
 

Attachments

Quote from late apex:

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.)
More...
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?

Hans
 
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.
 
You must log in or register to reply here.
Back
Top