Hello all,
I am reaching out to you guys because I am trying to figure out a way to do the following. It is possible to do however somewhat puzzling, a good cahllenge.
GOAL:
[Please open attachment to follow or it won't make sense]
I would like the formula in A2 under 'Corresponding Future Contract' to smartly detect the right contract in the Future Contract Chain on the right, it would do this based of reading the date embedded in A5 'Option Details'. The formula I am looking to build in A2 must look at the date embeded in the string located in A5, and than select the correct Future Contract from the list on the right that is [1] on this date or [2] after this date (not before)
For example:
A2 = SPXW US 10/16/17 C2560 Index
1. The date there is October 16th, 2017
2. Use this to find nearest contract date in array "C2:E17"
3. Closest one in this case is ESZ7 Index that expires 12/15/2017
4. Formula outputs ESZ7 Index
Let's take another example,
Say A2 = SPXW US 3/17/17 C2560 Index
1. In this case, the date there is March 17th, 2017
2. The nearest date in array is the March 16th, 2017 contract 'ESH8 Index' however this is expired by then and I MUST use the next one in the list
3. The output in this case should be 'ESM8 Index' as it is the next avaiable contract after that date
This data is static in my sample workbook but in reality it is constantly changing and I really need a formula to do this for me because a lot of other things in my workbook will work it's magic off C2.
I am actually stumped, I really don't know how to do this. I know it is possible if you perhaps parce out the date and use find/index functions. I am lost however and I am turning to a guru like yourself
Bookmarks