{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Versions history","ST16_Updater","Recognised commands"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":4,"comments":1,"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":"Versions history","index":0,"n_rows":6,"n_cols":3,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[null,"From","To"]

{"sheet":"ST16_Updater","index":1,"n_rows":161,"n_cols":6,"rows_kept":161,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,"Tool version: 2.0"]
["ST16 Automatic updater",null,null,null,null,{"$f":"\"v\"&MAX(B:B)","v":"v20160629"}]
[]
[]
[null,"Purpose of this tool"]
[null,null,"This tool allows the upgrade of an existing Insurance Stress Test 2016 reporting spreadsheet, keeping the existing data."]
[null,null,"Works with versions 20150523 (initial version) and newer"]
[]
[]
[]
[null,"Step 1"]
[]
[]
[]
[]
[]
[null,"Step 2"]
[]
[]
[]
[]
[null,null,"The detailed corrections steps applied are defined below."]
[]
[]
[]
[]
[]
[]
["Index","Version","Sheet","Address","Operations","Comments"]
[1,20160601,"#I.Information","$A$3","Pinformation and instructions. Version history from row 41.","Adding list of updates / version history to I.Information"]
[{"$f":"A30+1","v":2},20160601,"#I.Information","$B$41","SDC_Note","Adding list of updates / version history to I.Information"]
[3,20160601,"#I.Information","$B$41","SDC_Note","Adding list of updates / version history to I.Information"]
[{"$f":"A32+1","v":4},20160601,"#I.Information","$B$41","PVersion history","Adding list of updates / version history to I.Information"]
[{"$f":"A33+1","v":5},20160601,"#I.Information","$B$42","SDC_Note","Adding initial version to version history"]
[6,20160601,"#I.Information","$B$42","P20160523","Adding initial version to version history"]
[7,20160601,"#I.Information","$C$42","SDC_Note","Adding initial version to version history"]
[8,20160601,"#I.Information","$C$42","PInitial version","Adding initial version to version history"]
[9,20160601,"#I.Information","$B$43","SDC_Note","Adding version 20160601 to version history"]
[10,20160601,"#I.Information","$B$43","P20160601","Adding version 20160601 to version history"]
[11,20160601,"#I.Information","$C$43","SDC_Note","Describing version 20160601 in version history"]
[12,20160601,"#I.Information","$C$43","PSCR and MCR cells removed (greyed) in DH.LTG and LY.LTG (cells M20, M22, O20, O22). See Q&A Question 1.","Describing version 20160601 in version history"]
[13,20160601,"#DH.LTG","$M$20","D","Update (DY.LTG and LY.LTG): Delete contents"]
[14,20160601,"#DH.LTG","$M$22","D","Update (DY.LTG and LY.LTG): Delete contents"]
[15,20160601,"#DH.LTG","$O$20","D","Update (DY.LTG and LY.LTG): Delete contents"]
[16,20160601,"#DH.LTG","$O$22","D","Update (DY.LTG and LY.LTG): Delete contents"]
[17,20160601,"#DH.LTG","$M$20","SDC_NotRelevant","Update (DY.LTG and LY.LTG): Grey cell"]
[18,20160601,"#DH.LTG","$M$22","SDC_NotRelevant","Update (DY.LTG and LY.LTG): Grey cell"]
[19,20160601,"#DH.LTG","$O$20","SDC_NotRelevant","Update (DY.LTG and LY.LTG): Grey cell"]
[20,20160601,"#DH.LTG","$O$22","SDC_NotRelevant","Update (DY.LTG and LY.LTG): Grey cell"]
[21,20160601,"#LY.LTG","$M$20","D","Update (DY.LTG and LY.LTG): Delete contents"]
[22,20160601,"#LY.LTG","$M$22","D","Update (DY.LTG and LY.LTG): Delete contents"]
[23,20160601,"#LY.LTG","$O$20","D","Update (DY.LTG and LY.LTG): Delete contents"]
[24,20160601,"#LY.LTG","$O$22","D","Update (DY.LTG and LY.LTG): Delete contents"]
[25,20160601,"#LY.LTG","$M$20","SDC_NotRelevant","Update (DY.LTG and LY.LTG): Grey cell"]
[26,20160601,"#LY.LTG","$M$22","SDC_NotRelevant","Update (DY.LTG and LY.LTG): Grey cell"]
[27,20160601,"#LY.LTG","$O$20","SDC_NotRelevant","Update (DY.LTG and LY.LTG): Grey cell"]
[28,20160601,"#LY.LTG","$O$22","SDC_NotRelevant","Update (DY.LTG and LY.LTG): Grey cell"]
[29,20160601,"#DH.LTG","$M$20","B","Update (DY.LTG and LY.LTG): Set border"]
[30,20160601,"#DH.LTG","$M$22","B","Update (DY.LTG and LY.LTG): Set border"]
[31,20160601,"#DH.LTG","$O$20","B","Update (DY.LTG and LY.LTG): Set border"]
[32,20160601,"#DH.LTG","$O$22","B","Update (DY.LTG and LY.LTG): Set border"]
[33,20160601,"#LY.LTG","$M$20","B","Update (DY.LTG and LY.LTG): Set border"]
[34,20160601,"#LY.LTG","$M$22","B","Update (DY.LTG and LY.LTG): Set border"]
[35,20160601,"#LY.LTG","$O$20","B","Update (DY.LTG and LY.LTG): Set border"]
[36,20160601,"#LY.LTG","$O$22","B","Update (DY.LTG and LY.LTG): Set border"]
[37,20160601,"#I.Information","$C$44","SDC_Note","Describing version 20160601 in version history"]
[38,20160601,"#I.Information","$C$44","PUpdates to the D.Derivatives tab in line with the Q&A (see questions 7-9)","Describing version 20160601 in version history"]
[39,20160601,"#D.Derivatives","$B$14:$G$14","D","Update (D.Derivatives): Delete cells for SCR request"]
[40,20160601,"#D.Derivatives","$B$14:$G$14","SDC_Empty","Update (D.Derivatives): Grey cells"]
[41,20160601,"#D.Derivatives","$B$14:$G$14","B","Update (D.Derivatives): Border"]
[42,20160601,"#D.Derivatives","$B$28:$G$28","D","Update (D.Derivatives): Delete cells for SCR request"]
[43,20160601,"#D.Derivatives","$B$28:$G$28","SDC_Empty","Update (D.Derivatives): Grey cells"]
[44,20160601,"#D.Derivatives","$B$28:$G$28","B","Update (D.Derivatives): Border"]
[45,20160601,"#D.Derivatives","$G$25","SDC_Input_DecNumber","Update (D.Derivatives): Open (optional) fields"]
[46,20160601,"#D.Derivatives","$G$25","P-","Update (D.Derivatives): Open (optional) fields"]
[47,20160601,"#D.Derivatives","$G$39","SDC_Input_DecNumber","Update (D.Derivatives): Open (optional) fields"]
[48,20160601,"#D.Derivatives","$G$39","P-","Update (D.Derivatives): Open (optional) fields"]
[49,20160601,"#D.Derivatives","$B$15","PAmount of assets covering the liabilities","Update (D.Derivatives): Add field"]
[50,20160601,"#D.Derivatives","$B$15","SDC_Label","Update (D.Derivatives): Add field"]
[51,20160601,"#D.Derivatives","$C$15","SDC_Input_DecNumber","Update (D.Derivatives): Add field"]
[52,20160601,"#D.Derivatives","$C$15","P-","Update (D.Derivatives): Add field"]
[53,20160601,"#D.Derivatives","$B$15:$C$15","B","Update (D.Derivatives): Set border"]
[54,20160601,"#D.Derivatives","$B$29","PAmount of assets covering the liabilities","Update (D.Derivatives): Add field"]
[55,20160601,"#D.Derivatives","$B$29","SDC_Label","Update (D.Derivatives): Add field"]
[56,20160601,"#D.Derivatives","$C$29","SDC_Input_DecNumber","Update (D.Derivatives): Add field"]
[57,20160601,"#D.Derivatives","$C$29","P-","Update (D.Derivatives): Add field"]
[58,20160601,"#D.Derivatives","$B$29:$C$29","B","Update (D.Derivatives): Set border"]
[59,20160601,"#D.Derivatives","$B$42","P*** The effective duration is used to calculate liability duration, asset duration and net duration and shall be approximated by dividing the change in the present values of cash flows under baseline and stressed scenario by 2*PV0*ΔY where PV0 is the present value of the cash flow under the baseline scenario and ΔY represent the change in the internal rate of return of the 2 cash flows","Update (D.Derivatives): Minor correction in footnote"]
[60,20160601,"#I.Information","$C$45","SDC_Note","Describing version 20160601 in version history"]
[61,20160601,"#I.Information","$C$45","PCorrected formula for the calculation of the combined MCR in the 0.MCR and 0.MCR.Comp templates","Describing version 20160601 in version history"]
[62,20160601,"#0.MCR","$D$64","F=MIN(MAX(D60,D63),D62)","Update (0.MCR): Corrected cell D64"]
[63,20160601,"#0.MCR.Comp","$D$70","F=MIN(MAX(D66,D69),D68)","Update (0.MCR.Comp): Corrected cell D70"]
[{"$f":"A92+1","v":64},20160615,"#I.Information","$B$46","P20160615","Adding version 20160615 to version history"]
[{"$f":"A93+1","v":65},20160615,"#I.Information","$B$46","SDC_Note","Adding version 20160615 to version history"]
[66,20160615,"#I.Information","$C$46","SDC_Note","Describing version 20160615 in version history"]
[67,20160615,"#I.Information","$C$46","PUpdates to the D.Derivatives tab in line with the Q&A (see question #28)","Describing version 20160615 in version history"]
[68,20160615,"#D.Derivatives","$B$14","PAmount of liabilities (for liability duration)","Update (D.Derivatives): Add field"]
[69,20160615,"#D.Derivatives","$B$14","SDC_Label","Update (D.Derivatives): Add field"]
[70,20160615,"#D.Derivatives","$C$14","SDC_Input_DecNumber","Update (D.Derivatives): Add field"]
[71,20160615,"#D.Derivatives","$C$14","P-","Update (D.Derivatives): Add field"]
[72,20160615,"#D.Derivatives","$B$14:$C$14","B","Update (D.Derivatives): Set border"]
[73,20160615,"#D.Derivatives","$B$28","PAmount of liabilities (for liability duration)","Update (D.Derivatives): Add field"]
[74,20160615,"#D.Derivatives","$B$28","SDC_Label","Update (D.Derivatives): Add field"]
[75,20160615,"#D.Derivatives","$C$28","SDC_Input_DecNumber","Update (D.Derivatives): Add field"]
[76,20160615,"#D.Derivatives","$C$28","P-","Update (D.Derivatives): Add field"]
[77,20160615,"#D.Derivatives","$B$28:$C$28","B","Update (D.Derivatives): Set border"]
[78,20160615,"#I.Information","$C$47","SDC_Note","Describing version 20160615 in version history"]
[79,20160615,"#I.Information","$C$47","PClarifications (Q&A #32, #36, #39, #57)","Describing version 20160615 in version history"]
[80,20160615,"#0.Assets","$A$3","PAssets description under baseline (excl. assets held for pure unit-linked products)","Update (0.Assets): Clarifications "]
[81,20160615,"#0.Assets","$B$5","PFixed-income products (excl. assets held for pure unit-linked products)","Update (0.Assets): Clarifications "]
[82,20160615,"#0.Assets","$B$79","PBond portfolio (excl. assets held for pure unit-linked products)","Update (0.Assets): Clarifications "]
[83,20160615,"#0.Assets","$H$83","PModified duration","Update (0.Assets): Clarifications "]
[84,20160615,"#0.Liabilities.Char","$B$5","PLife rate guaranteed assessment (excl. pure unit-linked)","Update (0.Liabilities.Char): Clarifications "]
[85,20160615,"#0.Liabilities.Char","$B$32","PLife - without rate guaranteed on future premiums (excl. pure unit-linked)","Update (0.Liabilities.Char): Clarifications "]
[86,20160615,"#LY.LTG","$O$11","P(Optional)","Update (LY.LTG and DY.LTG): Clarifications "]
[87,20160615,"#DH.LTG","$O$11","P(Optional)","Update (LY.LTG and DY.LTG): Clarifications "]
[88,20160615,"#I.Information","$C$48","SDC_Note","Describing version 20160615 in version history"]
[89,20160615,"#I.Information","$C$48","PCorrections (Q&A #31 and #44)","Describing version 20160615 in version history"]
[90,20160615,"#LTG.LTG","$O$12:$P$19","SDC_NotRelevant","Update (LTG.LTG): Set grey"]
[91,20160615,"#LTG.LTG","$O$21:$P$21","SDC_NotRelevant","Update (LTG.LTG): Set grey"]
[92,20160615,"#LTG.LTG","$O$12:$P$19","D","Update (LTG.LTG): Delete contents"]
[93,20160615,"#LTG.LTG","$O$21:$P$21","D","Update (LTG.LTG): Delete contents"]
[94,20160615,"#LTG.LTG","$O$12:$P$19","B","Update (LTG.LTG): Set border"]
[95,20160615,"#LTG.LTG","$O$21:$P$21","B","Update (LTG.LTG): Set border"]
[96,20160615,"#LY.Q","$C$68","PContract term","Update (LY.Q): Clarifications"]
[97,20160615,"#P.Participant","$C$24","F=IFERROR(VLOOKUP(C10,$P$5:$T$35,5,FALSE),\"[Filled from country of authorisation]\")","Update (P.Participant): Correction"]
[98,20160615,"#P.Participant","$C$30","F=IFERROR(VLOOKUP(C10,$P$5:$T$35,5,FALSE),\"[Countrycode]\")&\"-\"&IF(C29=\" [N/A]\",\"[Participant ID]\",C29)","Update (P.Participant): Correction"]
[99,20160615,"#P.Participant","$T$35","PUK","Update (P.Participant): Correction (set UK as country code)"]
[100,20160615,"#0.Assets","$BS$34","PUK","Update (P.Participant): Correction (set UK as country code)"]
[101,20160615,"#I.Information","$C$49","SDC_Note","Describing version 20160615 in version history"]
[102,20160615,"#I.Information","$C$49","PChanged country code for United Kingdom to UK","Describing version 20160615 in version history"]
[103,20160615,"#P.Participant","$I$47","PUnit of reporting","Added processing field"]
[104,20160615,"#P.Participant","$I$48","PUNIT","Added processing field"]
[105,20160615,"#P.Participant","$I$48","SDC_Input_General","Added processing field"]
[106,20160629,"#I.Information","$C$50","SDC_Note","Describing version 20160629 in version history"]
[107,20160629,"#I.Information","$C$50","PMinor corrections and clarifications","Describing version 20160629 in version history"]
[{"$f":"A136+1","v":108},20160629,"#I.Information","$B$50","SDC_Note","Adding version 20160615 to version history"]
[{"$f":"A137+1","v":109},20160629,"#I.Information","$B$50","P20160629","Adding version 20160615 to version history"]
[{"$f":"A138+1","v":110},20160629,"#0.Assets","$E$117:$E$166","VL=$BQ$113:$BQ$200","Extending drop-down list to include all currencies"]
[111,20160629,"#LY.Assets","$A$3","PAssets description under low yield (excl. assets held for pure unit-linked products)","Update (LY.Assets): Clarifications "]
[112,20160629,"#LY.Assets","$B$5","PFixed-income products (excl. assets held for pure unit-linked products)","Update (LY.Assets): Clarifications "]
[113,20160629,"#0.Liabilities.Char","$B$32","PLife - without rate guaranteed on future premiums (excl. pure unit-linked) or without future premiums","Update (0.Liabilities.Char): Clarifications "]
[114,20160629,"#0.Assets","$E$90","PEUR","Update (0.Assets): Correction"]
[115,20160629,"#O.Overview","$D$36:$E$38","SDC_Empty","Update (O.Overview): Remove not relevant cell"]
[116,20160629,"#O.Overview","$G$36:$H$38","SDC_Empty","Update (O.Overview): Remove not relevant cell"]
[117,20160629,"#O.Overview","$J$36:$K$38","SDC_Empty","Update (O.Overview): Remove not relevant cell"]
[118,20160629,"#O.Overview","$D$36:$E$38","D","Update (O.Overview): Remove not relevant cell"]
[119,20160629,"#O.Overview","$G$36:$H$38","D","Update (O.Overview): Remove not relevant cell"]
[120,20160629,"#O.Overview","$J$36:$K$38","D","Update (O.Overview): Remove not relevant cell"]
[121,20160629,"#O.Overview","$D$40:$E$46","SDC_Empty","Update (O.Overview): Remove not relevant cell"]
[122,20160629,"#O.Overview","$G$40:$H$46","SDC_Empty","Update (O.Overview): Remove not relevant cell"]
[123,20160629,"#O.Overview","$J$40:$K$46","SDC_Empty","Update (O.Overview): Remove not relevant cell"]
[124,20160629,"#O.Overview","$D$40:$E$46","D","Update (O.Overview): Remove not relevant cell"]
[125,20160629,"#O.Overview","$G$40:$H$46","D","Update (O.Overview): Remove not relevant cell"]
[126,20160629,"#O.Overview","$J$40:$K$46","D","Update (O.Overview): Remove not relevant cell"]
[127,20160629,"#O.Overview","$D$36:$E$38","B","Update (O.Overview): Remove not relevant cell"]
[128,20160629,"#O.Overview","$G$36:$H$38","B","Update (O.Overview): Remove not relevant cell"]
[129,20160629,"#O.Overview","$J$36:$K$38","B","Update (O.Overview): Remove not relevant cell"]
[130,20160629,"#O.Overview","$D$40:$E$46","B","Update (O.Overview): Remove not relevant cell"]
[131,20160629,"#O.Overview","$G$40:$H$46","B","Update (O.Overview): Remove not relevant cell"]
[132,20160629,"#O.Overview","$J$40:$K$46","B","Update (O.Overview): Remove not relevant cell"]

{"sheet":"Recognised commands","index":2,"n_rows":22,"n_cols":3,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,"'' This spreadsheet allows to update an existing IORP reporting spreadsheet by applying a set of in-spreadsheet patches defined in sheet \"IORP_ST15Updater\""]
[null,null,"''' operations are defined by a sheet level operations (column C) and cell level operations (column E)."]
[null,null,"''' Sheet level operations and cell level operations are codified with the first character of the patch parameters"]
[null,null,"'''"]
[null,null,"''' Sheet level operations"]
[null,null,"'' §<Sheetname>: defines an excel name at the <Sheetname> level"]
[null,null,"'' ~<Sheetname>: defines an operation applied to the full sheet <Sheetname>"]
[null,null,"'' #<Sheetname>: apply a cell level operation on sheet <Sheetname>"]
[null,null,"'''"]
[null,null,"'' Cell level operations"]
[null,null,"'' the first character of the individual patch definition defines the kind of operation performed"]
[null,null," \"F\": defines a formula"]
[null,null,"' \"A\": defines an array formula"]
[null,null,"' \"P\": define a value"]
[null,null,"' \"D\": clear a cell. \"DV\" will also delete any validation rules applied to this cell"]
[null,null,"' \"S\": apply an excel named style"]
[null,null,"' \"C\": add an excel comment to a cell"]
[null,null,"' \"R\": remove an excel comment"]
[null,null," \"VL\": Create a new validation rule, of type List"]
[null,null," \"B\": Add a border around a cell"]