Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Simple Calculation - but Difficult!
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with a simple Time calculation | Excel Worksheet Functions | |||
Need help with a simple Time calculation | Excel Discussion (Misc queries) | |||
difficult calculation | Excel Worksheet Functions | |||
error in simple calculation? | Excel Discussion (Misc queries) | |||
Difficult (for me) formula/UDF calculation | Excel Worksheet Functions |