General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# 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?

Thanks.

Hans

2. ### hans123

Anyone who can help?

Hans

attachment

File size:
9.4 KB
Views:
531
4. ### hans123

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

Hans

File size:
14.5 KB
Views:
446
5. ### ashcroftsinger

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?

Hans

7. ### late apex

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

File size:
17 KB
Views:
343
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?

Hans

9. ### late apex

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.

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: