List of formulas
Contents[Hide]
1. Overview
You can apply mathematical formulas to data by entering small expressions into the formula bar, similar to a spreadsheet program. These formula expressions typically include functions such as AVG and SUM, arithmetic operators or other math functions, or a combination of these. For more complex formulas, you can write DundasScript.
This article lists the functions that are available for use in formula expressions.
Related video: Formulas
2. Standard functions
These are the most common functions for statistical or financial calculations, such as calculating the mean or generating a moving average. Additional statistical and financial functions can be found in subsequent sections.
| Function | Name | Description |
|---|---|---|
| AVG | Mean (Simple Average) | The Mean function computes the mean (or simple average) of a set of input values. |
| BOLLINGERUPPER BOLLINGERLOWER | Bollinger Bands | The Bollinger Bands functions are based on methods developed by John Bollinger. They compute a pair of data bands that envelop the moving average of the input value series. Each data band is positioned a specified number of standard deviations based on the moving average of the selected input value series. Applications of Bollinger Bands include the measurement of stock price volatility. |
|
CMLTOTAL |
Cumulative Total | The Cumulative Total functions compute a running sum or total of a set of input values which can be optionally grouped by specific hierarchies. |
| CLUSTERING | K-Means Clustering |
The Clustering function uses the K-Means algorithm to split data points into clusters based on similarity of the measures provided. |
| DISTANCE | Distance |
The Distance function computes the as-the-crow-flies distance between a source and destination identified by latitude and longitude. |
| EXPMOVAVG | Exponential Moving Average | The Exponential Moving Average function computes the average of a set of input values over a specified number of time periods. In this function, a greater weight is given to more recent data. This function can be used to smooth a data series, which helps to reduce noise and make it easier to spot data trends. |
| HISTVOL | Historical Volatility | The Historical Volatility function calculates the volatility of a set of input data values, such as stock prices over a period of time. |
| MEDIAN | Median | The Median function computes the median (e.g., middle) value from a set of input values. |
| MODE | Mode | The Mode function finds the value that is repeated more often than any other in a value series. |
| MOVAVG | Moving Average | The Moving Average function computes the average of a set of input values over a specified number of time periods. The smaller the number of time periods, the faster the SMA responds to changes in the input values. |
| MOVAVGEVPUPPER MOVAVGEVPLOWER | Moving Average Envelopes | The Moving Average Envelopes functions compute a pair of data bands that envelop a moving average of the input data values. Each data band is positioned a percentage-based magnitude away from the moving average line. The average line can be calculated using a simple moving average or an exponential moving average. Applications of the Moving Average Envelopes function include indication of overbought/oversold conditions and price trends for a security. |
| OUTIQ OUTTAU |
Data Outliers | The data outlier functions identify the outliers in a set of input values. |
| PERCENTTOTAL PERCENTTOTALGROUP |
Percent of Total | The Percent of Total functions return the percentage of each value in the selected series out of the total sum. |
| RANK | Rank | The Rank function calculates the rank for each value in a set of input values. |
| RANKVAL | Rank Value | The Rank Value function returns the value found at the specified position, either starting from the highest or the lowest value. |
| SUM | Totals | The Totals function calculates the sum total for an input value series. |
| TRENDFORE TRENDFOREUPPER TRENDFORELOWER |
Trend and Forecasting | The Trend and Forecasting functions apply a regression function to historical data in order to produce a line of best fit and optionally forecast future values, with optional upper and lower error boundaries. |
| WGHTMOVAVG | Weighted Moving Average | The Weighted Moving Average function computes the average of a set of input values over a specified number of time periods. In this function, a greater weight is given to more recent data. The function can be used to smooth a data series, which helps to reduce noise and make it easier to spot data trends. |
3. Statistical functions
Use statistical functions to perform statistical analysis on your data.
| Function | Name | Description |
|---|---|---|
| CORRELATION CORRELATIONGROUP |
Correlation | The Correlation functions calculate the correlation between two input data series. It measures the strength of the relationship between the two series. The resulting correlation value ranges between -1 and 1. A correlation value of zero implies that the two series are independent and have no relationship. A correlation value that is close to 1 implies that the two series are positively correlated. Similarly, a correlation value that is close to -1 implies that the two series are negatively correlated. If the correlation value is negative, this means that when the first input is large, the second input is small (and vice-versa). |
|
CORRMATRIX |
Correlation Matrix | The Correlation Matrix function calculates the strength of the relationships between all possible pairings of the specified data series as a value between -1 and 1. Zero implies that the two series are independent; a value close to 1 implies they are positively correlated, and a value close to -1 implies they are negatively correlated. A negative correlation means that when the first input is large, the second input is small (and vice-versa). |
|
FORECASTING |
Exponential Smoothing | The Exponential Smoothing functions apply an exponentially-decreasing weight to historical data in order to forecast future values based on emerging trends. |
|
HISTOGRAM HISTOGRAMRANGE |
Histogram | The Histogram functions assign numeric values to bins and returns the counts. |
| NORMALDIST NORMALDISTGROUP |
Normal Distribution | The Normal Distribution functions calculate the standard normal cumulative distribution for an input data series. Specifically, the function computes the probability that a series, which has a mean of 0 and a standard deviation of 1, contains a value less than the input value. |
| PERCENTILE | Percentile | The Percentile function returns a scalar value below which the specified percentage of input data values fall. |
| PERCENTRANK PERCENTRANKGROUP |
Percent Rank | The Percent Rank functions calculate the percent rank for each value in a set of input values. Specifically, the function computes the percentage of all input values that are less than a given input value. Percent Rank values range between 0 and 1. |
| STDEV | Sample Standard Deviation | The Sample Standard Deviation function measures volatility as the difference between values and their moving average. A larger difference implies a larger standard deviation value. |
| STDEVPOP | Population Standard Deviation | The Population Standard Deviation function measures volatility as the difference between values and their average. A larger difference implies a larger standard deviation value. |
| VARIANCE | Variance | The Variance function calculates the variance of a set of input values. It measures how spread out a set of values are from each other. Variance is defined as the average of the squared differences of each value from the mean of the input values. |
4. Subset functions
Subset functions let you return a subset of your input data based on various criteria.
| Function | Name | Description |
|---|---|---|
| BOTTOM | Minimum Subset | The Minimum Subset function returns a specified number of the smallest values in an input data series. For example, the function can be used to return the 3 smallest values. |
| FIRST | First Subset | The First Subset function returns a specified number of values that appear first in an input data series. For example, the function can be used to return the first 3 values. |
| LAST | Last Subset | The Last Subset function returns a specified number of values which appear last in an input data series. For example, the function can be used to return the last 3 values. |
| MIDDLE | Middle Subset | The Middle Subset function returns a specified number of the middle values in an input data series. For example, the function can be used to return the 3 middle values in a set of input values. |
| TOP | Maximum Subset | The Maximum Subset function returns a specified number of the largest values in an input data series. For example, the function can be used to return the 3 largest values. |
| ZIGZAG | Zig Zag Subset | The Zig Zag Subset function returns the input data values that show movement greater than the specified sensitivity percentage value. |
5. Financial functions
Financial functions are used in financial applications such as technical analysis. Generally, these functions accept the price and/or volume of a security (e.g., a stock) as input and generate a result set that may help you to confirm price trends, spot reversals, or determine when to buy or sell.
| Function | Name | Description |
|---|---|---|
| ACCDIST | Accumulation Distribution | The Accumulation Distribution function is a technical indicator that considers both stock price and volume. When the indicator increases, it implies an accumulation or buying of the related stock. When the indicator decreases, it implies distribution or selling of the related stock. Accumulation Distribution can be used to detect divergences between stock price movement and volume movement. Other applications include confirmation of price trends and providing advance warning of a possible reversal in stock price. |
| AROONOSC AROONOSCUP AROONOSCDOWN | Aroon Oscillator | The Aroon Oscillator function is calculated as the difference between the Aroon Up and Aroon Down indicators, with resulting values ranging from -100 to 100. An uptrend in stock price is indicated when the Aroon Oscillator value is positive. A downtrend is indicated when the oscillator value is negative. The further away the Aroon Oscillator value is from the zero line, the stronger the trend. Applications of the Aroon Oscillator include determining whether a stock is trending or not, and gauging the strength of the current trend. |
| AVGDIRECT AVGDIRECTPOS AVGDIRECTNEG | Average Directional Index | The Average Directional Index was developed by J. Welles Wilder. It combines two other indicators, the Positive Directional Indicator and the Negative Directional Indicator, which were also developed by Wilder. Applications include indicating the strength of the current trend in stock price (but not the trend direction). |
| AVGTRURANGE | Average True Range | The Average True Range function computes an average of the True Range, which represents the range in price of a stock or commodity over the course of one day, or from one day to the next. Average True Range is used to indicate price volatility. |
| CHKOSC | Chaikin Oscillator | The Chaikin Oscillator was developed by Marc Chaikin. It is calculated by subtracting the 10-day exponential moving average from the 3-day exponential moving average of the Accumulation Distribution line. Resulting values oscillate around the zero level. Applications of Chaikin Oscillator include confirmation of price trends and providing advance warning of price reversals. |
| CHKMONEYFLO | Chaikin Money Flow | The Chaikin Money Flow was developed by Marc Chaikin. It computes the sum of the Accumulation Distribution line values for a stock over a specified number of time periods, and divides this by the total stock volume over the same duration. When the CMF value is above zero, this indicates buying pressure. When the CMF value is below zero, this indicates selling pressure. Applications of Chaikin Money Flow include determining if a stock is under accumulation or distribution. For example, if the CMF value remains above zero for an extended period of time, this indicates accumulation. |
| COMMCHANNEL | Commodity Channel Index | The Commodity Channel Index was developed by Donald Lambert. It compares a stock price to its average over a specified number of time periods. Applications of CCI include identification of overbought or oversold conditions, and signaling when a price trend may be weakening. |
| FASTSTOC FASTSTOCTRG | Fast Stochastic | The Fast Stochastic function compares the close price of a stock against its price range (high-low) over a specified number of time periods. Applications of this function include the generation of buy and sell signals. |
| MOMENTUM | Momentum | Momentum is a trend-following indicator that measures the speed of price movements by comparing the current stock price against its value from a specified number of time periods in the past. A stock price has upward momentum when the indicator value is positive, and downward momentum when the indicator value is negative. Applications of Momentum include indication of possible trend reversals. |
| MONEYFLO | Money Flow Index | The Money Flow Index function computes a relationship between the typical stock price and stock volume over a specified number of time periods. Resulting values range between 0 and 100. MFI is similar to the Relative Strength Indicator except that it incorporates volume. Applications of Money Flow Index include identifying market tops and market bottoms, and indicating price reversals. |
| MOVAVGCD MOVAVGCDSIG MOVAVGCDHIST | Moving Average Convergence-Divergence | Moving Average Convergence-Divergence was developed by Gerald Appel. It computes the difference between a short-term and a long-term exponential moving average of the stock price. Applications of MACD include generation of buy and sell signals. Signals are generated when the MACD line crosses the zero line or its signal line. |
| NEGVOLUME | Negative Volume Index | The Negative Volume Index function calculates a cumulative value based on days where the trading volume has decreased from the previous day. On days where the volume remained the same or increased, the NVI remains unchanged. Applications of NVI include indication of market trends. For example, a bull market may be indicated when the NVI rises above its one-year moving average. |
| ONBALVOLUME | On Balance Volume | The On Balance Volume was developed by Joseph Granville. It calculates a cumulative volume that depends on stock price. When the closing stock price for the day is greater than the previous day's close, the volume for the day is added to the cumulative result. If the stock price decreases, volume is subtracted from the result. Applications of On Balance Volume include confirmation of price movements. |
| ONBALVOLWGHT | On Balance Weighted Volume | The On Balance Weighted Volume function calculates a cumulative volume that depends on stock price. The daily volume is weighted by the difference in stock price between the day's close and the previous day's close before it is added to the cumulative result. Applications of On Balance Weighted Volume include confirmation of price movements and providing indication of possible trend reversals. |
| PARABOLSAR | Parabolic SAR | Parabolic SAR (stop-and-reverse) was developed by J. Welles Wilder, Jr. It is a lagging technical indicator that can help you to determine when a stock is set to have a change in trend. Applications of Parabolic SAR include generation of buy-sell signals and, in particular, determining when to exit from trades. |
| POSVOLUME | Positive Volume Index | The Positive Volume Index function calculates a cumulative value based on days where the trading volume has increased from the previous day. On days where the volume remained the same or decreased, the PVI remains unchanged. Applications of PVI include indication of market trends. For example, a bear market may be indicated when the PVI drops below its one-year moving average. |
| RATECHANGE | Rate of Change | The Rate of Change function is a variation on the Momentum function. ROC calculates the percentage difference between the day's closing stock price and the price from a specified number of time periods in the past. Applications of ROC include indication of price reversals. For example, if the price is trending upward while the ROC is moving downward, this may indicate a large drop in price is forthcoming. |
| RELSTRENGTH | Relative Strength Indicator | The Relative Strength Indicator was developed by J. Welles Wilder. It compares the average of up closes against the average of down closes over a specified number of time periods. Resulting values range from 0 to 100. Applications of RSI include identifying overbought or oversold situations. For example, if the RSI line stalls at a level above 70, this may indicate the stock is overbought. Or, if the RSI line drops to a level below 30, this may indicate the stock is oversold and becoming undervalued. |
| SLOSTOC SLOSTOCTRG | Slow Stochastic | The Slow Stochastic function compares the close price of a stock against its price range (high-low) over a specified number of time periods. It is derived by applying a 3-period, simple moving average to the Fast Stochastic line. Applications of Slow Stochastic include the generation of buy and sell signals. |
| STOCOSC STOCOSCTRG | Stochastic Oscillator | The Stochastic Oscillator function compares the close price of a stock against its price range (high-low) over a specified number of time periods. Applications of Stochastic Oscillator include the generation of buy and sell signals. |
| TRENDCONF | Trend Confirmation Indicator | The Trend Confirmation Indicator function computes the ratio between a short-term moving average and a longer-term moving average of the stock price. Resulting values oscillate around the 100 level. Applications of TCI include generation and confirmation of buy and sell signals. For example, when the TCI advances above 100, this may be interpreted as a buy-signal. |
| ULTIMOSC | Ultimate Oscillator | The Ultimate Oscillator was developed by Larry Williams. It computes a weighted total of three oscillators, each of which is calculated using a different time period. Resulting values range from 0 to 100. Applications of Ultimate Oscillator include buy and sell signals, and providing indication of overbought or oversold conditions. |
| VOLUMEOSC VOLUMEOSCSIG VOLUMEOSCHIST | Volume Oscillator | The Volume Oscillator function computes the relationship between a short-term moving average and a long-term moving average of the stock volume. Resulting values oscillate around the zero line. Applications of Volume Oscillator include indication of volume surges, which may be due to panic selling or greedy buying. |
| WILLIAMSR | Williams %R | Williams %R (or Percent Range) was developed by Larry Williams. It is similar to the Stochastic Oscillator function in that it relates the close price of a stock to its price range (high-low) over a specified number of time periods. Applications of Williams %R include indication of price reversals and determining overbought or oversold conditions. |
6. Math functions
In addition to calling the formula functions listed above, you can use arithmetic operators and math functions like the following.
While formula functions listed above work with the entire series of input values you pass to them, arithmetic operators and math functions operate on each row's values separately. For example, $Measure 1$ / $Measure 2$ divides each row's Measure 1 value by its Measure 2 value, while SUM($Measure 1$) takes an entire set of measure values and outputs a single sum. The math function call Math.Round($Measure 1$) takes each row's measure value and rounds it to the nearest whole number.
| Operator | Description |
|---|---|
| + | Add |
| - | Subtract |
| * | Multiply |
| / | Divide |
| () | Specify order of operations |
| % | Calculate the remainder after dividing |
The following are some of the .NET Math fields and methods you can access (use the links for the complete list and details):
| Field | Description |
|---|---|
| Math.E | Represents the natural logarithmic base, specified by the constant, e. |
| Math.PI | Represents the ratio of the circumference of a circle to its diameter, specified by the constant, π. |
| Method | Description |
|---|---|
| Math.Abs | Returns the absolute value of a decimal or a number. |
| Math.Acos | Returns the angle whose cosine is the specified number. |
| Math.Asin | Returns the angle whose sine is the specified number. |
| Math.Atan2 | Returns the angle whose tangent is the quotient of two specified numbers. |
| Math.Ceiling | Returns the smallest integral value that is greater than or equal to the specified number. |
| Math.Cos | Returns the cosine of the specified angle. |
| Math.Cosh | Returns the hyperbolic cosine of the specified angle. |
| Math.Exp | Returns e raised to the specified power. |
| Math.Floor | Returns the largest integer less than or equal to the specified number. |
| Math.Log | Returns the natural logarithm of the specified number. |
| Math.Log10 | Returns the logarithm of the specified number. |
| Math.Max | Returns the larger of two numbers. |
| Math.Min | Returns the smaller of two numbers. |
| Math.Pow | Returns the specified number raised to the specified power. |
| Math.Round | Rounds a decimal or double-precision value to the nearest integer or to the specified number of decimal places. |
| Math.Sign | Returns an integer that indicates the sign of a number. |
| Math.Sin | Returns the sine of the specified angle. |
| Math.Sinh | Returns the hyperbolic sine of the specified angle. |
| Math.Sqrt | Returns the square root of the specified number. |
| Math.Tan |
Returns the tangent of the specified angle. |
| Math.Tanh | Returns the hyperbolic tangent of the specified angle. |
| Math.Truncate | Returns the integral part of the specified number. |
7. DundasScript
Formulas run as DundasScript, so you have access to additional operators such as if/else statements, and other functions and types. It can be easier to write more complex formula scripts in advanced mode, described in detail in the article Adding formulas.
For details about script syntax and accessible classes and their methods, see Writing data scripts with DundasScript.