Calculating profit and loss

[dismal]

Greetings,

I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.

I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.

Where:
Market position is either 1 or -1
Entry price is the price where the position is reversed.

For example:
H3=1 and I3=100.56, then
H4=-1 and I4=100.88, then
H5=1 and I5=100.53

I need to calculate the profit as follows:
I4 - I3 (because market position of 1 expects the price to go up) and then,
I4 - I5 (because market position of -1 expects the price to go down)

I want to calculate this as a running total to the bottom of the column.

This is the formula I was working with, but it doesn't work:

=IF(OR($J4="",ISERROR(MATCH(-$J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH (-$J4,$J4:$J$55,0)-1,)-$K4))

Any help would be greatly appreciated.

Scott
Edit/Delete Message

 

[ravishankar]

Hi
It is not clear to me what you want done. Assuming that profit calculation is based on col H
Paste the following codes in the macro window ( Alt F8)

for a = 4 to 55
if cells(a,8) = ABS(cells(a,8) then
cells(a, 15) = cells(a+1,9) - cells(a,9)
else
cells(a,15) = cells(a-1,9) - cells(a,9)
endif
next a

Run the macro. It will fill col O with subtracted values. If this is not what you want, explain step by step what you want done
Ravi

 

[dismal]

Hi Ravi, thanks for the reply.

It might be easier to explain if I could email you a piece of the spreadsheet.

Scott