Q$5.$ For shared index data$-$set, Calculate VaR and Expected shortfall at $99\%$ & $95\%$ confidence level as per

a$)$ Historical simulation $($BRW$)$ method equal weight

b$)$ Historical simulation $($BRW$)$ method with weight. $($Take lambda value as $0\mathrm{.}97).$

c$)$ Following are the portfolio weight:

Portfolio X Y Z A

Investments $4000300010002000$

Index X Index Y Index Z Index A

$11219\mathrm{.}3811131\mathrm{.}842242282\mathrm{.}6715154\mathrm{.}06$

$11173\mathrm{.}5911096\mathrm{.}280322303\mathrm{.}7115464\mathrm{.}66$

$11076\mathrm{.}1811185\mathrm{.}35032316\mathrm{.}4115656\mathrm{.}59$

$11124\mathrm{.}3711016\mathrm{.}708122308\mathrm{.}8615630\mathrm{.}91$

$11088\mathrm{.}0211040\mathrm{.}72972312\mathrm{.}215565\mathrm{.}02$

$11097\mathrm{.}8711109\mathrm{.}504072345\mathrm{.}9515857\mathrm{.}11$

$11230\mathrm{.}2611179\mathrm{.}469452365\mathrm{.}4815816\mathrm{.}19$

$11327\mathrm{.}1211203\mathrm{.}542380\mathrm{.}5916071\mathrm{.}36$

$11334\mathrm{.}9611170\mathrm{.}047242382\mathrm{.}3616020\mathrm{.}84$

$11381\mathrm{.}4711096\mathrm{.}030642388\mathrm{.}0616105\mathrm{.}98$

$11345\mathrm{.}0411221\mathrm{.}13442364\mathrm{.}0515969\mathrm{.}04$

$11339\mathrm{.}8411147\mathrm{.}06012337\mathrm{.}4316181\mathrm{.}17$

$11297\mathrm{.}911097\mathrm{.}0822376\mathrm{.}7216163\mathrm{.}03$

$11304\mathrm{.}4611100\mathrm{.}815742395\mathrm{.}6715960\mathrm{.}62$

Shared the sample for data set as well. Looking For excel based solution to this. Please also share the data or numbers being used for reference during calculations to could relate it properly