Dashboard Sheet 3 - Import YF Data

This sheet imports the historical data for a variable being forecasted, the concomitant forecasts, dates of the actual events and forecasts as well as three variables that typically denote the month, day of the week and hour of the day corresponding to when the observations were made for the variable being predicted. The latter three variables can be used to capture up to 24 levels of a nominal, ordinal or categorised interval or ratio variable that are deemed related to the variable being predicted. It can be used for example to capture for a specific observation: which of nine provinces it pertains to (nominal variable); whether the state of the economy was poor, fair, good or excellent (ordinal); whether the petrol price was low (less than quartile 1), average (in the inter-quartile range) or high (greater than inter-quartile 3).

87

To simplify the specification of the details regarding the source and layout of the data, the only compulsory input field in this sheet is for the file name. For each of the other input fields a default applies if the applicable field is left blank:

 Folder name – if blank then it is assumed that the required input data file has been opened by the user;

 Data sheet – if no sheet name is given from where the data must be imported, the default name is assumed to be Sheet1 – the Excel default name for the first sheet in a workbook;

 Row with variable names – row number 1 is the default row;

 Column with case ID’s – column number 1 (column A in Excel) is the default column.

No options are provided regarding the layout of the data – apart from specifying the first row and column – or for selecting a subset of the data because:

a) The required layout of the input data file can be obtained by utilising basic Excel features, e.g. copy and paste;

b) A subset of the input data can easily be obtained by applying Excel’s Data Filter which has a rich data selection repertoire.

The interface and an example of the input data that shows the required data layout are depicted in Figure 5.2 and .Figure 5.3 Note that the Record column is not part of the input file; it is provided in the sheet to assist the user when specifying the analysis and holdout samples in sheet Select Samples.

The names of the input variables are all at the discretion of the user, that is for the case identifier (GTYFM.ID in the Figure 5.2 example), the date of the actual observations, the date of the forecasts, the time-periods (M for month of the year; W for weekday and H for hour of the day), the variable being forecasted (Y3.01in the example above) and the forecasts. The only requirement is that names should be non-blank.

The following statistics are calculated and displayed in this sheet for each input forecast:

its Pearson product moment correlation with the variable being predicted and its Mean Absolute Percentage Error (MAPE).

88

Figure 5.2: Dashboard Sheet 3 – Import YF Data – User Interface and input data (excerpt 1)

Figure 5.3: Dashboard Sheet 3 – Import YF Data – Input data (excerpt 2), correlations with variable being forecasted and Mean Absolute Percentage Error (MAPE) for each input forecast

As stated earlier, the time-period columns (labelled M, W and H in the Figure 5.2

example) can be used for any categorical variable (24 levels per variable maximum) that is related to the variable being forecasted.

No missing input data are allowed except for the forecasts because the only imputation routine that is provided in the dashboard is a routine provided in sheet Select Samples to impute missing forecast values as the average of the available forecasts for the relevant observed actual value.

89 5.5 Dashboard Sheet 4 – Select Samples

The primary function of this sheet is to provide the user with options for the manual and random selection of two samples to be used in the dashboard:

i) The analysis sample that is used to obtain the regression models for the

consolidation of forecasts (maximum of 1000 records if sufficient input records);

ii) The holdout sample that is used to assess the performance of the regression models derived from the analysis sample (maximum of 1000 records if sufficient input records).

The samples are selected based on the specified analysis and holdout sample sizes na

and nh and the specified first input record to be used for the analysis sample, an integer value ia such that:

i) The analysis sample consists of case numbers ia to (ia + na - 1);

ii) The holdout sample consists of case numbers (ia + na) to (ia + na + nh - 1).

Excel’s data validation routine with applicable formulas is used to ensure valid input values for na, nh and ia. The formulas calculating the minima and maxima for the data validation routines take into account that a sample of at least five input records is made available for post-holdout testing. Valid na and nh values are required, else the macro generating the samples will abort. If ia, the first record number of the input data to be used for the analysis sample, is left blank then a random number is generated by the macro processing the samples.

The user interface and an excerpt of an example of analysis sample data for this sheet are presented in Figure 5.4.

90

Figure 5.4: Dashboard Sheet 2 – Select Samples – User interface and example of analysis sample data (excerpt)

The sheet provides options for selecting a specific or random subset of the forecasts for analysis and options for controlling the imputation process:

 Forecasts selection – The user must first choose between a “Random” and Specific” subset of the forecasts. If the “Random” option is selected, then the user must specify how many forecasts are to be randomly selected. If the number of forecasts to be randomly selected is not specified, then the macro processing the sample will prompt the user to enter a valid number. If the

“Specific” option is selected then the user must indicate using an “x” which forecasts are to be selected for processing. Between two and the number of forecasts per input record (maximum of 20) forecasts can be selected for processing.

91

 Imputation – The user can choose between omitting cases with missing forecast values and replacing the missing values by the average of the available forecasts for an input record. For imputation the user can specify m the minimum number of available forecasts required for imputation, e.g. if there are 12 input forecasts then the user may specify m = 6 resulting in cases with less than six forecasts being omitted from further analysis.

Two macros are provided in this sheet:

i) CF_SS_Process_Samples activated by button Process Samples that do the following; calls macro CF_SS_Random_Forecasts if required to randomly select a subset of the forecasts for processing; prompts the user for the minimum number of forecasts for imputation if the number specified in the sheet is invalid or blank; selects a random first record to analyse if the user value is missing or invalid; set default options for the graphs in sheet Graphs Samples; clears redundant forecast (beyond the number of forecasts in the input record) selections in this sheet and in sheet Consolidate Forecasts.

ii) CF_SS_Copy_Output activated by button Copy Correlations & MAPEs that copies the reported correlations and MAPEs.