Excel sheet for FII trading activity
FII trading activity is very important to understand and analyze the stock market. This data is available on the NSE website. A lot of retail investors find it difficult to understand the data points of FII trading activity, without understanding the data points you cannot understand the future and options trading activity and be profitable in future trading.
I this blog we are sharing important parameters that are most for future trading.
Procedure to Record FII trading activity
- Visit FPI website
- Watch values in Index Futures, Index Options, Stock Futures, Stock Options, Interest Rate
- Check the highest value in Gross Purchases
- Visit NSE website
- Select Participant wise Open Interest from the report section
- Select date and download FII trading activity file
- Record three different parameters Index Future, Index Options, Stock Options
- Copy and paste the respective data in different excel files.
Excel sheet for FII trading activity
- Download FII trading activity
- Visit NSE website
- Select Participant wise Open Interest from the report section
- Select date and download FII trading activity file
- Copy and paste option data in different as per the sample file.
- Note changes in FII Data
- There are 4 different data points 1. Index future 2. Index Call 3. Index Put 4. Stock Future
FII trading activity Data Analysis
- FII trade against Client and DII Because if trade only happens when there are one buyer and one seller.
- Find out the most active positions in 4 different data points e.g. Index Future - Call - Put - Future
- FII will active mostly in Index Future and stock future. Their activity in call and put is limited. However, we need to track the options data also
- In the first step we need to check the net positions in the net position column.
- Net position is very important data.
- Analyze the daily data with regard to net positions
- If net position is bearish and daily data suggest bullish it is a sign of reversal.
- Analyze all 4 data points and check the positions of other clients too.
- Check who has similar positions like FII e.g. If pro + FII have bearish positions in the market against client market will move down and vice versa
- There an always pro and FII rarely take positions against each other
- If FII is active in Index future. Pro Will be active in Index options
- If FII is active in Options pro takes positions in index future
- These two combines are like a whale and they eat small fish e.g. Client positions
- These two FII + Pro take exact opposite trade against client.
- Study all 4 people data point to find the direction of the market.
Posts
52 Comments
Thank you for your great effort,
ReplyDeletecould you please answer below what if Position is increased in call long and put short as well as Position is increased in put long and call short.
Does it mean indecision.
Thank you for your valuable comment.
DeleteIn this case Look for highest quantily on put or call side compare them. Higher the quantity bigger the risk.FII never loose money. If they trade both side they will cover their position in lose on small quentity and book profit on bigger quantity.Hope it will clear your confusion if you have an other question on any pages ask your question.
Quite sensible and logical, one more thing i wanted to ask, i think we should concentrate more on FIIS and proprietary trader data right ?
ReplyDeletePlease correct me if I am wrong.
Yes reason is simple they have more money then retail investors.
DeleteI am new to your blog.
ReplyDeleteWhy DII data not captured in the excel sheet. is there position is not important for the analysis?
Hi How this data helps to gauge market direction?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteI recently started following your blogs and some of the doubts which i had from ages got clarified by your posts. Thanks a lot.
ReplyDeleteSome doubts on fii data
1. What are we doing with data from FPI site? IS this needed for analysis
2. Once we collect open interest data from nse site how do we analyze it. With which we need to compare the data?
3.Does "open interest at the end of the day" data from fpi site of any significance?
4. In the excel which is uploaded , difference in open interest is blank. This is the difference between which 2 values?
5.Can we understand if FII postions are still open or partially closed their position from this?
Thankyou in advance
https://stockfuturesnse.blogspot.com/2019/09/fii-dii-client-pro-derivatives-open-intrest.html check this link hope this will answer some of your questions. If you are still in doubt write us back.
ReplyDeleteCan you please tell me thr difference between Smart money and FII s
ReplyDeleteHow to find out where they are active ? in future or in options?
ReplyDeletehttps://stockfuturesnse.blogspot.com/2019/09/fii-dii-client-pro-derivatives-open-intrest.html check this link hope this will answer some of your questions. If you are still in doubt write us back.
DeleteGood information. Thanks for sharing. If you want to know more about stock market related topics then visit Bank Nifty
ReplyDeleteBank Nifty
HFFC IPO
Stock Market
About file "Participant wise open interest".
ReplyDeleteThis file is available on daily basic so my question is that the OI/contracts it is showing are these fresh contracts generated on daily basic or are these the open positions from the beginning of current series ?.
For example:
1. On 8-Apr-2020 file, I see that 52931 contracts on "Future Index Long" column against FII.
2. On 9-Apr-2020 file, I see that 63410 contracts on "Future Index Long" column against FII.
If above is the data so how do I read that:
Thought 1: FII made fresh 52931 positions on 8-Apr and fresh positions 63410 on 9-Apr.
Thought 2: Till the eod of 8-Apr total open position of FII was 52931 and on eod of 9-Apr-2020 the open position of FII is 63410 which means that today they made some new position(10479) which increased the OI to 63410.!
Could you please suggest on this.
Thought 2 new position 10479 is added
ReplyDeleteHi Team, Many thanks.
DeleteThis comment has been removed by the author.
ReplyDeleteThere are two styles of contracts in Future and Option segments i.e, European and American.
ReplyDeleteAnd it is said that we follow European style and in this style the contract can only be exercised on expiry day and not before that but I keep hearing that the contract can be exercised by their buyer and seller at any time before or till expiry so it confuses me whether we are following European or still American style ?
Could you please suggest on this.
About file "Participant wise open interest".
ReplyDeleteThis file has contracts of Future and Option segments.
Is there any way whether the OI/Contracts in this file belong to current week expiry or monthly expiry !.
Could you please suggest on this.
no
DeleteIn this "Participant wise open interest" file there are four types of entities, FII,DII, PRO, CLIENT.
ReplyDeleteSo my question is about 'CLIENT' does it mean retail traders data like me and other retail traders ?
clients includes some HNI and retail investors of india
DeleteThanks.
DeleteProbability is of high failure for Retail investors but what about HNI ?
The first problem for retail investor is limited amount of money. They keep the stop losses small. Stop loss trigger many times. No funds for averaging. HNI investors take their positions in different types like if they short nifty future they will buy call options or sell put options for hedging. Retail investors cant do that. Problem is money. It is very difficult to explain.
DeleteThanks.
DeleteVery true.
Does short covering on option contract has any impact on Nifty ?, I am guessing that it has no impact on Nifty but only impact on particular strike price of option because price will increase now as there are more buyer. !!
ReplyDeleteCould you please suggest on this ?.
Volume in cash segment means total traded quantities whether intraday or deliverable ?. Is that correct ?
ReplyDeleteHi,
ReplyDeleteHave downloaded the excel sheet. Pls resolve the following query
1. In the Summary Section:
a. How is the value arrived or referenced in the column" Net Data" ?
b. How to arrive at the value for "Analysis" column. For e.g. For the entire "Client" block how to arrive whether its Bullish or Bearish?
c. How to determine the SMART MONEY? Which Columns under which section to be used?
It will be nice if you can download th latest sheet and explain with respect to that.
Thank you
Data is collected from nse file. Client wise open interest positions. All the data is drived from there.
ReplyDeleteIf you create Index long positions you will be bullish in market. If you create index short positions you will be bearish in market.
If you reduce index long positions you are now bearish .
If you Reduce index short position you are bullish.
For smart Money. FII - DII - PRO are smart money . If any two have same positions then they will turn the market in their favour. They always take postions aganist the retail investors.
The excel sheet is latest. With date wise previous data is also provided for backtesting.
If you have any other questions please share with us.
Visit this link this will clear your some doubt
http://stockfuturesnse.blogspot.com/2020/05/fii-derivative-data-of-nifty-future.html?showComment=1591156936188#c556634398829854545
Hi,
DeleteThanks for your response. I have pasted below the data of 5th June from your excel sheet. See below in this reply.
Pls resolve the following Query:
a) By looking at the Daily Data and Net Data columns, how to decide / comprehend whether the "Overall Analysis" is Bearish or Bullish. Pls explain by taking an example of say client or FII or Pro from the below data.
b) Second Query is: Please help me to identify SMART MONEY from the below data considering that two of them is having same position and the 3rd part is having opposite position.
I have taken the pain to type the complete data for Client, FII, PRO, DII below.
Thank you
Summary Daily Data Net Data Overall Analysis
Client Index Future -371 Bearish Bearish
Client Index Calls -10208 Bearish Bearish Bearish
Client Index Put 27587 Bullish Bullish
Client Stock Future 8036 Bullish Bullish
Summary Daily Data Net Data Overall Analysis
FII Index Future -5503 Bearish Bullish
FII Index Calls 12530 Bullish Bullish Bearish
FII Index Put -2059 Bullish Bearish
FII Stock Future 17887 Bullish Bullish
Summary Daily Data Net Data Overall Analysis
Pro Index Future 5820 Bullish Bearish
Pro Index Calls -2322 Bearish Bearish Bearish
Pro Index Put -25198 Bearish Bullish
Pro Stock Future -12046 Bearish Bullish
Summary Daily Data Net Data Overall Analysis
DII Index Future 54 Bullish Bullish
DII Index Calls 0 Bearish Bullish Bullish
DII Index Put -330 Bearish Bearish
DII Stock Future -13877 Bearish Bearish
If you really want to understand the data first you need to feel yourself you have that you have the positions or other person is taking the opposite positions against you.
ReplyDelete1st example: I first look at the daily data find out where the maximum trading took place. After looking the data i found that maxmium trading is happened in Index Put - Index Calls.
What happened in index put - Client buys Index put long positions 198342 contracts. and 27587 contracts put long - Means they took bearish positions after the today market move. Look at the who is selling the puts - Pro. Hint 1 . Pro already have - 49247 contracts and today they added -25918 contracts.
Next Hint 2 : Client Index short . They are selling calls if the market move up they will lose money . Client already has -23931 contracts and they are adding -10208 contracts today because they love averaging. We all love averaging. and pray for god if my average price come i will exit. That is why they keep adding if market move against them. They took bearish positions and they are net sellers in index calls. So who is buying calls - FII.
Hint 3 ; Index future data . Client are net short in index futures they dont try to average today. They are already in loss. FII are reducing positions but they are increasing positions in Stock futures. Means they hedged their positions with index future. How i know they hedged because they buy 17887 contracts future long and short index future with -5503.
Now who is smart money here
After looking at the client positions they are net short in Futures and Calls. they will earn only through index put short positions but they book the profits and keep averaging in futures and calls typical retail client thinking. They put their gains from index put positions in calls and future positions for averaging means they are in net loss.
Look at fii - they are long in futures- Calls- Stock futures and hedged through index put long. They took swing positions book at least 10-15% profits in thier positions and losses are limited. Typical FII.
Look ar pro data : No positions in index futures : No loss Shorts positions in calls bearish but double positions in index put short bullish positions. and stock future data is net positive but they are reducing their positions.
After analyzing the data : Retail client are bearish : FII are creating short term positions in Stock futures to kill retail client positions. Pro are supporting FII by creating Put short positions.
What is key findings : How this works: You will lose some part but you gain from other side. You gain double from one side and lose only half from other side net you are in profit . This is how pro and FII are managing positions.
What retail investors are doing : Keep averaging positions from the profits of put short positions keep losing money. Why retail investors create these positions because of tv anchors.
If you need more information write us. Ask as many questions as possible and start making money.
And Please share your blog and help us.
DeleteHi,
DeleteThank you for your elaborate response. Appreciate your assistance and guidance.
Few Queries from your response:
1) Next Hint 2: What is the parameter on the basis of which it can be concluded that clients are averaging? I mean that since client added a substantial number of -10208 contracts that is why it can be concluded ?
Consider a situation wherein clients just added -300 contracts , so in this case we can conclude that they are not averaging?
2) Hint 3: You concluded that clients are net short in index Future by looking at which column ? ("Index future Daily Data" or "Total Positions" column or "Net Trade today" column)
3) Hint 3: how to conclude that Clients are NOT Averaging Today and are in loss? (Which columns to refer from the excel sheet - Index future Daily Data" or "Total Positions" column or "Net Trade today" column?)
4) Who is Smart Money here: Please see below:
After looking at the client positions they are net short in Futures and Calls. they will earn only through index put short positions but they book the profits and keep averaging in futures and calls typical retail client thinking. They put their gains from index put positions in calls and future positions for averaging means they are in net loss.
Query: I am unable to comprehend above line. Please elaborate using the figures/numbers please.
5) look at FII: Please see below:
They took swing positions book at least 10-15% profits in their positions and losses are limited. Typical FII.
Query: How did we comprehend this that they have taken SWING position and booked 10-15% profit?
6) Look ar pro data : Please see below:
No positions in index futures : No loss Shorts positions in calls bearish but double positions in index put short bullish positions. and stock future data is net positive but they are reducing their positions.
Query: How did we conclude that no positions in Index Future ? In my understanding they took Long position in Index future since they added long 2728 contracts and exited their short positions -3092. Therefore Today's total position is reduced to just -256 contracts. Pls guide.
Query: Pro net shorted today -2322 contracts in CALLS and thus increased their bearish viewpoint from -36792 contracts to - 39114 contracts today (in Total Positions column). How did we conclude that no loss short positions in calls?
7) After analyzing the data : Please see below:
Retail client are bearish : FII are creating short term positions in Stock futures to kill retail client positions. Pro are supporting FII by creating Put short positions.
Query: Unable to comprehend this? Please elaborate with figures/numbers.
Thank you in advance for your guidance and understanding.
Hi,
DeleteI am referring to to the excel sheet of 5th June 2020 and evaluated your response to this excel sheet and came with further queries and published it few minutes back.
In excel sheet there are four important data points.
ReplyDeleteYou need to check the total positions data and co relate the daily data.
E.g. If buys 100 at the start of month and on daily basis yo are adding 10 contracts your total positions are 150.
Total position data is the main data. Daily data is to identify when FII are changing their positions.
. Daily data : Total positions : Net Trade & Summary.
Daily data shows the daily change in positions . Why it is helpful ? It is helpful to identify when FII & Pro & DII change the positions.
Total Positions : Shows the sentiment or trend . Why it is helpful ?
It is helpful to identify whether we need to buy in dips or sell in rise.
If FII have net long or bullish positions we need to buy in dips . If FII are in net short positions we need to sell in rise.
Net Trade : Is daily Data : of subtracting the long and short positions in daily.
Summery is a collection of all the data points in one positions. So that we can
1) Next Hint 2: What is the parameter on the basis of which it can be concluded that clients are averaging? I mean that since client added a substantial number of -10208 contracts that is why it can be concluded ?
Answer = Let me clear you. if they have -ve positions in index call positions They will lose money if market move up & vice versa.
Query: How did we conclude that no positions in Index Future ? In my understanding they took Long position in Index future since they added long 2728 contracts and exited their short positions -3092. Therefore Today's total position is reduced to just -256 contracts. Pls guide.
Answer : Please refer the 1 day ago data of total positions data points.
I day age they are in net short of -6076. They booked their losses when market moved up.
Look at 1 day ago data -23931 positions they added -10208 contracts while market is moving up. Ideally they need to close their short positions. If market moves they will loose money
Hint 3: You concluded that clients are net short in index Future by looking at which column ? ("Index future Daily Data" or "Total Positions" column or "Net Trade today" column)
Answer : Total Positions decides the trend or thinking.
4) Who is Smart Money here: Please see below:
Answer : Look the data in index put section Total positions : 1 day ago it was -89082 today it is -61495. They are in profit in this positions but some retail clients booked their profits put the money in Call short positions.
Query: How did we comprehend this that they have taken SWING position and booked 10-15% profit?
Look at the Stock future data positions it is increasing every day the total positions data points .
Query: Pro net shorted today -2322 contracts in CALLS and thus increased their bearish viewpoint from -36792 contracts to - 39114 contracts today (in Total Positions column). How did we conclude that no loss short positions in calls?
Because they added -25198 contracts in index put.10 times more than index calls
They are not adding Index futures positing but are adding stock future positions.
If the view is for long term they will buy in cash instead of futures.The day they close their stock future positions market will fall.
Hi,
DeleteThank you for your reply.
Please can I have your contact email id and can take it there also if you are ok?
Hi Anurag,
DeleteWe can share our email id but it will be helpful for us if you post questions in blog. It solve two problems. Other retail investors have similar questions if we answered they too get their answer and your queries also solved.
If you face any difficulty in understanding the data it is our improvement point so keep posting. Help us to make it simple to understand. Share where you are find the excel difficult to understand.
Which data points is difficult to understand. write us back we will improve based upon your queries
This comment has been removed by the author.
DeleteHi,
DeleteI understand.
However, I need to communicate with respect to some opportunities.
Let me know how to take that ?
Thanks
Hi,
ReplyDeletePlease if you could verify my analysis of the data for 8th June 2020. (It doesn't have the provision to upload the excel sheet, so please fetch it for google drive),Please refer to the Query raised by me, validate and and provide your valuable inputs.
Hint 1: Index Calls has maximum contracts. Clients exited their bearish positions and became bullish today by ADDING NEW POSITIONS. Clients went long and bought it from Pro
Query: In my understanding clients did not make profits and simply made new positions. Pls confirm
Hint 2: Index Puts have second highest number of contracts. Client is bullish but reduced/exited positions as compared to yesterday. Pro is bullish and added 19209 contracts. Client bought it from Pro.
Query: In my understanding clients booked profits. Please confirm
Hint 3: Index Future: Clients are net Long (10854) and reduced their total position to -22373.
Query: Clients booked profit and thus their position got reduced. Please confirm.
Clients bought it from DII (-2779) and FII (-4872)
DII and FII reduced their bullish position and hedged it in Stocks future by going net long.
Stocks Future:
Clients in a net short position -25321 and thus have reduced their total position compared to 1 day before.
DII hedged its Index future position with net long position here - 13324
FII hedged its Index future position with net long position here - 12547
================================================
Who is SMART MONEY HERE:
1. Clients are bullish in CALLS
2. Clients booked profits in PUTS as bulish positions reduced from -61495 to - 45028
3. QUERY: In Stock futures, Clients reduced their total position bullish from 232237 to 207006. What does this exactly mean? They sold it at a loss ?
FII: Hedged in stock future (long) in comparison to Index Future (short)
Pro: Bearish positions increased in CALLS -47002
Bullish in PUTS as positions increased to -93654 from -74445
Conclusion: FII is supported by PRO
FII, Stock Future, Bullish 613084
PRO, PUTS, Bullish, -93654
Therefore SMART MONEY = FII + PRO
Thank you
Hi Anurag,
DeleteEvery day we write a blog about stock market report date wise before market opening around 8 AM. Which includes news and Derivatives data analysis. You can match your analysis their and if you have any queries write us back on daily stock market report blog.
Hi,
DeleteSure. Pls share the direct link (URL) for the blog.
Thank you
Check stock market report date wise it includes the data analysis
Deletewhere i get net postion values
ReplyDeletedownload the excel sheet.. It contains daily data, total positions is the net position values .
Deleteif i want to prepare where should i take the net position values from
DeleteDownload the excel sheet. Check total position it is a net position of derivative data.
Deletehow to identify who position is what ???
DeleteOn left hand side client DII FII Pro are available. They are the 4 type of people in stock market.
DeleteClient refer to retail investors - Individual traders
DII - refers to banks or mutual funds house
FII - Foreign investors
Pro - Brockrage house
How to get latest total position.
DeleteHey, thanks for the information. your posts are informative and useful. I am regularly following your posts.
ReplyDeleteYes Bank Public Offer
Can't understand how to get total position.
ReplyDeleteThank you for your valuable comment