Simple Calculation - but Difficult!

[steve2]

I have a spreadsheet of dates and an associated stock price in columns.
At certain times my "stock system" tells me to buy or sell the stock. I
want to simply calculate the gain/loss of each signal. The problem is
that the signals do not occur at regular intervals. I can easily
extract the stock price when the signal changes from Buy to Sell, but
how do I associate this with the previous signal (from Sell to Buy)?
For example in the data below I buy on 6/5/99 at 53.685 and sell on
21/5/99 at 53.53. Then buy on 2/6/99 (50.845) and sell on 15/6/99
(50.93) and so on. Is there a way to calculate each such Buy/Sell
combination right down a long column? Obviously the calculation is easy
done manually.

Thanks,

Steve

Date Price Signal

05/05/1999 52.625 Sell
06/05/1999 53.685 Buy
07/05/1999 52.685 Buy
10/05/1999 53.31 Buy
11/05/1999 53.935 Buy
12/05/1999 54.595 Buy
13/05/1999 55.905 Buy
14/05/1999 53.375 Buy
17/05/1999 52.935 Buy
18/05/1999 54.31 Buy
19/05/1999 54.345 Buy
20/05/1999 54.99 Buy
21/05/1999 53.53 Sell
24/05/1999 52.97 Sell
25/05/1999 51.47 Sell
26/05/1999 50.5 Sell
27/05/1999 51.03 Sell
28/05/1999 51.5 Sell
01/06/1999 51.935 Sell
02/06/1999 50.845 Buy
03/06/1999 52.125 Buy
04/06/1999 51.375 Buy
07/06/1999 52.93 Buy
08/06/1999 53.78 Buy
09/06/1999 52.5 Buy
10/06/1999 52.625 Buy
11/06/1999 52.935 Buy
14/06/1999 51.97 Buy
15/06/1999 50.93 Sell
16/06/1999 52.845 Sell
17/06/1999 53.5 Sell
18/06/1999 54.31 Sell
21/06/1999 55.435 Buy
22/06/1999 56.375 Buy

 

[Stephen Bullen]

Hi Steve

I have a spreadsheet of dates and an associated stock price in columns.
At certain times my "stock system" tells me to buy or sell the stock. I
want to simply calculate the gain/loss of each signal. The problem is
that the signals do not occur at regular intervals. I can easily
extract the stock price when the signal changes from Buy to Sell, but
how do I associate this with the previous signal (from Sell to Buy)?
For example in the data below I buy on 6/5/99 at 53.685 and sell on
21/5/99 at 53.53. Then buy on 2/6/99 (50.845) and sell on 15/6/99
(50.93) and so on. Is there a way to calculate each such Buy/Sell
combination right down a long column? Obviously the calculation is easy
done manually.

The easiest way is probably to use extra columns, so assuming the 'Date'
header is in A1:

A B C D E
1 Date Price Signal ChangePrice Margin
2
3 05/05/1999 52.625 Sell =B3 0
4 06/05/1999 53.685 Buy =IF(C4<>C3,B4,D3) =D4-D3
5 07/05/1999 52.685 Buy =IF(C5<>C4,B5,D4) =D5-D4

Column D will then show the price at the last change between Buy and
Sell, so column E is the gain/loss on the trade.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev