FORMULA FUNCTIONS / CROSS SECTIONAL
Aggregate("formula", scope [, method, outlier_pct, outlier_handl, ex_zero, ex_adrs, median_fallback ])
Full Description
Returns the aggregate value for each scope. Can be used to calculate your own averages for Industries or SP500. It allows either a simple average or a cap-weighted average. Most of the time we would recommend a simple average as cap-weighting results in more volatile values (for ex.if a large cap is a borderline outlier).
It works like this:
- The formula in quotes is evaluated for all the stocks in each scope. The formula could be a single factor, like "MktCap" or more complex like "Close(0)/Close(10)".
- The results for each scope are placed in an arrays. N/A's are discarded
- The arrays are sorted.
- The arrays are trimmed or winsorized
- The arithmetic or cap-weighted average for each scope is calculated
Parameters
formula: any valid stock formula
scope: how stocks are grouped before the aggregate is calculated (see below)
method:how the aggregate value is calculated
#Avg: average the formula values (default, recommended)
#CapAvg: market cap weighted average for the formula
outlier_pct: trim percent for outliers from each side. Default is 16.5%
outlier_handler: how the outliers are treated
#Exclude: excludes outliers from the aggregation (default)
#Winsor: the outliers are set to the highest/lowest value that are not outliers
ex_zero: when set to TRUE 0 values are discarded (handy for factors like Yield). Default is FALSE.
ex_adrs: when set to TRUE ADR's are excluded. Default is TRUE.
median_fallback: when set to > 0 it triggers a fallback to a simple median calculation when not enough stocks are in the scope. Handy for small industries. Default is 0 or no fallback.
Examples
Screen for stocks whose PE is less than the Industry average
PEExclXorTTM < Aggregate("PEExclXorTTM",#Industry, #Avg)
Scope Parameter
| Values | Stocks | ETFs | Description |
| #All | Y | Y | Operates on current universe |
| #Previous1 | Y | Y | Operates on the results from previous rule |
| #GroupVar2 | Y | Y | Operates on groups based on value of variable @Group |
| --------------------------- For Stocks Only --------------------------- | |||
| #SP500 | Y | Operates within the SP500 universe | |
| #Sector | Y | Operates within each Sector of current universe | |
| #SubSector | Y | Operates within each SubSector of current universe | |
| #Industry | Y | Operates within each Industry of current universe | |
| #SubIndustry | Y | Operates within each SubIndustry of current universe | |
| --------------------------- For ETFs Only ------------------------------ | |||
| #Family | Y | Operates within each ETF Family | |
| #AssetClass | Y | Operates within each ETF Class | |
| #Region | Y | Operates within each ETF Region | |
| #Country | Y | Operates within each ETF Country | |
| #Method | Y | Operates within each ETF Method | |
| #Style | Y | Operates within each ETF Style | |
| #Size | Y | Operates within each ETF Size | |
| #ETFSector | Y | Operates within each ETF Sector | |
2Not available in Ranking Systems