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