I want to backtest a copper strategy that is predicated on long candle 'wick' setups (.005 or longer). I have in Excel OHLC for 15 min copper for 2 years' worth, so pretty solid amount to test which is good. The strategy at this point is if say it is a green candle (C-O>0) that means the low wick is open - low, and if this open minus low is > or equal to .005, I want to buy the open of the next candle and assume a one tick slippage (.0005). And vice versa for selling, and it matters whether it is a red or green candle, because wicks become close paired with low if it were a red candle. I still would buy a long tailed red candle, so candle 'color' doesn't matter one bit, just wick length. How to get these 'if, then' inputted into excel is my question. First is to get it to calculate high side wick and low side wick per candle. This is what the above involved. Then I am looking for + 18 ticks profit tgt and 12 ticks as a SL per position (assume one lot per setup, but can have more than one lot at same time). So this is .009 and .006, respectively, for 3:2 ratio. This is just off hand, nothing refined obviously. So if a green candle prints, and say the open is 3.237 and low is 3.232, that is .005 wick length on tail, and so I would buy the next candle open and assume 1 tick slippage, so if it were 3.246, it'd be buy 3.2465. And then target 3.2565, or SL if it hits 3.2405. I have no idea how such a test would be run, as it is very nuanced. Might have to just be done manually at this point. And there is no time length limit for holding a position, and no intra position stop losses or anything. Whatever it hits first, target profit or SL, that is it.
You can just add a column where you generate your entry/exit signals based on indicator values, and then in another column keep track of P/L based on those signals.
It all can be done pretty easily in excel with basic formulas. You can also look at pivot tables basic data manipulation.