ET News & Sponsor Info
General Topics
Trading Instruments
Technical Topics
Brokerage Firms
Tools of the Trade
Trading for a Living
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:
470
4. hans123

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

Hans

File size:
14.5 KB
Views:
397
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:
295
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:
 24option Forex & CFD Trading AMP Global Clearing Futures and FX Trading GFF Brokers Low-Cost Futures Brokerage IC Markets True ECN for FX and CFDs Jigsaw Trading Advanced Trading Tools Lightspeed Trading Equities & Options Trading NinjaTrader Trading Software & Brokerage Optimus Futures Futures Trading Platforms and Order Routing Option Workshop Option Analysis Software PTMC Full Multi-Asset Trading Platform Rithmic Futures Trade Execution Platform SpreadProfessor Spread Trading Instruction TD Ameritrade Get the #1 trading app in the App Store TopstepTrader We Fund Traders TradersStudio System Development Platform Trading Technologies Trading Software Provider Tradovate Commision-Free Futures Trading