CODEWITHSUNDEEP
rcsvxts
Ramon
Ramon

Reputation: 81

csv data convert to xts

I have a csv file with 1 minute data from the nasdaq100 for the year 2016. I would like to convert it to xts to work with quantstrat. After the import it looks like this:

          date     open     high      low    close volume adjusted
         <chr>    <dbl>    <dbl>    <dbl>    <dbl>  <int>    <int>
1 04.01.2016 14:30 48963818 48964272 48952363 48958789      0        0
2 04.01.2016 14:31 48923579 48940259  4891752 48940259      0        0
3 04.01.2016 14:32 48941753 48992466 48941753 48988589      0        0
4 04.01.2016 14:33 48992227 48992227 48948281 48965469      0        0
5 04.01.2016 14:34 48962915  4896418 48923838 48934326      0        0
6 04.01.2016 14:35 48931196 48963301 48931196 48954341      0        0

I use the code 

NASD_xts = xts(NASD, order.by=as.POSIXct(NASD$date, format="%d-%m-%y %H:%M"))

and get this result.

     date               open       high       low        close      volume       adjusted
<NA> "04.01.2016 14:30" "48963818" "48964272" "48952363" "48958789" "         0" "0"     
<NA> "04.01.2016 14:31" "48923579" "48940259" " 4891752" "48940259" "         0" "0"     
<NA> "04.01.2016 14:32" "48941753" "48992466" "48941753" "48988589" "         0" "0"     
<NA> "04.01.2016 14:33" "48992227" "48992227" "48948281" "48965469" "         0" "0"     
<NA> "04.01.2016 14:34" "48962915" " 4896418" "48923838" "48934326" "         0" "0"     
<NA> "04.01.2016 14:35" "48931196" "48963301" "48931196" "48954341" "         0" "0"

My problem is the NA value in the first row, there should be the time. So I don't get the right xts to work on with quantstrat.

Upvotes: 1

Views: 768

Answers (2)

Joshua Ulrich
Joshua Ulrich

Reputation: 176648

Your format argument to as.POSIXct is incorrect. And you should not include the date column in your xts object because the datetimes are already included in the index attribute, and xts objects can only contain a single type (because they are a matrix underneath).

Including the date column is what causes the rest of the columns in your xts object to be character. Since xts objects can only contain a single type, all columns of your data.frame are coerced to a common type (character in this case).

Your command should be:

NASD_xts <- xts(NASD[,-1],
                order.by = as.POSIXct(NASD$date, format = "%d.%m.%Y %H:%M"))

Note that the format assumes the date column is specified as month, day, year. The month and day are ambiguous in the sample data you provide. So the true format could be day, month, year (meaning the dates could be either January fourth, or April first).

Upvotes: 1

akrun
akrun

Reputation: 887118

Apply the xts on columns except the 'date' column and order.by with 'date'

library(xts)
library(lubridate)
xts(NASD[-1], order.by = mdy_hm(NASD$date))
#                        open     high      low    close volume adjusted
#2016-04-01 14:30:00 48963818 48964272 48952363 48958789      0        0
#2016-04-01 14:31:00 48923579 48940259  4891752 48940259      0        0
#2016-04-01 14:32:00 48941753 48992466 48941753 48988589      0        0
#2016-04-01 14:33:00 48992227 48992227 48948281 48965469      0        0
#2016-04-01 14:34:00 48962915  4896418 48923838 48934326      0        0
#2016-04-01 14:35:00 48931196 48963301 48931196 48954341      0        0

data

NASD <- structure(list(date = c("04.01.2016 14:30", "04.01.2016 14:31", 
"04.01.2016 14:32", "04.01.2016 14:33", "04.01.2016 14:34", "04.01.2016 14:35"
), open = c(48963818L, 48923579L, 48941753L, 48992227L, 48962915L, 
48931196L), high = c(48964272L, 48940259L, 48992466L, 48992227L, 
4896418L, 48963301L), low = c(48952363L, 4891752L, 48941753L, 
48948281L, 48923838L, 48931196L), close = c(48958789L, 48940259L, 
48988589L, 48965469L, 48934326L, 48954341L), volume = c(0L, 0L, 
0L, 0L, 0L, 0L), adjusted = c(0L, 0L, 0L, 0L, 0L, 0L)), .Names = c("date", 
"open", "high", "low", "close", "volume", "adjusted"), row.names = c("1", 
"2", "3", "4", "5", "6"), class = c("tbl_df", "tbl", "data.frame"
))

Upvotes: 0

Related Questions