Results 1 to 9 of 9

Thread: Excel formula requested for Open interest movers

  1. #1

    Excel formula requested for Open interest movers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Stocks name Strike Type Date Open interest change Name of stock Open Interest mover strike today Type
    YESBANK 680 CE 1/12/2016 YESBANK 680 CE
    YESBANK 680 CE 1/19/2016 5000000 Adani Port 210 CE
    YESBANK 680 PE 1/12/2016
    YESBANK 680 PE 1/13/2016 700
    YESBANK 680 PE 1/15/2016 -1400
    YESBANK 680 PE 1/18/2016 7000
    YESBANK 680 PE 1/19/2016 -700
    YESBANK 680 PE 1/12/2016 40600
    YESBANK 720 CE 1/15/2016 7000
    YESBANK 720 CE 1/18/2016 9100
    YESBANK 720 CE 1/19/2016 -58100
    YESBANK 720 PE 1/18/2016
    YESBANK 720 PE 1/12/2016 -3500
    YESBANK 720 PE 1/13/2016 -2100
    YESBANK 720 PE 1/14/2016 -1400
    YESBANK 720 PE 1/15/2016 -2800
    YESBANK 720 PE 1/18/2016 -2100
    YESBANK 720 PE 1/19/2016 -4200
    ADANIPORTS 205 PE 1/15/2016 0
    ADANIPORTS 205 PE 1/18/2016 14400
    ADANIPORTS 205 PE 1/19/2016 3200
    ADANIPORTS 210 CE 1/18/2016
    ADANIPORTS 210 CE 1/15/2016
    ADANIPORTS 210 CE 1/18/2016 20800
    ADANIPORTS 210 CE 1/19/2016 20000000
    ADANIPORTS 210 PE 1/18/2016
    ADANIPORTS 210 PE 1/19/2016 1600


    hi friends i have an excel data as above , for the above i want to search open interest mover for the today(here i suppose it 1/19/2016). My definition for open interest mover is open interest added on 01.19.2016 exceeds open interest on that stock in all the dates in which data is available( here data is available from 01.12.2016 to 01.19.2016(last date).The Output of the formula for the stock is mentioned in separate column shown I and J. I have data for many such stocks and new rows are getting added each day in the table. For example for stock named yes bank Highest open interest added is in the row E10. Can some expert help me to write the formula for column I and J as shown.iF there is no output then formula(in case the highest open interest added today (01.09.2015) is not maximum of all the data then should print an error message as "sorry".
    Thanks all

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,512
    Articles
    0
    Excel Version
    Excel 2016
    Assumptions:

    1. Name of Stock in column H matches exactly names in column A
    2. Date of interest is in L1 (you can enter formula in L1: =TODAY() for today's date)

    Do this:

    In another cell, say M1, enter formula: =MATCH(REPT("z",255),A:A)
    This determines the last row in column A, so that we can set a moving range size for the array formula we need for the final results.

    Now in I2 enter formula:

    =IFERROR(INDEX(B$2:INDEX(B:B,$M$1),MATCH(1,($A$2:INDEX($A:$A,$M$1)=$H2)*($D$2:INDEX($D:$D,$M$1)=$L$1)*($E$2:INDEX($E:$E,$M$1)=MAX(IF(($A$2:INDEX($A:$A,$M$1)=$H2)*($D$2:INDEX($D:$D,$M$1)=$L$1),$E$2:INDEX($E:$E,$M$1)))),0)),"Sorry")

    confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down and over to column J.


  3. #3
    Thanks Sir for your help.I applied the formula a small amendment is requested.The Logic i am looking for is Match names in column H with column A and find maximum of column D and column E in which contents of A,H matched . If the row numbers of maximum of column D and E match then our output should be contents of column B,C in column I,J. Mistake is mine that i could not write the exact wording of my requirement.
    Thanks and best regards

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,512
    Articles
    0
    Excel Version
    Excel 2016
    See attached for my interpretation. Is it correct?
    Attached Files Attached Files


  5. #5
    Thanks sir for your reply and devoting your valuable time. My observation is here http: //imgur. com/uW5EaGP
    (Please remove space before com and after double colon ,as forum rule is not allowing me to update a image/file )
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Reply to forum.jpg 
Views:	22 
Size:	106.1 KB 
ID:	4445  

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,512
    Articles
    0
    Excel Version
    Excel 2016
    Ok.

    Try:

    =IF(MAX(IF(($A$2:INDEX($A:$A,$M$1)=$H2),$E$2:INDEX($E:$E,$M$1)))=MAX(IF(($A$2:INDEX($A:$A,$M$1)=$H2)*($D$2:INDEX($D:$D,$M$1)=$L$1),$E$2:INDEX($E:$E,$M$1))),INDEX(B$2:INDEX(B:B,$M$1),MATCH(1,($A$2:INDEX($A:$A,$M$1)=$H2)*($D$2:INDEX($D:$D,$M$1)=$L$1)*($E$2:INDEX($E:$E,$M$1)=MAX(IF(($A$2:INDEX($A:$A,$M$1)=$H2)*($D$2:INDEX($D:$D,$M$1)=$L$1),$E$2:INDEX($E:$E,$M$1)))),0)),"Sorry")

    confirmed with CTRL+SHIFT+ENTER and copy down and over.



  7. #7
    Not working Sir, it also having errors.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,512
    Articles
    0
    Excel Version
    Excel 2016
    See attached.
    Attached Files Attached Files


  9. #9
    Thanks Sir, its perfect> I love Excelguru. Warm Regards

Tags for this Thread

Posting Permissions

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