{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["数据联动","情景矩阵","动态对冲自定义","动态对冲阈值法"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":27,"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":"数据联动","index":0,"n_rows":7,"n_cols":21,"rows_kept":7,"cut":false,"hidden":false,"merged":[[0,20,1,22],[0,13,1,19],[5,0,5,19],[3,13,3,14],[4,13,4,14],[0,6,1,12],[2,0,2,19]]}
[null,null,null,null,null,null,"行情数据及标的数据",null,null,null,null,null,null,"1.请先在无限易中点击导出dde之后，再点击导入行情数据活标的数据\n2.\n3.如果系统日期出错，计算会错\n4.若要修改宏，请先关闭无限易以停止动态数据变动再修改",null,null,null,null,null,null,"操作栏"]
[]
["标的资产"]
["合约代码","合约名称","品种","最新","涨跌","涨跌%","买量","买价","卖价","卖量","更新时间","r","q"]
[]
["行情数据"]
["到期日","合约乘数","交易所","昨结","IV","净持仓","最新","合约名称","执行价","合约名称","最新","净持仓","IV","昨结","交易所","合约乘数","到期日","IVC","IVP","剩余天数"]

{"sheet":"情景矩阵","index":1,"n_rows":48,"n_cols":18,"rows_kept":48,"cut":false,"hidden":false,"merged":[[0,16,1,18],[2,0,2,15],[25,0,25,15],[0,0,1,3],[0,4,1,8],[0,9,1,15]]}
[null,null,null,null,"情景矩阵",null,null,null,null,"保证金矩阵中，目前只能计算中金所保证金，待完成：上期所，上交所，大连交易所保证金的计算公式",null,null,null,null,null,null,"操作栏"]
[]
["Greeks情景矩阵"]
["波动率变动%|标的价格变动%",-7,-6,-5,-4,-3,-2,-1,0,1,2,3,4,5,6,7]
[10,-9.3551736959120696E-2,-9.1920217007166669E-2,-8.9232352813793228E-2,-8.5160448388490295E-2,-7.9473734580679967E-2,-7.2134403188462828E-2,-6.3360311835603561E-2,-5.3622066962159015E-2,-4.3565742524507678E-2,-3.3883155028305287E-2,-2.5173147902643793E-2,-1.7838637939454539E-2,-1.2046080569600753E-2,-7.7476573805378762E-3,-4.7453613688543863E-3]
[9,-9.3630265133492194E-2,-9.2038825482482947E-2,-8.9392671726154768E-2,-8.5352973409568433E-2,-7.9676092013172106E-2,-7.231445077785581E-2,-6.348403715358289E-2,-5.3663637954639493E-2,-4.3515573062102318E-2,-3.3750547420637281E-2,-2.4982620532783332E-2,-1.7621908443989098E-2,-1.1833247067933726E-2,-7.5607754686783006E-3,-4.5959251146168272E-3]
[8,-9.3707106157580308E-2,-9.2156090659245474E-2,-8.9552565195124514E-2,-8.5546373573539447E-2,-7.9880534051585281E-2,-7.2497140148240879E-2,-6.3609968082575644E-2,-5.3706089666010176E-2,-4.3464601202291335E-2,-3.3615786252382568E-2,-2.4789424756033934E-2,-1.7402934782308192E-2,-1.1619251524669388E-2,-7.374004424517985E-3,-4.4476451613746091E-3]
[7,-9.3782224157972369E-2,-9.227194997144296E-2,-8.9711966168018326E-2,-8.5740608572874327E-2,-8.0087067524189259E-2,-7.2682518295325405E-2,-6.3738159027072416E-2,-5.3749446455760873E-2,-4.3412804698567641E-2,-3.3478817762596438E-2,-2.459351069212826E-2,-1.7181703096884274E-2,-1.1404126631546158E-2,-7.1874099844074614E-3,-4.3005941987421742E-3]
[6,-9.3855584444916082E-2,-9.2386340219506549E-2,-8.9870805155372963E-2,-8.5935635255971421E-2,-8.0295697825797652E-2,-7.2870632890498235E-2,-6.3868666107893948E-2,-5.3793733586705826E-2,-4.3360160476818059E-2,-3.3339586462768536E-2,-2.4394827604928136E-2,-1.6958200686595145E-2,-1.1187907863692914E-2,-7.0010607945649068E-3,-4.1548464889838077E-3]
[5,-9.392715365245341E-2,-9.2499197671968222E-2,-9.0029010207479596E-2,-8.6131407496236628E-2,-8.0506428794509394E-2,-7.3061532261614492E-2,-6.4001547225670113E-2,-5.3838977267084663E-2,-4.3306644596598644E-2,-3.3198035070985683E-2,-2.419332391075571E-2,-1.6732416121380159E-2,-1.0970633626213221E-2,-6.8150284798671685E-3,-4.0104778088693796E-3]
[4,-9.399689988378912E-2,-9.2610458177627572E-2,-9.0186506897099381E-2,-8.6327876057542663E-2,-8.0719262580185885E-2,-7.3255265370021694E-2,-6.4136862126831118E-2,-5.3885204695030345E-2,-4.3252232210222279E-2,-3.3054104443339609E-2,-2.3988947189818272E-2,-1.6504339365225794E-2,-1.075234540646431E-2,-6.6293877093175045E-3,-3.8675653810346746E-3]
[3,-9.4064792861575264E-2,-9.2720057288769864E-2,-9.0343218309178699E-2,-8.6524988456254406E-2,-8.0934199504176935E-2,-7.3451881783429665E-2,-6.4274672472198757E-2,-5.3932444105560168E-2,-4.3196897519513569E-2,-3.2907733502468027E-2,-2.3781644201084792E-2,-1.6273961908025308E-2,-1.0533087932038413E-2,-6.444216257464129E-3,-3.7261877940153506E-3]
[2,-9.4130804082714403E-2,-9.2827930395967373E-2,-9.0499065038449428E-2,-8.672268882005757E-2,-8.1151237909787968E-2,-7.3651431644189441E-2,-6.4415041908267079E-2,-5.3980724820257606E-2,-4.3140613730075002E-2,-3.2758859163114343E-2,-2.357136090100807E-2,-1.6041276906886427E-2,-1.0312909334417414E-2,-6.259595060965374E-3,-3.5864249100908555E-3]
[1,-9.4194906977196763E-2,-9.2934012874978808E-2,-9.0653965195870295E-2,-8.6920917743886522E-2,-8.1370374002960308E-2,-7.3853965632503432E-2,-6.4558036141260142E-2,-5.4030077299825709E-2,-4.308335300289607E-2,-3.2607416254589454E-2,-2.3358042466526192E-2,-1.5806279337492384E-2,-1.0091861318229685E-2,-6.0756082694134461E-3,-3.4483577600458938E-3]
[0,-9.4257077070392353E-2,-9.3038240246235956E-2,-9.0807834424938935E-2,-8.7119612143311556E-2,-8.159160168261681E-2,-7.4059534924044587E-2,-6.4703723014055151E-2,-5.4080533199706626E-2,-4.3025086403124406E-2,-3.2453337440016689E-2,-2.3141633322820727E-2,-1.5568966156152478E-2,-9.8699993359884688E-3,-5.8923432894387893E-3,-3.312068423931864E-3]
[-1,-9.4317292148115836E-2,-9.3140548347371357E-2,-9.0960585928980417E-2,-8.731870510582028E-2,-8.1814912360105921E-2,-7.4268191141411685E-2,-6.4852172586056397E-2,-5.413212542897694E-2,-4.2965783845803483E-2,-3.2296553132239718E-2,-2.292207717635723E-2,-1.5329336473210145E-2,-9.6473827681340905E-3,-5.7098908210205263E-3,-3.1776398968911914E-3]
[-2,-9.4375532423671901E-2,-9.3240873519195286E-2,-9.111213051059594E-2,-8.751812574051028E-2,-8.2040294767161334E-2,-7.4479986298793821E-2,-6.5003457216103511E-2,-5.4184888212745141E-2,-4.2905414038366008E-2,-3.2136991406272408E-2,-2.2699317053785795E-2,-1.5087391738507946E-2,-9.4240751081362591E-3,-5.528344884826189E-3,-3.0451559390944041E-3]
[-3,-9.4431780705969678E-2,-9.3339152805471962E-2,-9.1262376624534527E-2,-8.7717799026803406E-2,-8.2267734751777927E-2,-7.4694972739157289E-2,-6.5157651648493881E-2,-5.423885715829517E-2,-4.2843944419655486E-2,-3.1974577908169148E-2,-2.2473295347337972E-2,-1.4843135939640173E-2,-9.2001441523369889E-3,-5.3478028392946585E-3,-2.9147009088348452E-3]
[-4,-9.4486022567670053E-2,-9.3435324166773429E-2,-9.1411230445330768E-2,-8.7917645662895963E-2,-8.2497215061394014E-2,-7.4913203063203329E-2,-6.5314833102193562E-2,-5.4294069325239849E-2,-4.2781341095229852E-2,-3.1809235760195453E-2,-2.2243953867419293E-2,-1.4596575813754488E-2,-8.9756621941280128E-3,-5.1683653860617592E-3,-2.7863595778308552E-3]
[-5,-9.4538246512196972E-2,-9.3529326708602603E-2,-9.1558595951130459E-2,-8.8117581914775339E-2,-8.2728715112760073E-2,-7.5134730049273749E-2,-6.5475081363305015E-2,-5.4350563299969255E-2,-4.2717568768681144E-2,-3.1640885462179955E-2,-2.2011233903166501E-2,-1.4347721073692589E-2,-8.7507062219575032E-3,-4.9901365622066762E-3,-2.6602169278016627E-3]
[-6,-9.4588444138305541E-2,-9.3621100923872311E-2,-9.1704375025203733E-2,-8.8317519466762806E-2,-8.2962210747870385E-2,-7.5359606563304371E-2,-6.5638478880851364E-2,-5.4408379274701796E-2,-4.2652590668683273E-2,-3.1469444788932467E-2,-2.1775076291815385E-2,-1.4096584649287789E-2,-8.5253581205485384E-3,-4.8132237176716974E-3,-2.536357927413697E-3]
[-7,-9.4636610300757637E-2,-9.3710588949704979E-2,-9.1848467576719772E-2,-8.8517365274685691E-2,-8.3197673975334868E-2,-7.5587885457841453E-2,-6.5805110865926664E-2,-5.446755913147229E-2,-4.2586368471456096E-2,-3.1294828683617271E-2,-2.1535421497808471E-2,-1.3843182944661194E-2,-8.2997048745842052E-3,-4.6377374760765196E-3,-2.414867288740347E-3]
[-8,-9.468274326550967E-2,-9.3797734838373559E-2,-9.1990771682425976E-2,-8.871702142294087E-2,-8.3435072696576101E-2,-7.5819619459043849E-2,-6.5975065394248328E-2,-5.4528146531417722E-2,-4.2518862218307661E-2,-3.1116949146977289E-2,-2.1292209702664266E-2,-1.3587536112379632E-2,-8.0738387739701725E-3,-4.4637916770141591E-3,-2.2958292024436687E-3]
[-9,-9.4726844857670134E-2,-9.3882484842038205E-2,-9.2131183750936929E-2,-8.8916384986886879E-2,-8.3674370416251823E-2,-7.6054861040493804E-2,-6.6148433512132745E-2,-5.459018700975278E-2,-4.2450030227888139E-2,-3.0935715122313884E-2,-2.1045380906730405E-2,-1.3329668345353663E-2,-7.8478576196230956E-3,-4.291503297777925E-3,-2.1793270509732165E-3]
[-10,-9.4768920600339646E-2,-9.3964787710743236E-2,-9.2269598711390621E-2,-8.9115347902194664E-2,-8.3915525936329061E-2,-7.6293662282529581E-2,-6.6325309345895062E-2,-5.465372807685992E-2,-4.2379829002757163E-2,-3.0751032376137799E-2,-2.0794875044053408E-2,-1.3069608187364782E-2,-7.621864928551051E-3,-4.1209923523311343E-3,-2.0654430991889545E-3,null," "]
["保证金情景矩阵"]
["期权价格变动%|标的价格变动%",-7,-6,-5,-4,-3,-2,-1,0,1,2,3,4,5,6,7]
[10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[7,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[6,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-6,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-7,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[-10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]

{"sheet":"动态对冲自定义","index":2,"n_rows":17,"n_cols":14,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,13,1,15],[2,0,2,12],[14,0,14,12],[3,8,3,12],[0,2,1,7],[0,8,1,12],[15,7,15,12],[3,5,3,7],[15,0,15,5],[3,1,3,2],[3,3,3,4],[8,0,8,12],[9,3,9,4],[9,5,9,8],[9,9,9,12]]}
[null,null,"delta中性对冲",null,null,null,null,null,"1.目前此模板仅用于确定好累期权持仓后，计算delta对冲后的标的资产\n2.gamma对冲下delta也是中性的",null,null,null,null,"操作栏"]
[]
["delta对冲情况"]
["当前变动资产","call",null,"put",null,"标的资产",null,null,"开仓收支"]
[{"$f":"G6","v":0},"delta","保证金","delta","保证金","持仓","资产名称","当前价格","期权开仓支出","保证金","标的支出","组合收支","当前delta"]
["当前情况",{"$f":"SUMPRODUCT(D18:D36,E18:E36)*数据联动!B8","v":0},{"$f":"SUM(C18:C99)","v":0},{"$f":"SUMPRODUCT(I18:I36,J18:J36)*数据联动!P8","v":0},{"$f":"SUM(K18:K101)","v":0},1,{"$f":"数据联动!B5","v":0},{"$f":"数据联动!D5","v":0},0,{"$f":"SUM(K19:K37,C18:C37)","v":0},"——",{"$f":"SUM(I6:K6)","v":0},{"$f":"F6*300+B6+D6","v":300}]
["对冲后情况",{"$f":"B6","v":0},{"$f":"C6","v":0},{"$f":"D6","v":0},{"$f":"E6","v":0},1,{"$f":"G6","v":0},4337.6000000000004,0,0,{"$f":"F7*H7*300","v":1301280},{"$f":"SUM(I7:K7)","v":1301280},{"$f":" F7 * 300+ B7+D7","v":300}]
["对冲后组合价值变动","——","——","——","——","——","——","——",{"$f":"D7*(H7-H6)+B7*(H7-H6)","v":0},{"$f":"J7-J6","v":0},{"$f":"(H7-H6)*F7*300","v":1301280},{"$f":"K8+I8","v":1301280},"——"]
["gamma对冲情况"]
["对冲合约名称","call","put","标的资产",null,"对冲合约",null,null,null,"开仓收支"]
["IO","gamma","gamma","持仓","当前价格","持仓","gamma","合约乘数","delta","期权","标的","当前delta","当前gamma"]
["当前情况",{"$f":"SUMPRODUCT(B18:B36,E18:E36)*数据联动!B8","v":0},{"$f":"SUMPRODUCT(I18:I36,L18:L36)*数据联动!P8","v":0},1,"股指2207",0,4.7489800000000002E-4,100,-0.23851066300000001,0,"——",{"$f":"B6+D6+D12*300+I12*F12*H12","v":300},{"$f":"B12+C12+G12*F12*H12","v":0}]
["对冲后情况",{"$f":"B12","v":0},{"$f":"C12","v":0},-10.075648494325705,{"$f":"H7","v":4337.6000000000004},-2.7163792846412096E-3,{"$f":"G12","v":4.7489800000000002E-4},{"$f":"H12","v":100},{"$f":"I12","v":-0.23851066300000001},0,{"$f":"D13*H6*300","v":0},{"$f":"B7+D7+D13*300+I13*F13*H13","v":-3022.6297597552975},{"$f":"F13*H13*1+B13","v":-0.27163792846412094}]
["对冲后组合价值变动","——","——","——","——","——","——","——","——",{"$f":"B7*(H7-H6)+D7*(H7-H6)+I13*(H7-H6)","v":-1034.5638518288001},{"$f":"(H7-H6)*F7*300","v":1301280},"组合价值变动",{"$f":"K14+J14","v":1300245.4361481713}]
["当前情况"]
["call",null,null,null,null,null,null,"put"]
["合约名称","gamma","保证金","delta","持仓","最新价","执行价","最新价","持仓","Delta","保证金","gamma","合约名称"]

{"sheet":"动态对冲阈值法","index":3,"n_rows":11,"n_cols":15,"rows_kept":11,"cut":false,"hidden":false,"merged":[[8,0,9,12],[0,13,1,15],[4,13,4,15],[0,2,1,7],[3,1,3,2],[3,3,3,4],[3,5,3,7],[0,8,1,12],[2,0,2,12],[3,8,3,11]]}
[null,null,"自动动态delta对冲-固定阈值",null,null,null,null,null,"点击行情页面的导入行情数据之后，相关栏位动态数据会导入这里，并自动计算动态delta，超过阈值之后，自动进行对冲，并跳出警示窗口\n阈值需要自己填写",null,null,null,null,"操作栏"]
[]
["实现对冲的持有资产比例（固定阈值）"]
["阈值","call",null,"put",null,"标的资产",null,null,"费用情况"]
[0.5,"持有delta","保证金","持有delta","保证金","持仓","资产名称","当前价格","期权支出","保证金","标的支出","组合收支","当前delta","自动化条件"]
["当前情况",{"$f":"SUMPRODUCT(D12:D30,E12:E30)*数据联动!B8","v":0},{"$f":"SUM(C12:C95)","v":0},{"$f":"SUMPRODUCT(I12:I30,J12:J30)*数据联动!P8","v":0},{"$f":"SUM(K12:K97)","v":0},1,{"$f":"数据联动!B5","v":0},{"$f":"数据联动!D5","v":0},{"$f":"SUMPRODUCT(F12:F93,E12:E93)+SUMPRODUCT(H12:H93,I12:I93)","v":0},{"$f":"C6+E6","v":0},{"$f":"F6*H6","v":0},{"$f":"SUM(I6:K6)","v":0},{"$f":"B6+D6+F6*300","v":300},null,0]
["对冲后情况",{"$f":"B6","v":0},{"$f":"C6","v":0},{"$f":"D6","v":0},{"$f":"E6","v":0},0.77904780286095399,{"$f":"G6","v":0},{"$f":"H6","v":0},{"$f":"SUMPRODUCT(F12:F93,E12:E93)+SUMPRODUCT(H12:H93,I12:I93)","v":0},{"$f":"C7+E7","v":0},{"$f":"F7*H7","v":0},{"$f":"SUM(I7:K7)","v":0},{"$f":"B7+D7+F7*300","v":233.71434085828619}]
["变动","——","——","——","——",{"$f":"F7-F6","v":-0.22095219713904601},"——","——",{"$f":"I7-I6","v":0},{"$f":"J7-J6","v":0},{"$f":"K7-K6","v":0},{"$f":"L7-L6","v":0},"——"]
["当前情况"]
[]
["合约名称","gamma","保证金","delta","持仓","最新价","执行价","最新价","持仓","Delta","保证金","gamma","合约名称"]