Solved | Sell stock when it rises 5% and close when it falls 5%

[pao13]

Hi. I'm attaching my workbook. I have some daily stock prices. What I'm trying to do:
Sell the stock when it rises 5% or more. Close the position when it falls 5% or more.
01/10/2009 28
02/10/2009 28
05/10/2009 26
06/10/2009 27
07/10/2009 29
08/10/2009 30
09/10/2009 29
12/10/2009 23
13/10/2009 22
14/10/2009 24
15/10/2009 25
16/10/2009 22
So, from 5/10/2009 to 7/10/2009 it rises more than 5%. So I want to get a 1 (to sell) on 7/10/2009. Now that I sold the stock I want to buy it back when it falls 5%. This happens at 12/10/2009. Get a 2 here. Now, at 14/10/2009 it has risen more than 5% from the previous low(13/10/2009). So get a 1. Buy it back at 16/10/2009. etc.

It should check the lowest from the range where the last transaction took place.
The stock can be first sold and then bought back. It can't be sold and then another sold. The sequence has to be 1,2,1,2,1,2.

So here is the data horizontally.
28 28 26 27 29 30 29 23 22 24 25 22 21 20 28 20

In cell 2 it checks 28 with 28 no good.
In cell 3 it checks 26 with 28 (the lowest of the previous 2) no good.
In cell 4 it checks 27 with 26 not 5% rally, no good.
In cell 5 it checks 29 with 26 (the lowest of this range) more than 5% rally! So its good. Get 1.
Now it tries to find 5% or more down move even if the stock goes to 100. It finds that at a price of 23. Get 2 here.
After that it wants to get 1 (to sell stock) again.
Now it checks 22 with 23 no good.
It checks 24 with 22 (this is the new low from the new range (22-24)). That's good. Get 1.
It correctly gets out (buy back) at 22.
Now, 21 is lower than 22. No good.
20 is lower than 22 21 (check both of them)
28 is good because it is more than 5% of the minimum of the range 22-21-20.

At 29/10/2009 it should not buy back because it's not 5% or more down from 22 where we got in (sold stock). (Even if it climbs very high keep the stock short without buying back).

P.S. I have already posted this problem here http://www.excelforum.com/showthread.php?t=1128587&p=4326427#post4326427

Hope you understand what I'm trying to do.
Thanks for any help!

 

[pao13]

Another way to post the above without having to read all the requirements is my workbook which I attach in this post. Column D gives what I want to do. So I only use 2 formulas:
If cell in Ex is 1 then cell in D+1x should be IF(Bx+1<B$x*(1-0.05),Bx+1/B$x-1,"")
If cell in Ex is 2 then cell in D+1x should be IF(Bx+1/MIN(B$x:Bx+1)-1>0.05,Bx+1/MIN(B$x:Bx+1)-1,"")

I'm not a native english speaker so if you don't understand what I'm trying to please ask me.

So to sum up, the question is for a different data set how can the 2 above formulas change so that I get the correct results.

Thanks for any help!

 

[pao13]

Hi again. I just got the answer I wanted in another forum. Should I post the answer here?

 

[Deepak]

Hi again. I just got the answer I wanted in another forum. Should I post the answer here?

Yes bro!!

Also share the link of that post too...

 

[pao13]

Here is the link and the macro. http://www.excelforum.com/showthread.php?t=1128587&page=2&p=4326836#post4326836

Sub buysell1()
Range("C4:C10000").ClearContents
k = 4
p = 2
lt = 0.001
For n = 5 To Range("A" & Rows.Count).End(xlUp).Row
lmin = Application.Min(Range("B" & k & ":B" & n - 1))
If (Range("B" & n).Value - lmin) / lmin >= 0.05 And p = 2 Then
Range("C" & n).Value = 1
k = n
p = 1
lt = Range("B" & n).Value
ElseIf (Range("B" & n).Value - lt) / lt <= -0.05 And p = 1 Then
Range("C" & n).Value = 2
k = n
p = 2
End If
Next n
End Sub