Calculating loss or profit from multiple trade signals

[Elvis]

Hi,

I have pricing data on a company's stock price in column C ordered with oldest pricing history at the top and newest at bottom.

In column F, I have trading signals based on the prices in Column C, 1 denotes a buy and -1 denote a sell.

I need to work out the profit/loss I would make buying and selling 200 shares/units based on the corresponding buy and sell prices in column C.

I would buy every time a cell in column F generates a 1 and continue to hold the stock until a cell column F generates the next -1. And then I would buy agian on on the next occurrence of 1 in coloum F and sell on the next occurrence of -1.

So for example,

The first 1 occurs in F8, so I would to buy at the corresponding price in c8, all subsequent cells continue to have 1 until F13 (i.e. F8 to F12 all have 1 (buy signals in them so I hold the stock), a -1 appears in F13, I want to sell at the corresponding price in C13. Then I want to buy at the nest buy signal F15 and sell at the next occurring -1 (F16).

My pricing data runs from D8: D2948, I want to calculate the returns from all these separate trades and sum the results in say G2 so I can work out what that trading strategy over the entire period would yield in terms of loss or profit.

I know how I could do this for any given single trade but know all trades. I'm assuming it's likely I need some kind of continous formula in say column G to work out profit/loss for each trade and then I could simply sum that range in G2 but I'm juist guessing.

Hope someone can help!

Thanks,

Elvis

 

[venkat1926]

I guess your data is something like the sheet given below.
a helper column in G or any other blank column . see formula in G2 and copy this formula down

and see the formula in F23
check whether this is ok.

Book1
	A	B	C	D	E	F	G
1	date		price			buy/sell
2	1/1/2009		70			1	70
3	1/2/2009		85			1	0
4	1/3/2009		91			1	0
5	1/4/2009		75			1	0
6	1/5/2009		84			-1	84
7	1/6/2009		84			-1	0
8	1/7/2009		91			-1	0
9	1/8/2009		94			-1	0
10	1/9/2009		72			1	72
11	1/10/2009		73			1	0
12	1/11/2009		93			1	0
13	1/12/2009		78			1	0
14	1/13/2009		83			-1	83
15	1/14/2009		89			-1	0
16	1/15/2009		79			-1	0
17	1/16/2009		70			-1	0
18	1/17/2009		73			1	73
19	1/18/2009		89			1	0
20	1/19/2009		78			1	0
21	1/20/2009		94			-1	94
22
23					profit/loss	46
Sheet1

 

[Elvis]

Hello Venkat,

Thanks for your solution.

I've worked out the profit/loss for few trades manually and compared against your sheet and the numbers look fine.

Using the existing framework, would it be easy to also work out the number of trades that return a profit and those that generate a loss?

Thanks,

Elvis

 

[venkat1926]

At present I am not able to think of any formula solutions. I have written two macro 1."blank" and 2. "test:. copy paste the two macro in vb editor of my file sent earlier.(IN A SEPARATE MODULE IF YOU HAVE ALREADY SOME MODULES)
then run ONLY THE MACRO 'TEST"
the no. of profits and losses are in H2 and I2. of your sheet
posts feedback .

if this is ok with m y file try this on your file.

Dim rng As Range, cel As Range
Dim profit As Integer, loss As Integer
Sub blank()
'Set rng = Range("H1:H2828")
Set rng = Range(Range("G2"), Range("G2").End(xlDown))
For Each cel In rng
If cel = 0 Then cel.Clear
Next

End Sub

Sub test()
blank
profit = 0
loss = 0
Set rng = Range(Range("G3"), Cells(Rows.Count, "G").End(xlUp))
'MsgBox rng.Address

For Each cel In rng

If cel <> "" Then
If cel.Offset(0, -1) = -1 Then
If cel > cel.End(xlUp) Then
profit = profit + 1
ElseIf cel < cel.End(xlUp) Then
'MsgBox cel.Address
loss = loss + 1
End If
End If
End If
line1:
Next
Range("H1") = "no. of profits"
Range("H2") = profit
Range("I1") = "no. of losses"
Range("I2") = loss
End Sub

 

[Elvis]

Hello again venkat1926,

Thanks for the code. I tried running it but it doesn't appear to work.

The no. of profits and losses H2 and I2 appear as 0.

Also I notice a couple of other things after running the macro, some of the the values in column H get overwritten by the h2 and I2 entries, which changes the total profit and loss number in G23. I have changed the macro for the macro output to appear in non empty cells as per below which fixes the overwriting isuue but the number of profit and loss trades still appear as 0 which is incorrect!

Thanks

Elvis

Range("j1") = "no. of profits"
Range("j2") = profit
Range("k1") = "no. of losses"
Range("k2") = loss

 

[venkat1926]

see my sheet below.
F24 is manual formula
H2 and I2 are results of macro. of course my columns H and I are empty.

will this help you to modify the macro.

Elvis.xls
	A	B	C	D	E	F	G	H	I
1	DATE		price			buy/sell		no.ofprofits	no.oflosses
2	1/1/2009		70			1	70	2	1
3	1/2/2009		85			1
4	1/3/2009		91			1
5	1/4/2009		75			1
6	1/5/2009		84			-1	84
7	1/6/2009		84			-1
8	1/7/2009		91			-1
9	1/8/2009		94			-1
10	1/9/2009		72			1	72
11	1/10/2009		73			1
12	1/11/2009		93			1
13	1/12/2009		78			1
14	1/13/2009		83			-1	83
15	1/14/2009		89			-1
16	1/15/2009		79			-1
17	1/16/2009		70			-1
18	1/17/2009		73			1	73
19	1/18/2009		89			1
20	1/19/2009		78			1
21	1/20/2009		94			-1	54
22
23
24						6
Sheet1

 

[Elvis]

Hello venkat1926,

Sorry my mistake, because I didn't see the full worksheet range, I was looking at the wrong columns.

On the sumproduct function, I noticed if the first value of the sumproduct range as a -1 in column G then it includes this in the calculation (via populating the equivalent cell in column G with value from column C) when it should be excluded. Similary if the last cell in the sumproduct range (Column F) is a postive number it also included this in the calculation by (via populating equivalent number in column F based on value in column C).

In your example, the calculation is correct because the first value starts with a 1 and the last value ends with a -1, and so column G has zeros for these cells. But where the first value is a -1 and or last value is 1, then calculation is incorrect because the cells in column G do not include zeros but actula values from column C. These should be excluded from the calcuation.

Is there anyway to get the sumproduct to autoselect the range based the first occcuring 1 value and the last occuring 1- value? Or is that any other way round it?

Thanks,

Elvis

 

[venkat1926]

I presume you are doing paper trading. In that case you should not start with the date of sell sign but start with a date of buy sign. similalry end with a date of sell sign.