Stock screener using Open Interest Volume Volatility

#1
This is a live analysis of stock based on OI, volume and volatility.
This excel sheet pulls data from nseindia live data. You have click the 'Refresh all' button to see latest value. It takes around 5 min.
Column M to P are my own analysis.
Column M is change in open interest from previous day.
Column N and O will turn blue if Volume > OI.
I request people add their ideas for defining rules for shortlisting stocks for intraday.



https://drive.google.com/open?id=0BxnCoEWhIaiLRGtwVVFDNlFFS3M
 
#2
Folks, this is the next version of this system.
This time google spreadsheet is used to extract data. Data is fetched from moneycontrol and updated every 5 min.
Current version contains scrips for all banking stocks.
You can check the live data published here LINK
https://docs.google.com/spreadsheet...jnNMQ8Uz2mvWD2B1iArmb65A4h3nrKF5LGrb9/pubhtml

Let me brief you about the columns.
1. Future OI: This is percentage change in open interest compared to previous day close.
2. Future Vol: Percentage change in futures volume.
3. PCR(OI) : Percent change in Put/Call OI ratio from previous day.
4. CPR(Vol): Percent change in Call/Put Vol ratio from previous day.
5. Call Vol: Percent change in Call option volume.
6.Put Vol: Percent change in Put option volume.

Below is example of 27-Oct-2017.


Rules for buy sell I want to discuss. Please feel free to discuss your thoughts/ideas to define the rules. I am posting here for this purpuse. There are several room for improvement to be done in next versions based on your input.

My idea for finding trend is below (based on reading from different discussions).
Future data gives the hint of stock strength and option data gives direction.
Buy Rule: 1. PCR(OI) is positive. Sell Rule: 1. PCR(OI) is negative.
2. CPR(Vol) is positive. 2. CPR(Vol) is negative.
3. Call Volume is positive. 3. Put Volume is positive.

The next version I am planning to add the change in all above values every 15 min break.
There will be separate page for each individual stock which will have a link from above page.
You can see in below image how these values are progressed every 15 min gap.
This development is not complete, just for your reference how it will look.



Please let me know your thoughts on developing strategies. For example we will sort the sheet based on last 15 min PCR change.
If the stocks with highest PCR change is accompanied by change in Future also that mean lots of hedging is going on that stock.
This is just one example. I am not sure exactly how it will work. New ideas from different people can make a boost.
 
#4
A new version is ready. You can see a link corresponding to each bank that will take to a detailed sheet.
Click Here

This is how it will look. Take the case of SBI.


There are 2 tables. Top one is table A and bottom one is table B.
Table B description:
1. TIME: This is the time when this sheet is updated. The data in blue box is about the current values. The data in Red box contain values every 15 min. Column C,D,E are last 3 five minute breakups.
2. PCR(OI): This is the percent change in PCR from previous day. Example: at 12:10 PCR is 0.37, that means it is 0.37% up from previous day.
3. CPR(Vol): example: at 12:10 it is -61.52. This means volume of call option is 61.52% less than volume of put option.
Rest you can understand in similar way.

Table A is increase or decrease of same data from previous bar.
Currently this script begins at 9am and stops at 3pm. It captures last 3 hours data.
Always combine these signals with technical indicators (like heiken ashi) for confirmation.
I request everyone to analyze different combination and post their result.
If anything else is missing to find trend direction please let me know I will consider for future versions.
 
Last edited:

LOVEENAJYOTHI

Well-Known Member
#5
A new version is ready. You can see a link corresponding to each bank that will take to a detailed sheet.
Click Here

This is how it will look. Take the case of SBI.


There are 2 tables. Top one is table A and bottom one is table B.
Table B description:
1. TIME: This is the time when this sheet is updated. The data in blue box is about the current values. The data in Red box contain values every 15 min. Column C,D,E are last 3 five minute breakups.
2. PCR(OI): This is the percent change in PCR from previous day. Example: at 12:10 PCR is 0.37, that means it is 0.37% up from previous day.
3. CPR(Vol): example: at 12:10 it is -61.52. This means volume of call option is 61.52% less than volume of put option.
Rest you can understand in similar way.

Table A is increase or decrease of same data from previous bar.
Currently this script begins at 9am and stops at 3pm. It captures last 3 hours data.
Always combine these signals with technical indicators (like heiken ashi) for confirmation.
I request everyone to analyze different combination and post their result.
If anything else is missing to find trend direction please let me know I will consider for future versions.
Thank you ,
Possible to Edit/Customize those sheets ?
Regards.
 

newtrader101

Well-Known Member
#7
Great research, Dillip. Thanks for the spreadsheet.
 
#8
Bid/Offer Ratio analysis:

A new toy based on Bid/Offer ratio. Few months back I joined a paid site to receive stock signals. What I observed is they are looking for stocks where abnormally high bidding/offering comes. Then they ask us to place bid at a slightly higher price. Sometimes it work very well but they stop sending signal after 1 success. Another issue is sometimes the target is reached very fast(less than 1 min). As per their theory market moves well when high quantity buying selling happens. So instead of looking into some fancy technical indicators check the bids.
Then I started thinking to have a tool to capture this data. Data is extracted from ICICI Direct site(Sample).

Sharable Link. Published Link.

Features:
1. Column F and G: This is current Bid and Offer quantity multiplied with the stock price. If you sort the spreadsheet on this field it will show stocks where highest bidding is going on.
2. Column H: The ratio of Bid/Offer quantity.
3. Column B and C: This is the change in Bid/Offer value in last 15 min. So if you sort on this field you will get recent data in terms of absolute amount. Last 15 min means if current time is 10:17 am it will show the data between 10:02 to 10:17.
4. Column D: Ratio of Bid/Offer quantity change in last 15 min
5. Column E: This is another way of evaluating the change in bid/offer quantity in last 15 min. Example: for SBI column E shows 'U10 D-1'. This means bid quantity is increased by 10% and offer quantity is decreased by 1% in last 15 min.
Hint: When bid quantity growing and offer quantity is falling means bulls have upper hand. When both are increasing this means its a close fight between bulls and bears with no clear direction.

Column M onwards UD data is recorded for post market analysis. If some other important data recording is required for post market analysis I have to modify this sheet.
I request the members of Tji to observe the sheet for few days and post their findings. More eyes can see what I cant see alone. I will make necessary changes if required accordingly.
 

headstrong007

----- Full-Time ----- Day-Trader
#10
Hi, above excel is good. :up:
If possible made exactly similar excel with all NIFTY 50 stocks.
ADD NIFTY AND BANK NIFTY FUTURE TOO ON TOP TWO ROWS OF BOTH EXCEL:-
1. BANK STOCKS(ABOVE EXCEL) AND 2. NIFTY 50 STOCKS FUTURES.
ABOVE EXCEL BANK NIFTY HAS NO VALUE!
 

Similar threads

Broker Special Offers

Zerodha – Open Paperless Account

Open online account with Zerodha. Free delivery trading and Max Rs 20 for Intraday, F&O, Currency and Commodity Trading. Intraday High leverage with MIS, CO and BO.

Name:Phone:
Email:City:
State:
Are you a day trader?