Combining forecasts into a data frame in R and then exporting into excel -

i'm running multiple auto.arima() forecasts in r generate series of point forecasts confidence intervals i'd able pull directly excel. sample of script i've been using below shown portion of data.

require(forecast)   # customer gm arima forecasts (1 quarter ahead)  f1 <- read.csv("c:/datapath/desktop/dataname.csv")  f1 <- ts(f1, frequency = 12, start = c(2014, 1), end = c(2015, 12))  coonan <- f1[,3] gallo  <- f1[,4] kempton<- f1[,5] moore  <- f1[,6] nekic  <- f1[,7]   fit.coonan  <- auto.arima(coonan, stepwise = false) fc.coonan   <- forecast(fit.coonan, h=3, level = c(20, 40, 80))  fit.gallo   <- auto.arima(gallo, stepwise = false) fc.gallo    <- forecast(fit.gallo, h=3, level = c(20, 40, 80))  fit.kempton <- auto.arima(kempton, stepwise = false) fc.kempton  <- forecast(fit.kempton, h=3, level = c(20, 40, 80))  fit.kempton <- auto.arima(kempton, stepwise = false) fc.kempton  <- forecast(fit.kempton, h=3, level = c(20, 40, 80))  fit.moore   <- auto.arima(moore, stepwise = false) fc.moore    <- forecast(fit.moore, h=3, level = c(20, 40, 80))  fit.nekic   <- auto.arima(nekic, stepwise = false) fc.nekic    <- forecast(fit.nekic, h=3, level = c(20, 40, 80))    # save clipboard copy , paste excel  write.excel <- function(x,row.names=true,col.names=true,...) {   write.table(x,"clipboard",sep="\t",row.names=row.names,col.names=col.names,...) }  write.excel(fc.coonan)   # can paste coonan forecasts directly excel 

after pasting result excel table looks this (i'd shift on column names, that's not big problem right now).

as written need manually change name of model in bottom function, run function (in order save results clipboard), , copy , paste results excel. process has become time consuming , i'm wondering if there's simple way combine series of point forecasts , confidence intervals 1 data frame can export @ once excel.

thank help.

here's 1 using openxlsx find preferable of other packages because uses c++ instead of java runs out of memory writing small sheets.

you may need set path zip though if error:

error: zipping workbook failed. please make sure rtools installed or zip application available r. try installr::install.rtools() on windows.

library(forecast) library(openxlsx) sys.setenv(r_zipcmd = "c:/rbuildtools/3.1/bin/zip")  # create dummy data library(data.table) set.seed(1) build <- data.table() f1 <- build[, lapply(seq(7), function(x) runif(24))]  f1 <- ts(f1, frequency = 12, start = c(2014, 1), end = c(2015, 12))  coonan <- f1[,3] gallo  <- f1[,4] kempton<- f1[,5] moore  <- f1[,6] nekic  <- f1[,7]  results <- list()  fit.coonan  <- auto.arima(coonan, stepwise = false) results[["coonan"]] <- forecast(fit.coonan, h=3, level = c(20, 40, 80))  fit.gallo   <- auto.arima(gallo, stepwise = false) results[["gallo"]] <- forecast(fit.gallo, h=3, level = c(20, 40, 80))  fit.kempton <- auto.arima(kempton, stepwise = false) results[["kempton"]] <- forecast(fit.kempton, h=3, level = c(20, 40, 80))  fit.moore   <- auto.arima(moore, stepwise = false) results[["moore"]] <- forecast(fit.moore, h=3, level = c(20, 40, 80))  fit.nekic   <- auto.arima(nekic, stepwise = false) results[["nekic"]] <- forecast(fit.nekic, h=3, level = c(20, 40, 80))  results_together <- do.call(rbind,lapply(names(results),function(x){   transform(as.data.frame(results[[x]]), name = x) }))  wb <- createworkbook()  addworksheet(wb, "forecasts") writedata(wb, "forecasts", results_together, rownames = true) saveworkbook(wb, "forcasts.xlsx", overwrite = true) 

results in this:

you can put each result on own tab (with or without adding name column):

wb <- createworkbook()  (nm in names(results)){   addworksheet(wb, nm)   writedata(wb, nm, results[[nm]], rownames = true) }  saveworkbook(wb, "forcasts.xlsx", overwrite = true)   

resulting in: