Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Price Change Calculation
Trying to create formula that will automatically calculate the % price change
in the closing price of a stock where the closing price is greater than a given value. Currently, I manually scroll down column D until I get the first "Buy" signal and then write the formula (Bx/$B$y-1) and do the samething everytime my price paramater is met. Column A=Date, B=Close Price, C="Buy"where close price greater than $75, D=% P/L. The % P/L is calculated (most recent close price/first buy value-1). Date Close $75.00 % 3/10/2010 80.08 Buy 6.55% 3/9/2010 79.76 Buy 6.12% 3/8/2010 80.62 Buy 7.26% 3/5/2010 80.71 Buy 7.38% 3/4/2010 78.84 Buy 4.90% 3/3/2010 79.24 Buy 5.43% 3/2/2010 77.98 Buy 3.75% 3/1/2010 76.45 Buy 1.72% 2/26/2010 75.16 Buy 0.00% 2/25/2010 74.07 2/24/2010 72.68 2/23/2010 73.64 2/22/2010 76.24 Buy 0.40% 2/19/2010 77.16 Buy 1.61% 2/18/2010 76.6 Buy 0.87% 2/17/2010 75.12 Buy -1.08% 2/16/2010 75.94 Buy 0.00% 2/12/2010 73.68 2/11/2010 74.17 2/10/2010 71.03 2/9/2010 71.58 2/8/2010 69.2 2/5/2010 70.23 2/4/2010 66.74 2/3/2010 70.46 2/2/2010 72.51 2/1/2010 71.59 1/29/2010 66.69 1/28/2010 68.82 1/27/2010 71.25 1/26/2010 71.99 1/25/2010 74.6 1/22/2010 74.23 1/21/2010 76.28 Buy -8.60% 1/20/2010 83.52 Buy 0.07% 1/19/2010 84.6 Buy 1.37% 1/15/2010 84.3 Buy 1.01% 1/14/2010 85.08 Buy 1.94% 1/13/2010 86.15 Buy 3.22% 1/12/2010 84.77 Buy 1.57% 1/11/2010 88.09 Buy 5.55% 1/8/2010 88.1 Buy 5.56% 1/7/2010 85.64 Buy 2.61% 1/6/2010 87.31 Buy 4.61% 1/5/2010 83.96 Buy 0.60% 1/4/2010 83.46 Buy 0.00% Example 3/10/10 close = 80.08, first buy value in series =75.16 on 2/26/10. (80.08/75.16-1)= 6.546% which is rounded to 6.55%. 3/9/10 =(79.76/75.16-1)=6.12% etc. Is there anyway this can be done automatically with a formula or code? Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Price Change Calculation
Try this formula: =IF(C2="Buy",(B2/INDEX(INDIRECT("B"&ROW()&":B65536"),MATCH(0,INDIRE CT("D"&ROW()&":D65536"),0)))-1,"")
What the formula is based on is that the first "BUY" in the series always turns out to be zero. That's what the match performs. I have to slide the match down a row for each new comparison (otherwise it always finds the FIRST zero and not necessarily the first zero in the series). So thats what the indirect and row() functions do. I use index off the same relative range. Anyway, I tried it with the data you provided and it seems to work. --- frmsrcurl: http://msgroups.net/microsoft.public...ge-Calculation |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation based on lowest price | Excel Discussion (Misc queries) | |||
Price calculation - rounding | Excel Discussion (Misc queries) | |||
Price function difference in Output formula vis a vis Manual Calculation | Excel Worksheet Functions | |||
SUMIF function in "Price quote with tax calculation" templae | Excel Worksheet Functions | |||
Excel spreadsheet/template for developing a retail price calculation | Excel Programming |