Excel Code Help required

[mik1973]

I'm putting together a spreadsheet of trades i've made in the last few months and require some help on how i can automate the profit and loss calculation.
I need to enter code in the last column (called P/L) which will base its calculation on the values of the open and close column. I believe a third column called L/S (for long and short )is required to automate the spreadsheet.

Could someone explain how i can enter a formula which (for example) will go along the lines of if cell A1 = s (for short) then P/L=C1(close) -B1(open) or if cell A1 = l (for long) then P/L=B1 (open)-C1 (Close).

Hope the above makes sense and would appreciate any help.

Cheers,
Mick

* I do realise that i could manually type in values to take one cell from another but would like to enter code based on certain conditions

 

[JonnyT]

Use the If statement =IF(A1="s",A3-A2,A2-A3)

Where A3 = openprice and A2 = closeprice

JonnyT

 

[mutantcar]

If you want to do Profit or Loss in the same cell you'll need:

=IF(A1="S",C1-B1,IF(A1="L",B1-C1,""))

Forgot to put "" in the 'else' so edited

Mute.

 

[mik1973]

Hi Jonny, Mutant,

Thanks for your response. Managed to put the relevant 'code' into the cells to calculate my results. From my very limited knowledge of coding the commas seem to have a very similar effect to that of using 'then' and 'else' statements?

Thanks again for your help.

Mick

 

[JonnyT]

Excel tells you everything in the help. If you just type in =IF( then it will automatically help aswell.

JonnyT

 

[RedE]

The most efficient way to distinguish Long from Short is to use positive values for long and negative values for short in the trade quantitty column. That way, simply multiplying the trade price column by the trade quantitiy gives you a cash impact, which when summed gives you the P&L to date.

Also, if you change the number format of the trade quantity columns to "Custom" and then enter:
"Long "0;"Short "0;"-" as the format code the column will appear more meaningful to the human eye!

Hope this tip helps

 

[RedE]

A further refinement is to add a trade code to each row, so you can then calculate P&L per closed trade.

In this way, each row represents a single ticket and groups of rows, not necessarily contiguous ones, represent a whole trade, as inidicated by the trade code.

You then do a =SUMIF (TradeCodeColumn, "A", CashImpactColumn) to get the closed P&L, where "A" is a trade code.