NumXL Pro

NumXL is a suite of time series Excel add-ins. It transforms your Microsoft® Excel® application into a first-class time series and econometric tool, offering the kind of statistical accuracy provided by far more expensive statistical packages. NumXL integrates seamlessly with Excel, adding scores of econometric functions, a rich set of shortcuts, and an intuitive user interface to guide you through the entire process.

Whether you have a simple homework problem or a large-scale business project, NumXL simplifies your efforts. It helps you reach your goal in the quickest, most thorough way possible.

NumXL also keeps your data and results connected in Excel, allowing you to trace your calculations, add new data points, update an existing analysis, and share your results with ease.

NumXL allows you to hit the ground running, it does it not require any programming or scripting knowledge and is designed for ease of use: You will not have to move your data between any external programs. You can also perform any ad-hoc analysis, as all of the NumXL functions are accessible in your spreadsheet, and inside the VBA environment, should you choose to write a macro(s).

Tutorials

Getting Started

User's Guide

Reference Manual

Tips & Tricks

Case Studies

What can NumXL do for me?

Summary Statistics

Statistical testing

Data Fitting

Transform

Statistical Sampling

Smoothing

ARMA/ARIMA

ARCH/GARCH

Date & Calendar

Forecasting Performance

Factors Analysis

Spectral Analysis

Portfolio Analysis

Power Tools

Utilities

Summary Statistics

Descriptive statistics is the discipline of quantitatively describing the main features of a collection of data; The main aim is to summarize a data set, rather than use the data to learn about the population that the data are thought to represent.

Some measures that are commonly used to describe a data set:

Location / central tendency: mean, median, mode, etc.
Variability or statistical dispersion
The Shape of the distribution, either via indices such as skewness, kurtosis, quantile, or via tabular and/or graphical format, such as Histogram, EDF, and Kernel density estimate (KDE).
Statistical dependence: serial/autocorrelation and cross-correlation.

Location or Central tendency

Examples: mean, median, mode, etc.

Variability or Dispersion

Featured Functions:

Function	Description
EWMA	Exponential-weighted moving volatility
GINI	Gini Coefficient
Quantile	Sample Quantile
IQR	Inter-quartile Range
MD	Mean absolute difference
MAD	Median absolute difference
RMD	Relative mean difference
LRVar	long-run variance (Bartlett kernel)

Shape of Distribution

The variable Distribution is depicted via a tabular or a graphical representation.

Featured Functions:

Function	Description
EDF	Empirical Distribution Function
KDE	Kernel Density Function
NxHistogram	Frequency distribution (Histogram)

Statistical Dependency

For one data set, serial or auto-correlation functions (e.g. ACF, PACF) are commonly used tools for not only checking randomness, but also in model identification stage for ARMA-type of models.
Featured Functions
Function Description
ACF Autocorrelation function
PACF Partial autocorrelation function
Hurst Hurst Exponent
For two or more variables, cross-correlations are used to measure how those variables are related to one another.
Featured Functions
Function Description
XCF Cross-correlation function
EWXCF Exponentially-weighted cross correlation

Function	Description
ACF	Autocorrelation function
PACF	Partial autocorrelation function
Hurst	Hurst Exponent

Function	Description
XCF	Cross-correlation function
EWXCF	Exponentially-weighted cross correlation

Featured Functions:

Function	Description
TEST_MEAN	One-sample population mean Test

Variability / Statistical dispersion

These are also a one-sample test that compares the variance (or another statistical dispersion measure) of one sample to a known standard (known from theory or are calculated from the population).

Featured Functions:

Function	Description
TEST_STDEV	Standard Deviation Test
TEST_SKEW	Skewness Test
TEST_XKURT	Excess Kurtosis Test

Shape of Distribution

The statistical tests are one-sample tests that examine an assumption about the distribution of the random variable (e.g. Normality).

Featured Functions:

Function	Description
NormalityTest	Testing for Normal/Gaussian Distribution

Statistical Dependence

In this category, we group the functionality based on the number of variables they apply to:

One variable – Serial (aka. Auto) correlation
Featured Functions:
Function Description
ACFTest Autocorrelation Test
WNTest White-Noise Test
ARCHTest Test of ARCH Effect
ADFTest Augmented Dickey-Fuller Stationary Test
Two or more variables – cross-correlation
Featured Functions:
Function Description
XCFTest Cross-Correlation Test
JohansenTest Johansen Cointegration Test
Chow Test Regression Stability Test
CollinearityTest Test for the Presence of Multi-colinearity

Function	Description
ACFTest	Autocorrelation Test
WNTest	White-Noise Test
ARCHTest	Test of ARCH Effect
ADFTest	Augmented Dickey-Fuller Stationary Test

Function	Description
XCFTest	Cross-Correlation Test
JohansenTest	Johansen Cointegration Test
Chow Test	Regression Stability Test
CollinearityTest	Test for the Presence of Multi-colinearity

Featured Functions:

Function	Description
NxINTRPL	Linear, polynomial, cubic spline Interpolation and Extrapolation
NxINTRPL2D	2-Dimension-interpolation-extrapolation

Smoothing

Find a curve-function that is an approximate fit to the data points: we permit the actual data points to be near, but not necessarily on the curve; while minimizing the overall error.

Featured Functions:

Function	Description
NxRegress	Simple Linear Regression Function
NxKNN	K Nearest neighbors (K-NN ) Regression
NxKREG	Kernel Regression
NxLOCREG	Local or moving polynomial regression

Featured Functions:

Function	Description
DIFF	Time Series Difference Operator
LAG	Time Series Lag or Backshift Operator
INTG	Time Series Integration Operator

Variable values distribution

Featured Functions:

Function	Description
BoxCox	Box-Cox Transform
CLOGLOG	Complementary Log-Log Transform
LOGIT	Logit Transform
PROBIT	Probit Transform
DETREND	Remove Deterministic Trend From Time Series

Missing Values

Featured Functions:

Function	Description
SUBNA	Missing values interpolation

Sampling

Featured Functions:

Function	Description
RESAMPLE	Time series resampling

Featured Functions:

Function	Description
NxSort	Sort input array
NxReverse	Reverse Chronicle Order of Time Series
NxShuffle	Shuffle the order of elements in data set

Sub-sampling

Featured Functions:

Function	Description
NxSubset	Time Series Subset
NxChoose	Random sampling

Featured Functions:

Function	Description
NxEMA	exponentially-weighted moving (rolling/running) average
NxMA	Moving (rolling) average using prior data points
WMA	Weighted-Moving Average
NxCMA	Centered Moving-average Filter
NxSMA	Seasonal Moving-average Filter

Exponential Smoothing

Built-in optimizer for smoothing parameter, and initial value.

Featured Functions:

Function	Description
SESMTH	(Brown’s) Simple Exponential Smoothing
LESMTH	(Brown’s) Linear Exponential Smoothing
DESMTH	(Holt’s) Double Exponential Smoothing
TESMTH	(Holt-Winters’s) Triple Exponential Smoothing
GESMTH	Generalized Exponential Smoothing Function

Trend

Trend analysis is very often used (or abused) in the industry to make a quick (and dirty) forecast. Executives might use the trending tool as a sanity check when he/she examines results from more advanced models. NumXL supports several forms of trend: linear, polynomial, power, exponential and logarithmic.

Featured Functions:

Function	Description
NxTrend	Deterministic trend in a time series

Featured Functions:

Function	Description
ARMA	Defining an ARMA model
ARMA_CHECK	Check parameters’ values for model stability
ARMA_PARAM	Values of the Model’s Parameters
ARMA_GOF	Goodness-of-fit of an ARMA Model
ARMA_FIT	ARMA Model Fitted Values
ARMA_FORE	Forecasting for ARMA Model
ARMA_SIM	Simulated values of an ARMA Model

ARIMA Model

By definition, auto-regressive integrated moving average (ARIMA) is a stationary stochastic process made up of sums of auto-regressive and moving average components:

$(1-\phi_1 L – \phi_2 L^2 -\cdots – \phi_p L^p)(1-L)^d x_t – \phi_o= \\(1+\theta_1 L+\theta_2 L^2 + \cdots + \theta_q L^q)a_t$
$y_t = (1-L)^d x_t$

Where

$x_t$ is the original non-stationary output at time t.
$y_t$ is the observed differenced (stationary) output at time t.
$d$ is the integration order of the time series.
$a_t$ is the innovation, shock, or error term at time t.
$p$ is the order of the last lagged variables.
$q$ is the order of the last lagged innovation or shock.
$a_t$ time-series observations are independent and identically distributed
(i.e. i.i.d) and follow a Gaussian distribution (i.e. $\Phi(0,\sigma^2)$)

Featured Functions:

Function	Description
ARIMA	Defining an ARIMA model
ARIMA_CHECK	Check parameters’ values for model stability
ARIMA_PARAM	Values of the Model’s Parameters
ARIMA_GOF	Goodness-of-fit of an ARIMA Model
ARIMA_FIT	ARIMA Model Fitted Values
ARIMA_FORE	Forecasting for ARIMA Model
ARIMA_SIM	Simulated values of an ARIMA Model

SARIMA Model

The SARIMA model is an extension of the ARIMA model, often used when we suspect a model may have a seasonal effect.

By definition, the seasonal auto-regressive integrated moving average process ( SARIMA(p,d,q)(P, D, Q)s ) is a multiplicative of two ARMA processes of the differenced time series.

$(1-\sum_{i=1}^p {\phi_i L^i})(1-\sum_{j=1}^P {\Phi_j L^{j \times s}})(1-L)^d
(1-L^s)^D x_t = \\ (1+\sum_{i=1}^q {\theta_i L^i})(1+\sum_{j=1}^Q {\Theta_j L^{j
\times s}}) a_t$
$y_t = (1-L)^d (1-L^s)^D $

Where:

$x_t$ is the original non-stationary output at time t.
$y_y$ is the differenced (stationary) output at time t.
$d$ is the non-seasonal integration order of the time series.
$p$ is the order of the non-seasonal AR component.
$P$ is the order of the seasonal AR component.
$q$ is the order of the non-seasonal MA component.
$Q$ is the order of the seasonal MA component.
$s$ is the seasonal length.
$D$ is the seasonal integration order of the time series.
$a_t$ is the innovation, shock, or the error term at time t.
$\{a_t\}$ time-series observations are independent and identically distributed
(i.e. i.i.d) and follow a Gaussian distribution (i.e. $\Phi(0,\sigma^2)$)

Featured Functions:

Function	Description
SARIMA	Defining a SARIMA model
SARIMA_CHECK	Check parameters’ values for model stability
SARIMA_PARAM	Values of the Model’s Parameters
SARIMA_GOF	Goodness-of-fit of an ARIMA Model
SARIMA_FIT	ARIMA Model Fitted Values
SARIMA_FORE	Forecasting for SARIMA Model
SARIMA_SIM	Simulated values of a SARIMA Model

AirLine Model

The airline model is a special, but often used, case of multiplicative ARIMA model. For a given seasonality length (s), the airline model is defined by four(4) parameters: $\mu$, $\sigma$, $\theta$ and $\Theta$).

$ (1-L^s)(1-L)Y_t = \mu + (1-\theta L)(1-\Theta L^s)a_t$ OR
$ Z_t = (1-L^s)(1-L)Y_t = \mu + (1-\theta L)(1-\Theta L^s)a_t $ OR
$Z_t = \mu -\theta \times a_{t-1}-\Theta \times a_{t-s} +\theta\times\Theta \times a_{t-s-1}+ a_t $

Where:

$s$ is the length of seasonality.
$\mu$ is the model mean
$\theta$ is the coefficient of first lagged innovation
$\Theta$ is the coefficient of s-lagged innovation.
$\left [a_t\right ] $ is the innovations time series.

Featured Functions:

Function	Description
AIRLINE_CHECK	Check parameters’ values for model stability
AIRLINE_PARAM	Values of the Model’s Parameters
AIRLINE_GOF	Goodness-of-fit of an AIRLINE Model
AIRLINE_FIT	ARIMA Model Fitted Values
AIRLINE_FORE	Forecasting for AIRLINE Model
AIRLINE_SIM	Simulated values of an AIRLINE Model

ARMAX Model

In principle, an ARMAX model is a linear regression model that uses an ARMA-type process (i.e. $w_t$) to model residuals:

$y_t = \alpha_o + \beta_1 x_{1,t} + \beta_2 x_{2,t} + \cdots + \beta_b x_{b,t} + w_t$
$(1-\phi_1 L – \phi_2L^2-\cdots-\phi_pL^p)(y_t-\alpha_o -\beta_1 x_{1,t} – \beta_2 x_{2,t} – \cdots – \beta_b x_{b,t})= (1+ \theta_1 L + \theta_2 L^2 + \cdots + \theta_q L^q)a_t$
$(1-\phi_1 L – \phi_2 L^2 – \cdots – \phi_p L^p)w_t= (1+\theta_1 L+ \theta_2 L^2 + \cdots + \theta_q L^q ) a_t$
$a_t \sim \textrm{i.i.d} \sim \Phi (0,\sigma^2)$

$L$ is the lag (aka back-shift) operator.
$y_t$ is the observed output at time t.
$x_{k,t}$ is the k-th exogenous input variable at time t.
$\beta_k$ is the coefficient value for the k-th exogenous (explanatory)
input variable.
$b$ is the number of exogenous input variables.
$w_t$ is the auto-correlated regression residuals.
$p$ is the order of the last lagged variables.
$q$ is the order of the last lagged innovation or shock.
$a_t$ is the innovation, shock or error term at time t.
$a_t$ time series observations are independent and identically distributed
(i.e. i.i.d) and follow a Gaussian distribution (i.e. $\Phi(0,\sigma^2)$)

Featured Functions:

Function	Description
ARMAX	Defining an ARMAX model
ARMAX_CHECK	Check parameters’ values for model stability
ARMAX_PARAM	Values of the Model’s Parameters
ARMAX_GOF	Goodness of fit of an ARMAX Model
ARMAX_FIT	ARMAX Model Fitted Values
ARMAX_FORE	Forecasting for ARMAX Model
ARMAX_SIM	Simulated values of an ARMAX Model

SARIMAX Model

In principle, an SARIMAX model is a linear regression model that uses a SARIMA-type process (i.e. ) This model is useful in cases we suspect that residuals may exhibit a seasonal trend or pattern.

$w_t = y_t – \beta_1 x_{1,t}-\beta_2 x_{2,t} – \cdots – \beta_b x_{b,t}$
$(1-\sum_{i=1}^p {\phi_i L^i})(1-\sum_{j=1}^P {\Phi_j L^{j \times s}})(1-L)^d (1-L^s)^D w_t -\eta = \\ (1+\sum_{i=1}^q {\theta_i L^i})(1+\sum_{j=1}^Q {\Theta_j L^{j \times s}}) a_t$ $a_t \sim \textrm{i.i.d} \sim \Phi(0,\sigma^2)$

Where:

$L$ is the lag (aka back-shift) operator.
$y_t$ is the observed output at time t.
$x_{k,t}$ is the k-th exogenous input variable at time t.
$\beta_k$ is the coefficient value for the k-th exogenous (explanatory)
input variable.
$b$ is the number of exogenous input variables.
$w_t$ is the auto-correlated regression residuals.
$p$ is the order of the non-seasonal AR component.
$P$ is the order of the seasonal AR component.
$q$ is the order of the non-seasonal MA component.
$Q$ is the order of the seasonal MA component.
$s$ is the seasonal length.
$D$ is the seasonal integration order of the time series.
$\eta$ is a constant in the SARIMA model
$a_t$ is the innovation, shock, or error term at time t.
$\{a_t\}$ time-series observations are independent and identically distributed
(i.e. i.i.d) and follow a Gaussian distribution (i.e. $\Phi(0,\sigma^2)$)

Featured Functions

Function	Description
SARIMAX	Defining a SARIMAX model
SARIMAX_PARAM	Values of the Model’s Parameters
SARIMAX_CHECK	Check parameters’ values for model stability
SARIMAX_GOF	Goodness-of-fit of a SARIMAX Model
SARIMAX_FIT	SARIMAX In-Sample Fitted Values
SARIMAX_FORE	Forecasting for SARIMAX Model
SARIMAX_SIM	Simulated values of a SARIMAX Model

X12-ARIMA Model

The X-12-ARIMA software comes with extensive time series modeling and model selection capabilities for linear regression models with ARIMA errors (regARIMA models). The ARIMA models, as discussed by Box and Jenkins (1976), are frequently used for seasonal time series. A general multiplicative seasonal ARIMA model for a time series z_t can be written:

$\phi(L)\Phi(L^s)(1-L)^d (1-L^s)^D\times z_t = \theta(L)\Theta(L^s)a_t$

Where:

$L$ is the Lag or the Backshift operator
$s$ is the seasonal period
$(\phi(L)=\phi_o+\phi_1 L+\phi_2 L^2 +\cdots +\phi_p L^p)$ is the nonseasonal auto-regressive (AR) model component
$(\Phi(L)=\Phi_o+\Phi_1 L+\Phi_2 L^2 +\cdots +\Phi_P L^P)$ is the seasonal auto-regressive (AR) model component
$(\theta(L)=\theta_o+\theta_1 L+\theta_2 L^2 +\cdots +\theta_q L^q)$ is the nonseasonal moving average (MA) model component
$(\Theta(L)=\Theta_o+\Theta_1 L+\Theta_2 L^2 +\cdots +\Theta_Q L^Q)$ is the seasonal moving average (MA) model component
$(1-L)^d$ is the non-seasonal differencing operator of order d
$(1-L^s)^D$ is the seasonal differencing operator of order D and seasonal period (s)
$\{a_t\}\sim \textrm{i.i.d}\sim N(0,\sigma^2)$

Featured Functions:

Function	Description
X12ARIMA	Defining an X12-ARIMA Model
X12APROP	X11 Seasonal Adjustment and X12-ARIMA Model properties
X12ACOMP	X12-ARIMA Output Time Series
X12AFORE	Forecasting for X12-ARIMA Model

X-13ARIMA-SEATS Model

The X-13ARIMA-SEATS software comes with extensive time series modeling and model selection capabilities for linear regression models with ARIMA errors (regARIMA models).

Furthermore, the X-13ARIMA-SEATS includes the capability to generate ARIMA model-based seasonal adjustment using a version of the SEATS software originally developed by Victor Gómez and Agustín Maravall at the Bank of Spain, as well as nonparametric adjustments from the X-11 procedure;

Featured Functions:

Function	Description
X13AAS	Defining an X-13ARIMA-SEATS Model
X13ASCOMP	X-13ARIMA-SEATS output components time series
X13ASFORE	Forecasting for X13ARIMA-SEATS Model

Featured Functions:

Function	Description
GARCH	Defining a GARCH model
GARCH_GUESS	Initial values for Model’s Parameters
GARCH_CHECK	Check parameters’ values for model stability
GARCH_LLF	Goodness-of-fit of a SARIMAX Model
GARCH_RESID	GARCH In-Sample (standardized) residuals
GARCH_FORE	Forecasting for GARCH Model
GARCH_SIM	Simulated values of a GARCH Model
GARCH_VL	Long-run Volatility of the GARCH Model

E-GARCH Model

The exponential general autoregressive conditional heteroskedastic (EGARCH) is another form of the GARCH model. E-GARCH model was proposed by Nelson (1991) to overcome the weakness in GARCH handling of financial time series. In particular, to allow for asymmetric effects between positive and negative asset returns:

$x_t = \mu + a_t$
$\ln\sigma_t^2 = \alpha_o + \sum_{i=1}^p {\alpha_i \left(\left|\epsilon_{t-i}\right|+\gamma_i\epsilon_{t-i}\right )}+\sum_{j=1}^q{\beta_j \ln\sigma_{t-j}^2}$
$ a_t = \sigma_t \times \epsilon_t$
$ \epsilon_t \sim P_{\nu}(0,1)$

Where

$x_t$ is the time series value at time t.
$\mu$ is the mean of the GARCH model.
$a_t$ is the model’s residual at time t.
$\sigma_t$ is the conditional standard deviation (i.e. volatility) at time t.
$p$ is the order of the ARCH component model.
$\alpha_o,\alpha_1,\alpha_2,…,\alpha_p$ are the parameters of the ARCH component model.
$q$ is the order of the GARCH component model.
$\beta_1,\beta_2,…,\beta_q$ are the parameters of the GARCH component model.
$\left[\epsilon_t\right]$ are the standardized residuals:
$ \left[\epsilon_t \right] \sim i.i.d$
$ E\left[\epsilon_t\right]=0$
$ \mathit{VAR}\left[\epsilon_t\right]=1$
$P_{\nu}$ is the probability distribution function for $\epsilon_t$.
Currently, the following distributions are supported:
1. Normal Distribution
  $P_{\nu} = N(0,1) $
2. Student’s t-Distribution
  $P_{\nu} = t_{\nu}(0,1) $
  $\nu \gt 4 $
3. Generalized Error Distribution (GED)
  $P_{\nu} = \mathit{GED}_{\nu}(0,1) $
  $\nu \gt 1 $

Featured Functions:

Function	Description
EGARCH	Defining an EGARCH model
EGARCH_GUESS	Initial values for Model’s Parameters
EGARCH_CHECK	Check parameters’ values for model stability
EGARCH_LLF	Goodness of fit of a EGARCH Model
EGARCH_RESID	EGARCH In-Sample (standardized) residuals
EGARCH_FORE	Forecasting for EGARCH Model
EGARCH_SIM	Simulated values of a EGARCH Model
EGARCH_VL	Long-run Volatility of the EGARCH Model

GARCH-M Model

In finance, the return of a security may depend on its volatility (risk). To model such phenomena, the GARCH-in-mean (GARCH-M) model adds a heteroskedasticity term into the mean equation. It has the specification:

The GARCH-M(p,q) model is written as:
$x_t = \mu + \lambda \sigma_t + a_t$
$\sigma_t^2 = \alpha_o + \sum_{i=1}^p {\alpha_i a_{t- i}^2}+\sum_{j=1}^q{\beta_j \sigma_{t-j}^2}$
$ a_t = \sigma_t \times \epsilon_t$
$ \epsilon_t \sim P_{\nu}(0,1)$

Where:

$x_t$ is the time series value at time t.
$\mu$ is the mean of the GARCH model.
$\lambda$ is the volatility coefficient for the mean.
$a_t$ is the model’s residual at time t.
$\sigma_t$ is the conditional standard deviation (i.e. volatility) at time t.
$p$ is the order of the ARCH component model.
$\alpha_o,\alpha_1,\alpha_2,…,\alpha_p$ are the parameters of the ARCH component model.
$q$ is the order of the GARCH component model.
$\beta_1,\beta_2,…,\beta_q$ are the parameters of the GARCH component model.
$\left[\epsilon_t\right]$ are the standardized residuals:
$ \left[\epsilon_t \right]\sim i.i.d$
$ E\left[\epsilon_t\right]=0$
$ \mathit{VAR}\left[\epsilon_t\right]=1$
$P_{\nu}$ is the probability distribution function for $\epsilon_t$.
Currently, the following distributions are supported:
1. Normal Distribution
  $P_{\nu} = N(0,1) $
2. Student’s t-Distribution
  $P_{\nu} = t_{\nu}(0,1) $
  $\nu \gt 4 $
3. Generalized Error Distribution (GED)
  $P_{\nu} = \mathit{GED}_{\nu}(0,1) $
  $\nu \gt 1 $

Featured Functions:

Function	Description
GARCHM	Defining a GARCH-M model
GARCHM_GUESS	Initial values for Model’s Parameters
GARCHM_CHECK	Check parameters’ values for model stability
GARCHM_LLF	Goodness-of-fit of a GARCH-M Model
GARCHM_RESID	GARCH-M In-Sample (standardized) residuals
GARCHM_FORE	Forecasting for GARCH-M Model
GARCHM_SIM	Simulated values of a GARCH-M Model
GARCHM_VL	Long-run Volatility of the GARCH-M Model

Featured Functions:

Function	Description
NxAdjust	Move to the nearest workday
NxEDATE	Advance a date by a given period
NxNetWorkdays	Number of working days in a given date range
NxNWKDY	Date of the N-th Weekday in a Month
NxWKDYOrder	Order of the weekday for a given date
NxWorkday	Advance a given date N-Working Days

Holidays

A holiday is a day designated as having special significance for individuals, governments, or religious groups. Typically, a holiday does not necessarily exclude doing normal work but for our purposes, NumXL assumes all supported holidays (e.g. National Holidays) exclude normal work.

Observed holidays and actual dates for particular holidays (e.g. Easter) may vary between countries, so in NumXL a holiday code is prefixed by the country ISO code (e.g. USA, CAN, GBR, etc.).

Featured Functions:

Function	Description
NxIsHoliday	Examine a given date if it falls on any holiday.
NxFindHLDY	Lookup the holiday name that falls on a given date
NxHLDYDate	Calculate the occurrence date of a given holiday in a given year.
NxHLDYDates	Lookup and return all holiday dates in a given date range.

Weekend

As of Excel 2007, Microsoft supports different weekend occurrences in the international version of the date functions (e.g. WORKDAY.INTL). The weekend conventions are defined by either a number or a 7-characters long string (code).

Featured Functions:

Function	Description
NxWKNDStr	Weekend Code
NxWKNDUR	Weekend Duration
NxWKNDate	Next/Last Weekend date
NxWKNDNo	Weekend Convention Number
NxIsWeekend	Examine a given date whether it falls on a weekend.

Calendar

For financial time analysis, a calendar is a definition of a list of observed holidays and a weekend days convention.

Calendars are used extensively in date calculation functions. NumXL comes in with many predefined calendars (e.g. US Federal Government Holidays, US Bank holidays, EU Bank calendar, etc, ), but the user can also define a custom calendar by specifying supported holidays and weekend convention.

Featured Functions:

Function	Description
NxCalendars	Returns a list of supported calendars with a given prefix (optional)
NxCALHolidays	Returns a list of supported holidays in a given calendar
NxCALWKND	Return the weekend convention for a given calendar.

Featured Functions:

Function	Description
SSE	Sum of squared errors
MSE	Mean of squared errors
GMSE	Geometric mean of squared errors
SAE	Sum of absolute errors
MAE	Mean of absolute erros
RMSE	Root mean squared errors
GRMSE	Geometric root mean squared errors

Percentage (scale independent) measures

Featured Functions:

Function	Description
MAPE	Mean absolute percentage error
MdAPE	Median absolute percentage error
MAAPE	Mean arctangent absolute percentage error

Relative measures

In this category, we use relative forecast error to some benchmark forecast such as the latest available value (Naïve 1 forecast), or the latest available value after seasonality has to be taken into account (Naïve 2 forecast).

Featured Functions:

Function	Description
MRAE	Mean relative absolute error
MdRAE	Median relative absolute error
GMRAE	Geometric mean relative absolute error
MASE	Mean absolute scaled error
PB	Percentage better
MDA	Mean directional accuracy

Featured Functions:

Function	Description
SLR_PARAM	Coefficients’ values for the SLR model
SLR_GOF	Goodness-of-fit (R^2, LLF, AIC) for SLR model
SLR_FITTED	Fitted values (mean and residuals) of the SLR model
SLR_FORE	Forecast (mean and error) of the SLR model
SLR_SNOVA	Analysis of Variance of the SLR model

Multiple Linear Regression (MLR)

Given a data set $\{y_i,\, x_{i1}, \ldots, x_{ip}\}_{i=1}^n$ of n statistical units, a linear regression model assumes that the relationship between the dependent variable $y_i$ and the p-vector of regressors $x_i$ is linear. This relationship is modeled through a disturbance term or error variable $\epsilon_i$ — an unobserved random variable that adds noise to the linear relationship between the dependent variable and regressors.

The MLR is described as follows:
$$ y_i = \beta_1 x_{i1} + \cdots + \beta_p x_{ip} + \varepsilon_i = \mathbf{x}^{\rm
T}_i\boldsymbol\beta + \varepsilon_i, \qquad i = 1, \ldots, n, $$

Where

$\mathbf{x}^{\rm T}_i$ is the transpose matrix

Featured Functions:

Function	Description
MLR_PARAM	Coefficients’ values of the MLR model
MLR_GOF	Goodness of fit of MLR model
MLR_FITTED	Fitted (mean and residuals) values of MLR model
MLR_FORE	Forecasting (mean, error, C.I.) for MLR model
MLR_ANOVA	Analysis of Variance for MLR model
MLR_PRTest	Partial F-Test for regression variables
MLR_STEPWISE	Regression variables selection method (stepwise)

Principal Component Analysis (PCA)

Principal component analysis (PCA) is a mathematical procedure that uses an orthogonal linear transformation to convert a set of observations of possibly correlated variables into a set of values of linearly uncorrelated variables called principal components.

Let’s define a matrix $\mathbf{X}$, where each column corresponds to one
variable and each row corresponds to a different repetition (or measurement) of the experiment:
$$\mathbf{X} = \begin{pmatrix} \mathbf{x}^{\rm T}_1 \\ \mathbf{x}^{\rm T}_2
\\ \vdots \\ \mathbf{x}^{\rm T}_n \end{pmatrix} = \begin{pmatrix} x_{11}
& \cdots & x_{1p} \\ x_{21} & \cdots & x_{2p} \\ \vdots &
\ddots & \vdots \\ x_{n1} & \cdots & x_{np} \end{pmatrix} $$

Furthermore, each column (variable) has a zero empirical mean (the empirical (sample) mean of the distribution has been subtracted from the data set).

The PCA transformation that preserves dimensionality (that is, gives the same number of principal components as original variables) Y is then given by:
$$ \mathbf{Y}^{\rm T} = \mathbf{X}^{\rm T}\mathbf{W} $$

Using singular value decomposition (SVD) for the $ \mathbf{X}^{\rm T}$, we
can express the PCA transformation as
$$ \mathbf{Y}^{\rm T} = (\mathbf{W}\mathbf{\Sigma}\mathbf{V}^{\rm T})^{\rm T}\mathbf{W}$$

Where:

$\mathbf{W}$ is the matrix of eigenvectors of the covariance matrix $\mathbf{X}
\mathbf{X}^{\rm T}$
$\mathbf{V}$ is the matrix of eigenvectors of the matrix $\mathbf{X}^{\rm
T} \mathbf{X}$
$\mathbf{\Sigma}$ is a rectangle matrix with nonnegative real numbers
on the diagonal

The PCA transformation $\mathbf{Y}$ is given by:
$ \mathbf{Y}^{\rm T} = \mathbf{V}\mathbf{\Sigma}^{\rm T} $

Featured Functions:

Function	Description
PCA_COMP	Principal component (PC) values
PCA_VAR	Calculate the variable values using a subset of the principal components.

Principal Component Regression (PCR)

Principal component regression (PCR) is a two-stage procedure; first, it reduces the predictor variables using principal component analysis, then uses the reduced variables in an OLS regression fit.

PCR is often used when the number of predictor variables is large or when strong correlations exist among the predictor variables.

Featured Functions:

Function	Description
PCR_PARAM	Calculate the coefficients’ values (end std. errors) for the PCR model
PCR_GOF	Calculate the goodness-of-fit (e.g., R^2, LLF, AIC) for the PCR model.
PCR_ANOVA	Conduct the Analysis of Variance (ANOVA) for the PCR model.
PCR_FITTED	Calculate the in-sample fitted values (mean, residuals) for the PCR model.
PCR_FORE	Forecasting (mean, error, and C.I.) for the PCR model.
PCR_PRTest	Partial F-Test for PCR.
PCR_STEPWISE	Perform the stepwise variables selection method.

Generalized Linear Model (GLM)

The generalized linear model (GLM) is a flexible generalization of ordinary least squares regression. The GLM generalizes linear regression by allowing
the linear model to be related to the response variable (i.e., $Y$) via a
link function (i.e. $g(.)$)and by allowing the magnitude of the variance
of each measurement to be a function of its predicted value.

The GLM is described as follows:
$$Y = \mu + \epsilon $$
And
$$ E\left[Y\right]=\mu=g^{-1}(X\beta) = g^{-1}(\eta)$$

Where

$\epsilon$ is the residual or deviation from the mean
$g(.)$ is the link function
$g^{-1}(.)$ is the inverse-link function
$X$ is the independent variables or the exogenous factors
$\beta$ is a parameter vector
$\eta$ is the linear predictor: the quantity which incorporates
the information about the independent variables in the model.
$\eta=X\beta$

Featured Functions:

Function	Description
GLM	GLM model definition
GLM_CHECK	Verify the parameters’ values of the GLM model
GLM_GUESS	Calculate a rough but valid set of coefficient values for the given GLM model.
GLM_CALIBRATE	Calculate the GLM-optimized model coefficient values (and errors).
GLM_LLF	Calculate the log-likelihood function (LLF) for the GLM Model.
GLM_MEAN	Calculate the in-sample fitted value of the GLM Model
GLM_RESID	Calculate the in-sample error terms/residuals for the GLM model
GLM_FORE	Calculate the mean forecast of the GLM Model
GLM_FORECI	GLM Forecasting Confidence Interval

Featured Functions:

Function	Description
DFT	Calculate the Discrete (Fast) Fourier Transform
IDFT	Calculate the Inverse Discrete Fourier Transform

Filters

The filters functions decompose the time series into a trend and cyclical components.

Featured Functions:

Function	Description
NxBK	Apply the Baxter-King filter
NxHP	Apply Hodrick-Prescott filter
NxConv	Compute the convolution operator between the two time-series.

Periodogram

A periodogram is an estimate of the power spectral density of a time series. This method is useful to identify dominant seasonality in the underlying data set.

Featured Functions:

Function	Description
Periodogram	Calculates the Power Spectral Density (PSD) estimate.

Featured Functions:

Function	Description
NxVaR	Returns the historical/theoretical value at risk (VaR).
NxDWS	Returns the downside deviation of a given data set.
NxCVaR	Returns the historical/theoretical conditional value at risk (CVaR).
NxCAPM	Returns the Capital Asset Pricing Model (CAPM) beta.
NxCalmar	Returns the Calmar ratio.
NxSharpe	Returns the Sharpe ratio.
NxMCR	Returns the upside and downside market capture ratio (MCR).
NxJensen	Returns the Jensen’s alpha measure in annual percentage rate (APR).
NxMDD	Returns the maximum drawdown (MDD) in the given time series data set.
NxCAGR	Returns the compound annual growth rate.
NxTreynor	Returns the Treynor ratio.

Featured Functions:

Function	Description
NxMatch	Regular Expression (regex) Match Function
NxReplace	Regular Expression (regex) Replace Function
NxTokenize	Separate a string by a delimiter into an array of sub-strings

Missing Values

Detect any missing value in a data set, and, possibly remove them.

Featured Functions:

Function	Description
HASNA	Check input for observations with missing Value
RMNA	Remove Missing Values
MV_OBS	Number of observations with non-missing values
MV_VARS	Effective Number of Variables

Array Functions

The functions in the category operate on one(1) or two(2) dimensional data cells. They are very useful, especially when we handle missing values, interpolation, and/or selecting a subset of input variables.

Featured Functions:

Function	Description
NxArray	Create an array using constants and/or cells references
NxFold	Convert a 3-column dataset into a 2-D table or matrix
NxFlatten	collapse a data table into a flat 3-column dataset form
NxTranspose	Convert row to column (and vice versa)

System Function

NumXL installation and user’s license information.

Featured Function:

Function	Description
NUMXL_INFO	Retrieves NumXL’s installation information.

NumXL Pro

Tutorials

Getting Started

User's Guide

Reference Manual

Tips & Tricks

Case Studies

What can NumXL do for me?

Summary Statistics

Featured Functions:

Featured Functions:

Featured Functions

Featured Functions

See Also

Featured Functions:

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

Featured Functions:

Featured Functions:

Featured Functions:

Featured Functions

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

Featured Functions:

Featured Functions:

Featured Functions:

See Also

Featured Functions:

Featured Functions:

Featured Functions:

See Also

Featured Functions:

See Also

See Also

Featured Functions:

Featured Functions:

Featured Functions:

Featured Function:

See Also

Customers Reviews

See Also

Useful Links

NumXL Tips & Tricks Newsletter