Ramon Hörler Ramon Hörler - 3 years ago 167
R Question

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.

Answer Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download