Exporting time series data

[alpineke]

I'm trying to export multivariate time series data from R to an excel file, but each time it excludes the column for time period. I've pulled data using APIs from the St. Louis FRED, then converted the monthly data to quarterly and converted to time series, then combined all three dataset into one time series dataset, and then exported to excel. But each time the export in excel excludes the column for quarters (or months when I don't average by quarter).

Is there a way to include the time periods in the export? For example, a column with values such as "2019 Q1"? Any help most appreciated!

Below is the code I'm using:

#Total Nonfarm Employment Washington state, Thousands of Persons, Seasonally Adjusted, Monthly
getSymbols("WANA",src="FRED")
WANA<-ts(WANA,start=c(1990,1),frequency = 12)

#Unemployment Rate WA, Seasonally Adjusted, Monthly
getSymbols("WAUR",src="FRED")
#Data goes back to 1976, so starting at 1990
WAUR<-ts(WAUR["1990-01-01/"],start=c(1990,1),frequency = 12)

#All Employees: Education and Health Services in WA, Seasonally Adjusted, Monthly
getSymbols("WAEDUH",src="FRED")
WAEDUH<-ts(WAEDUH,start = c(1990,1), frequency = 12)

DATA <- cbind(WAUR, WANA, WAEDUH) #combining all three into one dataset

Data.quarterly <- aggregate(ts(DATA,start=c(1990,1),frequency = 12),nfrequency = 4,mean) #averaging data by quarter

write.xlsx(Data.quarterly, "testdata.xlsx")

[DavoWW]

Hi @alpineke,
Welcome to the RStudio Community Forum.

You'll need to provide a simple reproducible example of what's not working so that we can help.
See the posting guide for assistance with this.

[alpineke]

Thanks @DavoWW, I just posted the code I'm using above.

[DavoWW]

Hi @alpineke,
Sorry for delay - you may have solved this already.
You need to convert your time-series matrix into a dataframe before saving to an Excel file.
See here, for a discussion:
https://stackoverflow.com/questions/25353002/converting-ts-object-to-data-frame

Here's some reproducible code (i.e. it includes some dummy data to demonstrate the point):

# Make a dummy monthly time-series, plus a data column, put into dataframe
my_ts <- ts(1:60, frequency = 12, start = c(2008, 1))
my_vals <- rnorm(60, mean=15, sd=2)
DATA <- data.frame(my_ts, my_vals)

head(DATA)
str(DATA)

# OK, now average data by quarters, only for certain years
Data.quarterly <- aggregate(ts(DATA,start=c(2009,1),
                               frequency=12),
                               nfrequency=4,
                               mean)

head(Data.quarterly)
str(Data.quarterly)

class(Data.quarterly)  # So, its actually a time-series matrix

# Put time-series into a dataframe for writing to disk
try1 <- data.frame(qtr_date=time(Data.quarterly), Y=Data.quarterly)
head(try1)

# Personally, I refer this output
library(timetk)
help("timetk")

try2 <- timetk::tk_tbl(Data.quarterly)
head(try2)

library(xlsx)
write.xlsx(try1, "test_try1.xlsx")
write.xlsx(try2, "test_try2.xlsx")

HTH

[alpineke]

Thanks so much @DavoWW! Works great.