Manual version 21.3

Warning : previous version of manual

This version of the manual was displayed and/or printed while a more recent version of the addin was already available.
This document version : 21.3
Most recent version available : 24.7

Table of content

• Edgelab Functions
• Edgelab Ribbon
• Edgelab Usage

Edgelab Functions

UDFs listing

• admelSetApiKey
• elAssetCurrency
• elAssetMeasure
• elAssetName
• elAssetProxy
• elBenchmarkMeasure
• elCurrentMeasure
• elCurrentNpv
• elDiversificationBenefit
• elEs
• elFixedIncomeShift
• elMarketData
• elPortWeightToQuantity
• elPrc
• elPrg
• elProxyCleanQuantities
• elRiskMeasure
• elScenarioPnl
• elStressScenario
• elTrackingError
• elVar
• elVolatility
• elYieldSolving
• ewrBenchmarkMeasure
• ewrCurrentMeasure
• ewrDistribution
• ewrDiversificationBenefit
• ewrEs
• ewrPrg
• ewrRiskMeasure
• ewrScenarioPnl
• ewrStressScenario
• ewrTrackingError
• ewrVar
• ewrVolatility

UDFs details

---

• admelSetApiKey

First introduced in version: 20.04
Last modified in version : -
Purpose : admin access to setting the API key

Summary

The admelSetApiKey function allows to save a new API key through a function instead of the ribbon. Warning: this function should only be used under the assistance of Edgelab support.

---

• elAssetCurrency

First introduced in version: 18.10
Last modified in version : 20.7.2
Purpose : retrieve the underlying currency of the instrument

Summary

The elAssetCurrency function retrieves the trading currency of the instrument as saved in the EdgeLab database. This function can also be used as a parameter inside other UDFs (see example below).

Examples

=elAssetCurrency("US0378331005")

Retrieves the currency for the Apple share.

=elAssetCurrency(A10)

Retrieves the currency for the identifier in a cell.

=elAssetCurrency(B2:B10)

Retrieves the currencies for a list of cells.

=elCurrentNpv("US0378331005",elAssetCurrency(B2))

Retrieves the NPV of “US0378331005” using elAssetCurrency as one of its parameter.

Syntax

elAssetCurrency(AssetIDs[])

Argument name	Description
AssetIDs	Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).

---

• elAssetMeasure

First introduced in version: 20.7
Last modified in version : -
Purpose : retrieve risk measures specific to fixed income and structured product instruments

Summary

The elAssetMeasure function retrieves a number of different measures such as the probability of hitting the barrier, the yield-to-call, the probability of autocall on the next observation etc. Note: The expected-time-to-maturity measure is only applicable to structured products.

Examples

=elAssetMeasure("ytm", C11)

Retrieves the yield-to-maturity of a bond (fixed instrument) whose id is located in C11 .

=elAssetMeasure("ytm", C11:C50)

Retrieves the yield-to-maturity for each bond in the range C11:C50.

Syntax

elAssetMeasure(AssetMeasureType, AssetIDs[])

Argument name	Default	Description
AssetMeasureType		The name of the measure to be returned.
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).

---

• elAssetName

First introduced in version: 18.10
Last modified in version : 19.04
Purpose : retrieve the name of the instrument

Summary

The elAssetName function retrieves the name of the instrument as saved in the EdgeLab database.

Examples

=elAssetName("US0378331005")

Retrieves the name for an Apple share.

=elAssetName(A1)

Retrieves the name for the identifier in a cell.

=elAssetName(B2:B10)

Retrieves the names for a list of cells.

=elAssetName(B2:B10,30)

Retrieves the names for a list of cells limiting the results to a maximum length of 30 characters.

Syntax

elAssetName(AssetIDs[], Length)

Argument name	Description
AssetIDs	Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Length (Optional)	The maximum length above which the returned name will be cropped. The default is to return the full name.

---

• elAssetProxy

First introduced in version: 20.7
Last modified in version : -
Purpose : returns the proxy ID if the input asset is replaced by a proxy

Summary

The elAssetProxy function indicates the proxy ID if the asset is being replaced by a proxy.

Examples

=elAssetProxy("US0378331005")

Retrieves the proxy ID of asset “US0378331005”. The proxy ID is stored in the asset proxy configuration file. It returns a blank cell if the input is not replaced by a proxy.

Syntax

elAssetProxy(AssetIDs)

Argument name	Description
AssetIDs	Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).

---

• elBenchmarkMeasure

First introduced in version: 20.12
Last modified in version : -
Purpose : generic function to retrieve benchmark measures

Summary

The elBenchmarkMeasure function allows retrieving benchmark measures. As of now, only tracking-error is available. The benchmark can be composed of one position (e.g. an Index) or multiple ones (e.g. a portfolio benchmark). You can specify the amounts in weights or quantities. Bear in mind that if the amount scheme “weight” is selected, the sum of the weights must be equal to 1.

Examples

=elBenchmarkMeasure("tracking-error", C11:C20, B11:B20, D9, "weight", 1, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and quantities in B11:B20. The benchmark is one position in D9 and could be an Index for example. The amount scheme for the benchmark is “weight” but it could have been “quantity”. The amount is 1 as the benchmark is one position. Then you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

=elBenchmarkMeasure("tracking-error", C11:C20, B11:B20, D9:D18, "weight", E9:E18, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and quantities in B11:B20. The benchmark is a portfolio whose instruments are in D9:D18. The amount scheme for the benchmark is “weight”. In E9:E18, the weights must sum up to 1. Then, you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

=elBenchmarkMeasure("tracking-error", C11:C20, B11:B20, D9:D18, "quantity", F9:F18, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and quantities in B11:B20. The benchmark is a portfolio whose instruments are in D9:D18. The amount scheme for the benchmark is “quantity”. F9:F18 represents the quantities. Then, you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

Syntax

elBenchmarkMeasure(Measure, AssetIDs[], Quantities[], BenchmarkAssetids[], BenchmarkAmountScheme, BenchmarkAmounts, Currency, RiskHorizon, ScenarioType)

Argument name	Default	Description
Measure		tracking-error
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities		Asset quantities in the same orders as the specified asset ids.
BenchmarkAssetids		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
BenchmarkAmountScheme (Optional)	quantity	weight or quantity
BenchmarkAmounts		Weight scheme: Asset weights in the same orders as the specified asset ids and must sum up to 100%. Quantity scheme: Asset quantities in the same orders as the specified asset ids.
Currency		The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.

---

• elCurrentMeasure

First introduced in version: 20.04
Last modified in version : -
Purpose : generic function to retrieve specific measures

Summary

The elCurrentMeasure function retrieves a number of different current measures for a list of instruments. The function supports positions, contributions and portfolio as calculation levels.

Examples

In these examples the risk measure that the function aims to retrieve is the net present value (NPV).

=elCurrentMeasure("NPV","positions",C11,D11,"EUR", "relative")

Retrieves the current NPV of one unique asset whose ID is in cell C11 and quantity in cell D11. The currency is EUR. MeasureType is set to relative.

=elCurrentMeasure("NPV","portfolio",C11:C50,D11:D50,"EUR", "relative")

Retrieves the current NPV of a portfolio where C11:C50 are the asset IDs within the portfolio and D11:D50 are the quantities of each asset. The currency is EUR.

=elCurrentMeasure("NPV","portfolio",{"CHF","EUR"},{1000,1000},"EUR", "relative")

This example can be copied and pasted to Excel and will give an instantaneous result.

Syntax

elCurrentMeasure(Measure, CaluclationsLevel, AssetIDs[], Quantities[], Currency, MeasureType)

Argument name	Default	Description
Measures		The name of the measure to be returned.
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities (Optional)	1	Asset quantities in the same orders as the specified asset ids.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
MeasureType (Optional)	relative	Whether the result should be normalized. The input should be “relative” or “absolute”. “True” or “False” are still supported for the moment.

---

• elCurrentNpv

First introduced in version: 18.10
Last modified in version : 20.04
Purpose : retrieve the current valuation of the product

Summary

The elCurrentNpv function retrieves the current valuation (Edgelab valuation) as saved in the Edgelab database.

Examples

=elCurrentNpv("US0378331005")

Retrieves the price of an Apple share.

=elCurrentNpv(A10)

Retrieves the price for the identifier in a cell.

=elCurrentNpv("US0378331005", "CHF")

Retrieves the price of an Apple share converted to Swiss Francs.

Syntax

elCurrentNpv(AssetIDs[], Currency)

Argument name	Description
AssetID	A single asset identifier representing the instrument (ISIN, FIGI, currency ISO, Edgelab ID).
RefCurrency	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.

---

• elDiversificationBenefit

First introduced in version: 20.12
Last modified in version : -
Purpose : measure the diversification benefit in a portfolio

Summary

With elDiversificationBenefit, you can measure how much your portfolio benefits from diversification. The Add-in function can be translated as such:

$$ DB = 1 - \frac{risk \quad measure(portfolio \quad level)}{risk \quad measure(positions \quad level)} $$

The numerator is the risk measure of the portfolio. The denominator is the sum of the risk measures of each position, weighted by the position’s weights.

$$ risk \quad measure(positions \quad level) = \sum_{i} w_{i} riskMeasure_{i}. $$

The larger is the diversification benefit, the more the portfolio benefits from diversification. The risk measure can be either volatility, value-at-risk, or expected shortfall.

Intuitively, if there is no diversification in the portfolio, all the positions behave in the same way and the correlation between positions are 1. Then, the risk measure of the portfolio is equal to the risk measure of the positions, the fraction is equal to 1 and there is no diversification benefit. For the diversification benefit to increase, the positions in the portfolio should have a small correlation so that the portfolio risk is smaller than the total risk of the positions.

A simple example with risk measured by the volatility and constant correlation

Consider a simple example with a portfolio made of $n$ positions, all with the same weights $w_i = 1/n$ and the same volatility $\sigma$. All the positions share the same correlation $\rho$, namely the correlation between 2 positions $i$ and $j$ is $\rho_{i,j} = \rho$ (and the correlation is one when $i = j$). The risk measure is the volatility.

With this setting, the analytical computations are straightforward.

At the position level, the risk of each position is $\sigma$, and the total risk evaluated at the position level is $\sum_{i} w_{i} \sigma = \sigma$.

At the portfolio level, the risk is given by

\[\sigma(portfolio) = \sigma \sqrt{\rho + (1-\rho)/n}.\]

The ratio of the risks at portfolio and position levels is not depending on $\sigma$, but only on the correlation and the number of positions. The diversification benefit is $DB = 1 - \sqrt{\rho + (1-\rho)/n}$. If the correlation is one, \(\rho = 1\), diversification is not possible as all assets behave similarly. In this case, the diversification benefit is zero, namely regardless of the number of positions, diversification cannot be achieved.

If the correlation is zero, the diversification benefit is $DB = 1 - \sqrt{1/n}$, namely the larger is the portfolio, the larger the diversification benefit.

The correlation can be negative. For 2 random variables, the correlation is between -1 and +1. But with more random variables, the correlations cannot be all -1 for all pairs of variables! For the present model with a constant correlation coefficient, it can be shown that the correlation coefficient is bounded with

\[\frac{1}{n-1} \leq \rho \leq 1\]

With the correlation at the lower bound, the volatility of the portfolio is null, and the diversification benefit is 1. This is the best that can be achieved. Alas, it is very difficult to find many assets with mutual correlation coefficients that are all negatives. Hence, this case is rather theoretical, and the realistic domain for this model is to consider positive correlations $\rho$.

For a correlation between 0 and 1, the term in $\rho$ in the square root is the component that cannot be diversified away. The second term given by $(1-\rho)/n$ is the part that is decreasing with increasing portfolio size. This simple model concludes that to have the largest diversification benefit, a portfolio should be constructed from a large number of positions with a low correlation $\rho$.

This simple model was constructed to make straightforward analytical computations. In general, the correlations on a typical stock market are in the range of 10 to 60%, and rarely null or negative. Then, the volatilities differ, and the position weights are different. But the conclusions of the simple model remain, namely diversified portfolios are made of a large number of positions with low correlations. And completely diversified portfolios cannot be constructed in practice, even with a very large number of positions.

Examples

=elDiversificationBenefit("volatility",C11:C50,D11:D50, "EUR", "10d", 95, "historicalInnovation",True)

The above example would return the diversification benefit for the volatility of a portfolio whose assets and quantities are in C11:C50 and D11:D50 respectively. The portfolio currency is EUR and the scenario type is historicalInnovation. The rest of the parameters are the default ones.

Syntax

elDiversificationBenefit(Measure, CalculationsLevel, AssetIDs[], Quantities[], Currency, Prices[], RiskHorizon, ConfidenceLevel, ScenarioType,Annualized)

Argument name	Default	Description
Measure		The name of the measure to be returned.
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities		Asset quantities in the same orders as the specified asset ids.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
Prices (Optional)	Edgelab prices	You have the possibility to insert your own instrument prices. This parameter is optional and the function will take the Edgelab prices by default.
RiskHorizon (Optional)	10	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ConfidenceLevel (Optional)	0.95	The confidence level for the tail statistics estimators VaR and ES. It is number in percent between 85 and 99 [%].
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• elEs

First introduced in version: 19.04
Last modified in version : -
Purpose : retrieve the expected shortfall of an instrument or of a portfolio

Summary

The elEs function retrieves the expected shortfall for a list of instruments.

Examples

=elEs("positions","US0378331005")

Retrieves the expected shortfall of an Apple share using the default calculation parameters.

=elEs("positions",A2:A10)

Retrieves the expected shortfall of a list of instruments provided in cells A2 to A10.

=elEs("positions","US0378331005",,,"30d")

Retrieves the expected shortfall of an Apple share over a horizon of 30 days.

=elEs("portfolio",C11:C50)

Retrieves the aggregate expected shortfall of a portfolio of instruments provided in cells C11 to C50.

=elEs("portfolio",C11:C50,,"EUR","252d",90,"historicalInnovation")

Retrieves the aggregate expected shortfall of a portfolio in EUR whose positions are located in C11:C50. The parameters are a risk horizon of 252 days, a confidence level of 90% and historical innovation that is the model from which the assets’ risk factors are drawn.

=elEs("contributions",C11:C50,,"EUR","252d",90,"historicalInnovation")

Retrieves the contribution of each position located in C11:C50 to the aggregated expected shortfall of a portfolio in EUR. The function uses a risk horizon of 252 days, a confidence level of 90% and historical innovation that is the model from which the assets’ risk factors are drawn.

Syntax

elEs(CalculationsLevel, AssetIDs[], Quantities[], Currency, 
	RiskHorizon, ConfidenceLevel, ScenarioType, 
	MeasureType, Annualized)

Argument name	Default	Description
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities (Optional)	1	Asset quantities in the same orders as the specified asset ids.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ConfidenceLevel (Optional)	0.95	The confidence level for the tail statistics estimators VaR and ES. It is number in percent between 85 and 99 [%].
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
MeasureType (Optional)	relative	Whether the result should be normalized. The input should be “relative” or “absolute”. “True” or “False” are still supported for the moment.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• elFixedIncomeShift

First introduced in version: 19.04
Last modified in version : 20.7
Purpose : calculate aggregate risk sensitivity

Summary

This function calculates a high level aggregate sensitivity figure of a fixed income asset. It takes into account shifts in both interest rate and credit curves by combining the effects of several standard sensitivity measures.

Formula

$$ - IRShift\cdot DV01 + 0.5 \cdot \left(IRShift \right)^2 \cdot \frac{Convexity}{\left (10000 \right)^2} + CreditShift \cdot CS01 $$

Examples

=elFixedIncomeShift(A1,B1) or =elFixedIncomeShift(100,3047)

Returns the sensitivity based solely on the assets DV01 (second argument) and the assumed shift in interest rates (first argument).

=elFixedIncomeShift(A1,B1,C1) or =elFixedIncomeShift(100,3047,9205)

Returns the sensitivity taking into account, in addition to the precedent function, the instruments convexity (third argument) with regards to interest rate changes.

=elFixedIncomeShift(A1,B1,C1,D1,E1) or =elFixedIncomeShift(100,3047,9205,100,-2993)

Returns the sensitivity including, in addition to the precedent function, the instruments sensitivity to credit curve shifts (CS01) (fifth argument) and the corresponding assumed shift in the curve (fourth argument).

Syntax

elFixedIncomeShift(IRShift, DV01, Convexity, CreditShift, CS01)

Argument name	Description
IRShift	The assumed parallel shift in interest rates expressed in basis points (bps).
DV01	The DV01 of the asset: its sensitivity to 1 bp change in interest rates.
Convexity (Optional)	The convexity of the asset: measures the non-linearity of the asset interest rate sensitivity.
CreditShift (Optional)	The assumed parallel shift in the asset credit curves expressed in basis points (bps).
CS01 (Optional)	The CS01 of the asset: its sensitivity to 1 bp change in the credit curve.

---

• elMarketData

First introduced in version: 20.04
Last modified in version : -
Purpose : retrieve the parameter of the specified instrument

Summary

The elMarketData function retrieves the specific parameter of the instrument as saved in the EdgeLab database.

Examples

=elMarketData("US0378331005", "liquidityHorizon")

Retrieves the liquidity horizon for an Apple share.

Syntax

elMarketData(AssetIDs[], ParameterName)

Argument name	Description
AssetIDs	Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
ParameterName	liquidityHorizon

---

• elPortWeightToQuantity

First introduced in version: 18.10
Last modified in version : 20.7.2
Purpose : calculate the required quantity of an asset to reach a target weight in a portfolio of a given value

Summary

The elPortWeightToQuantity function converts the asset’s weight into quantity given that the instrument is part of a portfolio which has a specific value and currency.

Examples

=elPortWeightToQuantity("US0378331005",0.2,"CHF",1000000)

Calculates the number of Apple shares required to achieve a target weight of 20% within a portfolio of 1,000,000 CHF.

=elPortWeightToQuantity(A1,30%,"GBP",500000)

Calculates the required quantity of the instrument whose ID is specified in cell A1 to achieve a target weight of 30% within a portfolio of 500,000 GBP.

=elPortWeightToQuantity(A1:A25,B1:B25,"GBP",500000)

The function is now dynamic. In this example, it calculates the required quantities of the instruments (A1:A25) to achieve their respected weights (B1:B25) within a portfolio of 500,000 GBP.

Syntax

elPortWeightToQuantity(AssetIDs[],Weights[],PortfolioCurrency,PortfolioValue)

Argument name	Description
AssetID	A single asset identifier representing the instrument (ISIN, FIGI, currency ISO, Edgelab ID).
Weight	The desired weight of the instrument expressed in percentage within the portfolio.
PortfolioCurrency	The ISO code of the currency in which the overall portfolio value is specified.
PortfolioValue	The total value of the portfolio within which the desired weight should be achieved.

---

• elPrc

First introduced in version: 18.10
Last modified in version : 21.3
Purpose : the Product Risk Classification (PRC) of instruments

Summary

The elPrc function provides you with the PRC of a single or a range of assets.

Examples

=elPrc("US0378331005", "2020q3")

Retrieves the PRC of an Apple share using using the methodology “historicalInnovations”.

=elPrc(A2:A10, "2017q4)

Retrieves the PRC for a list of assets using the methodology “historicalReturns”.

Syntax

elPrc(AssetIDs[], Methodology)

Argument name	Default	Description
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Methodology	2017q4	“2017q4”, “2020q3”

---

• elPrg

First introduced in version: 18.10
Last modified in version : 18.12
Purpose : retrieve the Portfolio Risk Grade (PRG) of a portfolio

Summary

The elPrg function returns the Portfolio Risk Grade of a portfolio in a reference currency.

Examples

=elPrg(C11:C50,D11:D50,"EUR")

Retrieves the risk grade of a portfolio of EUR instruments with the list of instruments provided in column C and their corresponding quantities in column D.

Syntax

elPrg(AssetIDs[], Quantities[], Currency)

Argument name	Description
AssetIDs	Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities	Asset quantities in the same orders as the specified asset ids.
Currency	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.

---

• elProxyCleanQuantities

First introduced in version: 18.10
Last modified in version : 19.04
Purpose : return a cleaned quantity between a main quantity and a proxy quantity.

Summary

The function elProxyCleanQuantities is a help function, which provides a simplified way to handle quantities. This functions also checks for null or empty strings.

Examples

=elProxyCleanQuantities(A1) or =elProxyCleanQuantities(A1, "")

Returns a verified version of the quantity in A1. If the value referenced in the second parameters is empty or null, this value is discarded.

=elProxyCleanQuantities(A1, B2)

In this case, the function overrides the quantity in A1 and returns a checked version of the quantity in B2. This allows an override of the quantity in A1, for example in the case where a proxy asset is used.

Syntax

elProxyCleanQuantities(QuantityMain, QuantityOverride)

Argument name	Description
QuantityMain	A main quantity.
QuantityOverride	A replacement quantity.

---

• elRiskMeasure

First introduced in version: 20.04
Last modified in version : -
Purpose : generic function to retrieve any type of risk figures

Summary

The elRiskMeasure function retrieves a number of different risk measures for a list of instruments based on a variety of scenarios and calculation parameters.

Examples

In these examples the risk measure that the function aims to retrieve is the value at risk (VaR).

=elRiskMeasure("var","positions",C11,D11,"EUR","10d",95, "historicalInnovation", "relative", TRUE)

Retrieves the value at risk of one unique asset whose ID and quantity are is in cell C11 and D11 respectively. The currency is EUR. The RiskHorizon is 10 days, the CondifenceLevel is 95%, the ScenarioType is historical innovation and MeasureType and Annualized are set at “relative” and TRUE. These are the default parameters.

=elRiskMeasure("var","portfolio",C11:C50,D11:D50,"EUR","10d",95, "historicalInnovation", "relative", TRUE)

Retrieves the value at risk of a portfolio whose positions and quantities are displayed by C11:C50 and D11:D50 respectively. The currency is EUR. The rest are the default parameters

=elRiskMeasure("var","portfolio",{"CHF","EUR"},{1000, 1000},"EUR","10d",95, "historicalInnovation", "relative", TRUE)

This example can be copied and pasted to Excel and will give an instantaneous result.

=elRiskMeasure("var","contributions",C11:C50,D11:D50,"EUR","10d",95, "historicalInnovation", "relative", TRUE)

Retrieves the contributions to the portfolio value at risk of each asset inside the portfolio. C11:C50 stands for the asset IDs of the portfolio and D11:D50 for the quantities. The currency is EUR and the rest are the default parameters.

Syntax

elRiskMeasure(Measure, CaluclationsLevel, AssetIDs[], Quantities[], Currency, RiskHorizon, ConfidenceLevel, ScenarioType, MeasureType, Annualized)

Argument name	Default	Description
Measures		The name of the measure to be returned.
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities (Optional)	1	Asset quantities in the same orders as the specified asset ids.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10d	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ConfidenceLevel (Optional)	95	The confidence level for the tail statistics estimators VaR and ES. It is number in percent between 85 and 99 [%].
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
MeasureType(Optional)	relative	Whether the result should be normalized. The input should be “relative” or “absolute”. “True” or “False” are still supported for the moment.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• elScenarioPnl

First introduced in version: 18.12
Last modified in version : -
Purpose : retrieve the profit and loss of a portfolio or its instruments

Summary

The elScenarioPnl function retrieves the profit and loss of a portfolio during a stress scenario event. This function supports only positions and portfolio as calculation levels.

Examples

=elScenarioPnl(C11:C50,3021,"portfolio",D11:D50,"CHF")

Calculates the P&L impact a market stress scenario like the 2007-2009 sub-prime crisis (scenario 3021) would have on a portfolio of instruments defined in column C (with corresponding quantities in column D) expressed in CHF.

Syntax

elScenarioPnl(AssetsIDs[], ScenarioID, 
	CalculationsLevel, Quantities[], Currency, MeasureType)

Argument name	Default	Description
AssetsIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
ScenarioID		The ID of a pre-defined scenario to serve as the basis for the simulation.
CalculationsLevel	positions	The granularity at which the results should be calculated (individual asset, portfolio).
Quantities		Asset quantities in the same orders as the specified asset ids.
Currency	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
MeasureType (Optional)	relative	Whether the result should be normalized. The input should be “relative” or “absolute”. “True” or “False” are still supported for the moment.

---

• elStressScenario

First introduced in version: 20.04
Last modified in version : -
Purpose : retrieve a specific measure of a portfolio in the context of a specified stress scenario

Summary

The elStressScenario function retrieves the risk measure of a portfolio in the context of a specified stress scenario. This function supports only positions and portfolio as calculation levels.

Examples

In these example the chosen stress scenario is the 2007-2009 sub-prime crisis (scenario 3021).

=elStressScenario("pnl","portfolio", 3021, C11,D11,"EUR", "relative")

Calculates the P&L impact a market stress scenario would have on a portfolio (here one asset) defined by C11 and its corresponding quantity defined by D11. Here, the currency is EUR. MeasureType is set to relative.

=elStressScenario("pnl","portfolio", 3021, C11:C50,D11:D50,"EUR", "relative")

Calculates the P&L impact a market stress scenario would have on a portfolio whose instrument IDs are defined by C11:C50 and their corresponding quantities are listed in D11:D50. The currency is still EUR.

=elStressScenario("pnl","portfolio", 3021, {"CHF","EUR"},{1000,1000},"EUR", "relative")

This example can be copied and pasted to Excel and will give an instantaneous result.

Syntax

elStressScenario(Measure, CalculationsLevel, ScenarioID, AssetsIDs[], Quantities[], Currency, MeasureType)

Argument name	Default	Description
Measure		“pnl” (Profit and Loss).
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
ScenarioID		The ID of a pre-defined scenario to serve as the basis for the simulation.
AssetsIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities		Asset quantities in the same orders as the specified asset ids.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
MeasureType (Optional)	relative	Whether the result should be normalized. The input should be “relative” or “absolute”. “True” or “False” are still supported for the moment.

---

• elTrackingError

First introduced in version: 20.12
Last modified in version : -
Purpose : shortcut function to retrieve the tracking error.

Summary

The elTrackingError function allows retrieving the tracking error. The benchmark can be composed of one position (e.g. an Index) or multiple ones (e.g. a portfolio benchmark). You can specify the amounts in weights or quantities. Bear in mind that if the amount scheme “weight” is selected, the sum of the weights must be equal to 1.

Examples

=elTrackingError(C11:C20, B11:B20, D9, "weight", 1, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and quantities in B11:B20. The benchmark is one position in D9 and could be an Index for example. The amount scheme for the benchmark is “weight” but it could have been “quantity”. The amount is 1 as the benchmark is one position. Then you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

=elTrackingError(C11:C20, B11:B20, D9:D18, "weight", E9:E18, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and quantities in B11:B20. The benchmark is a portfolio whose instruments are in D9:D18. The amount scheme for the benchmark is “weight”. In E9:E18, the weights must sum up to 1. Then, you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

=elTrackingError(C11:C20, B11:B20, D9:D18, "quantity", F9:F18, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and quantities in B11:B20. The benchmark is a portfolio whose instruments are in D9:D18. The amount scheme for the benchmark is “quantity”. F9:F18 represents the quantities. Then, you have the portfolio currency (“CHF” in this example), thet time horizon (10 days), and finally the scenario type “historicalInnovation”.

Syntax

elTrackingError(AssetIDs[], Quantities[], BenchmarkAssetids[], BenchmarkAmountScheme, BenchmarkAmounts, Currency, RiskHorizon, ScenarioType)

Argument name	Default	Description
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities		Asset quantities in the same orders as the specified asset ids.
BenchmarkAssetids		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
BenchmarkAmountScheme (Optional)	quantity	weight or quantity
BenchmarkAmounts		Weight scheme: Asset weights in the same orders as the specified asset ids and must sum up to 100%. Quantity scheme: Asset quantities in the same orders as the specified asset ids.
Currency		The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.

---

• elVar

First introduced in version: 19.04
Last modified in version : -
Purpose : retrieve the value at risk (VaR) of an instrument or of a portfolio

Summary

The elVar function retrieves the value at risk for a list of instruments.

Examples

=elVar("positions","US0378331005")

Retrieves the value at risk of an Apple share using the default calculation parameters.

=elVar("positions",A2:A10)

Retrieves the value at risk of a list of instruments provided in cells A2 to A10.

=elVar("positions","US0378331005",,,"30d")

Retrieves the value at risk of an Apple share over a horizon of 30 days.

=elVar("portfolio",C11:C50)

Retrieves the value at risk of a portfolio whose positions are in cells C11 to C50.

=elVar("portfolio",C11:C50,,"CHF","30d",99,"historicalInnovation")

Retrieves the value at risk of a portfolio in CHF using a risk horizon of 30 days, a confidence level of 99% and historical innovation that is the model from which the assets’ risk factors are drawn.

=elVar("contributions",C11:C50,,"CHF","30d",99,"historicalInnovation")

Retrieves the contribution of each position located in C11:C50 to the value at risk of a portfolio in CHF. The function uses a risk horizon of 30 days, a confidence interval of 99% and historical innovation that is the model from which the assets’ risk factors are drawn.

Syntax

elVar(CalculationLevel, AssetIDs[], Quantities[], Currency, 
	RiskHorizon, ConfidenceLevel, ScenarioType, 
	MeasureType, Annualized)

Argument name	Default	Description
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities (Optional)	1	Asset quantities in the same orders as the specified asset ids.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10d	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ConfidenceLevel (Optional)	95	The confidence level for the tail statistics estimators VaR and ES. It is number in percent between 85 and 99 [%].
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
MeasureType (Optional)	relative	Whether the result should be normalized. The input should be “relative” or “absolute”. “True” or “False” are still supported for the moment.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• elVolatility

First introduced in version: 19.04
Last modified in version : -
Purpose : retrieve the volatility of an instrument or of a portfolio

Summary

The elVolatility function retrieves the volatility for a list of instruments.

Examples

=elVolatility("positions","US0378331005")

Retrieves the volatility of an Apple share.

=elVolatility("positions","US0378331005",,,"252d")

Retrieves the volatility of an Apple share over a period of 252 days.

=elVolatility("portfolio",A2:A10,,"CHF")

Retrieves the volatility of a portfolio in CHF whose positions are in cells A2 to A10.

=elVolatility("contributions",C11:C50,,"CHF","30d","historicalInnovation")

Retrieves the contribution of each position located in C11:C50 to the aggregated volatility of a portfolio in CHF using a risk horizon of 30 days, and historical innovation that is the model from which the assets’ risk factors are drawn.

Syntax

elVolatility(CalculationsLevel, AssetIDs[], Quantities[], Currency, 
	RiskHorizon, ScenarioType, 
	MeasureType, Annualized)

Argument name	Default	Description
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Quantities (Optional)	1	Asset quantities in the same orders as the specified asset ids.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10d	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
MeasureType (Optional)	relative	Whether the result should be normalized. The input should be “relative” or “absolute”. “True” or “False” are still supported for the moment.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• elYieldSolving

First introduced in version: 20.12
Last modified in version : -
Purpose : retrieve risk measures of bonds

Summary

The elYieldSolving function retrieves several different measures such as the yield-to-maturity (ytm), the yield-to-call (ytc), the yield-to-put (ytp) and the current-yield.

Note: The correct date format is the ISO-8601 string, namely “yyyy-MM-dd”. We strongly recommend to use this format only. Yet, tests have been succesfully conducted with the following formats also: “dd.MM.yyy”, dd/MM/yyyy” and “dd-MM-yyyy”.

Examples

=elYieldSolving("ytm", C11, 100, "dirty", "EUR", "2021-01-15")

Retrieves the yield-to-maturity for a bond in C11 that has a price of 100 EUR at the valuation date of 15.01.2021.

=elYieldSolving("current-yield", "US58013MFB54", 99, "clean", "EUR", "2021-01-15")

Retrieves the yield-to-call for a bond from McDonald’s Corporation that has a price of 99 EUR at the valuation date of 15.01.2021.

=elYieldSolving("ytp", C13, 100, "clean", "EUR", "2021-01-15")

Retrieves the yield-to-put for a bond in C12 that has a price of 100 EUR at the valuation date of 15.01.2021.

Syntax

elYieldSolving(YieldMeasure, AssetID, Price, PriceType, Currency, ValueDate)

Argument name	Default	Description
YieldMeasure		The name of the measure to be returned.
AssetID		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Price		The current price for which the yield is computed.
PriceType (Optional)	clean	clean or dirty
Currency		The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
ValueDate (Optional)	the current date and time	Valuation date accepts Excel and text date format according to ISO-8601. We recommend to use yyyy-MM-dd.

---

• ewrBenchmarkMeasure

First introduced in version: 20.12
Last modified in version : -
Purpose : generic function to retrieve benchmark measures

Summary

The ewrBenchmarkMeasure function allows retrieving benchmark measures. As of now, only tracking-error is available. The benchmark can be composed of one position (e.g. an Index) or multiple ones (e.g. a portfolio benchmark). You can specify the amounts in weights or quantities. Bear in mind that if the amount scheme “weight” is selected, the sum of the weights must be equal to 1.

Examples

=ewrBenchmarkMeasure("tracking-error", C11:C20, B11:B20, D9, "weight", 1, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and weights in B11:B20. The benchmark is one position in D9 and could be an Index for example. The amount scheme for the benchmark is “weight” but it could have been “quantity”. The amount is 1 as the benchmark is one position. Then you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

=ewrBenchmarkMeasure("tracking-error", C11:C20, B11:B20, D9:D18, "weight", E9:E18, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and weights in B11:B20. The benchmark is a portfolio whose instruments are in D9:D18. The amount scheme for the benchmark is “weight”. In E9:E18, the weights must sum up to 1. Then, you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

=ewrBenchmarkMeasure("tracking-error", C11:C20, B11:B20, D9:D18, "quantity", F9:F18, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and weights in B11:B20. The benchmark is a portfolio whose instruments are in D9:D18. The amount scheme for the benchmark is “quantity”. F9:F18 represents the quantities. Then, you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

Syntax

ewrBenchmarkMeasure(Measure, AssetIDs[], Weights[], BenchmarkAssetids[], BenchmarkAmountScheme, BenchmarkAmounts, Currency, RiskHorizon, ScenarioType)

Argument name	Default	Description
Measure		tracking-error
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
BenchmarkAssetids		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
BenchmarkAmountScheme (Optional)	weight	weight or quantity
BenchmarkAmounts		Weight scheme: Asset weights in the same orders as the specified asset ids and must sum up to 100%. Quantity scheme: Asset quantities in the same orders as the specified asset ids.
Currency		The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.

---

• ewrCurrentMeasure

First introduced in version: 20.04
Last modified in version : -
Purpose : generic function to retrieve specific measures

Summary

The ewrCurrentMeasure function retrieves a number of different current measures for a list of instruments. The function supports positions, contributions and portfolio as calculation levels.

Examples

In these examples the risk measure that the function aims to retrieve is the net present value (NPV).

=ewrCurrentMeasure("NPV","portfolio",C11,D11,"EUR")

Retrieves the current NPV of a portfolio containing one unique asset whose ID is in cell C11 and weight in cell D11. The weight must be equal to 100%. The currency is EUR.

=ewrCurrentMeasure("NPV","portfolio",C11:C50,D11:D50,"EUR")

Retrieves the current NPV of a portfolio where C11:C50 are the asset IDs within the portfolio and D11:D50 are the weights of each asset that sum up to 100%. The currency is EUR.

=ewrCurrentMeasure("NPV","portfolio",{"CHF","EUR"},{0.5,0.5},"EUR")

This example can be copied and pasted to Excel and will give an instantaneous result.

Syntax

ewrCurrentMeasure(Measure, CaluclationsLevel, AssetIDs[], Weights[], Currency)

Argument name	Default	Description
Measures		The name of the measure to be returned.
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.

---

• ewrDistribution

First introduced in version: 20.7
Last modified in version : -
Purpose : retrieve the returns' distribution of an instrument or a portfolio using one of the scenario type

Summary

The ewrDistribution function spills the asset or portfolio returns over 500 cells. The only calculation level possible is portfolio.

Examples

=ewrDistribution("pnl","portfolio","historicalInnovation",252,C11,100%,"EUR",FALSE)

Spans the distribution of asset C11 using the historical innovation model with a time horizon of 252 days. The weight D11 must be equal to 100%. The currency is EUR. The last parameter is set to FALSE hence the output will be a column (a row otherwise).

=ewrDistribution("pnl","portfolio","historicalInnovation",252,"CH03994127",100%,"EUR",FALSE)

This example can be copied and pasted to Excel and will give an instantaneous result.

Syntax

ewrDistribution(Measure, CalculationsLevel, ScenarioType, TimeHorizon, AssetIDs[], Weights[], Currency, IsRow)

Argument name	Default	Description
Measure		pnl
CalculationsLevel		portfolio
ScenarioType		How the possible scenarios for the asset prices are computed.
TimeHorizon		The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
IsRow (Optional)	false	If sets to true, the output is displayed as a row

---

• ewrDiversificationBenefit

First introduced in version: 20.12
Last modified in version : -
Purpose : measure the diversification benefit in a portfolio

Summary

With ewrDiversificationBenefit, you can measure how much your portfolio benefits from diversification. The Add-in function can be translated as such:

$$ DB = 1 - \frac{risk \quad measure(portfolio \quad level)}{risk \quad measure(positions \quad level)} $$

The numerator is the risk measure of the portfolio. The denominator is the sum of the risk measures of each position, weighted by the position’s weights

$$ risk \quad measure(positions \quad level) = \sum_{i} w_{i} riskMeasure_{i}. $$

The larger is the diversification benefit, the more the portfolio benefits from diversification. The risk measure can be either volatility, value-at-risk, or expected shortfall.

Intuitively, if there is no diversification in the portfolio, all the positions behave in the same way and the correlation between positions are 1. Then, the risk measure of the portfolio is equal to the risk measure of the positions, the fraction is equal to 1 and there is no diversification benefit. For the diversification benefit to increase, the positions in the portfolio should have a small correlation so that the portfolio risk is smaller than the total risk of the positions.

A simple example with risk measured by the volatility and constant correlation

Consider a simple example with a portfolio made of $n$ positions, all with the same weights $w_i = 1/n$ and the same volatility $\sigma$. All the positions share the same correlation $\rho$, namely the correlation between 2 positions $i$ and $j$ is $\rho_{i,j} = \rho$ (and the correlation is one when $i = j$). The risk measure is the volatility.

With this setting, the analytical computations are straightforward.

At the position level, the risk of each position is $\sigma$, and the total risk evaluated at the position level is $\sum_{i} w_{i} \sigma = \sigma$.

At the portfolio level, the risk is given by

\[\sigma(portfolio) = \sigma \sqrt{\rho + (1-\rho)/n}.\]

The ratio of the risks at portfolio and position levels is not depending on $\sigma$, but only on the correlation and the number of positions. The diversification benefit is $DB = 1 - \sqrt{\rho + (1-\rho)/n}$. If the correlation is one, \(\rho = 1\), diversification is not possible as all assets behave similarly. In this case, the diversification benefit is zero, namely regardless of the number of positions, diversification cannot be achieved.

If the correlation is zero, the diversification benefit is $DB = 1 - \sqrt{1/n}$, namely the larger is the portfolio, the larger the diversification benefit.

The correlation can be negative. For 2 random variables, the correlation is between -1 and +1. But with more random variables, the correlations cannot be all -1 for all pairs of variables! For the present model with a constant correlation coefficient, it can be shown that the correlation coefficient is bounded with

\[\frac{1}{n-1} \leq \rho \leq 1\]

With the correlation at the lower bound, the volatility of the portfolio is null, and the diversification benefit is 1. This is the best that can be achieved. Alas, it is very difficult to find many assets with mutual correlation coefficients that are all negatives. Hence, this case is rather theoretical, and the realistic domain for this model is to consider positive correlations $\rho$.

For a correlation between 0 and 1, the term in $\rho$ in the square root is the component that cannot be diversified away. The second term given by $(1-\rho)/n$ is the part that is decreasing with increasing portfolio size. This simple model concludes that to have the largest diversification benefit, a portfolio should be constructed from a large number of positions with a low correlation $\rho$.

This simple model was constructed to make straightforward analytical computations. In general, the correlations on a typical stock market are in the range of 10 to 60%, and rarely null or negative. Then, the volatilities differ, and the position weights are different. But the conclusions of the simple model remain, namely diversified portfolios are made of a large number of positions with low correlations. And completely diversified portfolios cannot be constructed in practice, even with a very large number of positions.

Examples

ewrDiversificationBenefit("volatility",C11:C50,D11:D50, "EUR", "10d", 95, "historicalInnovation",True)

The above example would return the diversification benefit for the volatility of a portfolio whose assets and weights are in C11:C50 and D11:D50 respectively. The portfolio currency is EUR and the scenario type is historicalInnovation. The rest of the parameters are the default ones.

Syntax

ewrDiversificationBenefit(Measure, AssetIDs[], Weights[], Currency, RiskHorizon, ConfidenceLevel, ScenarioType,Annualized)

Argument name	Default	Description
Measure		The name of the measure to be returned.
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ConfidenceLevel (Optional)	95	The confidence level for the tail statistics estimators VaR and ES. It is number in percent between 85 and 99 [%].
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• ewrEs

First introduced in version: 20.04
Last modified in version : -
Purpose : retrieve the expected shortfall of an instrument or a portfolio

Summary

The ewrEs function retrieves the expected shortfall for a list of instruments when the calculation level is “portfolio” or the asset contributions to the expected shortfall when the calculation level is “contributions”. “Positions” level is also possible. Though, the measure will always be expressed in a relative term when using weights in the parameters instead of quantities.

Examples

=ewrEs("portfolio",C11,D11,"EUR","10d",95,"historicalInnovation",TRUE)

Retrieves the expected shortfall of a portfolio containing one unique asset whose ID is in cell C11 and weight in cell D11. The weight must be equal to 100%. The currency is EUR. The RiskHorizon is 10 days, the CondifenceLevel is 95%, the ScenarioType is historical innovation and Annualized is set at TRUE. These are the default parameters.

=ewrEs("portfolio",C11:C50,D11:D50,"EUR","10d",95,"historicalInnovation",TRUE)

Retrieves the expected shortfall of a portfolio where C11:C50 are the asset IDs within the portfolio and D11:D50 are the weights of each asset that sum up to 100%. The currency is EUR. The rest are the default parameters.

=ewrEs("portfolio",{"CHF","EUR"},{0.5,0.5},"EUR","10d",95,"historicalInnovation",TRUE)

This example can be copied and pasted to Excel and will give an instantaneous result.

=ewrEs("contributions",C11:C50,D11:D50,"EUR","10d",95,"historicalInnovation",TRUE)

Retrieves the contributions to the expected shortfall of each asset of the portfolio. C11:C50 stands for the asset IDs contained in the portfolio and D11:D50 for the weights. The weight must sum up to 100%. The currency is EUR and the rest are the default parameters.

Syntax

ewrEs(CalculationsLevel, AssetIDs[], Weights[], Currency, 
	RiskHorizon, ConfidenceLevel, ScenarioType, 
	Annualized)

Argument name	Default	Description
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10d	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ConfidenceLevel (Optional)	95	The confidence level for the tail statistics estimators VaR and ES. It is number in percent between 85 and 99 [%].
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• ewrPrg

First introduced in version: 20.04
Last modified in version : -
Purpose : retrieve the Portfolio Risk Grade (PRG) of a portfolio

Summary

The ewrPrg function returns the Portfolio Risk Grade of a portfolio in a reference currency

Examples

=ewrPrg(C11:C50,D11:D50,"EUR")

Retrieves the risk grade of a portfolio of EUR instruments with the list of instruments provided by C11:D50 and their corresponding weights located in D11:D50. Summing up the weights must yield to 100%.

=ewrPrg(C11:C50,D11:D50,"local")

Retrieves the risk grade of a portfolio of instruments with the list of instruments provided by C11:D50 and their corresponding weights located in D11:D50. The currency is set to “local”. Summing up the weights must yield to 100%.

=ewrPrg(C11:C50,D11:D50,)

Retrieves the risk grade of a portfolio of instruments whose IDs are provided by C11:D50 and their corresponding weights are located in D11:D50. The currency space is empty which yields to the same result as though it was set to “local”. Summing up the weights must yield to 100%.

=ewrPrg({"CHF","EUR"},{0.5,0.5},"EUR")

This example can be copied and pasted to Excel and will give an instantaneous result.

Syntax

ewrPrg(AssetIDs[], Weights[], Currency)

Argument name	Description
AssetIDs	Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights	Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.

---

• ewrRiskMeasure

First introduced in version: 20.04
Last modified in version : -
Purpose : generic function to retrieve any type of risk figures

Summary

The ewrRiskMeasure function allows to retrieve a number of different risk measures for a list of instruments based on a variety of scenarios and calculation parameters. Three calculation levels exist: “portfolio” level, “contributions” level and “positions” level.

Examples

In these examples the risk measure that the function aims to retrieve is the value at risk (VaR):

=ewrRiskMeasure("var","portfolio",C11,D11,"EUR","10d",95, "historicalInnovation",TRUE)

This function retrieves the value at risk of a portfolio containing one unique asset whose ID and weight are in cell C11 and D11 respectively. The weight must be equal to 100%. The currency is EUR. The RiskHorizon is 10 days, the CondifenceLevel is 95%, the ScenarioType is historical innovation and Annualized is set to TRUE. These are the default parameters.

=ewrRiskMeasure("var","portfolio",C11:C50,D11:D50,"EUR","10d",95, "historicalInnovation",TRUE)

This retrieves the value at risk of a portfolio of instruments whose IDs and weights are displayed by C11:C50 and D11:D50 respectively. The weights must sum up to 100%. The currency is EUR. The rest of the parameters are the default ones.

=ewrRiskMeasure("var","portfolio",{"CHF","EUR"},{0.5,0.5},"EUR","10d",95, "historicalInnovation",TRUE)

The example above can be copied and pasted to Excel and will give an instantaneous result.

=ewrRiskMeasure("var","contributions",C11:C50,D11:D50,"EUR","10d",95, "historicalInnovation",TRUE)

This retrieves the contributions to the value at risk of each asset of the portfolio. C11:C50 stands for the asset IDs of the portfolio and D11:D50 for the weights. The weights must sum up to 100%. The currency is EUR and the rest of the parameters are the default ones.

Syntax

ewrRiskMeasure(Measure, CaluclationsLevel, AssetIDs[], Weights[], Currency, RiskHorizon, ConfidenceLevel, ScenarioType, Annualized)

Argument name	Default	Description
Measures		The name of the measure to be returned.
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10d	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ConfidenceLevel (Optional)	95	The confidence level for the tail statistics estimators VaR and ES. It is number in percent between 85 and 99 [%].
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• ewrScenarioPnl

First introduced in version: 20.04
Last modified in version : -
Purpose : retrieve the Profit and Loss (P&L) of a portfolio in the context of a specified stress scenario

Summary

The ewrScenarioPnl function retrieves the profit and loss of a portfolio during a stress scenario event. This function supports only positions and portfolio as calculation levels. Since it is a ewr* function, the output will be expressed in relative terms.

Examples

In this example the chosen stress scenario is the 2007-2009 sub-prime crisis (scenario 3021).

=ewrScenarioPnl(C11,3021,"portfolio",D11,"EUR")

Calculates the P&L impact a market stress scenario would have on a portfolio (here one asset) defined by C11 and its corresponding weight defined by D11 that must be equal to 100%. Here, the currency is EUR.

=ewrScenarioPnl(C11:C50,3021,"portfolio",D11:D50,"EUR")

Calculates the P&L impact a market stress scenario would have on a portfolio whose instruments IDs are defined by C11:C50 and their corresponding weights are listed in D11:D50 that must sum up to 100%. The currency is still EUR.

=ewrScenarioPnl({"CHF","EUR"}, 3021,"portfolio",{0.5,0.5},"EUR")

This example can be copied and pasted to Excel and will give an instantaneous result.

Syntax

ewrScenarioPnl(AssetsIDs[], ScenarioID, 
	CalculationsLevel, Weights[], Currency)

Argument name	Default	Description
AssetsIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
ScenarioID		The ID of a pre-defined scenario to serve as the basis for the simulation.
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.

---

• ewrStressScenario

First introduced in version: 20.04
Last modified in version : -
Purpose : retrieve a specific measure of a portfolio in the context of a specified stress scenario

Summary

The ewrStressScenario function retrieves the profit and loss of a portfolio during a stress scenario event. This function supports only positions and portfolio as calculation levels. Since it is a ewr* function, the output will be expressed in relative terms.

Examples

In these example the chosen stress scenario is the 2007-2009 sub-prime crisis (scenario 3021).

=ewrStressScenario("pnl","portfolio", 3021, C11,D11,"EUR")

Calculates the P&L impact a market stress scenario would have on a portfolio (here one asset) defined by C11 and its corresponding weight defined by D11 that must be equal to 100%. Here, the currency is EUR.

=ewrStressScenario("pnl","portfolio", 3021, C11:C50,D11:D50,"EUR")

Calculates the P&L impact a market stress scenario would have on a portfolio whose instrument IDs are defined by C11:C50 and their corresponding weights are listed in D11:D50 that must sum up to 100%. The currency is still EUR.

=ewrStressScenario("pnl","portfolio", 3021, {"CHF","EUR"},{0.5,0.5},"EUR")

This example can be copied and pasted to Excel and will give an instantaneous result.

Syntax

ewrStressScenario(Measure, CalculationsLevel, ScenarioID, AssetsIDs[], Weights[], Currency)

Argument name	Default	Description
Measure		“pnl” (Profit and Loss).
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
ScenarioID		The ID of a pre-defined scenario to serve as the basis for the simulation.
AssetsIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weigths		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.

---

• ewrTrackingError

First introduced in version: 20.12
Last modified in version : -
Purpose : shortcut function to retrieve the tracking error.

Summary

The ewrTrackingError function allows retrieving the tracking error. The benchmark can be composed of one position (e.g. an Index) or multiple ones (e.g. a portfolio benchmark). You can specify the amounts in weights or quantities. Bear in mind that if the amount scheme “weight” is selected, the sum of the weights must be equal to 1.

Examples

=ewrTrackingError(C11:C20, B11:B20, D9, "weight", 1, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and weights in B11:B20. The benchmark is one position in D9 and could be an Index for example. The amount scheme for the benchmark is “weight” but it could have been “quantity”. The amount is 1 as the benchmark is one position. Then you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

=ewrTrackingError(C11:C20, B11:B20, D9:D18, "weight", E9:E18, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and weights in B11:B20. The benchmark is a portfolio whose instruments are in D9:D18. The amount scheme for the benchmark is “weight”. In E9:E18, the weights must sum up to 1. Then, you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

=ewrTrackingError(C11:C20, B11:B20, D9:D18, "quantity", F9:F18, "CHF", 10, "historicalInnovation")

This function retrieves the tracking error of a portfolio whose assets are in C11:C20 and weights in B11:B20. The benchmark is a portfolio whose instruments are in D9:D18. The amount scheme for the benchmark is “quantity”. F9:F18 represents the quantities. Then, you have the portfolio currency (“CHF” in this example), the time horizon (10 days), and finally the scenario type “historicalInnovation”.

Syntax

ewrTrackingError(AssetIDs[], Weights[], BenchmarkAssetids[], BenchmarkAmountScheme, BenchmarkAmounts, Currency, RiskHorizon, ScenarioType)

Argument name	Default	Description
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
BenchmarkAssetids		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
BenchmarkAmountScheme (Optional)	weight	weight or quantity
BenchmarkAmounts		Weight scheme: Asset weights in the same orders as the specified asset ids and must sum up to 100%. Quantity scheme: Asset quantities in the same orders as the specified asset ids.
Currency		The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.

---

• ewrVar

First introduced in version: 20.04
Last modified in version : -
Purpose : retrieve the value at risk (VaR) of an instrument or of a portfolio

Summary

The ewrVar function retieves the volatility for a list of instruments when the calculation level is “portfolio” or the asset contributions to the volatility when the calculation level is “contributions”. “Positions” level is also possible. Though, the measure will always be expressed in relative term when using weights in the parameters instead of quantities.

Examples

=ewrVar("portfolio",C11,D11,"EUR","10d",95,"historicalInnovation",TRUE)

Retrieves the value at risk of a portfolio containing one unique asset whose ID is in cell C11 and weight in cell D11. The weight must be equal to 100%. The currency is EUR. The RiskHorizon is 10 days, the ConfidenceLevel is 95%, the ScenarioType is historical innovation and Annualized is set at TRUE. These are the default parameters.

=ewrVar("portfolio",C11:C50,D11:D50,"EUR","10d",95,"hsitoricalInnovation",TRUE)

Retrieves the value at risk of a portfolio where C11:C50 are the asset IDs whithin the portfolio and D11:D50 are the weights of each asset that sum up to 100%. The currency is EUR. The rest are the default parameters.

=ewrVar("portfolio",{"CHF","EUR"},{0.5,0.5},"EUR","10d",95,"historicalInnovation",TRUE)

This example can be copied and pasted to Excel and will give an instantaneous result.

=ewrVar("contributions",C11:C50,D11:D50,"EUR","10d",95,"historicalInnovation",TRUE)

Retrieves the contributions to the value at risk of each asset of the portfolio. C11:C50 stands for the asset IDs contained in the portfolio and D11:D50 for the weights. The weight must sum up to 100%. The currency is EUR and the rest of the parameters are the default ones.

Syntax

ewrVar(CalculationsLevel, AssetIDs[], Weights[], Currency, 
	RiskHorizon, ConfidenceLevel, ScenarioType, 
	Annualized)

Argument name	Default	Description
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10d	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ConfidenceLevel (Optional)	95	The confidence level for the tail statistics estimators VaR and ES. It is number in percent between 85 and 99 [%].
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

---

• ewrVolatility

First introduced in version: 20.04
Last modified in version : -
Purpose : retrieve the volatility of an instrument or of a portfolio

Summary

The ewrVolatility function retieves the volatility for a list of instruments when the calculation level is “portfolio” or the asset contributions to the volatility when the calculation level is “contributions”. “Positions” level is also possible. Though, the measure will always be expressed in relative term when using weights in the parameters instead of quantities.

Examples

=ewrVolatility("portfolio",C11,D11,"EUR","10d","historicalInnovation",TRUE)

Retrieves the volatility of a portfolio containing one unique asset whose ID is in cell C11 and weight in cell D11. The weight must be equal to 100%. The currency is EUR. The RiskHorizon is 10 days, the ScenarioType is historical innovation and Annualized is set at TRUE. These are the default parameters.

=ewrVolatility("portfolio",C11:C50,D11:D50,"EUR","10d","historicalInnovation",TRUE)

Retrieves the volatility of a portfolio where C11:C50 are the asset IDs whithin the portfolio and D11:D50 are the weights of each asset that sum up to 100%. The currency is EUR. The rest are the default parameters.

=ewrVolatility("portfolio",{"CHF","EUR"},{0.5,0.5},"EUR","10d","historicalInnovation",TRUE)

This example can be copied and pasted to Excel and will give an instantaneous result.

=ewrVolatility("contributions",C11:C50,D11:D50,"EUR","10d","historicalInnovation",TRUE)

Retrieves the contributions to the volatility of each asset of the portfolio. C11:C50 stands for the asset IDs contained in the portfolio and D11:D50 for the weights. The weight must sum up to 100%. The currency is EUR and the rest of the parameters are the default ones.

Syntax

ewrVolatility(CalculationsLevel, AssetIDs[], Weights[], Currency, 
	RiskHorizon, ScenarioType, 
	Annualized)

Argument name	Default	Description
CalculationsLevel		The granularity at which the results should be calculated (individual asset, portfolio).
AssetIDs		Single or multiple asset identifiers (ISIN, FIGI, currency ISO, Edgelab ID).
Weights		Asset weights in the same orders as the specified asset ids and must sum up to 100%.
Currency (Optional)	local	The ISO code of the reference currency for deriving the calculation results. Specify “local” to use the instruments native currency where appropriate. It is mandatory to specify a currency when the granularity is “portfolio”.
RiskHorizon (Optional)	10d	The time interval over which the risks are estimated (i.e. between now and now + risk-horizon).
ScenarioType (Optional)	historicalInnovation	How the possible scenarios for the asset prices are computed.
Annualized (Optional)	true	Whether the result should be expressed as an annualized figure. True or False.

Edgelab Ribbon

Configuration group

The Configuration group in the Edgelab tab contains buttons to set up your settings and proxy choice.

API Key: opens a dialog box to enter the API key and test its authentication. The key can be saved even if its validation fails.

Network: opens a dialog box to enter proxy information. Three choices are offered:

1. None: no proxy are set up and the request will go directly to Edgelab.
2. System: all the requests will go through the local proxy setup on the machine. These proxies are usually parametrized by an IT admin and when a connection error occurs, this option should be preferred to a custom proxy.
3. Custom: in this case, full control is granted on the IP address and port of the proxy. This option is intended for advanced users.

Settings: opens a dialog box to parametrize the metal unit in which Commodity currency format (e.g. XAU) is expressed. There is also the error message helper box that is ticked by default. If it is unticked, then the feature is not activated.

Universe group

The Universe group permits to monitor your Edgelab asset universe

Open: opens the universe portal to access the asset universe. There, you can add and remove assets. UDFs are functional in the frame of the set-up universe. If you benefit from the automatic synchronization, you will have access to two universes: Main and ExcelAddin. The former is directly linked to our universe APIs and can be populated via manual synchronization. The latter is independent of any other services and can only be filled by assets used in the Add-in.

Synchronize: syncs up the universe to the spreadsheet. The latest updated version of your asset universe is then available for use. This button is only available if manual synchronization is activated.

The above picture shows the ribbon when the automated universe management feature has been implemented. The synchronize button disappears and only the Open one remains. The reason is that once the assets are used in one of our UDFs, they are automatically synchronized to your universe. Note that there are up to 20 seconds (default) of waiting between each synchronization.

Asset proxy group

The Asset Proxy group embeds the buttons to interact with the asset proxy configuration file as well as the ability to open it up directly from the Edgelab ribbon.

Setup: opens a dialog box in which the path to the configuration file is located. The browse button allows the user to select another asset proxy configuration file. The save button saves the currently selected file as the configuration file and the link “help on this feature” leads to the documentation on the asset proxy and configuration file.

Modify: opens up the asset proxy configuration file.

Templates group

The Templates” group is a button that will open the side panel dedicated to our templates. You’ll retrieve the categories and files that you can download.

Calculations group

The calculations group in the Edgelab tab contains buttons to refresh one or several sheets in the opened Excel files

Refresh worksheet: refreshes the current worksheet of the currently displayed file.

Refresh this file: refreshes every sheet within the currently displayed file.

Refresh every file: refreshes every sheet within any of the opened file. This function may be time-consuming as it forces the recalculation of any opened file. It is also similar to the built-in function of a full refresh.

Support group

The Support Group in the Edgelab tab contains all the information about the installed version of the Edgelab Add-in.

Error Helper: by clicking on this button, the error message ribbon appears on the side panel.

About: opens a window containing a link to the Edgelab website, a link to the Edgelab Add-in documentation, and a link to obtain support. Finally, it is possible to open the Add-in Logs.

Edgelab Usage

• Asset proxy in version 22.6 and later
• Asset proxy in version 21.8 and before
• Spilled array formulas