Hello everyone, I have a major problem with some excel. Im not sure if I
need to code it in VBA which I minimal knowledge about. I have been
trying to solve this for days now but without much success. I hope all
the excel gurus here can help me out with this one.

I have some financial data (time period, open price, close, high and
low). With this data I have simulated an indicator which is in column F
(Histogram). The actual derivation of the histogram is not relevant to
the problem.

The histogram is displayed as a bar chart and it acts as a trigger of
when to buy or sell.

When the histogram is positive it is above the x-axis. The gradient of
the histogram is also positive until it reaches its peak and the
gradient becomes negative. This is clearly seen in the graph and data
table for the first peak. Time period 3 is the peak. At the end of
time period 4, the gradient is now negative. Therefore at the beginning
of time period 5, the “sell” order is activated. “Sell” orders are
activated when the gradient of the histogram turns negative and “buy”
orders, when the gradient becomes positive. Note there is a difference
between “activate” and “trigger”

The “sell” order is set up so that it is only triggered when the price
reaches 50$ more than the opening price. For the first peak, this order
is triggered, because the opening price is 500$, the trigger is 50$ more
i.e 550$ and the high of this period is 600$.

The stop loss and take profit are both 100$ from the trigger price of
550$ i.e 650$ and 450$ respectively. Since the high of this period is
600$, it nevers reaches the stop loss of 650$. The low of this period
is however 300$, therefore the 450$ take profit is reached.

The 2nd case is simply the opposite of the 1st, seeing that it is now a
“valley” instead of a peak.

There are 2 conditions that have to be met before a buy or sell order
can be activated.

First, if the buy or sell order is not activated within 3 consecutive
periods, the order is cancelled. See 3rd row highlighted in yellow.

Secondly, the peak or valley has to =10 and <=10 respectively for the
order to be activated. See 4th row highlighted in yellow. This is in
reference to the histogram.

5th row in yellow is an example of where the stop loss is hit instead.


No 2 orders can run concurrently

There is a flaw in this model, being that both profit take and stop
loss can occur within the same time period, and it is impossible to
tell which occurs first. This is to be ignored

The desired results should be displayed like in column H and I in the
attached excel file.

Thanks to anyone who can help.


+-------------------------------------------------------------------+
|Filename: excel.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5076 |
+-------------------------------------------------------------------+

--
x3mist
------------------------------------------------------------------------
x3mist's Profile: http://www.excelforum.com/member.php...o&userid=36417
View this thread: http://www.excelforum.com/showthread...hreadid=564150