library(readxl); library(tseries); library(forecast); library(ggplot2)
df <- read_excel("C:/Users/a s u s/Downloads/Data WDI 15 Negara.xlsx", sheet = 1)
names(df) <- make.names(names(df), unique = TRUE)
char_cols <- names(df)[sapply(df, function(x) is.character(x) || is.factor(x))]
col_neg <- char_cols[grepl("country|negara|economy|wilayah", char_cols, ignore.case = TRUE)][1]
if (is.na(col_neg) || !nzchar(col_neg)) {
col_neg <- char_cols[sapply(char_cols, function(c) any(grepl("singapore", as.character(df[[c]]), ignore.case = TRUE)))][1]

}
df_sg <- df[grepl("singapore", as.character(df[[col_neg]]), ignore.case = TRUE), , drop = FALSE]
yr_cols <- names(df_sg)[grepl("^X?[0-9]{4}$", names(df_sg))]
if (length(yr_cols) > 0) {
ts_df <- data.frame(
Year  = as.numeric(sub("^X", "", yr_cols)),
Value = suppressWarnings(as.numeric(as.character(unlist(df_sg[1, yr_cols, drop = TRUE])))))
} else {
col_yr <- names(df_sg)[grepl("year|tahun", names(df_sg), ignore.case = TRUE)][1]
cand_cols <- setdiff(names(df_sg), c(col_neg, col_yr))
col_vl <- cand_cols[grepl("value|nilai|val", cand_cols, ignore.case = TRUE)][1]
if (is.na(col_vl) || !nzchar(col_vl)) {
num_cands <- cand_cols[sapply(cand_cols, function(c) sum(!is.na(suppressWarnings(as.numeric(as.character(df_sg[[c]]))))) > 0)]
col_vl <- num_cands[length(num_cands)]}
ts_df <- data.frame(
Year  = suppressWarnings(as.numeric(as.character(df_sg[[col_yr]]))),
Value = suppressWarnings(as.numeric(as.character(df_sg[[col_vl]]))))}
ts_df <- ts_df[!is.na(ts_df$Year) & !is.na(ts_df$Value), , drop = FALSE]
ts_df <- ts_df[order(ts_df$Year), , drop = FALSE]
ts_sg <- ts(ts_df$Value, start = min(ts_df$Year), frequency = 1)
adf_lvl <- adf.test(ts_sg)
ser_st  <- if (adf_lvl$p.value < 0.05) ts_sg else diff(ts_sg)
par(mfrow = c(1, 2)); acf(ser_st, main = "ACF - Singapura"); pacf(ser_st, main = "PACF - Singapura"); par(mfrow = c(1, 1))

mods <- list(
"AR(1)"        = Arima(ser_st, order = c(1, 0, 0)),
"MA(1)"        = Arima(ser_st, order = c(0, 0, 1)),
"ARMA(1,1)"    = Arima(ser_st, order = c(1, 0, 1)),
"ARIMA(1,1,1)" = Arima(ts_sg, order = c(1, 1, 1)),
"Auto ARIMA"   = auto.arima(ts_sg, seasonal = FALSE),
"ETS"          = ets(ts_sg))
tabel <- do.call(rbind, lapply(names(mods), function(nm) {
m <- mods[[nm]]
lb <- Box.test(residuals(m), lag = min(5, max(1, floor(length(ts_sg) / 4))), type = "Ljung-Box")

data.frame(Model = nm, AIC = round(AIC(m), 2), BIC = round(BIC(m), 2), LB_p = round(lb$p.value, 4))}))
tabel <- tabel[order(tabel$AIC), , drop = FALSE]; rownames(tabel) <- NULL
print(paste("ADF Level p-value:", round(adf_lvl$p.value, 4))); print(tabel)
## [1] "ADF Level p-value: 0.3292"
##          Model    AIC    BIC   LB_p
## 1   Auto ARIMA 142.08 143.07 0.9025
## 2        AR(1) 145.22 148.21 0.8881
## 3        MA(1) 145.22 148.21 0.8918
## 4 ARIMA(1,1,1) 145.88 148.87 0.8916
## 5    ARMA(1,1) 147.05 151.04 0.8651
## 6          ETS 156.38 159.52 0.9030
fc_auto <- forecast(mods[["Auto ARIMA"]], h = 5)
fc_ets  <- forecast(mods[["ETS"]], h = 5)
print(fc_auto)
##      Point Forecast    Lo 80    Hi 80     Lo 95    Hi 95
## 2021       128.4329 118.1440 138.7217 112.69741 144.1683
## 2022       128.4329 113.8822 142.9835 106.17957 150.6861
## 2023       128.4329 110.6120 146.2537 101.17826 155.6875
## 2024       128.4329 107.8551 149.0106  96.96196 159.9038
## 2025       128.4329 105.4263 151.4394  93.24733 163.6184
autoplot(ts_sg) +
autolayer(fc_auto, series = "Auto ARIMA", PI = FALSE) +
autolayer(fc_ets, series = "ETS", PI = FALSE) +
labs(title = "Proyeksi 5 Tahun - Singapura", x = "Tahun", y = "Nilai") +
theme_minimal()