Value at Risk Calculations

[#1]

Hi Folks

Quick Question:

Has anybody out there created a QlikView app that calculates Value at Risk?

I have charts that are doing this but the performance is really bad due to the aggregation taking place and then having to take the nth value from the resulting data set. I am wondering if there is a better, more efficient way to do this.

Here is a copy of the formula currently in use:

-min(
 aggr(
 sum(VaRPnL)
 ,VaRPnLDate
 ,CobDate
 ,Region
 ,Division
 ,Business
 ,[Sub Business]
 ,[Desk Location]
 ,Desk
 ,[Sub Desk]
 ,Book
 )
 ,( ( ( 1 - vVaR% ) * 500 + 1 ) + 1 )
)

Thanks for any help,

[jbb]

Hi Nigel,

I'm working on the same calculation and have run into the same issue with calculation performance. If you managed to find anything that improved the performance, it would be very useful to share the results.

Equally, if I work something out, I'll post my results here.

Kind regards,

James.

[jbb]

As an update to this, we found that using the Fractile function to return the nth value performed significantly better than using Min.

In your case, the formula would become:

fractile(aggr(sum(VaRPnL), VaRPnLDate ,CobDate ,Region ,Division ,Business ,[Sub Business] ,[Desk Location] ,Desk ,[Sub Desk] ,Book), 0.998-(vVAR%/100))

What we also discovered was that it may be acceptable to the business to simply show them the complete 500 scenario sorted list at each level of the hierarchy, using conditional formatting to highlight the relevant scenario based on the entered VAR%.

Using this method then makes it more simple to calculate values for MVaR, IVaR, ETL and CLL.

[Lee_Matthews]

I have been trying to work this out myself. The formula that worked for me at the highest level (ie. not breaking down by any dimensions) was:

sum( {<SCENARIO={'$(=FirstSortedValue(SCENARIO, AGGR(sum(VAR_P&L), SCENARIO), $(=vPercentileLowMember)))'}>} VAR_P&L)

-(

sum( {<SCENARIO={'$(=FirstSortedValue(SCENARIO, AGGR(sum(VAR_P&L), SCENARIO), $(=vPercentileHighMember)))'}>} VAR_P&L) -

sum( {<SCENARIO={'$(=FirstSortedValue(SCENARIO, AGGR(sum(VAR_P&L), SCENARIO), $(=vPercentileLowMember)))'}>} VAR_P&L)

)*(vPercentileFraction/1)

Where this expression relies upon the following variables:

vPercentileMember=(max(SCENARIO_COUNT+1)*(1-vPercentile))
vPercentileLowMember=FLOOR((max(SCENARIO_COUNT+1)*(1-vPercentile)))
vPercentileHighMember=CEIL((max(SCENARIO_COUNT+1)*(1-vPercentile)))
vPercentileFraction=vPercentileLowMember-vPercentileMember

[#5]

Are you guys using the Monte Carlo method?

Thanks,

Rodrigo

[plamen_alexiev]

Hi Rodrigo,

Do you solve a problem? I am also trying to calculate and aggregate Monte Carlo VaR in likewise manner, but got correct results only on top level

Pl.Alexiev

20.09.1018