Triggers to buy/sell based on histogram

[x3mist]

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.