Calculating profit and loss

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

Hi Erik, thanks for the reply.

I tried your suggestion without any success. Is it possible to email you a sample of the spreadsheet and then explain what I am trying to do?

Regards,

Scott

Hi Erik,

Thanks so much for your help.

You will see in the spread sheet the three columns: market position, entry price, and profit.

This is a trading strategy always in the market. When the market position is 1, it is long and when it changes from 1 to -1, it reverses the position to short. Meaning the long is closed for a profit or a loss and the short becomes the new position.

So, when totaling the profit, when a new market position is generated it has to be totaled one of two ways.

For example:

H3 - market position = 1, I3 - entry price = 100.56

when the market position changes to -1 in the next row,

H4 - market position = -1, I4 - entry price = 100.88

You would subtract I4 from I3 to get the profit from that trade.

Then,

H5 - market position = 1, I5 - entry price = 100.53

You would subtract I4 from I5 to get the profit from that trade (when the market position is -1, the closing price is subtracted from the opening price)

Then,

H12 - market position = -1, I12 - entry price = 101.13

You would subtract I12 from I5 to get the profit from that trade.

So in the profit column it would look like this:

I4 - I3 = .32
I4 - I5 = .35
I12 - I5 = .60

and so on.

I hope this is clear for you. If not please let me know

Thanks again,

Scott

Hi Erik,

You are on the right track and where I have gotten stumped. The issue is that you are showing profit in every cell. The only time there would be a profit or a loss would be when the market position changes from 1 to -1 or from -1 to 1.

When going from 1 to -1 the entry price at 1 is subtracted from the entry price at -1.

When going from -1 to 1 the entry price at 1 is subtracted from the entry price at -1.

Market position 1 expects the price to move higher and when it changes to -1 we close that position and subtract the entry from the close, and initiate a new position expecting the price to move lower. When market position -1 changes to 1 we close that position and subtract the closing price from the opening price. And so on.

What I am trying to do is sum the profit column for a grand total of the profit or loss of the system.

Thanks again,

Scott

Hi Erik,

I am not sure how to use the data I get from your formula. There should only be an entry in the profit column when the market position changes from 1 to -1 or -1 to 1. The rest of the time there is no profit to calculate.

I have added the results I am trying to achieve in column L and attached the spreadsheet.

I hope this explanation helps.

Scott

Hi Erik,

I believe I confused you with my last post. What I was trying to show you was what the values should be in the profit column. I hand calculated them and put them in the cell to show what I was trying to achieve, but I still do not know how to get there.

In the attached spreadsheet you will find the profit values in column J and the cells used to calculate it in column K.

The profit column should only have a value when the market position changes from 1 to -1 or -1 to 1.

Sorry for the confusion and thanks again for your help.

Hi Erik,

I think the problem is that I sent you a piece of the file with only winning trades, or positive profit.

I have attached a larger piece of the spreadsheet and highlighted a couple of the losing trades that should have calculated as a negative profit.

Are you growing weary of this yet?

Scott

Erik,

If I leave the ABS from the formula it does calculate some losses, however, it also calculates some trades as losses that were profitable.

I have highlighted some of the trades in red that are wrong in the attached sheet.

Scott

Hi Erik,

I understand your thoughts. Let me try and explain.

A long position is one you buy now with the hopes of selling in the future at a profit. A short position is one you sell now with the hopes of buying it back in the future at a lower price.

Long equals market position 1
Short equals market position -1

When a market position 1 is generated, the program buys the open the next day. It maintains this long position until a market position -1 is generated.
When a -1 is generated the program sells 2 positions at the open the next day - one position closes the previous long and the second position becomes the new short position.

Then, when a market position 1 is generated again, the program buys 2 positions at the open the next day. The first closing the already open short position and the second becoming the new long position.

This should explain the cells used and their order in the calculations. In simple terms, going from 1 to -1 requires the former entry price subtracted from the latter and going from -1 to 1 requires the latter entry price subtracted from the former.

I hope this helps.

Scott

Erik,

You put me on the right track. By totaling profit from long trades and profit from short trades in separate columns, it works great.

Thanks so much for your help.

Scott