Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
I need some assistance with this one: In my sheet below I have certain buy
and sell signals for this commodity. I used an =if(and()) statement with about 4 logical steps to come up with these buy and sell points. You can't see the formula because I have taken it out. But my next step which I don't understand is how to write a formula to get me out if the price goes against me. So lets take 7/19/2005 BUY signal for an example. I need to find a way to have the formula read that when I get a BUY signal take the close price that period and minus say 5 from that. Now if the low from the next period or however many of periods it is until you get a sell signal goes below that number it should read "STOP" and you exit the position. The same would be for the SELL signal but instead of minus you would add it to the close price and see if the HIGH periods go through that number. My problem is is there is a way to keep a running column with each new period changing to let you know if those parameters have been hit or not. I will be happy to answer any questions if this is not clear. I thought about =if then statements= but not sure on how to write all of this. Thank you again. NOel Date-SPH Open High Low Close BUY SELL 7/1/2005 1,204.50 1,207.50 1,202.50 1,206.00 7/5/2005 1,202.00 1,216.50 1,202.00 1,215.10 7/6/2005 1,215.00 1,216.10 1,204.00 1,204.70 7/7/2005 1,191.70 1,208.80 1,191.70 1,208.80 7/8/2005 1,215.00 1,223.50 1,207.50 1,222.50 7/11/2005 1,225.00 1,230.50 1,224.00 1,229.60 7/12/2005 1,229.50 1,235.00 1,227.00 1,231.80 7/13/2005 1,230.40 1,234.50 1,230.30 1,234.30 7/14/2005 1,243.00 1,243.30 1,234.30 1,238.10 7/15/2005 1,234.50 1,239.50 1,234.00 1,237.60 7/18/2005 1,235.00 1,235.00 1,230.50 1,232.80 7/19/2005 1,237.00 1,240.00 1,234.00 1,239.60 BUY 7/20/2005 1,235.70 1,247.00 1,232.70 1,242.90 7/21/2005 1,244.00 1,244.00 1,234.50 1,236.40 7/22/2005 1,239.00 1,244.00 1,235.50 1,243.40 7/25/2005 1,243.20 1,247.50 1,236.50 1,240.50 7/26/2005 1,239.50 1,243.00 1,239.00 1,241.40 7/27/2005 1,239.90 1,246.50 1,238.80 1,246.30 7/28/2005 1,249.70 1,254.00 1,245.00 1,253.60 7/29/2005 1,253.50 1,254.00 1,243.40 1,243.40 SELL 8/1/2005 1,246.50 1,248.00 1,243.50 1,244.20 8/2/2005 1,248.00 1,253.50 1,248.00 1,251.80 8/3/2005 1,250.00 1,254.50 1,250.00 1,254.50 8/4/2005 1,250.00 1,250.00 1,243.50 1,244.60 SELL 8/5/2005 1,241.00 1,242.00 1,233.60 1,236.40 8/8/2005 1,238.50 1,241.00 1,231.50 1,233.90 8/9/2005 1,239.50 1,242.50 1,236.00 1,241.00 BUY 8/10/2005 1,246.80 1,250.50 1,235.50 1,240.50 SELL 8/11/2005 1,239.00 1,246.50 1,237.20 1,245.20 8/12/2005 1,242.00 1,243.50 1,235.00 1,237.70 8/15/2005 1,238.80 1,244.70 1,235.20 1,244.20 BUY 8/16/2005 1,239.30 1,240.20 1,227.00 1,228.10 SELL 8/17/2005 1,227.30 1,233.60 1,226.50 1,228.10 8/18/2005 1,225.00 1,231.50 1,225.00 1,227.60 8/19/2005 1,232.30 1,233.50 1,228.20 1,230.40 8/22/2005 1,230.80 1,237.00 1,225.00 1,230.70 8/23/2005 1,228.60 1,231.00 1,222.50 1,228.00 8/24/2005 1,223.30 1,232.00 1,216.00 1,216.40 8/25/2005 1,218.60 1,221.00 1,217.10 1,220.20 8/26/2005 1,219.30 1,219.30 1,212.00 1,212.40 8/29/2005 1,208.50 1,222.20 1,208.50 1,221.00 BUY 8/30/2005 1,216.60 1,216.60 1,208.50 1,215.00 8/31/2005 1,217.00 1,230.30 1,211.50 1,227.80 9/1/2005 1,228.20 1,234.50 1,223.80 1,227.90 9/2/2005 1,231.90 1,232.00 1,224.00 1,225.90 9/6/2005 1,230.00 1,241.00 1,230.00 1,240.50 9/7/2005 1,239.50 1,244.00 1,238.00 1,241.90 9/8/2005 1,239.50 1,242.70 1,236.00 1,238.20 SELL 9/9/2005 1,238.00 1,250.30 1,237.20 1,248.50 BUY 9/12/2005 1,249.00 1,250.90 1,245.50 1,246.60 SELL 9/13/2005 1,246.10 1,246.60 1,237.70 1,238.90 9/14/2005 1,238.80 1,242.00 1,232.20 1,234.60 9/15/2005 1,234.50 1,238.50 1,230.70 1,233.50 9/16/2005 1,233.50 1,244.70 1,232.80 1,242.30 BUY 9/19/2005 1,242.60 1,242.70 1,233.50 1,238.00 SELL 9/20/2005 1,237.80 1,242.80 1,225.60 1,227.60 9/21/2005 1,227.40 1,229.30 1,214.50 1,215.90 9/22/2005 1,215.90 1,222.70 1,211.00 1,219.80 BUY 9/23/2005 1,219.60 1,224.80 1,215.10 1,220.50 9/26/2005 1,224.20 1,229.00 1,217.00 1,221.50 9/27/2005 1,221.50 1,226.00 1,216.20 1,221.70 9/28/2005 1,221.60 1,226.70 1,217.70 1,222.80 9/29/2005 1,222.40 1,234.30 1,216.30 1,231.80 9/30/2005 1,231.60 1,235.80 1,230.00 1,234.30 10/3/2005 1,235.30 1,238.80 1,229.90 1,231.80 SELL 10/4/2005 1,231.50 1,235.50 1,216.60 1,216.80 10/5/2005 1,216.80 1,218.30 1,200.20 1,200.70 10/6/2005 1,200.80 1,206.90 1,186.00 1,196.90 10/7/2005 1,197.00 1,204.50 1,195.20 1,200.00 BUY 10/10/200 1,199.70 1,204.30 1,189.60 1,190.00 10/11/200 1,190.00 1,198.00 1,187.30 1,188.40 10/12/200 1,189.20 1,194.70 1,177.60 1,178.50 10/13/200 1,179.00 1,184.30 1,172.00 1,178.10 10/14/200 1,178.50 1,191.50 1,178.40 1,189.90 BUY 10/17/200 1,189.80 1,195.50 1,186.50 1,194.20 10/18/200 1,193.90 1,196.70 1,181.00 1,181.40 SELL 10/19/200 1,182.00 1,200.80 1,174.00 1,200.40 10/20/200 1,199.10 1,200.80 1,175.70 1,179.50 10/21/200 1,179.80 1,190.00 1,175.90 1,183.80 10/24/200 1,183.80 1,203.30 1,183.30 1,202.40 10/25/200 1,202.00 1,204.70 1,192.00 1,198.90 SELL 10/26/200 1,198.80 1,207.00 1,193.90 1,196.00 10/27/200 1,195.40 1,196.10 1,181.00 1,182.50 10/28/200 1,181.30 1,201.20 1,179.50 1,199.70 BUY 10/31/200 1,199.80 1,214.50 1,199.70 1,209.80 11/1/2005 1,203.90 1,210.50 1,203.00 1,206.30 11/2/2005 1,206.10 1,218.50 1,202.50 1,218.10 11/3/2005 1,218.00 1,227.70 1,216.00 1,223.70 11/4/2005 1,223.50 1,225.70 1,216.70 1,222.00 SELL 11/7/2005 1,222.40 1,227.00 1,219.50 1,222.80 11/8/2005 1,222.70 1,224.30 1,218.50 1,222.80 11/9/2005 1,222.50 1,229.50 1,218.80 1,223.70 11/10/200 1,223.50 1,235.30 1,217.50 1,233.70 11/11/200 1,233.70 1,238.60 1,232.50 1,238.00 11/14/200 1,238.10 1,240.10 1,234.10 1,237.20 SELL 11/15/200 1,237.20 1,240.80 1,228.60 1,232.50 11/16/200 1,232.30 1,235.00 1,229.50 1,234.70 11/17/200 1,234.40 1,246.00 1,233.80 1,245.20 11/18/200 1,245.00 1,252.20 1,242.50 1,250.20 11/21/200 1,250.20 1,258.30 1,248.60 1,257.00 11/22/200 1,256.80 1,263.90 1,253.10 1,262.10 11/23/200 1,261.50 1,272.70 1,261.00 1,268.60 11/25/200 1,268.30 1,270.60 1,265.90 1,270.10 11/28/200 1,269.70 1,273.10 1,258.80 1,260.90 11/29/200 1,260.80 1,267.80 1,259.00 1,259.30 11/30/200 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Now that's a better explanation!
Enter this array formula in say H2 =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($F$1:$F$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) as an array formula, commit it with Ctrl-Shift-Enter, then copy down Hope we have got it now! -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... I need some assistance with this one: In my sheet below I have certain buy and sell signals for this commodity. I used an =if(and()) statement with about 4 logical steps to come up with these buy and sell points. You can't see the formula because I have taken it out. But my next step which I don't understand is how to write a formula to get me out if the price goes against me. So lets take 7/19/2005 BUY signal for an example. I need to find a way to have the formula read that when I get a BUY signal take the close price that period and minus say 5 from that. Now if the low from the next period or however many of periods it is until you get a sell signal goes below that number it should read "STOP" and you exit the position. The same would be for the SELL signal but instead of minus you would add it to the close price and see if the HIGH periods go through that number. My problem is is there is a way to keep a running column with each new period changing to let you know if those parameters have been hit or not. I will be happy to answer any questions if this is not clear. I thought about =if then statements= but not sure on how to write all of this. Thank you again. NOel Date-SPH Open High Low Close BUY SELL 7/1/2005 1,204.50 1,207.50 1,202.50 1,206.00 7/5/2005 1,202.00 1,216.50 1,202.00 1,215.10 7/6/2005 1,215.00 1,216.10 1,204.00 1,204.70 7/7/2005 1,191.70 1,208.80 1,191.70 1,208.80 7/8/2005 1,215.00 1,223.50 1,207.50 1,222.50 7/11/2005 1,225.00 1,230.50 1,224.00 1,229.60 7/12/2005 1,229.50 1,235.00 1,227.00 1,231.80 7/13/2005 1,230.40 1,234.50 1,230.30 1,234.30 7/14/2005 1,243.00 1,243.30 1,234.30 1,238.10 7/15/2005 1,234.50 1,239.50 1,234.00 1,237.60 7/18/2005 1,235.00 1,235.00 1,230.50 1,232.80 7/19/2005 1,237.00 1,240.00 1,234.00 1,239.60 BUY 7/20/2005 1,235.70 1,247.00 1,232.70 1,242.90 7/21/2005 1,244.00 1,244.00 1,234.50 1,236.40 7/22/2005 1,239.00 1,244.00 1,235.50 1,243.40 7/25/2005 1,243.20 1,247.50 1,236.50 1,240.50 7/26/2005 1,239.50 1,243.00 1,239.00 1,241.40 7/27/2005 1,239.90 1,246.50 1,238.80 1,246.30 7/28/2005 1,249.70 1,254.00 1,245.00 1,253.60 7/29/2005 1,253.50 1,254.00 1,243.40 1,243.40 SELL 8/1/2005 1,246.50 1,248.00 1,243.50 1,244.20 8/2/2005 1,248.00 1,253.50 1,248.00 1,251.80 8/3/2005 1,250.00 1,254.50 1,250.00 1,254.50 8/4/2005 1,250.00 1,250.00 1,243.50 1,244.60 SELL 8/5/2005 1,241.00 1,242.00 1,233.60 1,236.40 8/8/2005 1,238.50 1,241.00 1,231.50 1,233.90 8/9/2005 1,239.50 1,242.50 1,236.00 1,241.00 BUY 8/10/2005 1,246.80 1,250.50 1,235.50 1,240.50 SELL 8/11/2005 1,239.00 1,246.50 1,237.20 1,245.20 8/12/2005 1,242.00 1,243.50 1,235.00 1,237.70 8/15/2005 1,238.80 1,244.70 1,235.20 1,244.20 BUY 8/16/2005 1,239.30 1,240.20 1,227.00 1,228.10 SELL 8/17/2005 1,227.30 1,233.60 1,226.50 1,228.10 8/18/2005 1,225.00 1,231.50 1,225.00 1,227.60 8/19/2005 1,232.30 1,233.50 1,228.20 1,230.40 8/22/2005 1,230.80 1,237.00 1,225.00 1,230.70 8/23/2005 1,228.60 1,231.00 1,222.50 1,228.00 8/24/2005 1,223.30 1,232.00 1,216.00 1,216.40 8/25/2005 1,218.60 1,221.00 1,217.10 1,220.20 8/26/2005 1,219.30 1,219.30 1,212.00 1,212.40 8/29/2005 1,208.50 1,222.20 1,208.50 1,221.00 BUY 8/30/2005 1,216.60 1,216.60 1,208.50 1,215.00 8/31/2005 1,217.00 1,230.30 1,211.50 1,227.80 9/1/2005 1,228.20 1,234.50 1,223.80 1,227.90 9/2/2005 1,231.90 1,232.00 1,224.00 1,225.90 9/6/2005 1,230.00 1,241.00 1,230.00 1,240.50 9/7/2005 1,239.50 1,244.00 1,238.00 1,241.90 9/8/2005 1,239.50 1,242.70 1,236.00 1,238.20 SELL 9/9/2005 1,238.00 1,250.30 1,237.20 1,248.50 BUY 9/12/2005 1,249.00 1,250.90 1,245.50 1,246.60 SELL 9/13/2005 1,246.10 1,246.60 1,237.70 1,238.90 9/14/2005 1,238.80 1,242.00 1,232.20 1,234.60 9/15/2005 1,234.50 1,238.50 1,230.70 1,233.50 9/16/2005 1,233.50 1,244.70 1,232.80 1,242.30 BUY 9/19/2005 1,242.60 1,242.70 1,233.50 1,238.00 SELL 9/20/2005 1,237.80 1,242.80 1,225.60 1,227.60 9/21/2005 1,227.40 1,229.30 1,214.50 1,215.90 9/22/2005 1,215.90 1,222.70 1,211.00 1,219.80 BUY 9/23/2005 1,219.60 1,224.80 1,215.10 1,220.50 9/26/2005 1,224.20 1,229.00 1,217.00 1,221.50 9/27/2005 1,221.50 1,226.00 1,216.20 1,221.70 9/28/2005 1,221.60 1,226.70 1,217.70 1,222.80 9/29/2005 1,222.40 1,234.30 1,216.30 1,231.80 9/30/2005 1,231.60 1,235.80 1,230.00 1,234.30 10/3/2005 1,235.30 1,238.80 1,229.90 1,231.80 SELL 10/4/2005 1,231.50 1,235.50 1,216.60 1,216.80 10/5/2005 1,216.80 1,218.30 1,200.20 1,200.70 10/6/2005 1,200.80 1,206.90 1,186.00 1,196.90 10/7/2005 1,197.00 1,204.50 1,195.20 1,200.00 BUY 10/10/200 1,199.70 1,204.30 1,189.60 1,190.00 10/11/200 1,190.00 1,198.00 1,187.30 1,188.40 10/12/200 1,189.20 1,194.70 1,177.60 1,178.50 10/13/200 1,179.00 1,184.30 1,172.00 1,178.10 10/14/200 1,178.50 1,191.50 1,178.40 1,189.90 BUY 10/17/200 1,189.80 1,195.50 1,186.50 1,194.20 10/18/200 1,193.90 1,196.70 1,181.00 1,181.40 SELL 10/19/200 1,182.00 1,200.80 1,174.00 1,200.40 10/20/200 1,199.10 1,200.80 1,175.70 1,179.50 10/21/200 1,179.80 1,190.00 1,175.90 1,183.80 10/24/200 1,183.80 1,203.30 1,183.30 1,202.40 10/25/200 1,202.00 1,204.70 1,192.00 1,198.90 SELL 10/26/200 1,198.80 1,207.00 1,193.90 1,196.00 10/27/200 1,195.40 1,196.10 1,181.00 1,182.50 10/28/200 1,181.30 1,201.20 1,179.50 1,199.70 BUY 10/31/200 1,199.80 1,214.50 1,199.70 1,209.80 11/1/2005 1,203.90 1,210.50 1,203.00 1,206.30 11/2/2005 1,206.10 1,218.50 1,202.50 1,218.10 11/3/2005 1,218.00 1,227.70 1,216.00 1,223.70 11/4/2005 1,223.50 1,225.70 1,216.70 1,222.00 SELL 11/7/2005 1,222.40 1,227.00 1,219.50 1,222.80 11/8/2005 1,222.70 1,224.30 1,218.50 1,222.80 11/9/2005 1,222.50 1,229.50 1,218.80 1,223.70 11/10/200 1,223.50 1,235.30 1,217.50 1,233.70 11/11/200 1,233.70 1,238.60 1,232.50 1,238.00 11/14/200 1,238.10 1,240.10 1,234.10 1,237.20 SELL 11/15/200 1,237.20 1,240.80 1,228.60 1,232.50 11/16/200 1,232.30 1,235.00 1,229.50 1,234.70 11/17/200 1,234.40 1,246.00 1,233.80 1,245.20 11/18/200 1,245.00 1,252.20 1,242.50 1,250.20 11/21/200 1,250.20 1,258.30 1,248.60 1,257.00 11/22/200 1,256.80 1,263.90 1,253.10 1,262.10 11/23/200 1,261.50 1,272.70 1,261.00 1,268.60 11/25/200 1,268.30 1,270.60 1,265.90 1,270.10 11/28/200 1,269.70 1,273.10 1,258.80 1,260.90 11/29/200 1,260.80 1,267.80 1,259.00 1,259.30 11/30/200 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Thank you Bob,
I will try this, could you tell me what it means to commit it with Ctrl-SHift-Enter please. Thanks again. If this makes money I will let you know my secret. Noel "Bob Phillips" wrote: Now that's a better explanation! Enter this array formula in say H2 =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($F$1:$F$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) as an array formula, commit it with Ctrl-Shift-Enter, then copy down Hope we have got it now! -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... I need some assistance with this one: In my sheet below I have certain buy and sell signals for this commodity. I used an =if(and()) statement with about 4 logical steps to come up with these buy and sell points. You can't see the formula because I have taken it out. But my next step which I don't understand is how to write a formula to get me out if the price goes against me. So lets take 7/19/2005 BUY signal for an example. I need to find a way to have the formula read that when I get a BUY signal take the close price that period and minus say 5 from that. Now if the low from the next period or however many of periods it is until you get a sell signal goes below that number it should read "STOP" and you exit the position. The same would be for the SELL signal but instead of minus you would add it to the close price and see if the HIGH periods go through that number. My problem is is there is a way to keep a running column with each new period changing to let you know if those parameters have been hit or not. I will be happy to answer any questions if this is not clear. I thought about =if then statements= but not sure on how to write all of this. Thank you again. NOel Date-SPH Open High Low Close BUY SELL 7/1/2005 1,204.50 1,207.50 1,202.50 1,206.00 7/5/2005 1,202.00 1,216.50 1,202.00 1,215.10 7/6/2005 1,215.00 1,216.10 1,204.00 1,204.70 7/7/2005 1,191.70 1,208.80 1,191.70 1,208.80 7/8/2005 1,215.00 1,223.50 1,207.50 1,222.50 7/11/2005 1,225.00 1,230.50 1,224.00 1,229.60 7/12/2005 1,229.50 1,235.00 1,227.00 1,231.80 7/13/2005 1,230.40 1,234.50 1,230.30 1,234.30 7/14/2005 1,243.00 1,243.30 1,234.30 1,238.10 7/15/2005 1,234.50 1,239.50 1,234.00 1,237.60 7/18/2005 1,235.00 1,235.00 1,230.50 1,232.80 7/19/2005 1,237.00 1,240.00 1,234.00 1,239.60 BUY 7/20/2005 1,235.70 1,247.00 1,232.70 1,242.90 7/21/2005 1,244.00 1,244.00 1,234.50 1,236.40 7/22/2005 1,239.00 1,244.00 1,235.50 1,243.40 7/25/2005 1,243.20 1,247.50 1,236.50 1,240.50 7/26/2005 1,239.50 1,243.00 1,239.00 1,241.40 7/27/2005 1,239.90 1,246.50 1,238.80 1,246.30 7/28/2005 1,249.70 1,254.00 1,245.00 1,253.60 7/29/2005 1,253.50 1,254.00 1,243.40 1,243.40 SELL 8/1/2005 1,246.50 1,248.00 1,243.50 1,244.20 8/2/2005 1,248.00 1,253.50 1,248.00 1,251.80 8/3/2005 1,250.00 1,254.50 1,250.00 1,254.50 8/4/2005 1,250.00 1,250.00 1,243.50 1,244.60 SELL 8/5/2005 1,241.00 1,242.00 1,233.60 1,236.40 8/8/2005 1,238.50 1,241.00 1,231.50 1,233.90 8/9/2005 1,239.50 1,242.50 1,236.00 1,241.00 BUY 8/10/2005 1,246.80 1,250.50 1,235.50 1,240.50 SELL 8/11/2005 1,239.00 1,246.50 1,237.20 1,245.20 8/12/2005 1,242.00 1,243.50 1,235.00 1,237.70 8/15/2005 1,238.80 1,244.70 1,235.20 1,244.20 BUY 8/16/2005 1,239.30 1,240.20 1,227.00 1,228.10 SELL 8/17/2005 1,227.30 1,233.60 1,226.50 1,228.10 8/18/2005 1,225.00 1,231.50 1,225.00 1,227.60 8/19/2005 1,232.30 1,233.50 1,228.20 1,230.40 8/22/2005 1,230.80 1,237.00 1,225.00 1,230.70 8/23/2005 1,228.60 1,231.00 1,222.50 1,228.00 8/24/2005 1,223.30 1,232.00 1,216.00 1,216.40 8/25/2005 1,218.60 1,221.00 1,217.10 1,220.20 8/26/2005 1,219.30 1,219.30 1,212.00 1,212.40 8/29/2005 1,208.50 1,222.20 1,208.50 1,221.00 BUY 8/30/2005 1,216.60 1,216.60 1,208.50 1,215.00 8/31/2005 1,217.00 1,230.30 1,211.50 1,227.80 9/1/2005 1,228.20 1,234.50 1,223.80 1,227.90 9/2/2005 1,231.90 1,232.00 1,224.00 1,225.90 9/6/2005 1,230.00 1,241.00 1,230.00 1,240.50 9/7/2005 1,239.50 1,244.00 1,238.00 1,241.90 9/8/2005 1,239.50 1,242.70 1,236.00 1,238.20 SELL 9/9/2005 1,238.00 1,250.30 1,237.20 1,248.50 BUY 9/12/2005 1,249.00 1,250.90 1,245.50 1,246.60 SELL 9/13/2005 1,246.10 1,246.60 1,237.70 1,238.90 9/14/2005 1,238.80 1,242.00 1,232.20 1,234.60 9/15/2005 1,234.50 1,238.50 1,230.70 1,233.50 9/16/2005 1,233.50 1,244.70 1,232.80 1,242.30 BUY 9/19/2005 1,242.60 1,242.70 1,233.50 1,238.00 SELL 9/20/2005 1,237.80 1,242.80 1,225.60 1,227.60 9/21/2005 1,227.40 1,229.30 1,214.50 1,215.90 9/22/2005 1,215.90 1,222.70 1,211.00 1,219.80 BUY 9/23/2005 1,219.60 1,224.80 1,215.10 1,220.50 9/26/2005 1,224.20 1,229.00 1,217.00 1,221.50 9/27/2005 1,221.50 1,226.00 1,216.20 1,221.70 9/28/2005 1,221.60 1,226.70 1,217.70 1,222.80 9/29/2005 1,222.40 1,234.30 1,216.30 1,231.80 9/30/2005 1,231.60 1,235.80 1,230.00 1,234.30 10/3/2005 1,235.30 1,238.80 1,229.90 1,231.80 SELL 10/4/2005 1,231.50 1,235.50 1,216.60 1,216.80 10/5/2005 1,216.80 1,218.30 1,200.20 1,200.70 10/6/2005 1,200.80 1,206.90 1,186.00 1,196.90 10/7/2005 1,197.00 1,204.50 1,195.20 1,200.00 BUY 10/10/200 1,199.70 1,204.30 1,189.60 1,190.00 10/11/200 1,190.00 1,198.00 1,187.30 1,188.40 10/12/200 1,189.20 1,194.70 1,177.60 1,178.50 10/13/200 1,179.00 1,184.30 1,172.00 1,178.10 10/14/200 1,178.50 1,191.50 1,178.40 1,189.90 BUY 10/17/200 1,189.80 1,195.50 1,186.50 1,194.20 10/18/200 1,193.90 1,196.70 1,181.00 1,181.40 SELL 10/19/200 1,182.00 1,200.80 1,174.00 1,200.40 10/20/200 1,199.10 1,200.80 1,175.70 1,179.50 10/21/200 1,179.80 1,190.00 1,175.90 1,183.80 10/24/200 1,183.80 1,203.30 1,183.30 1,202.40 10/25/200 1,202.00 1,204.70 1,192.00 1,198.90 SELL 10/26/200 1,198.80 1,207.00 1,193.90 1,196.00 10/27/200 1,195.40 1,196.10 1,181.00 1,182.50 10/28/200 1,181.30 1,201.20 1,179.50 1,199.70 BUY 10/31/200 1,199.80 1,214.50 1,199.70 1,209.80 11/1/2005 1,203.90 1,210.50 1,203.00 1,206.30 11/2/2005 1,206.10 1,218.50 1,202.50 1,218.10 11/3/2005 1,218.00 1,227.70 1,216.00 1,223.70 11/4/2005 1,223.50 1,225.70 1,216.70 1,222.00 SELL 11/7/2005 1,222.40 1,227.00 1,219.50 1,222.80 11/8/2005 1,222.70 1,224.30 1,218.50 1,222.80 11/9/2005 1,222.50 1,229.50 1,218.80 1,223.70 11/10/200 1,223.50 1,235.30 1,217.50 1,233.70 11/11/200 1,233.70 1,238.60 1,232.50 1,238.00 11/14/200 1,238.10 1,240.10 1,234.10 1,237.20 SELL 11/15/200 1,237.20 1,240.80 1,228.60 1,232.50 11/16/200 1,232.30 1,235.00 1,229.50 1,234.70 11/17/200 1,234.40 1,246.00 1,233.80 1,245.20 11/18/200 1,245.00 1,252.20 1,242.50 1,250.20 11/21/200 1,250.20 1,258.30 1,248.60 1,257.00 11/22/200 1,256.80 1,263.90 1,253.10 1,262.10 11/23/200 1,261.50 1,272.70 1,261.00 1,268.60 11/25/200 1,268.30 1,270.60 1,265.90 1,270.10 11/28/200 1,269.70 1,273.10 1,258.80 1,260.90 11/29/200 1,260.80 1,267.80 1,259.00 1,259.30 11/30/200 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
HI Again Bob,
THanks for your help. I inputed all of that information and unfortunately I don't get any values or words in column h or i. Where i thought i was suppose to have the word stop if the market went against me. I appreciate this and thank you for you time. Noel "Bob Phillips" wrote: Now that's a better explanation! Enter this array formula in say H2 =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($F$1:$F$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) as an array formula, commit it with Ctrl-Shift-Enter, then copy down Hope we have got it now! -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... I need some assistance with this one: In my sheet below I have certain buy and sell signals for this commodity. I used an =if(and()) statement with about 4 logical steps to come up with these buy and sell points. You can't see the formula because I have taken it out. But my next step which I don't understand is how to write a formula to get me out if the price goes against me. So lets take 7/19/2005 BUY signal for an example. I need to find a way to have the formula read that when I get a BUY signal take the close price that period and minus say 5 from that. Now if the low from the next period or however many of periods it is until you get a sell signal goes below that number it should read "STOP" and you exit the position. The same would be for the SELL signal but instead of minus you would add it to the close price and see if the HIGH periods go through that number. My problem is is there is a way to keep a running column with each new period changing to let you know if those parameters have been hit or not. I will be happy to answer any questions if this is not clear. I thought about =if then statements= but not sure on how to write all of this. Thank you again. NOel Date-SPH Open High Low Close BUY SELL 7/1/2005 1,204.50 1,207.50 1,202.50 1,206.00 7/5/2005 1,202.00 1,216.50 1,202.00 1,215.10 7/6/2005 1,215.00 1,216.10 1,204.00 1,204.70 7/7/2005 1,191.70 1,208.80 1,191.70 1,208.80 7/8/2005 1,215.00 1,223.50 1,207.50 1,222.50 7/11/2005 1,225.00 1,230.50 1,224.00 1,229.60 7/12/2005 1,229.50 1,235.00 1,227.00 1,231.80 7/13/2005 1,230.40 1,234.50 1,230.30 1,234.30 7/14/2005 1,243.00 1,243.30 1,234.30 1,238.10 7/15/2005 1,234.50 1,239.50 1,234.00 1,237.60 7/18/2005 1,235.00 1,235.00 1,230.50 1,232.80 7/19/2005 1,237.00 1,240.00 1,234.00 1,239.60 BUY 7/20/2005 1,235.70 1,247.00 1,232.70 1,242.90 7/21/2005 1,244.00 1,244.00 1,234.50 1,236.40 7/22/2005 1,239.00 1,244.00 1,235.50 1,243.40 7/25/2005 1,243.20 1,247.50 1,236.50 1,240.50 7/26/2005 1,239.50 1,243.00 1,239.00 1,241.40 7/27/2005 1,239.90 1,246.50 1,238.80 1,246.30 7/28/2005 1,249.70 1,254.00 1,245.00 1,253.60 7/29/2005 1,253.50 1,254.00 1,243.40 1,243.40 SELL 8/1/2005 1,246.50 1,248.00 1,243.50 1,244.20 8/2/2005 1,248.00 1,253.50 1,248.00 1,251.80 8/3/2005 1,250.00 1,254.50 1,250.00 1,254.50 8/4/2005 1,250.00 1,250.00 1,243.50 1,244.60 SELL 8/5/2005 1,241.00 1,242.00 1,233.60 1,236.40 8/8/2005 1,238.50 1,241.00 1,231.50 1,233.90 8/9/2005 1,239.50 1,242.50 1,236.00 1,241.00 BUY 8/10/2005 1,246.80 1,250.50 1,235.50 1,240.50 SELL 8/11/2005 1,239.00 1,246.50 1,237.20 1,245.20 8/12/2005 1,242.00 1,243.50 1,235.00 1,237.70 8/15/2005 1,238.80 1,244.70 1,235.20 1,244.20 BUY 8/16/2005 1,239.30 1,240.20 1,227.00 1,228.10 SELL 8/17/2005 1,227.30 1,233.60 1,226.50 1,228.10 8/18/2005 1,225.00 1,231.50 1,225.00 1,227.60 8/19/2005 1,232.30 1,233.50 1,228.20 1,230.40 8/22/2005 1,230.80 1,237.00 1,225.00 1,230.70 8/23/2005 1,228.60 1,231.00 1,222.50 1,228.00 8/24/2005 1,223.30 1,232.00 1,216.00 1,216.40 8/25/2005 1,218.60 1,221.00 1,217.10 1,220.20 8/26/2005 1,219.30 1,219.30 1,212.00 1,212.40 8/29/2005 1,208.50 1,222.20 1,208.50 1,221.00 BUY 8/30/2005 1,216.60 1,216.60 1,208.50 1,215.00 8/31/2005 1,217.00 1,230.30 1,211.50 1,227.80 9/1/2005 1,228.20 1,234.50 1,223.80 1,227.90 9/2/2005 1,231.90 1,232.00 1,224.00 1,225.90 9/6/2005 1,230.00 1,241.00 1,230.00 1,240.50 9/7/2005 1,239.50 1,244.00 1,238.00 1,241.90 9/8/2005 1,239.50 1,242.70 1,236.00 1,238.20 SELL 9/9/2005 1,238.00 1,250.30 1,237.20 1,248.50 BUY 9/12/2005 1,249.00 1,250.90 1,245.50 1,246.60 SELL 9/13/2005 1,246.10 1,246.60 1,237.70 1,238.90 9/14/2005 1,238.80 1,242.00 1,232.20 1,234.60 9/15/2005 1,234.50 1,238.50 1,230.70 1,233.50 9/16/2005 1,233.50 1,244.70 1,232.80 1,242.30 BUY 9/19/2005 1,242.60 1,242.70 1,233.50 1,238.00 SELL 9/20/2005 1,237.80 1,242.80 1,225.60 1,227.60 9/21/2005 1,227.40 1,229.30 1,214.50 1,215.90 9/22/2005 1,215.90 1,222.70 1,211.00 1,219.80 BUY 9/23/2005 1,219.60 1,224.80 1,215.10 1,220.50 9/26/2005 1,224.20 1,229.00 1,217.00 1,221.50 9/27/2005 1,221.50 1,226.00 1,216.20 1,221.70 9/28/2005 1,221.60 1,226.70 1,217.70 1,222.80 9/29/2005 1,222.40 1,234.30 1,216.30 1,231.80 9/30/2005 1,231.60 1,235.80 1,230.00 1,234.30 10/3/2005 1,235.30 1,238.80 1,229.90 1,231.80 SELL 10/4/2005 1,231.50 1,235.50 1,216.60 1,216.80 10/5/2005 1,216.80 1,218.30 1,200.20 1,200.70 10/6/2005 1,200.80 1,206.90 1,186.00 1,196.90 10/7/2005 1,197.00 1,204.50 1,195.20 1,200.00 BUY 10/10/200 1,199.70 1,204.30 1,189.60 1,190.00 10/11/200 1,190.00 1,198.00 1,187.30 1,188.40 10/12/200 1,189.20 1,194.70 1,177.60 1,178.50 10/13/200 1,179.00 1,184.30 1,172.00 1,178.10 10/14/200 1,178.50 1,191.50 1,178.40 1,189.90 BUY 10/17/200 1,189.80 1,195.50 1,186.50 1,194.20 10/18/200 1,193.90 1,196.70 1,181.00 1,181.40 SELL 10/19/200 1,182.00 1,200.80 1,174.00 1,200.40 10/20/200 1,199.10 1,200.80 1,175.70 1,179.50 10/21/200 1,179.80 1,190.00 1,175.90 1,183.80 10/24/200 1,183.80 1,203.30 1,183.30 1,202.40 10/25/200 1,202.00 1,204.70 1,192.00 1,198.90 SELL 10/26/200 1,198.80 1,207.00 1,193.90 1,196.00 10/27/200 1,195.40 1,196.10 1,181.00 1,182.50 10/28/200 1,181.30 1,201.20 1,179.50 1,199.70 BUY 10/31/200 1,199.80 1,214.50 1,199.70 1,209.80 11/1/2005 1,203.90 1,210.50 1,203.00 1,206.30 11/2/2005 1,206.10 1,218.50 1,202.50 1,218.10 11/3/2005 1,218.00 1,227.70 1,216.00 1,223.70 11/4/2005 1,223.50 1,225.70 1,216.70 1,222.00 SELL 11/7/2005 1,222.40 1,227.00 1,219.50 1,222.80 11/8/2005 1,222.70 1,224.30 1,218.50 1,222.80 11/9/2005 1,222.50 1,229.50 1,218.80 1,223.70 11/10/200 1,223.50 1,235.30 1,217.50 1,233.70 11/11/200 1,233.70 1,238.60 1,232.50 1,238.00 11/14/200 1,238.10 1,240.10 1,234.10 1,237.20 SELL 11/15/200 1,237.20 1,240.80 1,228.60 1,232.50 11/16/200 1,232.30 1,235.00 1,229.50 1,234.70 11/17/200 1,234.40 1,246.00 1,233.80 1,245.20 11/18/200 1,245.00 1,252.20 1,242.50 1,250.20 11/21/200 1,250.20 1,258.30 1,248.60 1,257.00 11/22/200 1,256.80 1,263.90 1,253.10 1,262.10 11/23/200 1,261.50 1,272.70 1,261.00 1,268.60 11/25/200 1,268.30 1,270.60 1,265.90 1,270.10 11/28/200 1,269.70 1,273.10 1,258.80 1,260.90 11/29/200 1,260.80 1,267.80 1,259.00 1,259.30 11/30/200 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Here is a note Bob,
Do I have to change the array if the buy signal is in column F and the sell signal is in column g. Thanks for your help. NOel "zmr325" wrote: HI Again Bob, THanks for your help. I inputed all of that information and unfortunately I don't get any values or words in column h or i. Where i thought i was suppose to have the word stop if the market went against me. I appreciate this and thank you for you time. Noel "Bob Phillips" wrote: Now that's a better explanation! Enter this array formula in say H2 =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($F$1:$F$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) as an array formula, commit it with Ctrl-Shift-Enter, then copy down Hope we have got it now! -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... I need some assistance with this one: In my sheet below I have certain buy and sell signals for this commodity. I used an =if(and()) statement with about 4 logical steps to come up with these buy and sell points. You can't see the formula because I have taken it out. But my next step which I don't understand is how to write a formula to get me out if the price goes against me. So lets take 7/19/2005 BUY signal for an example. I need to find a way to have the formula read that when I get a BUY signal take the close price that period and minus say 5 from that. Now if the low from the next period or however many of periods it is until you get a sell signal goes below that number it should read "STOP" and you exit the position. The same would be for the SELL signal but instead of minus you would add it to the close price and see if the HIGH periods go through that number. My problem is is there is a way to keep a running column with each new period changing to let you know if those parameters have been hit or not. I will be happy to answer any questions if this is not clear. I thought about =if then statements= but not sure on how to write all of this. Thank you again. NOel Date-SPH Open High Low Close BUY SELL 7/1/2005 1,204.50 1,207.50 1,202.50 1,206.00 7/5/2005 1,202.00 1,216.50 1,202.00 1,215.10 7/6/2005 1,215.00 1,216.10 1,204.00 1,204.70 7/7/2005 1,191.70 1,208.80 1,191.70 1,208.80 7/8/2005 1,215.00 1,223.50 1,207.50 1,222.50 7/11/2005 1,225.00 1,230.50 1,224.00 1,229.60 7/12/2005 1,229.50 1,235.00 1,227.00 1,231.80 7/13/2005 1,230.40 1,234.50 1,230.30 1,234.30 7/14/2005 1,243.00 1,243.30 1,234.30 1,238.10 7/15/2005 1,234.50 1,239.50 1,234.00 1,237.60 7/18/2005 1,235.00 1,235.00 1,230.50 1,232.80 7/19/2005 1,237.00 1,240.00 1,234.00 1,239.60 BUY 7/20/2005 1,235.70 1,247.00 1,232.70 1,242.90 7/21/2005 1,244.00 1,244.00 1,234.50 1,236.40 7/22/2005 1,239.00 1,244.00 1,235.50 1,243.40 7/25/2005 1,243.20 1,247.50 1,236.50 1,240.50 7/26/2005 1,239.50 1,243.00 1,239.00 1,241.40 7/27/2005 1,239.90 1,246.50 1,238.80 1,246.30 7/28/2005 1,249.70 1,254.00 1,245.00 1,253.60 7/29/2005 1,253.50 1,254.00 1,243.40 1,243.40 SELL 8/1/2005 1,246.50 1,248.00 1,243.50 1,244.20 8/2/2005 1,248.00 1,253.50 1,248.00 1,251.80 8/3/2005 1,250.00 1,254.50 1,250.00 1,254.50 8/4/2005 1,250.00 1,250.00 1,243.50 1,244.60 SELL 8/5/2005 1,241.00 1,242.00 1,233.60 1,236.40 8/8/2005 1,238.50 1,241.00 1,231.50 1,233.90 8/9/2005 1,239.50 1,242.50 1,236.00 1,241.00 BUY 8/10/2005 1,246.80 1,250.50 1,235.50 1,240.50 SELL 8/11/2005 1,239.00 1,246.50 1,237.20 1,245.20 8/12/2005 1,242.00 1,243.50 1,235.00 1,237.70 8/15/2005 1,238.80 1,244.70 1,235.20 1,244.20 BUY 8/16/2005 1,239.30 1,240.20 1,227.00 1,228.10 SELL 8/17/2005 1,227.30 1,233.60 1,226.50 1,228.10 8/18/2005 1,225.00 1,231.50 1,225.00 1,227.60 8/19/2005 1,232.30 1,233.50 1,228.20 1,230.40 8/22/2005 1,230.80 1,237.00 1,225.00 1,230.70 8/23/2005 1,228.60 1,231.00 1,222.50 1,228.00 8/24/2005 1,223.30 1,232.00 1,216.00 1,216.40 8/25/2005 1,218.60 1,221.00 1,217.10 1,220.20 8/26/2005 1,219.30 1,219.30 1,212.00 1,212.40 8/29/2005 1,208.50 1,222.20 1,208.50 1,221.00 BUY 8/30/2005 1,216.60 1,216.60 1,208.50 1,215.00 8/31/2005 1,217.00 1,230.30 1,211.50 1,227.80 9/1/2005 1,228.20 1,234.50 1,223.80 1,227.90 9/2/2005 1,231.90 1,232.00 1,224.00 1,225.90 9/6/2005 1,230.00 1,241.00 1,230.00 1,240.50 9/7/2005 1,239.50 1,244.00 1,238.00 1,241.90 9/8/2005 1,239.50 1,242.70 1,236.00 1,238.20 SELL 9/9/2005 1,238.00 1,250.30 1,237.20 1,248.50 BUY 9/12/2005 1,249.00 1,250.90 1,245.50 1,246.60 SELL 9/13/2005 1,246.10 1,246.60 1,237.70 1,238.90 9/14/2005 1,238.80 1,242.00 1,232.20 1,234.60 9/15/2005 1,234.50 1,238.50 1,230.70 1,233.50 9/16/2005 1,233.50 1,244.70 1,232.80 1,242.30 BUY 9/19/2005 1,242.60 1,242.70 1,233.50 1,238.00 SELL 9/20/2005 1,237.80 1,242.80 1,225.60 1,227.60 9/21/2005 1,227.40 1,229.30 1,214.50 1,215.90 9/22/2005 1,215.90 1,222.70 1,211.00 1,219.80 BUY 9/23/2005 1,219.60 1,224.80 1,215.10 1,220.50 9/26/2005 1,224.20 1,229.00 1,217.00 1,221.50 9/27/2005 1,221.50 1,226.00 1,216.20 1,221.70 9/28/2005 1,221.60 1,226.70 1,217.70 1,222.80 9/29/2005 1,222.40 1,234.30 1,216.30 1,231.80 9/30/2005 1,231.60 1,235.80 1,230.00 1,234.30 10/3/2005 1,235.30 1,238.80 1,229.90 1,231.80 SELL 10/4/2005 1,231.50 1,235.50 1,216.60 1,216.80 10/5/2005 1,216.80 1,218.30 1,200.20 1,200.70 10/6/2005 1,200.80 1,206.90 1,186.00 1,196.90 10/7/2005 1,197.00 1,204.50 1,195.20 1,200.00 BUY 10/10/200 1,199.70 1,204.30 1,189.60 1,190.00 10/11/200 1,190.00 1,198.00 1,187.30 1,188.40 10/12/200 1,189.20 1,194.70 1,177.60 1,178.50 10/13/200 1,179.00 1,184.30 1,172.00 1,178.10 10/14/200 1,178.50 1,191.50 1,178.40 1,189.90 BUY 10/17/200 1,189.80 1,195.50 1,186.50 1,194.20 10/18/200 1,193.90 1,196.70 1,181.00 1,181.40 SELL 10/19/200 1,182.00 1,200.80 1,174.00 1,200.40 10/20/200 1,199.10 1,200.80 1,175.70 1,179.50 10/21/200 1,179.80 1,190.00 1,175.90 1,183.80 10/24/200 1,183.80 1,203.30 1,183.30 1,202.40 10/25/200 1,202.00 1,204.70 1,192.00 1,198.90 SELL 10/26/200 1,198.80 1,207.00 1,193.90 1,196.00 10/27/200 1,195.40 1,196.10 1,181.00 1,182.50 10/28/200 1,181.30 1,201.20 1,179.50 1,199.70 BUY 10/31/200 1,199.80 1,214.50 1,199.70 1,209.80 11/1/2005 1,203.90 1,210.50 1,203.00 1,206.30 11/2/2005 1,206.10 1,218.50 1,202.50 1,218.10 11/3/2005 1,218.00 1,227.70 1,216.00 1,223.70 11/4/2005 1,223.50 1,225.70 1,216.70 1,222.00 SELL 11/7/2005 1,222.40 1,227.00 1,219.50 1,222.80 11/8/2005 1,222.70 1,224.30 1,218.50 1,222.80 11/9/2005 1,222.50 1,229.50 1,218.80 1,223.70 11/10/200 1,223.50 1,235.30 1,217.50 1,233.70 11/11/200 1,233.70 1,238.60 1,232.50 1,238.00 11/14/200 1,238.10 1,240.10 1,234.10 1,237.20 SELL 11/15/200 1,237.20 1,240.80 1,228.60 1,232.50 11/16/200 1,232.30 1,235.00 1,229.50 1,234.70 11/17/200 1,234.40 1,246.00 1,233.80 1,245.20 11/18/200 1,245.00 1,252.20 1,242.50 1,250.20 11/21/200 1,250.20 1,258.30 1,248.60 1,257.00 11/22/200 1,256.80 1,263.90 1,253.10 1,262.10 11/23/200 1,261.50 1,272.70 1,261.00 1,268.60 11/25/200 1,268.30 1,270.60 1,265.90 1,270.10 11/28/200 1,269.70 1,273.10 1,258.80 1,260.90 11/29/200 1,260.80 1,267.80 1,259.00 1,259.30 11/30/200 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
If the BUY and SELL are in different columns it is a more complex formula.
Could you use column F for both? When you enter it, rather than just hitting Enter, hit Ctrl-Shift-Enter all together. Excel will then surround the formula with {} in the formula bar. Every time you edit the formula, the {} disappears so you will need to re-commit. I got results with your data. My STOP may be of-logic, but if you can at leats get some STOP signals, you will be able to tell me how it is off. Good luck. -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... Here is a note Bob, Do I have to change the array if the buy signal is in column F and the sell signal is in column g. Thanks for your help. NOel "zmr325" wrote: HI Again Bob, THanks for your help. I inputed all of that information and unfortunately I don't get any values or words in column h or i. Where i thought i was suppose to have the word stop if the market went against me. I appreciate this and thank you for you time. Noel "Bob Phillips" wrote: Now that's a better explanation! Enter this array formula in say H2 =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($F$1:$F$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) as an array formula, commit it with Ctrl-Shift-Enter, then copy down Hope we have got it now! -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... I need some assistance with this one: In my sheet below I have certain buy and sell signals for this commodity. I used an =if(and()) statement with about 4 logical steps to come up with these buy and sell points. You can't see the formula because I have taken it out. But my next step which I don't understand is how to write a formula to get me out if the price goes against me. So lets take 7/19/2005 BUY signal for an example. I need to find a way to have the formula read that when I get a BUY signal take the close price that period and minus say 5 from that. Now if the low from the next period or however many of periods it is until you get a sell signal goes below that number it should read "STOP" and you exit the position. The same would be for the SELL signal but instead of minus you would add it to the close price and see if the HIGH periods go through that number. My problem is is there is a way to keep a running column with each new period changing to let you know if those parameters have been hit or not. I will be happy to answer any questions if this is not clear. I thought about =if then statements= but not sure on how to write all of this. Thank you again. NOel Date-SPH Open High Low Close BUY SELL 7/1/2005 1,204.50 1,207.50 1,202.50 1,206.00 7/5/2005 1,202.00 1,216.50 1,202.00 1,215.10 7/6/2005 1,215.00 1,216.10 1,204.00 1,204.70 7/7/2005 1,191.70 1,208.80 1,191.70 1,208.80 7/8/2005 1,215.00 1,223.50 1,207.50 1,222.50 7/11/2005 1,225.00 1,230.50 1,224.00 1,229.60 7/12/2005 1,229.50 1,235.00 1,227.00 1,231.80 7/13/2005 1,230.40 1,234.50 1,230.30 1,234.30 7/14/2005 1,243.00 1,243.30 1,234.30 1,238.10 7/15/2005 1,234.50 1,239.50 1,234.00 1,237.60 7/18/2005 1,235.00 1,235.00 1,230.50 1,232.80 7/19/2005 1,237.00 1,240.00 1,234.00 1,239.60 BUY 7/20/2005 1,235.70 1,247.00 1,232.70 1,242.90 7/21/2005 1,244.00 1,244.00 1,234.50 1,236.40 7/22/2005 1,239.00 1,244.00 1,235.50 1,243.40 7/25/2005 1,243.20 1,247.50 1,236.50 1,240.50 7/26/2005 1,239.50 1,243.00 1,239.00 1,241.40 7/27/2005 1,239.90 1,246.50 1,238.80 1,246.30 7/28/2005 1,249.70 1,254.00 1,245.00 1,253.60 7/29/2005 1,253.50 1,254.00 1,243.40 1,243.40 SELL 8/1/2005 1,246.50 1,248.00 1,243.50 1,244.20 8/2/2005 1,248.00 1,253.50 1,248.00 1,251.80 8/3/2005 1,250.00 1,254.50 1,250.00 1,254.50 8/4/2005 1,250.00 1,250.00 1,243.50 1,244.60 SELL 8/5/2005 1,241.00 1,242.00 1,233.60 1,236.40 8/8/2005 1,238.50 1,241.00 1,231.50 1,233.90 8/9/2005 1,239.50 1,242.50 1,236.00 1,241.00 BUY 8/10/2005 1,246.80 1,250.50 1,235.50 1,240.50 SELL 8/11/2005 1,239.00 1,246.50 1,237.20 1,245.20 8/12/2005 1,242.00 1,243.50 1,235.00 1,237.70 8/15/2005 1,238.80 1,244.70 1,235.20 1,244.20 BUY 8/16/2005 1,239.30 1,240.20 1,227.00 1,228.10 SELL 8/17/2005 1,227.30 1,233.60 1,226.50 1,228.10 8/18/2005 1,225.00 1,231.50 1,225.00 1,227.60 8/19/2005 1,232.30 1,233.50 1,228.20 1,230.40 8/22/2005 1,230.80 1,237.00 1,225.00 1,230.70 8/23/2005 1,228.60 1,231.00 1,222.50 1,228.00 8/24/2005 1,223.30 1,232.00 1,216.00 1,216.40 8/25/2005 1,218.60 1,221.00 1,217.10 1,220.20 8/26/2005 1,219.30 1,219.30 1,212.00 1,212.40 8/29/2005 1,208.50 1,222.20 1,208.50 1,221.00 BUY 8/30/2005 1,216.60 1,216.60 1,208.50 1,215.00 8/31/2005 1,217.00 1,230.30 1,211.50 1,227.80 9/1/2005 1,228.20 1,234.50 1,223.80 1,227.90 9/2/2005 1,231.90 1,232.00 1,224.00 1,225.90 9/6/2005 1,230.00 1,241.00 1,230.00 1,240.50 9/7/2005 1,239.50 1,244.00 1,238.00 1,241.90 9/8/2005 1,239.50 1,242.70 1,236.00 1,238.20 SELL 9/9/2005 1,238.00 1,250.30 1,237.20 1,248.50 BUY 9/12/2005 1,249.00 1,250.90 1,245.50 1,246.60 SELL 9/13/2005 1,246.10 1,246.60 1,237.70 1,238.90 9/14/2005 1,238.80 1,242.00 1,232.20 1,234.60 9/15/2005 1,234.50 1,238.50 1,230.70 1,233.50 9/16/2005 1,233.50 1,244.70 1,232.80 1,242.30 BUY 9/19/2005 1,242.60 1,242.70 1,233.50 1,238.00 SELL 9/20/2005 1,237.80 1,242.80 1,225.60 1,227.60 9/21/2005 1,227.40 1,229.30 1,214.50 1,215.90 9/22/2005 1,215.90 1,222.70 1,211.00 1,219.80 BUY 9/23/2005 1,219.60 1,224.80 1,215.10 1,220.50 9/26/2005 1,224.20 1,229.00 1,217.00 1,221.50 9/27/2005 1,221.50 1,226.00 1,216.20 1,221.70 9/28/2005 1,221.60 1,226.70 1,217.70 1,222.80 9/29/2005 1,222.40 1,234.30 1,216.30 1,231.80 9/30/2005 1,231.60 1,235.80 1,230.00 1,234.30 10/3/2005 1,235.30 1,238.80 1,229.90 1,231.80 SELL 10/4/2005 1,231.50 1,235.50 1,216.60 1,216.80 10/5/2005 1,216.80 1,218.30 1,200.20 1,200.70 10/6/2005 1,200.80 1,206.90 1,186.00 1,196.90 10/7/2005 1,197.00 1,204.50 1,195.20 1,200.00 BUY 10/10/200 1,199.70 1,204.30 1,189.60 1,190.00 10/11/200 1,190.00 1,198.00 1,187.30 1,188.40 10/12/200 1,189.20 1,194.70 1,177.60 1,178.50 10/13/200 1,179.00 1,184.30 1,172.00 1,178.10 10/14/200 1,178.50 1,191.50 1,178.40 1,189.90 BUY 10/17/200 1,189.80 1,195.50 1,186.50 1,194.20 10/18/200 1,193.90 1,196.70 1,181.00 1,181.40 SELL 10/19/200 1,182.00 1,200.80 1,174.00 1,200.40 10/20/200 1,199.10 1,200.80 1,175.70 1,179.50 10/21/200 1,179.80 1,190.00 1,175.90 1,183.80 10/24/200 1,183.80 1,203.30 1,183.30 1,202.40 10/25/200 1,202.00 1,204.70 1,192.00 1,198.90 SELL 10/26/200 1,198.80 1,207.00 1,193.90 1,196.00 10/27/200 1,195.40 1,196.10 1,181.00 1,182.50 10/28/200 1,181.30 1,201.20 1,179.50 1,199.70 BUY 10/31/200 1,199.80 1,214.50 1,199.70 1,209.80 11/1/2005 1,203.90 1,210.50 1,203.00 1,206.30 11/2/2005 1,206.10 1,218.50 1,202.50 1,218.10 11/3/2005 1,218.00 1,227.70 1,216.00 1,223.70 11/4/2005 1,223.50 1,225.70 1,216.70 1,222.00 SELL 11/7/2005 1,222.40 1,227.00 1,219.50 1,222.80 11/8/2005 1,222.70 1,224.30 1,218.50 1,222.80 11/9/2005 1,222.50 1,229.50 1,218.80 1,223.70 11/10/200 1,223.50 1,235.30 1,217.50 1,233.70 11/11/200 1,233.70 1,238.60 1,232.50 1,238.00 11/14/200 1,238.10 1,240.10 1,234.10 1,237.20 SELL 11/15/200 1,237.20 1,240.80 1,228.60 1,232.50 11/16/200 1,232.30 1,235.00 1,229.50 1,234.70 11/17/200 1,234.40 1,246.00 1,233.80 1,245.20 11/18/200 1,245.00 1,252.20 1,242.50 1,250.20 11/21/200 1,250.20 1,258.30 1,248.60 1,257.00 11/22/200 1,256.80 1,263.90 1,253.10 1,262.10 11/23/200 1,261.50 1,272.70 1,261.00 1,268.60 11/25/200 1,268.30 1,270.60 1,265.90 1,270.10 11/28/200 1,269.70 1,273.10 1,258.80 1,260.90 11/29/200 1,260.80 1,267.80 1,259.00 1,259.30 11/30/200 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Good morning Bob,
I am sorry that the BUY and SELL are probably going to need to be in separate columns. I can try to play with them to see if I can combine but I don't think that that will be a good idea. I just got home from work, nightshift 8pm to 6am so I don't think that I am going to work on it until later this evening. I also got some stop info when I wrote the array but not sure if it is reading correctly. Here is a thought. I think that I have to have the buy stop off of the low price, this is because if in that period the close may not hit the stop price but the low definately has to or doesn't. Same for the sell stop using the high price for calculation, because the close may not touch the stop price but the high definately will or not. Thanks again for all of your help. NOel "Bob Phillips" wrote: If the BUY and SELL are in different columns it is a more complex formula. Could you use column F for both? When you enter it, rather than just hitting Enter, hit Ctrl-Shift-Enter all together. Excel will then surround the formula with {} in the formula bar. Every time you edit the formula, the {} disappears so you will need to re-commit. I got results with your data. My STOP may be of-logic, but if you can at leats get some STOP signals, you will be able to tell me how it is off. Good luck. -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... Here is a note Bob, Do I have to change the array if the buy signal is in column F and the sell signal is in column g. Thanks for your help. NOel "zmr325" wrote: HI Again Bob, THanks for your help. I inputed all of that information and unfortunately I don't get any values or words in column h or i. Where i thought i was suppose to have the word stop if the market went against me. I appreciate this and thank you for you time. Noel "Bob Phillips" wrote: Now that's a better explanation! Enter this array formula in say H2 =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($F$1:$F$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) as an array formula, commit it with Ctrl-Shift-Enter, then copy down Hope we have got it now! -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... I need some assistance with this one: In my sheet below I have certain buy and sell signals for this commodity. I used an =if(and()) statement with about 4 logical steps to come up with these buy and sell points. You can't see the formula because I have taken it out. But my next step which I don't understand is how to write a formula to get me out if the price goes against me. So lets take 7/19/2005 BUY signal for an example. I need to find a way to have the formula read that when I get a BUY signal take the close price that period and minus say 5 from that. Now if the low from the next period or however many of periods it is until you get a sell signal goes below that number it should read "STOP" and you exit the position. The same would be for the SELL signal but instead of minus you would add it to the close price and see if the HIGH periods go through that number. My problem is is there is a way to keep a running column with each new period changing to let you know if those parameters have been hit or not. I will be happy to answer any questions if this is not clear. I thought about =if then statements= but not sure on how to write all of this. Thank you again. NOel Date-SPH Open High Low Close BUY SELL 7/1/2005 1,204.50 1,207.50 1,202.50 1,206.00 7/5/2005 1,202.00 1,216.50 1,202.00 1,215.10 7/6/2005 1,215.00 1,216.10 1,204.00 1,204.70 7/7/2005 1,191.70 1,208.80 1,191.70 1,208.80 7/8/2005 1,215.00 1,223.50 1,207.50 1,222.50 7/11/2005 1,225.00 1,230.50 1,224.00 1,229.60 7/12/2005 1,229.50 1,235.00 1,227.00 1,231.80 7/13/2005 1,230.40 1,234.50 1,230.30 1,234.30 7/14/2005 1,243.00 1,243.30 1,234.30 1,238.10 7/15/2005 1,234.50 1,239.50 1,234.00 1,237.60 7/18/2005 1,235.00 1,235.00 1,230.50 1,232.80 7/19/2005 1,237.00 1,240.00 1,234.00 1,239.60 BUY 7/20/2005 1,235.70 1,247.00 1,232.70 1,242.90 7/21/2005 1,244.00 1,244.00 1,234.50 1,236.40 7/22/2005 1,239.00 1,244.00 1,235.50 1,243.40 7/25/2005 1,243.20 1,247.50 1,236.50 1,240.50 7/26/2005 1,239.50 1,243.00 1,239.00 1,241.40 7/27/2005 1,239.90 1,246.50 1,238.80 1,246.30 7/28/2005 1,249.70 1,254.00 1,245.00 1,253.60 7/29/2005 1,253.50 1,254.00 1,243.40 1,243.40 SELL 8/1/2005 1,246.50 1,248.00 1,243.50 1,244.20 8/2/2005 1,248.00 1,253.50 1,248.00 1,251.80 8/3/2005 1,250.00 1,254.50 1,250.00 1,254.50 8/4/2005 1,250.00 1,250.00 1,243.50 1,244.60 SELL 8/5/2005 1,241.00 1,242.00 1,233.60 1,236.40 8/8/2005 1,238.50 1,241.00 1,231.50 1,233.90 8/9/2005 1,239.50 1,242.50 1,236.00 1,241.00 BUY 8/10/2005 1,246.80 1,250.50 1,235.50 1,240.50 SELL 8/11/2005 1,239.00 1,246.50 1,237.20 1,245.20 8/12/2005 1,242.00 1,243.50 1,235.00 1,237.70 8/15/2005 1,238.80 1,244.70 1,235.20 1,244.20 BUY 8/16/2005 1,239.30 1,240.20 1,227.00 1,228.10 SELL 8/17/2005 1,227.30 1,233.60 1,226.50 1,228.10 8/18/2005 1,225.00 1,231.50 1,225.00 1,227.60 8/19/2005 1,232.30 1,233.50 1,228.20 1,230.40 8/22/2005 1,230.80 1,237.00 1,225.00 1,230.70 8/23/2005 1,228.60 1,231.00 1,222.50 1,228.00 8/24/2005 1,223.30 1,232.00 1,216.00 1,216.40 8/25/2005 1,218.60 1,221.00 1,217.10 1,220.20 8/26/2005 1,219.30 1,219.30 1,212.00 1,212.40 8/29/2005 1,208.50 1,222.20 1,208.50 1,221.00 BUY 8/30/2005 1,216.60 1,216.60 1,208.50 1,215.00 8/31/2005 1,217.00 1,230.30 1,211.50 1,227.80 9/1/2005 1,228.20 1,234.50 1,223.80 1,227.90 9/2/2005 1,231.90 1,232.00 1,224.00 1,225.90 9/6/2005 1,230.00 1,241.00 1,230.00 1,240.50 9/7/2005 1,239.50 1,244.00 1,238.00 1,241.90 9/8/2005 1,239.50 1,242.70 1,236.00 1,238.20 SELL 9/9/2005 1,238.00 1,250.30 1,237.20 1,248.50 BUY 9/12/2005 1,249.00 1,250.90 1,245.50 1,246.60 SELL 9/13/2005 1,246.10 1,246.60 1,237.70 1,238.90 9/14/2005 1,238.80 1,242.00 1,232.20 1,234.60 9/15/2005 1,234.50 1,238.50 1,230.70 1,233.50 9/16/2005 1,233.50 1,244.70 1,232.80 1,242.30 BUY 9/19/2005 1,242.60 1,242.70 1,233.50 1,238.00 SELL 9/20/2005 1,237.80 1,242.80 1,225.60 1,227.60 9/21/2005 1,227.40 1,229.30 1,214.50 1,215.90 9/22/2005 1,215.90 1,222.70 1,211.00 1,219.80 BUY 9/23/2005 1,219.60 1,224.80 1,215.10 1,220.50 9/26/2005 1,224.20 1,229.00 1,217.00 1,221.50 9/27/2005 1,221.50 1,226.00 1,216.20 1,221.70 9/28/2005 1,221.60 1,226.70 1,217.70 1,222.80 9/29/2005 1,222.40 1,234.30 1,216.30 1,231.80 9/30/2005 1,231.60 1,235.80 1,230.00 1,234.30 10/3/2005 1,235.30 1,238.80 1,229.90 1,231.80 SELL 10/4/2005 1,231.50 1,235.50 1,216.60 1,216.80 10/5/2005 1,216.80 1,218.30 1,200.20 1,200.70 10/6/2005 1,200.80 1,206.90 1,186.00 1,196.90 10/7/2005 1,197.00 1,204.50 1,195.20 1,200.00 BUY 10/10/200 1,199.70 1,204.30 1,189.60 1,190.00 10/11/200 1,190.00 1,198.00 1,187.30 1,188.40 10/12/200 1,189.20 1,194.70 1,177.60 1,178.50 10/13/200 1,179.00 1,184.30 1,172.00 1,178.10 10/14/200 1,178.50 1,191.50 1,178.40 1,189.90 BUY 10/17/200 1,189.80 1,195.50 1,186.50 1,194.20 10/18/200 1,193.90 1,196.70 1,181.00 1,181.40 SELL 10/19/200 1,182.00 1,200.80 1,174.00 1,200.40 10/20/200 1,199.10 1,200.80 1,175.70 1,179.50 10/21/200 1,179.80 1,190.00 1,175.90 1,183.80 10/24/200 1,183.80 1,203.30 1,183.30 1,202.40 10/25/200 1,202.00 1,204.70 1,192.00 1,198.90 SELL 10/26/200 1,198.80 1,207.00 1,193.90 1,196.00 10/27/200 1,195.40 1,196.10 1,181.00 1,182.50 10/28/200 1,181.30 1,201.20 1,179.50 1,199.70 BUY 10/31/200 1,199.80 1,214.50 1,199.70 1,209.80 11/1/2005 1,203.90 1,210.50 1,203.00 1,206.30 11/2/2005 1,206.10 1,218.50 1,202.50 1,218.10 11/3/2005 1,218.00 1,227.70 1,216.00 1,223.70 11/4/2005 1,223.50 1,225.70 1,216.70 1,222.00 SELL 11/7/2005 1,222.40 1,227.00 1,219.50 1,222.80 11/8/2005 1,222.70 1,224.30 1,218.50 1,222.80 11/9/2005 1,222.50 1,229.50 1,218.80 1,223.70 11/10/200 1,223.50 1,235.30 1,217.50 1,233.70 11/11/200 1,233.70 1,238.60 1,232.50 1,238.00 11/14/200 1,238.10 1,240.10 1,234.10 1,237.20 SELL 11/15/200 1,237.20 1,240.80 1,228.60 1,232.50 11/16/200 1,232.30 1,235.00 1,229.50 1,234.70 11/17/200 1,234.40 1,246.00 1,233.80 1,245.20 11/18/200 1,245.00 1,252.20 1,242.50 1,250.20 11/21/200 1,250.20 1,258.30 1,248.60 1,257.00 11/22/200 1,256.80 1,263.90 1,253.10 1,262.10 11/23/200 1,261.50 1,272.70 1,261.00 1,268.60 11/25/200 1,268.30 1,270.60 1,265.90 1,270.10 11/28/200 1,269.70 1,273.10 1,258.80 1,260.90 11/29/200 1,260.80 1,267.80 1,259.00 1,259.30 11/30/200 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Without reading the original post (or most the followups)....
Can't you just use Bob's suggested formula in one column. Then use a couple of other columns based on that single column: In G2: =if(f2="buy","buy","") In H2: =if(f2="sell,"sell","") You could even hide column F if you find it distracting. zmr325 wrote: Good morning Bob, I am sorry that the BUY and SELL are probably going to need to be in separate columns. I can try to play with them to see if I can combine but I don't think that that will be a good idea. I just got home from work, nightshift 8pm to 6am so I don't think that I am going to work on it until later this evening. I also got some stop info when I wrote the array but not sure if it is reading correctly. Here is a thought. I think that I have to have the buy stop off of the low price, this is because if in that period the close may not hit the stop price but the low definately has to or doesn't. Same for the sell stop using the high price for calculation, because the close may not touch the stop price but the high definately will or not. Thanks again for all of your help. NOel "Bob Phillips" wrote: If the BUY and SELL are in different columns it is a more complex formula. Could you use column F for both? When you enter it, rather than just hitting Enter, hit Ctrl-Shift-Enter all together. Excel will then surround the formula with {} in the formula bar. Every time you edit the formula, the {} disappears so you will need to re-commit. I got results with your data. My STOP may be of-logic, but if you can at leats get some STOP signals, you will be able to tell me how it is off. Good luck. -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... Here is a note Bob, Do I have to change the array if the buy signal is in column F and the sell signal is in column g. Thanks for your help. NOel "zmr325" wrote: HI Again Bob, THanks for your help. I inputed all of that information and unfortunately I don't get any values or words in column h or i. Where i thought i was suppose to have the word stop if the market went against me. I appreciate this and thank you for you time. Noel "Bob Phillips" wrote: Now that's a better explanation! Enter this array formula in say H2 =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($F$1:$F$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) as an array formula, commit it with Ctrl-Shift-Enter, then copy down Hope we have got it now! -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... I need some assistance with this one: In my sheet below I have certain buy and sell signals for this commodity. I used an =if(and()) statement with about 4 logical steps to come up with these buy and sell points. You can't see the formula because I have taken it out. But my next step which I don't understand is how to write a formula to get me out if the price goes against me. So lets take 7/19/2005 BUY signal for an example. I need to find a way to have the formula read that when I get a BUY signal take the close price that period and minus say 5 from that. Now if the low from the next period or however many of periods it is until you get a sell signal goes below that number it should read "STOP" and you exit the position. The same would be for the SELL signal but instead of minus you would add it to the close price and see if the HIGH periods go through that number. My problem is is there is a way to keep a running column with each new period changing to let you know if those parameters have been hit or not. I will be happy to answer any questions if this is not clear. I thought about =if then statements= but not sure on how to write all of this. Thank you again. NOel Date-SPH Open High Low Close BUY SELL 7/1/2005 1,204.50 1,207.50 1,202.50 1,206.00 7/5/2005 1,202.00 1,216.50 1,202.00 1,215.10 7/6/2005 1,215.00 1,216.10 1,204.00 1,204.70 7/7/2005 1,191.70 1,208.80 1,191.70 1,208.80 7/8/2005 1,215.00 1,223.50 1,207.50 1,222.50 7/11/2005 1,225.00 1,230.50 1,224.00 1,229.60 7/12/2005 1,229.50 1,235.00 1,227.00 1,231.80 7/13/2005 1,230.40 1,234.50 1,230.30 1,234.30 7/14/2005 1,243.00 1,243.30 1,234.30 1,238.10 7/15/2005 1,234.50 1,239.50 1,234.00 1,237.60 7/18/2005 1,235.00 1,235.00 1,230.50 1,232.80 7/19/2005 1,237.00 1,240.00 1,234.00 1,239.60 BUY 7/20/2005 1,235.70 1,247.00 1,232.70 1,242.90 7/21/2005 1,244.00 1,244.00 1,234.50 1,236.40 7/22/2005 1,239.00 1,244.00 1,235.50 1,243.40 7/25/2005 1,243.20 1,247.50 1,236.50 1,240.50 7/26/2005 1,239.50 1,243.00 1,239.00 1,241.40 7/27/2005 1,239.90 1,246.50 1,238.80 1,246.30 7/28/2005 1,249.70 1,254.00 1,245.00 1,253.60 7/29/2005 1,253.50 1,254.00 1,243.40 1,243.40 SELL 8/1/2005 1,246.50 1,248.00 1,243.50 1,244.20 8/2/2005 1,248.00 1,253.50 1,248.00 1,251.80 8/3/2005 1,250.00 1,254.50 1,250.00 1,254.50 8/4/2005 1,250.00 1,250.00 1,243.50 1,244.60 SELL 8/5/2005 1,241.00 1,242.00 1,233.60 1,236.40 8/8/2005 1,238.50 1,241.00 1,231.50 1,233.90 8/9/2005 1,239.50 1,242.50 1,236.00 1,241.00 BUY 8/10/2005 1,246.80 1,250.50 1,235.50 1,240.50 SELL 8/11/2005 1,239.00 1,246.50 1,237.20 1,245.20 8/12/2005 1,242.00 1,243.50 1,235.00 1,237.70 8/15/2005 1,238.80 1,244.70 1,235.20 1,244.20 BUY 8/16/2005 1,239.30 1,240.20 1,227.00 1,228.10 SELL 8/17/2005 1,227.30 1,233.60 1,226.50 1,228.10 8/18/2005 1,225.00 1,231.50 1,225.00 1,227.60 8/19/2005 1,232.30 1,233.50 1,228.20 1,230.40 8/22/2005 1,230.80 1,237.00 1,225.00 1,230.70 8/23/2005 1,228.60 1,231.00 1,222.50 1,228.00 8/24/2005 1,223.30 1,232.00 1,216.00 1,216.40 8/25/2005 1,218.60 1,221.00 1,217.10 1,220.20 8/26/2005 1,219.30 1,219.30 1,212.00 1,212.40 8/29/2005 1,208.50 1,222.20 1,208.50 1,221.00 BUY 8/30/2005 1,216.60 1,216.60 1,208.50 1,215.00 8/31/2005 1,217.00 1,230.30 1,211.50 1,227.80 9/1/2005 1,228.20 1,234.50 1,223.80 1,227.90 9/2/2005 1,231.90 1,232.00 1,224.00 1,225.90 9/6/2005 1,230.00 1,241.00 1,230.00 1,240.50 9/7/2005 1,239.50 1,244.00 1,238.00 1,241.90 9/8/2005 1,239.50 1,242.70 1,236.00 1,238.20 SELL 9/9/2005 1,238.00 1,250.30 1,237.20 1,248.50 BUY 9/12/2005 1,249.00 1,250.90 1,245.50 1,246.60 SELL 9/13/2005 1,246.10 1,246.60 1,237.70 1,238.90 9/14/2005 1,238.80 1,242.00 1,232.20 1,234.60 9/15/2005 1,234.50 1,238.50 1,230.70 1,233.50 9/16/2005 1,233.50 1,244.70 1,232.80 1,242.30 BUY 9/19/2005 1,242.60 1,242.70 1,233.50 1,238.00 SELL 9/20/2005 1,237.80 1,242.80 1,225.60 1,227.60 9/21/2005 1,227.40 1,229.30 1,214.50 1,215.90 9/22/2005 1,215.90 1,222.70 1,211.00 1,219.80 BUY 9/23/2005 1,219.60 1,224.80 1,215.10 1,220.50 9/26/2005 1,224.20 1,229.00 1,217.00 1,221.50 9/27/2005 1,221.50 1,226.00 1,216.20 1,221.70 9/28/2005 1,221.60 1,226.70 1,217.70 1,222.80 9/29/2005 1,222.40 1,234.30 1,216.30 1,231.80 9/30/2005 1,231.60 1,235.80 1,230.00 1,234.30 10/3/2005 1,235.30 1,238.80 1,229.90 1,231.80 SELL 10/4/2005 1,231.50 1,235.50 1,216.60 1,216.80 10/5/2005 1,216.80 1,218.30 1,200.20 1,200.70 10/6/2005 1,200.80 1,206.90 1,186.00 1,196.90 10/7/2005 1,197.00 1,204.50 1,195.20 1,200.00 BUY 10/10/200 1,199.70 1,204.30 1,189.60 1,190.00 10/11/200 1,190.00 1,198.00 1,187.30 1,188.40 10/12/200 1,189.20 1,194.70 1,177.60 1,178.50 10/13/200 1,179.00 1,184.30 1,172.00 1,178.10 10/14/200 1,178.50 1,191.50 1,178.40 1,189.90 BUY 10/17/200 1,189.80 1,195.50 1,186.50 1,194.20 10/18/200 1,193.90 1,196.70 1,181.00 1,181.40 SELL 10/19/200 1,182.00 1,200.80 1,174.00 1,200.40 10/20/200 1,199.10 1,200.80 1,175.70 1,179.50 10/21/200 1,179.80 1,190.00 1,175.90 1,183.80 10/24/200 1,183.80 1,203.30 1,183.30 1,202.40 10/25/200 1,202.00 1,204.70 1,192.00 1,198.90 SELL 10/26/200 1,198.80 1,207.00 1,193.90 1,196.00 10/27/200 1,195.40 1,196.10 1,181.00 1,182.50 10/28/200 1,181.30 1,201.20 1,179.50 1,199.70 BUY 10/31/200 1,199.80 1,214.50 1,199.70 1,209.80 11/1/2005 1,203.90 1,210.50 1,203.00 1,206.30 11/2/2005 1,206.10 1,218.50 1,202.50 1,218.10 11/3/2005 1,218.00 1,227.70 1,216.00 1,223.70 11/4/2005 1,223.50 1,225.70 1,216.70 1,222.00 SELL 11/7/2005 1,222.40 1,227.00 1,219.50 1,222.80 11/8/2005 1,222.70 1,224.30 1,218.50 1,222.80 11/9/2005 1,222.50 1,229.50 1,218.80 1,223.70 11/10/200 1,223.50 1,235.30 1,217.50 1,233.70 11/11/200 1,233.70 1,238.60 1,232.50 1,238.00 11/14/200 1,238.10 1,240.10 1,234.10 1,237.20 SELL 11/15/200 1,237.20 1,240.80 1,228.60 1,232.50 11/16/200 1,232.30 1,235.00 1,229.50 1,234.70 11/17/200 1,234.40 1,246.00 1,233.80 1,245.20 11/18/200 1,245.00 1,252.20 1,242.50 1,250.20 11/21/200 1,250.20 1,258.30 1,248.60 1,257.00 11/22/200 1,256.80 1,263.90 1,253.10 1,262.10 11/23/200 1,261.50 1,272.70 1,261.00 1,268.60 11/25/200 1,268.30 1,270.60 1,265.90 1,270.10 11/28/200 1,269.70 1,273.10 1,258.80 1,260.90 11/29/200 1,260.80 1,267.80 1,259.00 1,259.30 11/30/200 -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Evening Noel (UK),
-- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... Good morning Bob, I am sorry that the BUY and SELL are probably going to need to be in separate columns. I can try to play with them to see if I can combine but I don't think that that will be a good idea. I just got home from work, nightshift 8pm to 6am so I don't think that I am going to work on it until later this evening. I also got some stop info when I wrote the array but not sure if it is reading correctly. Here is an alternative allowing BUY and Sell to be in separate columns In J2, enter =MAX(2,MAX(IF(F$2:F2<"",ROW(F$2:F2))),MAX(IF(G$2: G2<"",ROW(G$2:G2)))) In H2, enter =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($G$1:$G$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) both are array formula, so after copying them to the formula bar, Ctrl-Shift-Enter together. I have tried it again with your data, shifting the SELLs over It is quite a complex analysis, so it might not be right, but can you highlight where it is going wrong, anjd in what way? Here is a thought. I think that I have to have the buy stop off of the low price, this is because if in that period the close may not hit the stop price but the low definately has to or doesn't. Same for the sell stop using the high price for calculation, because the close may not touch the stop price but the high definately will or not. I have absolutely no idea what all of that meant. Do you mean BUY should compare agaisnt column D not E, and the SELL should compare against C? Thanks again for all of your help. If this makes money, does that mean you give up the night-shifts? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Good evening Bob,
I tried to condense the buy and sell formulas down to one column but I had no success. Here is kind of what i wrote in the buy column formula: =if(and(d1<d2,c1<c2,e2<b2,b3<e3,e3e2),"BUY","") and the opposite for the sell side. This is why I used two columns. I tried to combine but got a "value" error. I am going to try to use Dave's suggestion and see if that works, but if you have any other suggestions I would greatly appreciate this. Thanks again. NOel "Bob Phillips" wrote: Evening Noel (UK), -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... Good morning Bob, I am sorry that the BUY and SELL are probably going to need to be in separate columns. I can try to play with them to see if I can combine but I don't think that that will be a good idea. I just got home from work, nightshift 8pm to 6am so I don't think that I am going to work on it until later this evening. I also got some stop info when I wrote the array but not sure if it is reading correctly. Here is an alternative allowing BUY and Sell to be in separate columns In J2, enter =MAX(2,MAX(IF(F$2:F2<"",ROW(F$2:F2))),MAX(IF(G$2: G2<"",ROW(G$2:G2)))) In H2, enter =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($G$1:$G$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) both are array formula, so after copying them to the formula bar, Ctrl-Shift-Enter together. I have tried it again with your data, shifting the SELLs over It is quite a complex analysis, so it might not be right, but can you highlight where it is going wrong, anjd in what way? Here is a thought. I think that I have to have the buy stop off of the low price, this is because if in that period the close may not hit the stop price but the low definately has to or doesn't. Same for the sell stop using the high price for calculation, because the close may not touch the stop price but the high definately will or not. I have absolutely no idea what all of that meant. Do you mean BUY should compare agaisnt column D not E, and the SELL should compare against C? Thanks again for all of your help. If this makes money, does that mean you give up the night-shifts? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Hi Bob,
Just got your new email. Thanks for posting! Something is working. Now I have to go through manually to see if that is reading everything correctly. I appreciate all of your time and help. Best Regards, NOel ZImmerman "Bob Phillips" wrote: Evening Noel (UK), -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... Good morning Bob, I am sorry that the BUY and SELL are probably going to need to be in separate columns. I can try to play with them to see if I can combine but I don't think that that will be a good idea. I just got home from work, nightshift 8pm to 6am so I don't think that I am going to work on it until later this evening. I also got some stop info when I wrote the array but not sure if it is reading correctly. Here is an alternative allowing BUY and Sell to be in separate columns In J2, enter =MAX(2,MAX(IF(F$2:F2<"",ROW(F$2:F2))),MAX(IF(G$2: G2<"",ROW(G$2:G2)))) In H2, enter =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($G$1:$G$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) both are array formula, so after copying them to the formula bar, Ctrl-Shift-Enter together. I have tried it again with your data, shifting the SELLs over It is quite a complex analysis, so it might not be right, but can you highlight where it is going wrong, anjd in what way? Here is a thought. I think that I have to have the buy stop off of the low price, this is because if in that period the close may not hit the stop price but the low definately has to or doesn't. Same for the sell stop using the high price for calculation, because the close may not touch the stop price but the high definately will or not. I have absolutely no idea what all of that meant. Do you mean BUY should compare agaisnt column D not E, and the SELL should compare against C? Thanks again for all of your help. If this makes money, does that mean you give up the night-shifts? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with this Formlua
Noel,
Keep us posted if working or not.. Bob "zmr325" wrote in message ... Hi Bob, Just got your new email. Thanks for posting! Something is working. Now I have to go through manually to see if that is reading everything correctly. I appreciate all of your time and help. Best Regards, NOel ZImmerman "Bob Phillips" wrote: Evening Noel (UK), -- HTH RP (remove nothere from the email address if mailing direct) "zmr325" wrote in message ... Good morning Bob, I am sorry that the BUY and SELL are probably going to need to be in separate columns. I can try to play with them to see if I can combine but I don't think that that will be a good idea. I just got home from work, nightshift 8pm to 6am so I don't think that I am going to work on it until later this evening. I also got some stop info when I wrote the array but not sure if it is reading correctly. Here is an alternative allowing BUY and Sell to be in separate columns In J2, enter =MAX(2,MAX(IF(F$2:F2<"",ROW(F$2:F2))),MAX(IF(G$2: G2<"",ROW(G$2:G2)))) In H2, enter =IF(F2<"","",IF(OR(AND(INDEX($F$1:$F$1000,J2)="BU Y",INDEX($E$1:$E$1000,J2)- 5E2),AND(INDEX($G$1:$G$1000,J2)="SELL",INDEX($E$1 :$E$1000,J2)+5<E2)),"STOP" ,"")) both are array formula, so after copying them to the formula bar, Ctrl-Shift-Enter together. I have tried it again with your data, shifting the SELLs over It is quite a complex analysis, so it might not be right, but can you highlight where it is going wrong, anjd in what way? Here is a thought. I think that I have to have the buy stop off of the low price, this is because if in that period the close may not hit the stop price but the low definately has to or doesn't. Same for the sell stop using the high price for calculation, because the close may not touch the stop price but the high definately will or not. I have absolutely no idea what all of that meant. Do you mean BUY should compare agaisnt column D not E, and the SELL should compare against C? Thanks again for all of your help. If this makes money, does that mean you give up the night-shifts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validate Cell contents in formlua | Excel Worksheet Functions |