+ Reply to Thread
Results 1 to 11 of 11

Lookup values in multiple columns based on a date range, return single value per criteria

  1. #1
    Registered User
    Join Date
    02-15-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Lookup values in multiple columns based on a date range, return single value per criteria

    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

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    Hi mprisbrey,

    welcome to the forum.

    Do you need values which you have shown in column D of "calculation & data" tab ?


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    02-15-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    I need smarter values. Column D as calculated is the simple return of my buy and sell dates without regard to where the price may float between those dates. Column E is the end result I need, % gain/loss of any given trade.

    Like most Excel puzzles, there are multiple ways to skin this cat. I am ok with a new column between D and E and update E with an IF statement to use D if no stop value is found in new column, or the intelligence could be embedded directly into column D or E. I suspect the former would be a better approach and more apparent what happened with any given trade, but I am open to suggestions.

    Thanks for your interest in helping me figure this one out.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    I would suggest you to provide your expected results (manually typed) along with logic.. thanks.



    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    02-15-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    Hopefully this is more clear. I have updated my file with the desired results manually placed in column H. I have also added comments in those cells to explain what triggers the result and how it is calculated.

    Mark
    Attached Files Attached Files

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    Hi Mark,

    So basically you are looking at:-
    Column N should be between start and End date,
    Column O should be less than Trade price.

    Use below formula:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached:- Excel Forum Lookup Help(1).xlsx


    Regards,
    DILIPandey
    <click on below * if this helps>

  7. #7
    Registered User
    Join Date
    02-15-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    It seems to get the result for the first row, but when I drag the column down none of the other rows seem to return correctly. Since I am not familiar with the INDEX function or multiplying within an IF statement, I don't know how to troubleshoot.

    Additional question: Will this function return the first value where the stop criteria are met or the lowest it finds in the date range? My intent is to return the former, since that is when the protective trade would trigger.

    In English, that would be something like:

    IF(N:N is between Start and End Dates, look for the first day where O:O is <= (Basic Trade Price)*(1-K3), then return the value from O:O, OR look for first day Q:Q is <= (Basic Trade Price)*(1-K3), then return (Basic Trade Price)*(1-K3).

    If O:O criteria is triggered then it trumps Q:Q, but if not, then the formula should look for Q:Q to trigger, if neither, look to the next row above until it hits the End Date.

    I hope that I am making myself clear. As you can see, I am having a difficult time with explaining the desired result in logical terms, which is why I am here for help.

    Thanks again,

    Mark

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    Check cell O302, which is coming correctly as formula result.

    Excel Forum Lookup Help(1).xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>

  9. #9
    Registered User
    Join Date
    02-15-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    Cell H3 Result looks good.

    The Formula in cell H5 should not return O302. Cell O302 is not below (Basic Trade Price)*(1-K3) = 50.84, therefore the formula needs to look to Q302. It should find that Q302 is below (Basic Trade Price)*(1-K3), therefore when Q:Q meets that criteria H5 should return (Basic Trade Price)*(1-K3) = 50.84. I do not see where this formula looks at Q:Q at all.

    Since neither criteria are met for H7, I would like it to return "" in stead of 0.00. I see the null string in there, but don't know why it is returning 0.00 instead.

    H9 result is good.

    H11 should return (Basic Trade Price)*(1-K3) = 56.15 for the same reason as H5.


    Thank you so much for all your work on this, I think we are getting close.

    Mark

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    The Formula in cell H5 should not return O302. Cell O302 is not below (Basic Trade Price)*(1-K3) = 50.84, therefore the formula needs to look to Q302.
    Can we have the above bold calculation in a column which we can easily refer in the formula like I am referring D5 in the formula ?
    Also, I believe we need to fetch result from column O and not sure why you mentioned column Q here ?

    Also, do you need result only for Uptrend rows and not for Downtrend rows ? If that is the case, you can include an IF statement to have formula only if column A value is "Uptrend".

    I would suggest, just look into my formula and understand that, then you can easily - either convert format to suit your need OR you can change your data layout to suit formula - either ways

    Regards,
    DILIPandey
    <click on below * if this helps>

  11. #11
    Registered User
    Join Date
    02-15-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Lookup values in multiple columns based on a date range, return single value per crite

    Bold calculation could be in separate column or embedded into formula, either way is OK with me.

    I tried to edit the bold section of your formula below to try embedding it:
    =INDEX($O:$O,MAX(IF((Table1[Date]>=$B3)*(Table1[Date]<=$C3)*(Table1[Open]<=($D3*(1-K3)),ROW(Table1[Open]),"")))

    But it does not seem to like my syntax of changing <$D3 to <=($D3*(1-K3). Again, that could be in another column, but shouldn't be necessary. Am I missing quotes or something? I couldn't get it to work.

    To answer your question about column Q, one of the major tricks of this formula is that per my original post, both column O and Q are important here. If column O is <= (Basic Trade Price)*(1-K3), then return the first column O value in the date range. However, if column O does not meet that criterion, the formula must then look to column Q, because column Q represents the lowest price at witch the security traded that day. If Q is below my stop price of (Basic Trade Price)*(1-K3), than it must return (Basic Trade Price)*(1-K3), because my protective stop price would have triggered an order.

    Or maybe put into other terms, perhaps the formula should first look at column Q, then if Q is <= (Basic Trade Price)*(1-K3) it should look to column O. If O <= (Basic Trade Price)*(1-K3), then return the value from O. If Q = yes and O = No, then return (Basic Trade Price)*(1-K3).

    Regarding Uptrend vs. Downtrend. Yes, I am interested in both, but I didn't want to complicate the situation in my original post. But since you asked, here goes: I currently have the trend coded into my % Gain/Loss column "I". In my original spreadsheet I have a group of columns for uptrend and a separate group for downtrend, because the formulas are different. Downtrend flips things around. The security is sold first, then bought back later (hopefully at a lower price), this is called shorting and the logic is different. For the purposes of a stop loss, we would be looking for prices at or above (Basic Trade Price)*(1-K3), rather than below for a normal trade. I was hoping that once we get this formula working, I could figure it out well enough to flip the logic myself.

    Regards,

    Mark
    Last edited by mprisbrey; 02-19-2013 at 02:46 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1