Reputation: 1305
Correct way to convert data frame to XTS to be used in PortfolioAnalytics package
I am using packages tidyverse and tidyquant to convert a data frame to a XTS object.
library(tidyverse)
library(tidyquant)
xts <- na.omit(as.data.frame(new.df))
xts <- as_xts(xts, Date)
It seems successful:
> str(xts)
An ‘xts’ object on 1981-01-29/2017-06-28 containing:
Data: chr [1:9184, 1:36] "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" "$SPX" ...
- attr(*, "dimnames")=List of 2
..$ : NULL
..$ : chr [1:36] "Ticker" "Open" "High" "Low" ...
Indexed by objects of class: [Date] TZ: UTC
xts Attributes:
NULL
xts object as printed
Next I am looking to load this into the performance analystic package. I extract the column I want to load into the package.
data=xts[,35]
Then str(data) shows again a xts object.
> str(data)
An ‘xts’ object on 1981-01-29/2017-06-28 containing:
Data: chr [1:9184, 1] "-7.671475e-02" "-5.297988e-01" "-2.037823e+00" " 1.221346e+00" " 1.011910e-01" " 8.087721e-01" " 7.482913e-01" ...
- attr(*, "dimnames")=List of 2
..$ : NULL
..$ : chr "long_exit_eq"
Indexed by objects of class: [Date] TZ: UTC
xts Attributes:
NULL
This is still a xts object with a date and column, and head(data):
> head(data)
long_exit_eq
1981-01-29 "-7.671475e-02"
1981-01-30 "-5.297988e-01"
1981-02-02 "-2.037823e+00"
1981-02-03 " 1.221346e+00"
1981-02-04 " 1.011910e-01"
1981-02-05 " 8.087721e-01"
For portfolio analytics:
> # create table of downside risk estimates
> table.DownsideRisk(data)
Error in MAR - r : non-numeric argument to binary operator
In addition: Warning message:
In mean.default(coredata(x), ...) :
argument is not numeric or logical: returning NA
> # chart equity curve, daily performance, and drawdowns
> charts.PerformanceSummary(data)
Error in na.omit.xts(x) : unsupported type
The error above I am not sure why they are occurring. The na.omit.xts... there are no na values in the data set. I removed them at the start when i was working with a data frame using: xts <- na.omit(as.data.frame(new.df))
dataframe to xts object appears to be working, pulling the column i want to use in portfolio analytics appears to be working.
Just to show your the head of my xts object:
> head(xts)
Ticker Open High Low Close Volume Open.Interest roll.mean.n3 roll.mean.n5 roll.mean.n7
1981-01-29 "$SPX" " 130.34" " 131.78" " 128.97" " 130.24" " 26900000" "0" " 130.5667" " 130.354" " 130.4843"
1981-01-30 "$SPX" " 130.24" " 131.65" " 128.61" " 129.55" " 29100000" "0" " 130.0433" " 130.218" " 130.2257"
1981-02-02 "$SPX" " 129.48" " 129.48" " 125.82" " 126.91" " 29100000" "0" " 128.9000" " 129.632" " 129.7471"
1981-02-03 "$SPX" " 126.91" " 128.92" " 125.89" " 128.46" " 33400000" "0" " 128.3067" " 129.100" " 129.4943"
1981-02-04 "$SPX" " 128.46" " 129.71" " 127.29" " 128.59" " 32600000" "0" " 127.9867" " 128.750" " 129.3157"
1981-02-05 "$SPX" " 128.59" " 130.49" " 127.99" " 129.63" " 33900000" "0" " 128.8933" " 128.628" " 129.1029"
roll.mean.n10 roll.mean.n12 roll.mean.n14 roll.mean.n17 roll.mean.n20 roll.sd.n3 roll.sd.n5 roll.sd.n7 roll.sd.n10
1981-01-29 " 131.418" " 131.8225" " 132.0486" " 132.3706" " 133.1365" " 0.48179878" " 0.4689130" " 0.5449115" " 1.7603142"
1981-01-30 " 130.896" " 131.4958" " 131.7650" " 132.0453" " 132.7970" " 0.43015730" " 0.5954159" " 0.4864105" " 1.3911236"
1981-02-02 " 130.150" " 130.8867" " 131.3093" " 131.6835" " 132.2440" " 1.75758738" " 1.6202694" " 1.3422092" " 1.3195694"
1981-02-03 " 129.831" " 130.3608" " 130.9514" " 131.3882" " 131.7610" " 1.32666306" " 1.4357411" " 1.4014972" " 1.3021291"
1981-02-04 " 129.554" " 129.8792" " 130.5493" " 131.0982" " 131.4365" " 0.93468608" " 1.2604983" " 1.4294622" " 1.2335503"
1981-02-05 " 129.491" " 129.7108" " 130.1821" " 130.8829" " 131.2650" " 0.64127139" " 1.0994631" " 1.2101207" " 1.2093379"
roll.sd.n12 roll.sd.n14 roll.sd.n17 roll.sd.n20 roll.z.score.n3 roll.z.score.n5 roll.z.score.n7 roll.z.score.n10
1981-01-29 " 1.8583186" " 1.8039775" " 1.8166778" " 2.5260546" "-6.780009e-01" "-2.430984e-01" "-4.482851e-01" "-0.6691924632"
1981-01-30 " 1.8867068" " 1.8658535" " 1.7961940" " 2.5291296" "-1.146875e+00" "-1.121908e+00" "-1.389185e+00" "-0.9675624607"
1981-02-02 " 2.0957110" " 2.2117916" " 2.1612750" " 2.5475899" "-1.132236e+00" "-1.679969e+00" "-2.113787e+00" "-2.4553457398"
1981-02-03 " 1.8040970" " 2.2404173" " 2.2419181" " 2.2761866" " 1.155832e-01" "-4.457586e-01" "-7.379812e-01" "-1.0528840651"
1981-02-04 " 1.3511217" " 2.1100825" " 2.2676411" " 2.2404529" " 6.454894e-01" "-1.269371e-01" "-5.076854e-01" "-0.7814849363"
1981-02-05 " 1.2309297" " 1.7326141" " 2.2197845" " 2.2409161" " 1.148754e+00" " 9.113521e-01" " 4.356094e-01" " 0.1149372685"
roll.z.score.n12 roll.z.score.n14 roll.z.score.n17 roll.z.score.n20 ocret clret sig_long long_exit_eq
1981-01-29 "-8.515704e-01" "-1.002540e+00" "-1.1727875453" "-1.146645e+00" "-7.671475e-02" "-7.671475e-02" "1" "-7.671475e-02"
1981-01-30 "-1.031338e+00" "-1.187124e+00" "-1.3892112666" "-1.283841e+00" "-5.297988e-01" "-5.297988e-01" "1" "-5.297988e-01"
1981-02-02 "-1.897526e+00" "-1.989014e+00" "-2.2086630159" "-2.093743e+00" "-1.984863e+00" "-2.037823e+00" "1" "-2.037823e+00"
1981-02-03 "-1.053615e+00" "-1.112033e+00" "-1.3061247727" "-1.450228e+00" " 1.221346e+00" " 1.221346e+00" "1" " 1.221346e+00"
1981-02-04 "-9.541455e-01" "-9.285351e-01" "-1.1060986231" "-1.270502e+00" " 1.011910e-01" " 1.011910e-01" "1" " 1.011910e-01"
1981-02-05 "-6.566920e-02" "-3.186762e-01" "-0.5644427193" "-7.296121e-01" " 8.087721e-01" " 8.087721e-01" "1" " 8.087721e-01"
cum_eq_long_exit
1981-01-29 " -1.06592882"
1981-01-30 " -1.59572761"
1981-02-02 " -3.63355084"
1981-02-03 " -2.41220501"
1981-02-04 " -2.31101398"
1981-02-05 " -1.50224192"
What on earth am i missing here??
EDIT**
This is the format of my new.df prior to converting to xts....
> str(new.df)
'data.frame': 9203 obs. of 37 variables:
$ Date : Date, format: "1981-01-02" "1981-01-05" "1981-01-06" "1981-01-07" ...
$ Ticker : chr "$SPX" "$SPX" "$SPX" "$SPX" ...
$ Open : num 136 136 138 136 135 ...
$ High : num 137 139 140 136 136 ...
$ Low : num 135 136 136 132 132 ...
$ Close : num 136 138 138 135 133 ...
$ Volume : num 19400000 41200000 49000000 58800000 39400000 35900000 34600000 28500000 28900000 27100000 ...
$ Open.Interest : int 0 0 0 0 0 0 0 0 0 0 ...
$ roll.mean.n3 : num 0 0 137 137 135 ...
$ roll.mean.n5 : num 0 0 0 0 136 ...
$ roll.mean.n7 : num 0 0 0 0 0 ...
$ roll.mean.n10 : num 0 0 0 0 0 ...
$ roll.mean.n12 : num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.mean.n14 : num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.mean.n17 : num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.mean.n20 : num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.sd.n3 : num 0 0 0.987 1.713 2.547 ...
$ roll.sd.n5 : num 0 0 0 0 2.12 ...
$ roll.sd.n7 : num 0 0 0 0 0 ...
$ roll.sd.n10 : num 0 0 0 0 0 ...
$ roll.sd.n12 : num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.sd.n14 : num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.sd.n17 : num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.sd.n20 : num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.z.score.n3 : num 0 0 0.652 -1.154 -0.927 ...
$ roll.z.score.n5 : num 0 0 0 0 -1.44 ...
$ roll.z.score.n7 : num 0 0 0 0 0 ...
$ roll.z.score.n10: num 0 0 0 0 0 ...
$ roll.z.score.n12: num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.z.score.n14: num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.z.score.n17: num 0 0 0 0 0 0 0 0 0 0 ...
$ roll.z.score.n20: num 0 0 0 0 0 0 0 0 0 0 ...
$ ocret : num 0.427 1.196 0.109 -0.691 -1.495 ...
$ clret : num 0 1.196 0.109 -2.201 -1.495 ...
$ sig_long : num [1:9203, 1] 0 0 0 0 0 0 0 0 0 0 ...
..- attr(*, "dimnames")=List of 2
.. ..$ : NULL
.. ..$ : chr "Lag.1"
$ long_exit_eq : num 0 0 0 0 0 0 0 0 0 0 ...
$ cum_eq_long_exit: num 0 0 0 0 0 0 0 0 0 0 ...
Upvotes: 0
Views: 2882
Answers (2)
Reputation: 1305
I found the solution. When converting an entire data frame to XTS. Even if the original formats of the data frame are correct, ie date = date and numbers = num. The XTS conversion will cause the formats to change. Hence why numbers were changing to characters thus causing the error with PortfolioAnalytics package.
The answer here is to pull specific column out of the data frame. Convert that to XTS whilst maintain the correct format during the process.
The below retains the number format while converting from data frame to XTS:
xts = xts(new.df$long_exit_eq, order.by=as.Date(new.df$Date, format="%m/%d/%Y"))
And if you want to pull two columns from the data frame. Lets say its your signal return column and buy and hold return column.
Can pull them both from the data frame like below as xts and then bind them together.
# Pull select columns from data frame to make XTS whilst retaining formats
xts = xts(new.df$long_exit_eq, order.by=as.Date(new.df$Date, format="%m/%d/%Y"))
xts1 = xts(new.df$clret, order.by=as.Date(new.df$Date, format="%m/%d/%Y"))
# Join XTS together
compare <- cbind(xts,xts1)
Upvotes: 1
Reputation: 178
You're receiving the unsupported type error because your data are characters. The structure call (str) shows this as Data: chr ... You can also tell this by looking at your output, the quotation marks around your numbers indicate they are the characters. You need numeric values for the charts.PerformanceSummary function. I'm not sure what your dataframe looks like but either try converting the data frame value to numeric values, see as.numeric, or pull in your data as numeric values to begin with and avoid the dataframe alltogether. The getSymbols function in Quantmod can pull in financial data as an xts.
https://stat.ethz.ch/R-manual/R-devel/library/base/html/numeric.html http://www.quantmod.com/documentation/getSymbols.html
Upvotes: 2
Related Questions
- Converting a data frame to xts
- Xts format of data.frame
- How to convert a data.frame to a xts in R?
- Convert Dataframe object into xts
- Convert xts to data.frame
- Converting an xts object to a useful data frame
- R - Converting downloaded data frame to xts
- How to put xts columns in to data frame
- How can i convert a dataframe with a factor column to a xts object?
- data.frame object to xts object conversion in R