{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["CostofCarry","Fut-Stock Arb","FactorModel","FactorModel_Simulations","VLookup","Open Interest","Sheet2"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"CostofCarry","index":0,"n_rows":41,"n_cols":12,"rows_kept":41,"cut":false,"hidden":false,"merged":[]}
[]
[]
["I","Future contract is the expected value of the underlying at the date of expiry!",null,null,null,null,null,null,null,null,null,"1st Feb"]
[null,null,null,null,null,null,null,null,null,null,null,"Stock is trading at $100"]
[null,"Future contract has an expiry date",null,null,null,null,null,null,null,null,null,"Future of that stock expiring on28th Feb might be trading at $102"]
[]
[]
[null,"Fut = Stock price * e^rt"]
[null,null,"t= time left for expiry"]
[null,null,"r is the rate of interest"]
[]
["II","Profit and Loss for a Future contract"]
[null,"A buys a Fut cont on 1st Feb for $102",null,null,null,"Fut contract expires on 28th Feb"]
[null,"B sells a Fut cont on 1st Feb for $102"]
[null,null,null,null,null,"If the stock ends at 110$ on 28th Feb"]
[null,"Open Int is",1,null,null,"A wins $8","110-102"]
[null,"Volume",1,null,null,"B loses $8"]
[]
[]
["III","Fut price - Stock price = Cost of carry"]
[null,"Spot price","interest rate "]
[null,100,0.02]
[null,"Current Future price  (expiring in a year)"]
[null,102]
[null,"Cost of Carry"]
[null,2]
[]
[]
["IV","Delivery Vol",null,null,null,null,null,"SBIN"]
[null,"Volume traded ",null,1000]
["100 stocks were taken as delivery",null,null,null,null,null,null,"Trader A buys 100 stock in the morning and sells 100 stock in the afternoon"]
[null,"%delivery",null,"100/1000",null,null,null,"This implies that his trading account will not get any stock!!"]
[]
[null,null,null,null,null,null,null,"Trader B buys 50 stock in the morning and keeps them till EOD."]
[null,null,null,null,null,null,null,"This implies that 50 stocks of SBIN will get added in his trading account!!"]
[null,null,null,null,null,null,null,"This is known as taking delivery!!"]
[null,null,null,null,null,null,null,"Delivery vol would be 50."]
[]
[null,null,null,null,null,null,null,"Total Volume traded would be 250"]
[]
[null,null,null,null,null,null,null,"% delivery Vol = 50/250"]

{"sheet":"Fut-Stock Arb","index":1,"n_rows":23,"n_cols":6,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,"Expiry"]
[null,"1st Feb",null,"28th Feb","Profit"]
["Stock Price",100,"Bought 100 shares",120,{"$f":"100*(D4-B4)","v":2000},"=100*(D4-B4)"]
[null,null,null,"Fut Price"]
["Fut Price",103,"Sold 100 futures (Sold 1 lot of future, where 1 lot is 100 qty)",{"$f":"D4","v":120},{"$f":"100*(B6-D6)","v":-1700},"=100*(B6-D6)"]
[null,null,null,"Total",{"$f":"SUM(E4:E6)","v":300}]
[]
[null,"I have 2 options"]
["(1)","Either I should square off these bought shares and sold futures in the middle of the month"]
["(2)","or I can sell the Mar Futures",null,"Rolling over",null,"So they could sell 100 futures of Mar expiry"]
[]
["However Rolling over isn't an attractive option if: "]
["Difference between Mar fut and Stock  is not high"]
["Mar Fut is trading at 104.5"]
["stock is trading at 104"]
[]
[]
["Trade"]
["Sell at 3pm"]
["Square off the sold position at ~3:15 or 3:20pm"]
["Enter a new buy at 3:20pm"]
["Square it off the next day"]

{"sheet":"FactorModel","index":2,"n_rows":61,"n_cols":15,"rows_kept":61,"cut":false,"hidden":false,"merged":[[15,3,15,5]]}
[null,null,null,1,null,2,null,3]
[null,null,"Factors:","change in OI ",null,"change in CoC",null," change in delivery volume"]
[]
[]
[null,null,"Signal = W1 * change in OI +  W2* change in COC + W3 * change in delivery volume"]
[]
[null,null,"Factors"]
[null,null,"W1 ",0.4,null,"These are found by simulation"]
[null,null,"W2 ",0.5,null,"Build a model and check which combination of weights is giving me the best profits"]
[null,null,"W3",{"$f":"1-D9-D8","v":9.9999999999999978E-2},"weights",null,null,null,null,null,"Weights","Range"]
[null,null,null,{"$f":"SUM(D8:D10)","v":1},{"$f":"D8","v":0.4},{"$f":"D9","v":0.5},{"$f":"D10","v":9.9999999999999978E-2},null,null,null,"w1","0.1-0.9"]
[null,null,null,null,null,null,null,null,null,null,"w2","0.1-0.9"]
[null,null,null,null,null,null,null,null,null,null,"w3","1-w1-w2"]
[]
[null,null,null,"This from Historical Data"]
[null,null,null,"Change"]
[null,null,null,"Change OI","Change COC","Change in DV",null,"Signal","Action"]
[null,null,"Day1",0.90401467302498029,0.62599917937409255,0.61184830620790165,null,{"$f":"SUMPRODUCT(D18:F18,$E$11:$G$11)","v":0.73579028951782866},"BUY"]
[null,null,"Day2",4.4658153467628026E-2,0.7041644096000742,0.82173210902505889,null,{"$f":"SUMPRODUCT(D19:F19,$E$11:$G$11)","v":0.45211867708959419},"NO Action"]
[null,null,"Day3",0.18063911672301503,0.98495135039078408,0.5750844159009475,null,{"$f":"SUMPRODUCT(D20:F20,$E$11:$G$11)","v":0.62223976347469279},"NO Action"]
[null,null,"Day4",-0.4,-0.233444202567411,-0.10560333962351399,null,-0.28728243524605696,"SELL"]
[null,null,"Day5",0.39598616391096098,0.16043503346653387,0.69166584786558039,null,0.30777856708420936,"NO Action"]
[]
[null,null,null,null,null,null,null,"Find the optimum threshold of signal through simulations"]
[null,null,null,null,null,null,null,"Threshold is >70%",null,"for BUY"]
[null,null,null,null,null,null,null,"Threshold< -25% ",null,"for SELL"]
[]
[]
[null,null,null,null,null,null,"Simulation",null,null,null,null,null,"weights"]
[null,null,null,null,null,null,1,"Profit ",0.25,"annually",null,"for",0.3,0.5,0.2]
[null,null,null,null,null,null,2,"Profit ",0.3,"annually",null,"for",0.4,0.5,9.9999999999999978E-2]
[]
[]
[]
[null,null,null,null,null,null,"Mean","Std dev"]
["what if the change in OI keeps between -4% and +10%",null,null,null,null,null,7.0000000000000007E-2,0.05]
["change in DV keeps between 10% and 70%",null,null,null,null,null,0.4,0.1]
["change in CoC keeps between -1% and 2%",null,null,null,null,null,5.0000000000000001E-3,2.5000000000000001E-3]
[]
["For normalization:"]
["Day1"]
["OI is say 14% then how many std dev away is it from its mean",null,null,null,null,null,"OI","Change in OI is 14%",null,"how many std dev away"]
[null,null,null,null,null,null,0.14000000000000001,"(14%-7%)/5%",null,1.4]
[]
[null,null,null,null,null,null,"Day 1"]
["Day1"]
["DV is say 25% then how many std dev away is it from its mean",null,null,null,null,null,"DV"]
[null,null,null,null,null,null,"(25%-40%)/10%"]
[null,null,null,null,null,null,"Day1",null,null,-1.5]
[]
["Day1"]
["CoC is say .75% then how many std dev away is it from its mean",null,null,null,null,null,"CoC"]
[null,null,null,null,null,null,"(0.75%-.5%)/0.3%"]
[null,null,null,null,null,null,"Day1",null,null,{"$f":"(0.75%-0.5%)/0.3%","v":0.83333333333333315}]
[]
[null,null,null,null,null,null,null,"Day 1","Factors","wts"]
[null,null,null,null,null,null,null,"OI",1.4,0.4]
[null,null,null,null,null,null,null,"DV",-1.5,0.5]
[null,null,null,null,null,null,null,"CoC",0.83,9.9999999999999978E-2]
[]
[null,null,null,null,null,null,"Day 1","Normalized Signal",null,{"$f":"SUMPRODUCT(I57:I59,J57:J59)","v":-0.10700000000000008}]

{"sheet":"FactorModel_Simulations","index":3,"n_rows":40,"n_cols":12,"rows_kept":40,"cut":false,"hidden":false,"merged":[[6,1,6,3],[19,1,19,3],[33,1,33,3]]}
[]
["Simulation 1"]
[null,"w1","w2","w3"]
[null,0.1,0.1,{"$f":"1-C4-B4","v":0.8}]
[]
[null,"This from Historical Data",null,null,null,null,null,"Simluation 1a",null,null,null,"Returns"]
[null,"Change",null,null,null,null,null,"Threshold is >70%",null,"for BUY",null,0.25]
[null,"Change OI","Change COC","Change in DV",null,"Signal",null,"Threshold< -25% ",null,"for SELL"]
["Day1",0.90401467302498029,0.62599917937409255,0.61184830620790165,null,{"$f":"SUMPRODUCT(B9:D9,$B$4:$D$4)","v":0.64248003020622857}]
["Day2",4.4658153467628026E-2,0.7041644096000742,0.82173210902505889,null,{"$f":"SUMPRODUCT(B10:D10,$B$4:$D$4)","v":0.73226794352681746},null,"Simulation 1b",null,null,null,"Returns"]
["Day3",0.18063911672301503,0.98495135039078408,0.5750844159009475,null,0.57662657943213791,null,"Threshold is >80%",null,"for BUY",null,0.3]
["Day4",-0.4,-0.233444202567411,-0.10560333962351399,null,-0.14782709195555233,null,"Threshold< -40% ",null,"for SELL"]
["Day5",0.39598616391096098,0.16043503346653387,0.69166584786558039,null,0.60897479803021382]
[]
["Simulation 2"]
[null,"w1","w2","w3"]
[null,0.1,0.2,{"$f":"1-C17-B17","v":0.70000000000000007}]
[]
[null,"This from Historical Data"]
[null,"Change"]
[null,"Change OI","Change COC","Change in DV",null,"Signal"]
["Day1",0.90401467302498029,0.62599917937409255,0.61184830620790165,null,{"$f":"SUMPRODUCT(B22:D22,$B$17:$D$17)","v":0.64389511752284778}]
["Day2",4.4658153467628026E-2,0.7041644096000742,0.82173210902505889,null,{"$f":"SUMPRODUCT(B23:D23,$B$17:$D$17)","v":0.72051117358431882}]
["Day3",0.18063911672301503,0.98495135039078408,0.5750844159009475,null,0.61761327288112167]
["Day4",-0.4,-0.233444202567411,-0.10560333962351399,null,-0.16061117824994203]
["Day5",0.39598616391096098,0.16043503346653387,0.69166584786558039,null,0.55585171659030919]
[]
[]
["Simulation 3"]
[null,"w1","w2","w3"]
[null,0.1,0.3,{"$f":"1-C31-B31","v":0.6}]
[]
[null,"This from Historical Data"]
[null,"Change"]
[null,"Change OI","Change COC","Change in DV",null,"Signal"]
["Day1",0.90401467302498029,0.62599917937409255,0.61184830620790165,null,{"$f":"SUMPRODUCT(B36:D36,$B$31:$D$31)","v":0.64531020483946677}]
["Day2",4.4658153467628026E-2,0.7041644096000742,0.82173210902505889,null,{"$f":"SUMPRODUCT(B37:D37,$B$31:$D$31)","v":0.7087544036418203}]
["Day3",0.18063911672301503,0.98495135039078408,0.5750844159009475,null,0.6585999663301052]
["Day4",-0.4,-0.233444202567411,-0.10560333962351399,null,-0.17339526454433168]
["Day5",0.39598616391096098,0.16043503346653387,0.69166584786558039,null,0.50272863515040445]

{"sheet":"VLookup","index":4,"n_rows":12,"n_cols":7,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[null,null,null,null,"Stock","Return","Profit"]
[null,null,null,null,"SBIN",0.01,100]
[null,null,null,null,"PNB",0.04,200]
[null,null,null,null,"APPLE",0.03,-100]
[null,null,null,null,"TATSTEEL",-0.02,400]
[]
[]
[null,null,null,null,null,{"$f":"VLOOKUP(\"PNB\",E5:G9,2,0)","v":0.04}]

{"sheet":"Open Interest","index":5,"n_rows":27,"n_cols":7,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["A and B have zero net positions"]
[]
["A buys 5 Futures and B sells 5 Futures ---> OI??"]
["OI is 5"]
[]
["C buys 1 Future and B sells 1 Future--->OI?"]
[null,"Buy","Sell"]
["A",5]
["B",null,6]
["C",1]
["No of positions",{"$f":"SUM(B8:B10)","v":6},{"$f":"SUM(C8:C10)","v":6}]
[]
[]
["C sells 1 future and B buys 1 future"]
[null,"Buy","Sell"]
["A",5]
["B",null,5]
["C",0]
["No of positions",{"$f":"SUM(B16:B18)","v":5},{"$f":"SUM(C16:C18)","v":5}]
["Case 1",null,null,null,"Case 2"]
["A sells 5 future to B (B buys)",null,null,null,"A sells 5 future to D (D buys)"]
[]
[null,"Buy","Sell",null,null,"Buy","Sell"]
["A",0,null,null,"A",0]
["B",null,0,null,"B",null,5]
["C",0,null,null,"D",5]
["No of positions",{"$f":"SUM(B24:B26)","v":0},{"$f":"SUM(C24:C26)","v":0},null,"No of positions",{"$f":"SUM(F24:F26)","v":5},{"$f":"SUM(G24:G26)","v":5}]

{"sheet":"Sheet2","index":6,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}