NbbTools > Documentation Applications
The ColorAnalyser is a tool to search outliers in an Excel worksheet containing time series. The program uses the algorithm Tramo for a fully automated Arima modelling and outliers detection. The detected anomalies are visualized by colouring the corresponding cells. Additional information are available through Excel comments (used model, value of outliers, ...). The program also estimates missing values.
At each execution a new sheet for the results is created with a name based on the current date and time. This sheet recalls the selected data to analyse flagged with different colours for each outlier found and some information as comments in the series titles cells and outliers cells.
Even if the Add-ins method would be a good solution for this application, we preferred a usual workbook because it is more transparent for the people who wants to know how it works by consulting the VBA Excel environment (type ALT+F11).
To start with this application you need to open the Excel workbook ColorAnalyser.xls (the default installation directory is C:\Program Files\NBB_DQ_RD\NbbTools\ExcelAddins). The loaded workbook is now minimised in the Excel desktop and the Excel menu is enriched with the "Tramo Seats " - "Color Analyser" option between the "Data" and "Window" Excel menu entry.
The "Color Analyser" sub menu offers 3 possibilities :
| Execute | Creates a new worksheet with the recall of the input selection flagged with different colours for each outlier and some information comments. |
| Tramo Settings | Displays the dialog box to set some parameters. |
| Help | Displays the help you are reading. |
You must first select the table to analyze and then execute the Excel menu command "Tramo Seats - Color Analyser - Execute".
You must follow some rules for the selection :
Or
- you select all the sheet (via the the top left cell at the intersection of the row and column headings of the sheet) and in that case the sheet can contain only the table you want to analyse,
Or
- you make a limited selection composed of at least 2 rows or 2 columns.
And
- for a vertical table (series in columns, dates in rows), the 1st column of the selection must be real Excel dates (no text and a sorted ascending vector).
- for an horizontal table (series in rows, dates in columns), the 1st row of the selection must be real Excel dates (no text and a sorted ascending vector).
- depending on the table frequency the selection must have a minimum number of observations (freq 1, 2, 4 = minimum 12 obs. and freq 12 = minimum 36 obs.) .
- for a vertical table, you can start the selection at the top with empty rows or with structured titles rows or directly at a specific date in the table (no obligations to start at the 1st date of the table).
- for an horizontal table, you can start the selection at the left with empty columns or with structured titles columns or directly at a specific date in the table (no obligations to start at the 1st date of the table).
- the dates of the series can be discontinuous.
- the series can have missing values (there are automatically extrapolated)
Two examples of good vertical selections (image selection was truncated, for monthly series don't forget to select minimum 3 years of observations):
Two examples of good horizontal selections :
If you accept the default settings you only have to click the Excel menu command "Tramo Seats" - "Color Analyser" - "Execute".
A new sheet is automatically created in the active workbook with a unique name based on the date and time of the calculation. The results sheet incorporates always a recall of the selected series with the outliers cells colourised and commented. The new sheet also contains the interpolated missing values.
On the right of the table results a legend for the different colours and a reminder of the used parameters are displayed.
The series titles cells are commented with some information on the Arima model chosen by Tramo.
Each outlier is also commented, with the outlier type, and the estimated coefficient (with its standard error). Be aware that the meaning of the value depends on the log/level option, mentioned in the title cell.
There are no links between the results sheet and any other workbook or sheet, so you can copy, move or delete the results sheets without any restrictions.
You can display the "Tramo Outliers Settings" dialog box via the Excel menu command "Tramo Seats" - "Color Analyser" - "Tramo Settings".
The dialog box displays the default values.
| Log transformation | By default = Pretest. You can force the log transformation with option Yes or prevent it with the option No. |
| Outliers | By default the 2 first types of outliers are searched (additive and level shift). |
| Sensibility | Three degrees of sensitivity are available to detect the abnormalities that you can fine tune with the "Settings" button. The default value is Low (VA = 5). |
Each sensitivity can be set in a bracket of values displayed to the right of the textbox.
Higher the value, less will be the sensitivity.
| Automatic model identification | By default the automatic identification is active. |
| Calendar effects | By default calendar effects are potentially
(pretest) activated but depending of the length of the series some functionalities are automatically
desactivated.
X = length of the series in years |
| X <= 3 | 4<= X < 1 | X >= 12 | |
| Leap year effect | No | No | Yes |
| Easter effect | No | Yes | Yes |
| Trading days effect | Yes | Yes | Yes |
| Reset Defaults | Sets back the original default values displayed in the dialog box upper. |