# How do I handle nonexistent or missing data?

I tried a forecasting method and want to check if my method is correct or not.

My study is comparing different kinds of mutual funds. I want to use the GCC index as a benchmark for one of them but the problem is that the GCC index stopped in September 2011 and my study is from January 2003 to July 2014. Thus, I tried to use another index, the MSCI index, to make a linear regression, but the problem is that the MSCI index is missing data from September 2010.

To get around this, I did the following. Are these steps valid?

1. The MSCI index is missing data for September 2010 through July 2012. I "provided" it by applying moving averages for five observations. Is this approach valid? If so, how many observations should I use?

2. After estimating the missing data, I performed a regression on the GCC index (as dependent variable) versus the MSCI index (as independent variable) for the mutually available period (from January 2007 to September 2011), then corrected the model from all problems. For each month, I replace the x by the data from the MSCI index for the rest period. Is this valid?

Below are the data in Comma-Separated-Values format containing the years by rows and the months by columns. The data are also available through this link.

Series GCC:

,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
2002,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,117.709
2003,120.176,117.983,120.913,134.036,145.829,143.108,149.712,156.997,162.158,158.526,166.42,180.306
2004,185.367,185.604,200.433,218.923,226.493,230.492,249.953,262.295,275.088,295.005,328.197,336.817
2005,346.721,363.919,423.232,492.508,519.074,605.804,581.975,676.021,692.077,761.837,863.65,844.865
2006,947.402,993.004,909.894,732.646,598.877,686.258,634.835,658.295,672.233,677.234,491.163,488.911
2007,440.237,486.828,456.164,452.141,495.19,473.926,492.782,525.295,519.081,575.744,599.984,668.192
2008,626.203,681.292,616.841,676.242,657.467,654.66,635.478,603.639,527.326,396.904,338.696,308.085
2009,279.706,252.054,272.082,314.367,340.354,325.99,326.46,327.053,354.192,339.035,329.668,318.267
2010,309.847,321.98,345.594,335.045,311.363,299.555,310.802,306.523,315.496,324.153,323.256,334.802
2011,331.133,311.292,323.08,327.105,320.258,312.749,305.073,297.087,298.671,NA,NA,NA

Series MSCI:

,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
2007,NA,NA,NA,NA,1000,958.645,1016.085,1049.468,1033.775,1118.854,1142.347,1298.223
2008,1197.656,1282.557,1164.874,1248.42,1227.061,1221.049,1161.246,1112.582,929.379,680.086,516.511,521.127
2009,487.562,450.331,478.255,560.667,605.143,598.611,609.559,615.73,662.891,655.639,628.404,602.14
2010,601.1,622.624,661.875,644.751,588.526,587.4,615.008,606.133,NA,NA,NA,NA
2011,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA
2012,NA,NA,NA,NA,NA,NA,NA,609.51,598.428,595.622,582.905,599.447
2013,627.561,619.581,636.284,632.099,651.995,651.39,687.194,676.76,694.575,704.806,727.625,739.842
2014,759.036,787.057,817.067,824.313,857.055,805.31,873.619,NA,NA,NA,NA,NA

• What is the x mentioned in the last paragraph? Oct 25, 2014 at 16:44
• the y is the price close of gcc index and the x is price close for msci index Oct 25, 2014 at 16:54
• You may be interested in this post, which shows an example about how to fill gaps in a time series by means of the Kalman filter applied in the framework of an ARIMA time series model. Oct 27, 2014 at 19:45
• thank you javlacalle does it work with my missing data? here is my file for missing data 4shared.com/file/qR0UZgfGba/missing_data.html Oct 27, 2014 at 22:20
• I couldn't download the file. You may post the data, for example displaying years by rows and months by columns and values separated by commas. Oct 28, 2014 at 7:34

My suggestion is similar to what you propose except that I would use a time series model instead of moving averages. The framework of ARIMA models is also suitable to obtain forecast including not only the series MSCI as a regressor but also lags of the GCC series that may also capture the dynamics of the data.

First, you may fit an ARIMA model for the series MSCI and interpolate the missing observations in this series. Then, you may fit an ARIMA model for the series GCC using MSCI as exogenous regressors and obtain the forecasts for GCC based on this model. In doing this, you must be careful dealing with the breaks that are graphically observed in the series and that may distort the selection and fit of the ARIMA model.

Here is what I get doing this analysis in R. I use the function forecast::auto.arima to make the selection of the ARIMA model and tsoutliers::tso to detect possible level shifts (LS), temporary changes (TC) or additive outliers (AO).

These are the data once loaded:

gcc <- structure(c(117.709, 120.176, 117.983, 120.913, 134.036, 145.829, 143.108, 149.712, 156.997, 162.158, 158.526, 166.42, 180.306, 185.367, 185.604, 200.433, 218.923, 226.493, 230.492, 249.953, 262.295, 275.088, 295.005, 328.197, 336.817, 346.721, 363.919, 423.232, 492.508, 519.074, 605.804, 581.975, 676.021, 692.077, 761.837, 863.65, 844.865, 947.402, 993.004, 909.894, 732.646, 598.877, 686.258, 634.835, 658.295, 672.233, 677.234, 491.163, 488.911, 440.237, 486.828, 456.164, 452.141, 495.19, 473.926,
492.782, 525.295, 519.081, 575.744, 599.984, 668.192, 626.203, 681.292, 616.841, 676.242, 657.467, 654.66, 635.478, 603.639, 527.326, 396.904, 338.696, 308.085, 279.706, 252.054, 272.082, 314.367, 340.354, 325.99, 326.46, 327.053, 354.192, 339.035, 329.668, 318.267, 309.847, 321.98, 345.594, 335.045, 311.363,
299.555, 310.802, 306.523, 315.496, 324.153, 323.256, 334.802, 331.133, 311.292, 323.08, 327.105, 320.258, 312.749, 305.073, 297.087, 298.671), .Tsp = c(2002.91666666667, 2011.66666666667, 12), class = "ts")
msci <- structure(c(1000, 958.645, 1016.085, 1049.468, 1033.775, 1118.854, 1142.347, 1298.223, 1197.656, 1282.557, 1164.874, 1248.42, 1227.061, 1221.049, 1161.246, 1112.582, 929.379, 680.086, 516.511, 521.127, 487.562, 450.331, 478.255, 560.667, 605.143, 598.611, 609.559, 615.73, 662.891, 655.639, 628.404, 602.14, 601.1, 622.624, 661.875, 644.751, 588.526, 587.4, 615.008, 606.133,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 609.51, 598.428, 595.622, 582.905, 599.447, 627.561, 619.581, 636.284, 632.099, 651.995, 651.39, 687.194, 676.76, 694.575, 704.806, 727.625, 739.842, 759.036, 787.057, 817.067, 824.313, 857.055, 805.31, 873.619), .Tsp = c(2007.33333333333, 2014.5, 12), class = "ts")

Step 1: Fit an ARIMA model to the series MSCI

Despite the graphic reveals the presence of some breaks, no outliers were detected by tso. This may be due to the fact that there are several missing observations in the middle of the sample. We can deal with this in two steps. First, fit an ARIMA model and use it to interpolate missing observations; second, fit an ARIMA model for the interpolated series checking for possible LS, TC, AO and refine the interpolated values if changes are found.

Choose ARIMA model for the series MSCI:

require("forecast")
fit1 <- auto.arima(msci)
fit1
# ARIMA(1,1,2) with drift
# Coefficients:
#           ar1     ma1     ma2    drift
#       -0.6935  1.1286  0.7906  -1.4606
# s.e.   0.1204  0.1040  0.1059   9.2071
# sigma^2 estimated as 2482:  log likelihood=-328.05
# AIC=666.11   AICc=666.86   BIC=678.38

Fill missing observations following the approach discussed in my answer to this post:

kr <- KalmanSmooth(msci, fit1$model) tmp <- which(fit1$model$Z == 1) id <- ifelse (length(tmp) == 1, tmp[1], tmp[2]) id.na <- which(is.na(msci)) msci.filled <- msci msci.filled[id.na] <- kr$smooth[id.na,id]

Fit an ARIMA model to the filled series msci.filled. Now some outliers are found. Nevertheless, using alternative options different outliers were detected. I will keep the one that was found in most cases, a level shift at October 2008 (observation 18). You can try for example these and other options.

require("tsoutliers")
tso(msci.filled, remove.method = "bottom-up", tsmethod = "arima",
args.tsmethod = list(order = c(1,1,1)))
tso(msci.filled, remove.method = "bottom-up", args.tsmethod = list(ic = "bic"))

The chosen model is now:

mo <- outliers("LS", 18)
ls <- outliers.effects(mo, length(msci))
fit2 <- auto.arima(msci, xreg = ls)
fit2
# ARIMA(2,1,0)
# Coefficients:
#           ar1     ar2       LS18
#       -0.1006  0.4857  -246.5287
# s.e.   0.1139  0.1093    45.3951
# sigma^2 estimated as 2127:  log likelihood=-321.78
# AIC=651.57   AICc=652.06   BIC=661.39

Use the previous model to refine the interpolation of missing observations:

kr <- KalmanSmooth(msci, fit2$model) tmp <- which(fit2$model$Z == 1) id <- ifelse (length(tmp) == 1, tmp[1], tmp[2]) msci.filled2 <- msci msci.filled2[id.na] <- kr$smooth[id.na,id]

The initial and the final interpolations can be compared in a plot (not shown here to save space):

plot(msci.filled, col = "gray")
lines(msci.filled2)

Step 2: Fit an ARIMA model to GCC using msci.filled2 as exogenous regressor

I ignore the missing observations at the beginning of msci.filled2. At this point I found some difficulties to use auto.arima along with tso, so I tried by hand several ARIMA models in tso and finally chose the ARIMA(1,1,0).

xreg <- window(cbind(gcc, msci.filled2)[,2], end = end(gcc))
fit3 <- tso(gcc, remove.method = "bottom-up", tsmethod = "arima",
args.tsmethod = list(order = c(1,1,0), xreg = data.frame(msci=xreg)))
fit3
# ARIMA(1,1,0)
# Coefficients:
#           ar1    msci     AO72
#       -0.1701  0.5131  30.2092
# s.e.   0.1377  0.0173   6.7387
# sigma^2 estimated as 71.1:  log likelihood=-180.62
# AIC=369.24   AICc=369.64   BIC=379.85
# Outliers:
#   type ind    time coefhat tstat
# 1   AO  72 2008:11   30.21 4.483

The plot of GCC shows a shift at the beginning 2008. However, it seems that it was already captured by the regressor MSCI and no additonal regressors were included except an additive outlier at November 2008.

The plot of the residuals did not suggest any autocorrelation structure but the plot suggested a level shift at November 2008 and an additive outlier at February 2011. However, adding the corresponding interventions the diagnostic of the model was worse. Further analysis may be needed at this point. Here, I will continue obtaining the forecasts based on the last model fit3.

The forecasts can be easily obtained. The plot below displays the original series, the interpolated values for MSCI and the forecast along with the $95\%$ confidence intervals for GCC. The confindence intervals does not account to the uncertainty in the values tht were interpolated in MSCA.

newxreg <- data.frame(msci=window(msci.filled2, start = c(2011, 10)), AO72=rep(0, 34))
p <- predict(fit3$fit, n.ahead = 34, newxreg = newxreg) head(p$pred)
# [1] 298.3544 298.2753 298.0958 298.0641 297.6829 297.7412
par(mar = c(3,3.5,2.5,2), las = 1)
plot(cbind(gcc, msci), xaxt = "n", xlab = "", ylab = "", plot.type = "single", type = "n")
grid()
lines(gcc, col = "blue", lwd = 2)
lines(msci, col = "green3", lwd = 2)
lines(window(msci.filled2, start = c(2010, 9), end = c(2012, 7)), col = "green", lwd = 2)
lines(p$pred, col = "red", lwd = 2) lines(p$pred + 1.96 * p$se, col = "red", lty = 2) lines(p$pred - 1.96 * p\$se, col = "red", lty = 2)
xaxis1 <- seq(2003, 2014)
axis(side = 1, at = xaxis1, labels = xaxis1)
legend("topleft", col = c("blue", "green3", "green", "red", "red"), lwd = 2, bty = "n", lty = c(1,1,1,1,2), legend = c("GCC", "MSCI", "Interpolated values", "Forecasts", "95% confidence interval"))

• thank you very much javlacalle,, i really appreciate your help that's exactly what i am looking for ,, i am sorry for taking your time ,,, i am going to do all the steps on eviews because i don't have the program R and i don't know how to use it.... thank you thank you again Oct 31, 2014 at 1:59
• I'm glad to see you found it useful. Oct 31, 2014 at 7:13
• I am new for R I couldn't find the answer in Eviews ...so I started to use R and I have some questions,, how should I import the data .. I mean all data with na vairables or just for available data for msci to R + there is Error message about no kalmansmooth or run found I already downloaded the packages for kalman filter what should I do? ..thank you Dec 12, 2014 at 8:13
• Questions related to software usage are off-topic in this site. Stack Overflow is more suitable for these kind of questions. If it is something specific to my answer you can send me an e-mail. Dec 12, 2014 at 11:48
1. Imputation (ie 'provided' by moving average') is valid if values are missing at random. If it is an uninterruted period of considerable length, this becomes unlikely. The second part of the question is unclear.
2. Depending on the question, it is considered anything from suboptimal to invalid to use your model for forecasting beyond the scope of your data: eg what if the relationship between the two indices changes in 2012-2014? You could use regression-estimated values (but not directly replacing by the raw values of another index) for the missing datapoints, but this would make sense only if there is a strong relationship between the two indices, and it is critical that these values are clearly marked as estimates. And what do you mean by "corrected the model from all problems"?
• Some large fraction of time series analysis is dedicated to predicting the future. For some, it is the main reason for statistics! #2 is thus a counsel of perfection dividing the timid from the time-series forecasters. Oct 27, 2014 at 18:55
• Fair enough, I agree/stand corrected. I still wonder whether it is more prudent to choose a predictor with missing values in mid-gradient vs. end of the gradient. If they are related. Oct 27, 2014 at 20:46
• i am sorry i tried to upload my file but i did not know how or where :( ... + i meant corrected the model from heteroscedasticity and serial correlation Oct 27, 2014 at 22:07
• here is my file for missing data on excel 4shared.com/file/qR0UZgfGba/missing_data.html Oct 27, 2014 at 22:18

2 Seems fine. I would go with it.

As for 1. I would suggest you to train a model to predict GCC using all features available in the dataset (which are not NA during the period Sept 2011 onwards) (ommit the rows which have any NA value before sep2011 while training). The model should be very good ( use K-fold cross validation). Now predict the GCC for the period Sept 2011 and onwards.

Alternatively, you can train a model which predicts MSCI, use it to predict the missing MSCI values. Now train a model to predict GCC using MSCI and then predict GCC for the period Sept 2011 and onwards