I have a large time-series file that I imported from my working directory and then turn them into log returns by:
read.csv("/Volumes/3TB/ALLsince1996.csv",header=T)-> ALL
all <- xts(ALL[,2:dim(ALL)[2]], order.by= as.POSIXct(ALL[,1], format="%m/%d/%y"))
RETS <- CalculateReturns(all, method= c("log"))
RETS<- na.locf(RETS)
RETS[is.na(RETS)] <- 0
I then download the 3-Month Treaury via FRED by:
# 3-Mo Treasury
data <- new.env()
FEDs <- c( "DGS3MO") # DGS3MO : 3-Mo Treasury Constant maturity
getSymbols( FEDs
, src = "FRED"
, env = data
)
data$DGS3MO -> TB3
TB3/100/365 -> TB3
na.locf(TB3["1996-01-01::"])-> TB3
I then try to combine the log returns series with the 3-month treasury using cbind() and get the following:
both <- cbind(RETS[,1], TB3)
both:
row.names ZX.Adjusted DGS3MO
1 1995-12-31 16:00:00 NA NA
2 1996-01-01 00:00:00 0 NA
3 1996-01-01 16:00:00 NA 0.0001424658
4 1996-01-02 00:00:00 0 NA
5 1996-01-02 16:00:00 NA 0.0001424658
6 1996-01-03 00:00:00 0 NA
7 1996-01-03 16:00:00 NA 0.0001421918
8 1996-01-04 00:00:00 0 NA
9 1996-01-04 16:00:00 NA 0.0001421918
But this returns a vector with two-times per day; such as 1996-01-01 00:00:00 and 1996-01-01 16:00:00 . What I would like is to combine the two by date not by time.
REPRODUCIBLE DATA:
#Pull Data from getSymbols()
library(quantmod)
dataset<- xts()
symbols <- c( "GLD", "IWM", "SPY", "GS")
system.time(
for(i in 1:length(symbols)) {
symbols[i]-> symbol
tryit <- try(getSymbols(symbol, from="1995-12-31", src='yahoo'))
if(inherits(tryit, "try-error")){
i <- i+1
} else {
data <- getSymbols(symbol, from="1995-12-31", src='yahoo')
dataset <- merge(dataset, Ad(get(symbols[i])))
rm(symbol)
}
}
)
Because it was a large file I saved dataset and index(dataset) in two separate files as I could not save the index with the dataset
write.csv(dataset, "dataset.csv")
write.csv(index(dataset), "index.csv")
I later opened the index.csv file in Excel & manually pasted the index to dataset.csv & saved the file.I later tried to reopen the .csv unto my workspace & calculate log returns
read.csv("dataset.csv",header=T)-> ALL
all <- xts(ALL[,2:dim(ALL)[2]], order.by= as.POSIXct(ALL[,1], format="%m/%d/%y"))
RETS <- CalculateReturns(all, method= c("log"))
RETS<- na.locf(RETS)
RETS[is.na(RETS)] <- 0
Next Download the 3-Month T-Bill, same code as above...
# 3-Mo Treasury
data <- new.env()
FEDs <- c( "DGS3MO") # DGS3MO : 3-Mo Treasury Constant maturity
getSymbols( FEDs
, src = "FRED"
, env = data
)
data$DGS3MO -> TB3
TB3/100/365 -> TB3
na.locf(TB3["1996-01-01::"])-> TB3
Now try to combine the RETS1 with TB3...
both <- cbind(RETS1, TB3)
@Rime, to reformat the index without the time information use the strptime function and later merge the series as suggested above.
index(dataset) <- strptime(index(dataset),"%Y-%m-%d")
A much easier and more elegant way to accomplish what you are trying to do is to use the makeReturnFrame function using the fantastic qmao-package (https://r-forge.r-project.org/R/?group_id=1113) with a lot of utility and helper function for this kind of stuff.
library(quantmod)
library(qmao)
symbols <- c( "GLD", "IWM", "SPY", "GS")
getSymbols(symbols, from="1995-12-31", src='yahoo')
rets <- makeReturnFrame(symbols,silent = TRUE)
FEDs <- c( "DGS3MO") # DGS3MO : 3-Mo Treasury Constant maturity
data <- new.env()
getSymbols( FEDs
, src = "FRED"
, env = data
)
data$DGS3MO -> TB3
TB3/100/365 -> TB3
na.locf(TB3["1996-01-01::"])-> TB3
series.merged <- merge(rets,TB3,join = "inner")
> tail(series.merged)
GLD IWM SPY GS DGS3MO
2014-08-07 4.050035e-03 -0.004844797 -0.005429405 -0.0037775986 8.219178e-07
2014-08-08 7.924872e-05 0.009666235 0.011502456 0.0185147075 8.219178e-07
2014-08-11 -1.824311e-03 0.009485466 0.002893760 0.0011603622 1.095890e-06
2014-08-12 2.381425e-04 -0.006905738 -0.001394160 -0.0007540822 8.219178e-07
2014-08-13 1.665411e-03 0.007787650 0.006746170 0.0002320859 1.095890e-06
2014-08-14 8.712527e-04 0.001497468 0.004710710 0.0020863525 1.095890e-06