+ Reply to Thread
Results 1 to 40 of 40

a tricky "index(match)" situation?

  1. #1
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    a tricky "index(match)" situation?

    hello experts. I'm trying to use the index(match) function to populate the cells in column Y ("Put BID") with the values from column L ("BID") that match the , but only if the option type is "Put" (=column F="p"). the problem is the file is organized in a Call row and a Put row (alternately). I need the formula to first identify if this row is for put or calls (F="p"). Also, the initial test is column W ("new minute?")="yes". if false then the cell in Y is irrelevant and should stay blank or "".

    so here's what I'm thinking (and of course isn't working): for the first row, to populate Y2 the tests are:
    1. First we test if this record (row) is a new minute or a continuation of the current minute quote: =if(W2="yes",round(O2,0)) - this is working fine.
    2. Now I'm trying to make the formula find the L value in the row where F=X2 (17 in this case). =if(isnumber($X$2),index(L:L,match(X2,E:E,0)),"")


    Attached is the spreadsheet sample..
    livol kevin -forum.xlsx

    P.S. for some reason I cannot isolate the time from column B using the =right(B2,4) function.. i get weird, informatable values

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: a tricky "index(match)" situation?

    =IF(ISNUMBER($X$2),INDEX(L:L,MATCH(X2&"p",E:E&F:F,0)),"")
    Confirm COntrol+Shift+Enter
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    Cheers Robert! that worked. However, The actual file is HUGE (50K rows, and this is just one day! so after applying your array formula to row Y2, I double click the + corner to apply it to to the entire Y column and man- it takes forever.. and this is a pretty decent machine I have here.. it's been like 5 minutes and all I get is "calculating: (4 processors: 6%)".. is there a reason why array formulas take so much CPU power? on the same subject: if I used filter tool to filter out some of the unnecessary rows, would that actuallly make calculations like this quicker or does the filter tool merely hide the data from the user but in fact it's still there?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    you can do 2 things to speed this up.

    1st, if you still want to use the array formula provided, restrict it to the range, dont use entire columns

    2nd, you could add a helper and combine E and F (in say, a new column inserted in G...=E2&F2), and then eliminate the array formula and use this regular formula (which could also be restricted to just the range needed)...

    =IF(ISNUMBER($X$2),INDEX(L:L,MATCH(X2&"p",G:G,0)),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    thanks FDibbing, that did speeded up the process immensely! thank a lot. Now, here's my concern: I've narrowed the spreadsheet down to "only" 26,000 Rows (this is a minute-by-minute track record of an entire option chain, with all expirations beside one removed from it). This is ONE DAY. the real file I'm about to purchase (for quite a handsome sum I might add) is going to be TWO YEARS WORTH of that same minute by minute data. that comes down to about 18,980,000 rows AFTER I somehow manage to get red of all the cluster unnecessary rows.

    now, I'm not a computers guy, so let me ask you: is a file this size still "excel-able"? I have a 8gb computer with two very strong video cards (which are probably useless for this) and are all brand new - could such a machine even process such file effectively?
    Do you have any tips for how to eliminate data from a spreadsheet? sure, deleting the rows is easy enough, but often requires searching for very large amounts for cell values.. are there any other ways such as pivots or data filters, that you know of?

  6. #6
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation? - Extended problem! solutions can't work

    first of all MY BAD: I should have warned you guys. this spreadsheet I attached was just a sawed off version of the real thing, to save you donwload time.. The real spreadsheet continues on and on below, to 09:32 (entire chain at that time)...09:33 (entire chain..)... etc. Let's call each such minute chain a 'block' shall we? So since the strikes (Column E, now combined with F into one column "G") repeat themselves, but the BID values (L:L) change from minute to minute. the current solution makes the formula go seek a value, say 17 in this array, and apparently it starts at row 2. and then always stops at the first 17 it hits (and matches it with the BID value of course).. so no matter how far down I get, I keep getting the BID for the 09:32 block's 17 strike.. I think we need to come up with some sort of (if formula is triggered, only search from current row and BELOW. this way the first 17 it hits (or whatever number its looking to match) is that of the current block.
    I attached the same file but it continues down a bit into a second block where you can see the problem; The right "answer" in Y74 would be 1.85 (found in L91) yet it keeps getting the same ol' 1.75 from L19
    You can also see I've applyied the same solution to other fields I need to find- and it works great.. for the first block! then it sticks to it ..

    I'd like to add that there's no way to manually define the indexed range - like I said this is going to be a MASSIVE spreadsheet, no man can manually do it.
    We need a creative mind here


    livol kevin -forum.xlsx

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    To begin with, you will have a problem because excel only has 1 048 576 rows, so no-way will you be able to come close to 18 mil

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: a tricky "index(match)" situation?

    Using MS Access could help. Millions of rows will not create a problem however, the size does have limitations.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  9. #9
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    I wasn't aware of this limitations. This is good however, as I presume the data supplier will be forced to split the data file into several separate blocks. this should make things easier..
    I will look into MS Access. never used it before. Can it manipulate the data using formulas like excel does though? or chart it?

    More importantly, any thoughts about this "repeating blocks" problem? is this a dead-end killer for me, or is there a way to tell the formulas "don't start from row 1 each time, only start from the row you're in", sorta?

    sorry for being such a noob.. this is the miserable level they taught us in economics

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: a tricky "index(match)" situation?

    Yes, splitting database front/back end is the solution if your size exceeds 2GB. The front-end can connect to hundreds or even thousands back-ends without problems.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    As far as the "repeating blocks" are concerned, you can combine whatever you want to create truly unique ID's. So, to column G, you could also add (combine) the value in L
    =E2&F2&L2

    the only problem is, Im not sure how you would incorporate that into your formula...
    =IF(ISNUMBER(X2),INDEX(L:L,MATCH(X2&"p"&1.75,G:G,0)),"")
    ...seems a bit too manual to me?

  12. #12
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    Quote Originally Posted by AlKey View Post
    Yes, splitting database front/back end is the solution if your size exceeds 2GB. The front-end can connect to hundreds or even thousands back-ends without problems.
    No idea what that means, honestly, but I take you word for it. we'll cross that bridge when we get there.. right now I'm trying to come up with some solution to the problem I mentioned to see if its even doable and whether or not I should even purchase the data file (price is in the thousands).

    Anyone? please?

  13. #13
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    Quote Originally Posted by FDibbins View Post
    As far as the "repeating blocks" are concerned, you can combine whatever you want to create truly unique ID's. So, to column G, you could also add (combine) the value in L
    =E2&F2&L2

    the only problem is, Im not sure how you would incorporate that into your formula...
    =IF(ISNUMBER(X2),INDEX(L:L,MATCH(X2&"p"&1.75,G:G,0)),"")
    ...seems a bit too manual to me?
    yeah I don't think how this could work if I have to first find the "unique" value to create the unique ID.. we are talking thousands of manual inputs, may as well fill the data by hand

    is there no way to set the start line/range of the index array using the same row the formula is triggered in? so that the formula triggered in Y47 the upper boundary of the index array would be L74? I'm just thinking out loud here and probably not helping much but I can't believe this is the one thing excel can't do right.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    OK, making this up as we go lol. lets try this...

    Add another helper column next to G and use this, copied down...
    =G2&COUNTIF($G$2:G2,G2)
    this will now give each "set" a unique number...
    9c1
    9p1
    10c1
    10p1
    9c2
    9p2
    10c2
    10p2

    Now we can play with those to ID what you want

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    I am out of time for now, will be back in a few hours to look at this again. See what you can do with my latest suggestion - this modification gives you the same 1.75 you had before...
    =IF(ISNUMBER(Y2),INDEX(M:M,MATCH(Y2&"p"&ROW(A1),H:H,0)),"") =17.75

  16. #16
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    Quote Originally Posted by FDibbins View Post
    OK, making this up as we go lol. lets try this...

    Add another helper column next to G and use this, copied down...
    =G2&COUNTIF($G$2:G2,G2)
    this will now give each "set" a unique number...
    9c1
    9p1
    10c1
    10p1
    9c2
    9p2
    10c2
    10p2

    Now we can play with those to ID what you want
    Still trying to figure out the condition you put in the COUNTIF.. can't say I understand it, but what do I know it sure looks like we're heading the right way. The problem is that every block contains far more rows than the counter is counting; So first trigger (trigger= the row where ISNUMBER(Y)=TRUE and the formulas come into play) works fine, but when we hit the second trigger at Z74 (Z in now the formula's cell) we encounter a problem: The ID counter in the helper column H has only reached 2 (ie 17p2) yet the formula counter is looking for MATCH(Y74&"p"&ROW(A73) (=it's looking for 17p73 to match, because the counter starts at ROW(A1)...

    has to be some way to work around that..

    I'll be waiting for your return. and thank you sir for your brains and time.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    OK back. What the countif is doing is counting how many times it see's a value (in this case, a combination of E & F). So the 1st time it sees 17p, it adds a 1 to it (17p1), then next time it sees 17p it adds 2 to it (17p2) etc


    I assume you are trying to create a summary table on Y:AG? what will this be based on? I see the calc in Y is pulling a value from P, but only for each new date

  18. #18
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    the calc in Y is simply rounding the value in P, which is something called synthetic forward price of the underlying asset for these options. the idea is that for ever 1 minute block there will be just one row of data, summarizing the prices of a straddle position (ie, the purchase or the sale of a put and a call at the ATM strike (strikes- column E, ATM strike= column Y)

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    ok I think I have it now

    add another helper (all helpers can be hidden) in X and use this, copied down...
    =IF(B2=B1,X1,X1+1)
    then, copied down...
    Y2=IFERROR(ROUND(IFERROR(INDEX(P:P,MATCH(ROW(A1),$X:$X,0),1),""),0),"")
    Z2=IF($Y2="","",INDEX(M:M,MATCH($Y2&LEFT(Z$1,1)&ROW($A1),$H:$H,0)))
    AA2=IF($Y2="","",INDEX(O:O,MATCH($Y2&LEFT(AA$1,1)&ROW($A1),$H:$H,0)))
    AB2=IF($Y2="","",INDEX(S:S,MATCH($Y2&LEFT(AB$1,1)&ROW($A1),$H:$H,0)))

    From that, Im sure you can get the drift on what to do with the other columns?

  20. #20
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    I was thinking maybe we can make the counter in the formula (in Z) match the counter in H by replacing &ROW(A1) with something like COUNT (everything above the triggered cell). now, i'm thinking that because when a cell above isn't triggered it returns "" which the counter doesn't count, the counter will only count the triggered cells above.

    but I don't know how to say "everything above" in excelish

  21. #21
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    I was thinking maybe we can make the counter in the formula (in Z) match the counter in H by replacing &ROW(A1) with something like COUNT (everything above the triggered cell). now, i'm thinking that because when a cell above isn't triggered it returns "" which the counter doesn't count, the counter will only count the triggered cells above.

    but I don't know how to say "everything above" in excelish

  22. #22
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    ok, must the new helper be in column X? because that columns is currently occupied by "Vega"
    ?
    assuming you meant X to be any column i put the helper in (I inserted a new one, I), I changed X to I and kept everything else as is. it returns #value
    Last edited by ryefield; 08-29-2013 at 12:54 AM.

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    my X was empty, (vega was W)
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    Quote Originally Posted by FDibbins View Post
    my X was empty, (vega was W)
    OK so my error was I didn't leave the header cell (I1) empty, I head text in there. now let me see if I can grasp what you did here with the other formulas

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    I am unsure as to why you are using the "implied_underlying_price" as a "marker" for each minute, but I just followed on with what you had there

  26. #26
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    oh I'm not.. or at least I never meant to.. I couldn't extract the time out of B for some reason (I tried =right(B,4). doesn't work.
    The thing about implied underlying price is that it's identical to the whole minute block, that's why I used it.

    gotta ask - why did you use "LEFT(Z$1,1)" to get a "P" letter instead of just writing "p"? curious.

  27. #27
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    Oh ok I see what you did here (not that I claim to understand the formulas) so what this is getting me is essentially a clustered summarization (meaning consecutive rows) of the "triggered" formulas in even minute, right? that saves me the time to later figure out a way to hide all the non-triggered rows.
    However, it's would a lot if I could somehow have a time column to the left of all this so I can match the values with the corresponding time block.. then I figure I can basically hide everything else and let the charting begin

  28. #28
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    yea I just realized this last task ("timestamping" each row of values) might be the most difficult task yet, right? without the minute each set of prices belongs to, I can't chart it

    Sir, regardless of all - I'm sorry the forum code won't let me give you more rep stars. you more than deserve it. I am so thankful for your time and ideas.

  29. #29
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    This will give you the time...
    =MOD(B2,1)
    formatted as time

    gotta ask - why did you use "LEFT(Z$1,1)" to get a "P" letter instead of just writing "p"? curious.
    This is so that you can just copy across and not need to adjust for p or c, it will pull that from the heading (just a little time saver).

    hmm in fact, try this for at least the BID and ASK columns...
    =IF($Y2="","",INDEX($M:$W,MATCH($Y2&LEFT(Z$1,1)&ROW($A1),$H:$H,0),MATCH(MID(Z$1,SEARCH(" ",Z$1)+1,99),$M$1:$W$1,0)))

    IF you cant get the other headings to use the same syntax/format, then this will work for all columns
    Last edited by FDibbins; 08-29-2013 at 01:58 AM.

  30. #30
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    surprisingly enough I've actually figured it out myself. what I didn't figure out is how to make it match the order of the results... you know what I mean? copying =MOD(B2,1) down from X2 (I put it on my X column) I get a long ling of 09:32.... then 09:33 etc.. while in your collected data (say ATM Strike in Y) Y3 is already the 09:33 data set..

  31. #31
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    ahhh never mind. I got it I got it! (lol you must hate me by now!

  32. #32
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    OK not sure where you want to put this, but this will give you your time change for each row...
    =IF(Y2="","",MOD(INDEX($B:$B,MATCH($Y2&LEFT(Z$1,1)&ROW($A1),$H:$H,0),1),1))

    It's getting to the point where you may need to start thinking about moving that 2nd table to a new worksheet lol?

    hate you? not in the least, i enjoy fiddling with stuff like this (wow that sounds kinda sad, doesnt it lol)

  33. #33
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    FDibbins thank you ever so much for all your help. needless to say I couldn't have done it without you.. While I have your attention, I'd like to hear if you have any advices as for how to plot this mountain of information (see attachment for the final result, and imagine this continuing on for say, 30 days more. so about 404*30 = 12,120 rows. The immediate thought is to chart it with daily candlestick chart, yet there are some problems with that approach (if the ATM Strike (Y) changes during the day, there has to be some notification because the formulas (and the chart) are now tracking different things..

    anyway I don't know if you have any advices, but I've never had to deal with such a huge amount of data and I'm fishing for clues and tips how best to go about it

  34. #34
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    Where did you attach the latest file?

  35. #35
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    Quote Originally Posted by FDibbins View Post
    Where did you attach the latest file?

    I can't trim down the file because no when I delete rows it messes up the chart and all the formulas.. I'm trying to upload a JPEG file instead for you to see what it looks like
    forumm.jpg

    These columns, named ATMS (At The Money Straddle) BID, ASK, MID and IV are just a simple averages of the corresponding Put and Call values from each 'triggered' row. these are in turn put on a chart. now imagine how wide and unreadable this chart is going to be when we add even just another day's data in.. let alone a month or more. that's the challenge. I was thinking candlesticks chart but that's also tricky.. I know excel needs the data to be organized in a very particular way for this chart type, and I don't want to mess up the data by moving columns and rows around.

    Plus, we gonna have to work out a way to make excel identify a new day (much like we did with a new minute I imagine) and then use first triggered row as "Open" and Last triggered as "Close", then find MAX and MIN for that range between them to find High and Low.

    Ugghh its so hard when dealing with so much data isn't it? can't do anything manually, everything has to be done "the smart way" lol..
    Last edited by ryefield; 08-30-2013 at 12:18 AM.

  36. #36
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    you could set up the chart to be based on a moving trend-line. You can set up the trend period when you set up the trend line. Pretty much what it does is to use a (for instance) 14 day rolling average for your data

  37. #37
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    hmmm ok.. let me process that.. the day I'm used to thinking about moving trendlines is have them drawn on top (ie- together) of the data series.. not instead of. how do I do that? the way I do it right now is to add a Moving Average trendline of say 5 periods. But of course that's not what you meant

  38. #38
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a tricky "index(match)" situation?

    well actually it kind of is what I meant. and when you add the trend line, you can set the color of the original like to white so it wont show (a tip though, create a copy somewhere, because if you need to find the "invisible" original line, it can be hard to find again

  39. #39
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    the problem with MAs is that the event are lagging the actual events, so a spike in IV or price at 09:32 won't show when it actually occurred but later on. you don't like the candlesticks idea? what I like about them is that they tell you the whole story (say, everything that happened in a certain day) in one clear bar. The challenge with those is how to deal with a change in the ATM Strike value. I don't know if you trade options so let me explain:
    the ATM Strike is the strike price closest to the price of the underlying asset at the time. If the asset is trading at 16.3 then the ATM options are the 16Call and the 16Put. If you trade the two together (buy them both or sell them both) you are trading a "Straddle" position, the 17Straddle (AKA buying or selling volatility). But if the price of the asset is later at 16.6 then the ATM Strike is 17 and you'll be trading the 17Straddle. pretty straight forward until you notice that the 17 and 16 straddles are priced differently(see those sharp spikes and drops in the left hand side of the chart? that's the ATM Strike shifting from 16 to 17 and back). Ideally, while still using this line chart, I'd have the color of the data points changing with the ATM Strike price, but I don't know if excel can even do that?

  40. #40
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    Re: a tricky "index(match)" situation?

    FDibbins: Boy do I have a challenge for you sir;

    you said you love fiddling with stuff like this so try this:
    The problem with the solution we have is that when the ATM Strike (the rounded value in AA) changes, theres a sharp shift in all other values, mainly in the IV (column S) of both the puts and the call. this 'skews up' the chart and messes up the results.
    Our solution: when indexing for the IV we should not only take that of the corresponding strike (ie 17/16, whatever the ATM Strike is at that time) but an average of a 3-strikes block around it. Here is the method we have in mind:

    say the SFP price(Q) is 16.5 let's deem this as 'base' value; the rounded value (AA) will return (=AVERAGE(16.5)=17). let's deem this our "ATM". Now here's how the 'block' is calculated:
    IF the original value was rounded UP, THEN the block is:
    ATM+1 (18)
    ATM (17)
    ATM-1 (16)
    ATM-2 (15)


    IF the original value was rounded DOWN(say the original value in Q were 16.49), THEN block is:

    ATM+2 (18)
    ATM+1 (17)
    ATM (16)
    ATM-1 (15)

    As you can see, the goal is to maintain the same 'block' for as long as possible, until there's a significant move in the ATM value.

    WHEN DO CHANGE THE BLOCK?
    Only whan a ROUND(base,0) function will return a different ATM. In other words we define a "dead zone" in which the block is indifferent to moves in the ATM values: as long as the 'base' (Q) doesn't breech Q+1/-1 (or in this case above 17.5/ below 15.5) the block doesn't change. Example: we have a base value of 16.5 right? so t was rounded UP to 17 (ATM=17) and so our block was 18,17,16,15. if in the next minute the base dropped to 16.43 it wouldn't change the block because since the first 'base' was 16.5 hence only when a new base below 16.5+1 and 16.5-1 comes the block will be calculated again. For clarification let imagine that some time after the base drops to 15.43. this is a clear breech of the 15.5 boundary so a new block is calculated: ROUND(15.49,0)=15; the base was rounded DOWN so the block is
    ATM+2 (17)
    ATM+1 (16)
    ATM (15)
    ATM-1 (14).

    I know it's quite a handful, I don't even know how to approach it (I am trying though) and more than anything I afraid to mess with the formulas you worked out the last time.. I'd appreciate your help when you find the time

    J
    Last edited by ryefield; 09-03-2013 at 01:31 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF(ISNA(INDEX,Match)"Yes", "No"
    By prince_xavier2001 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-22-2013, 11:36 AM
  2. Replies: 7
    Last Post: 04-11-2013, 12:46 AM
  3. Replies: 10
    Last Post: 12-18-2012, 07:59 AM
  4. Strings Variables in Range("A1").Formula = "=index/match" ?
    By nadnerb5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2012, 05:07 PM
  5. [SOLVED] Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function
    By Fiebuls in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2012, 04:09 PM

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