How do I convert a data set imported from excel from a ""tbl_df" "tbl" "data.frame"." to "xts"?

Question

I am trying to convert a dataset, which I imported from excel, into a time series so as to run regressions with it. The data is made up of monthly returns from the S&P500 over the time span of Jan 1997 to Dec 2018. Before I imported the data I made sure there were no NA's and I made my first column, which consists of the date, to be in the (m/d/y) date format. I had my second column, the return prices, be in "number" format. The data imported properly, however, the class shows up as "tbl_df" "tbl" "data.frame".

When I use "view(SP500.1)" the data shows up in a table which looks like this...

# A tibble: 264 x 2
   Date       `Adj Close`
   <chr>            <dbl>
 1 1997-01-01        786.
 2 1997-02-01        791.
 3 1997-03-01        757.
 4 1997-04-01        801.
 5 1997-05-01        848.
 6 1997-06-01        885.
 7 1997-07-01        954.
 8 1997-08-01        899.
 9 1997-09-01        947.
10 1997-10-01        915.
# … with 254 more rows

Any help would be much appreciated! Thanks in advance! Best, Emma

Answer 1

Assuming the data shown reproducibly in the Note at the end read it into a zoo series z with Date class index and then convert that to an xts object x :

library(xts)
library(tibble)

z <- read.zoo(SP500.1)
x <- as.xts(z)

or it may make more sense to use a yearmon class index:

z <- read.zoo(SP500.1, FUN = as.yearmon)
x <- as.xts(z)

Note

library(tibble)
SP500.1 <-
structure(list(Date = c("1997-01-01", "1997-02-01", "1997-03-01", 
"1997-04-01", "1997-05-01", "1997-06-01", "1997-07-01", "1997-08-01", 
"1997-09-01", "1997-10-01"), `Adj Close` = c(786, 791, 757, 801, 
848, 885, 954, 899, 947, 915)), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))