Excel Copying

#1
Hi All,

I have attached an auto refreshing excel sheet of HCL tech.

I am able to get the max volume using MAX formula.

I want to copy the related strike price where max volume falls for CALL & PUT.

Max volume in call put change on every refresh, so cannot use simple copy paste.

please help.

file link: 4shared
.com/file/v8KGdwOsce/hcl_sheet_2.html?

thax all. aman.
 
Last edited:
#2
use VLOOKUP function
give link L cell to X cell same strike price will be in CELL "L and X"
insert a column after Y cell, in Z2 =VLOOKUP(Y2,D4:L66,9,0) and in AB =VLOOKUP(AA2,T4:X66,5,0) paste the formula you will get the strike price of max call and put.

siva
 

Attachments

mastermind007

Well-Known Member
#3
Hi All,

I have attached an auto refreshing excel sheet of HCL tech.

I am able to get the max volume using MAX formula.

I want to copy the related strike price where max volume falls for CALL & PUT.

Max volume in call put change on every refresh, so cannot use simple copy paste.

please help.

file link: 4shared
.com/file/v8KGdwOsce/hcl_sheet_2.html?

thax all. aman.

Aman

Put following formula in Y3
Code:
=LOOKUP(Y2,D1:D900, L1:L900)
Put following formula in AA3
Code:
=LOOKUP(AA2,T1:T900, L1:L900)
Siva2005, LOOKUP works better in this kind of layout relative to VLOOKUP and also avoid having to make copy of strike prices again

Note that I am keeping the length as 900 whereas actual rows shown are only 29. Length is way bigger than what will normally be needed because (a) There is no performance penalty by keeping it large. (b) There is practical issue that has led me to keeping it so large. Once in a blue moon, refreshed Option page from NSE comes with lots of page breaks that Excel translates into rows and the whole sheet appears to have gone haywire, but it is correct because data has shifted to lower rows. I have encountered a refresh that placed Option data from row 750 and below whose headings were in Row 4.
 
Last edited:

Similar threads