pythonpandasstatisticsquantile# Python equivalent of Excel's PERCENTILE.EXC

I am using Pandas to compute some financial risk analytics, including Value at Risk. In short, to compute Value at Risk (VaR), you take a time series of simulated portfolio changes in value, and then compute a specific tail percentile loss. For example, 95% VaR is the 5th percentile figure in that time series.

I have my time series in a Pandas dataframe, and am currently using the pd.quantile() function to compute the percentile. My question is, typical market convention for VaR is use an exclusionary percentile (ie: 95% VaR is interpreted as: there is a 95% chance your portfolio will not loose MORE than the computed number) - akin to how MS Excel PERECENTILE.EXC() works. Pandas quantile() works akin to how Excel's PERCENTILE.INC() works - it includes the specified percentile. I have scoured several python math packages as well as this forum for a python solution that uses the same methodology as PERCENTILE.EXC() in Excel with no luck. I was hoping someone here might have a suggestion?

Here is sample code.

```
import pandas as pd
import numpy as np
test_pd = pd.Series([15,14,18,-2,6,-78,31,21,98,-54,-2,-36,5,2,46,-72,3,-2,7,9,34])
test_np = np.array([15,14,18,-2,6,-78,31,21,98,-54,-2,-36,5,2,46,-72,3,-2,7,9,34])
print 'pandas: ' + str(test_pd.quantile(.05))
print 'numpy: '+ str(np.percentile(test_np,5))
```

The answer i am looking for is -77.4

Thanks,

Ryan

Solution

It won't be as efficient as Pandas' own percentile but it should work:

```
def quantile_exc(ser, q):
ser_sorted = ser.sort_values()
rank = q * (len(ser) + 1) - 1
assert rank > 0, 'quantile is too small'
rank_l = int(rank)
return ser_sorted.iat[rank_l] + (ser_sorted.iat[rank_l + 1] -
ser_sorted.iat[rank_l]) * (rank - rank_l)
ser = pd.Series([15,14,18,-2,6,-78,31,21,98,-54,-2,-36,5,2,46,-72,3,-2,7,9,34])
quantile_exc(ser, 0.05)
Out: -77.400000000000006
quantile_exc(ser, 0.1)
Out: -68.399999999999991
quantile_exc(ser, 0.3)
Out: -2.0
```

Note that Excel fails for small percentiles; it is not a bug. It is because ranks that go below the minimum value is not suitable for interpolation. So you might want to check if rank > 0 in the `quantile_exc`

function (see the assertion part).

- How to write a list of list into excel using python?
- How do I change the dataset when I Fine-Tuning the Whisper model?
- Understanding Python HTTP streaming
- Aldebaran's NAO robot variable list
- Python changing dictionary values in a loop
- ROS: Can't publish on Topic using python
- Numpy error: Singular matrix
- Enumerating a tuple of indices with itertools.product
- Subtract two columns in dataframe
- How to instantiate a table object to bulk_insert rows using alembic / SQLAlchemy
- Is Any is the same as Forall a.a?
- File "<string>", line 1, in <lambda> NameError: name 'self' is not defined
- python: convert pywintyptes.datetime to datetime.datetime
- How to get the original variable name of variable passed to a function
- How to use lists in control flow statements in Python
- How to "append" to personalised ROS array message in python
- Is all the data in Firebase available to everyone?
- How to place bulleted lists within simple tables in restructured text?
- Redirecting stdout to "nothing" in python
- How to get Dart generator functions to act like Python?
- I want to get the dictionary key based on its values
- Django queryset - exclude True values
- `convert_time_zone` function to retrieve the values based on the timezone specified for each row in Polars
- pandas dataframe custom sort function
- Error in running exe file having xgboost package by using pyinstaller
- gpiozero.exc.PinPWMUnsupported: PWM is not supported on pin GPIO7 (Raspberry Pi 4B)
- How to generate graph from Pandas dataframe with full hierachical data?
- How do I make a flat list out of a list of lists?
- Group by + New column + oldest date when type repated
- is there package exist in python to do RWR(random walk with restart)