The time series fitting tool allows you to fit a time series model to time series data. The time series data is assumed to be stationary in the sense that mean and variance are constant over time. To achieve stationarity, the data may have to be transformed in some way. Examples of transformations include differencing, logarithms, or using returns instead of prices. If data is transformed, the fitted model is for the transformed data. This means that each data point in the time series model must be de-transformed to get back to the original data type.
For this tutorial we will be fitting a time series model to daily closing value (adjusted for dividends) of the S&P 500 index for the previous five years. The data was downloaded from Yahoo Finance and placed in a spreadsheet.
A time series plot of daily closing values is shown below.
As you can see from the time series plot, there is a clear trend and the mean is changing. Therefore, the time series is not stationary. This is common with financial time series. A common approach is to use the natural logarithm of returns. In this case we are transforming closing values into returns and performing a second transformation by taking the natural log of the returns.
The spreadsheet below shows the transformed data in column J. You can see how the data in column F (adjusted close) is transformed from the formula for cell J3. This formula is copied down to the last row. Note: There are 1258 data points, but we are only showing part of these for clarity.
If we look at the time series plot for the log returns we see that mean appears to be constant.
The autocorrelation function (ACF) correlogram can also give us insight into stationarity. A white noise process has an ACF that is statistically zero at each lag. If we plot the ACF for 30 lags with a 95% confidence interval we see that ACF is generally zero. There is one lag outside the bands, but this is likely due to the random nature of the process. A white noise process is stationary so we can conclude that our transformed data is stationary.
We're now ready to fit a time series model to the transformed data. Click the Fit Time Series button on the ribbon.
The Fit Time Series form will appear.
Data to Fit
Enter the cell range containing the time series data. You can type a valid cell range in the box, or you can use the minimize button next to the box and select the range in the worksheet. In our example, the data is in range J3:J1259.
Time series models are fitted using maximum likelihood estimation. To do this, the software must numerically find the best solution for each time series with the exception of GBM which is found analytically. The intensity setting determines how long the software will search for a solution. In most cases, the normal setting should be used.
Models to Fit
Select any time series models you want to fit. In our example, we will select all models.
Once everything has been entered, click the Fit Data button.
Once the fitting process is complete the Time Series Fit Results form will appear.
Time series fits are ranked using information criteria. There are three information criteria calculated:
- Bayesian Information Criterion (BIC)
- Aikake Information Criterion (AIC)
- Hannan-Quinn Information Criterion (HQIC)
A lower information criterion value indicates a better relative fit. In our example, GARCH(1,1) has the lowest value for all three information criteria (largest negative number), and is therefore the best fit among the time series models that we selected for fitting.
A given information criterion does not say anything about how well a time series model fits the data, it is only used as a comparison relative to other candidate models.
Create a Fit Report
A fit report can be created by clicking on the Create Report button. The Time Series Fit Reports form will appear.
We will create a report for GARCH(1,1) and it will be located in the current workbook. An extension plot uses the last value of the data as the initial value for a simulated time series that extends into the future. An overlay plot uses the initial value of the data as the initial value for a simulated time series for the same time period as the original data.
The report is shown below.
Write to Cells
Time series functions using the fitted parameters can be written to a worksheet. After clicking the Write to Cells button, the Write Time Series to Cells form will appear.
Time Series Range
Enter the range of cells where the time series function will be written. You can also select the range in the worksheet by using the minimize button next to the box. We will place 500 time series values in the range L2:L501.
Disturbance Variables Location
Each time series has a column of disturbances for each time index. ARCH and GARCH models also have a column of variances for each time index. These columns can be located to the left or right of the time series column. You can also automatically add the HIDERV( ) function to disturbances so that they will not be recorded during simulation. We will place variances and disturbances to the right of column L.
A time series requires an initial value. This value can be entered as a number or referenced to a cell containing the initial value. In our example, we will use the last value of the transformed data as the initial value of the time series model (cell J1259).
When everything is entered, click the Add Time Series button. We see that Simulation Master time series functions for GARCH(1,1) are placed in column L, variance functions for GARCH(1,1) in column M, and disturbance functions in column N.
De-Transforming the Model
Prior to simulating the time series, we need to transform the model back to its original form. In our case, the daily closing value of the S&P 500 index. Recall from earlier that we performed two transformations:
- Changed index value to returns
- Took the natural logarithm of returns
To get our model back to index value, we perform the transformation in reverse.
- EXP(model values)
- Index value = R_t * Y_t-1, where R_t = return at time t and Y_t-1 is index value at time t-1
The index values that we would simulate are located in column P of the spreadsheet shown below. Note the formula in cell P3 used to de-transform the model. There are a couple of things to do to set up our final data column.
Supply an Initial Value In step 2 of the de-transformation process we are multiplying the current return by the previous index value. To do this we need to specify an initial value. In our example, cell P2 is the last value of the original data (cell F1259).
Use RVUSERDIST( ) Function To record the value of each time index during simulation, we need to enclose the cell formulas in column P in the RVUSERDIST( ) function.
The model is now set-up to simulate the time series in column P.