Im_Offset
Member
Hello,
Here is a difficult matching problem: I'm trying to match one value to its optimal counterpart, and there are many criteria involved. Please see the attached spreadsheet.
The tab [Match Trades] is the relevant tab for this problem. Cells B6:F15 has information regarding "Short Calls". Labels for the data are at the top on row 5. Cells H2:Q4 has information regarding "Long Calls". Labels for the data are to the left in Column G. I need to match the Contr for each Long Call to the Optimal Short Call.
The first requirement is to identify which "Long Calls" are potential matches for "Short Calls". To be a potential match the Long Call must have a lower Strike value AND an Exp Date that is equal to or later than the corresponding Short Call. I have found the potential matches in H6:Q15. If the Long Call is a potential match for the Short Call, then the number of Contr associated with that Long Call is listed in the appropriate row for that Short Call. So you can see that the one Long Call we have is a potential match for the first 3 Short Calls.
The second requirement (and here is where I am having difficulty) is to identify the best Short Call to match to the Long Call. The best match will be the Short Call with the highest 2-Lot value (Column F). If the potential 2-Lot values happen to be equal, then the best match is the Short Call with the lowest Strike value. I have designated row 17 identify the Optimal Short Call for the Long Call.
What formula can I use in cell H17 and then drag to Q17 that will analyze only the 2-Lot values that the Long Call is a Potential Match for, and then tell me which Short Call (Column A) has the highest 2-Lot value for that particular Long Call? If for some reason, the 2-Lot values are equal, then I want to choose the Strike with the lowest value. In row 18, I want to do the same thing except I want to find the second best Short Call for that particular Long Call (If there is only one potential best match, then this cell can be blank).
Thank you for your help!
Im_Offset
Here is a difficult matching problem: I'm trying to match one value to its optimal counterpart, and there are many criteria involved. Please see the attached spreadsheet.
The tab [Match Trades] is the relevant tab for this problem. Cells B6:F15 has information regarding "Short Calls". Labels for the data are at the top on row 5. Cells H2:Q4 has information regarding "Long Calls". Labels for the data are to the left in Column G. I need to match the Contr for each Long Call to the Optimal Short Call.
The first requirement is to identify which "Long Calls" are potential matches for "Short Calls". To be a potential match the Long Call must have a lower Strike value AND an Exp Date that is equal to or later than the corresponding Short Call. I have found the potential matches in H6:Q15. If the Long Call is a potential match for the Short Call, then the number of Contr associated with that Long Call is listed in the appropriate row for that Short Call. So you can see that the one Long Call we have is a potential match for the first 3 Short Calls.
The second requirement (and here is where I am having difficulty) is to identify the best Short Call to match to the Long Call. The best match will be the Short Call with the highest 2-Lot value (Column F). If the potential 2-Lot values happen to be equal, then the best match is the Short Call with the lowest Strike value. I have designated row 17 identify the Optimal Short Call for the Long Call.
What formula can I use in cell H17 and then drag to Q17 that will analyze only the 2-Lot values that the Long Call is a Potential Match for, and then tell me which Short Call (Column A) has the highest 2-Lot value for that particular Long Call? If for some reason, the 2-Lot values are equal, then I want to choose the Strike with the lowest value. In row 18, I want to do the same thing except I want to find the second best Short Call for that particular Long Call (If there is only one potential best match, then this cell can be blank).
Thank you for your help!
Im_Offset