# Thread: Excel formula requested for Open interest movers

1. ## Excel formula requested for Open interest movers

 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. 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. 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. See attached for my interpretation. Is it correct?

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 )

6. 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. Not working Sir, it also having errors.

8. See attached.

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

#### Posting Permissions

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