This one is tricky. For those of you familiar with stock trading, I am trying to simulate a stop (or stop-loss) order in a strategy back testing tool I am developing.
I need to look up values based on a start date and end date (that part is easy enough), then if any values between those dates meet's Stop-loss % * start date value, I want to return the value of the first day that meets that criteria. I have uploaded a sample file with my formulas currently working to simply return the values based on start and end dates. But in the real world, one would need to place stop orders to protect from catastrophic damage to the portfolio. I need to be able to simulate these simple orders in my back testing tool or I could never know how good or bad my strategy is or run scenarios on the proper stop loss (percent).
The price at which a sell order would trigger and/or the % gain/loss is the end result I am trying to get at.
Note1: that Table 1 has open price and intra-day low prices in separate columns. I need to return the value if the criteria is met in either of those fields.
Note2: You cannot change the dates in columns B or C, they come from another system generating my buy/sell signals. The intent of this worksheet is to validate and gather data on my buy/sell signals.
Adding columns for additional dates or intermediary calculations is acceptable. I think it may be necessary to add intermediary calculations to the data table to make this work. Excel Forum Lookup Help.xlsx
Bookmarks