Importing Excel/Openoffice Dates into R
On Fri, 2 Jan 2004, Ashley Davies wrote:
/"I am puzzled: `csv' means `comma-separated values' and those files have no commas in them. You could use" / Yes sorry about that. The data posted was from the original csv files. I just copy and pasted a subsection from the spreadsheet as an example. /"(AB <- merge(A, B, by="row.names", all=T))"/ I did a "?merge" in R, and it said that this was for two dataframes. I would like to find a more general solution for a large number of variables with different dates. Financial time series dates are often dictated by the holidays of the respective stock/futures exchanges. This is a common problem I face.
merge() *is* the general solution: just apply it recursively.
/"Date <- as.POSIXct(strptime(as.character(AB$Row.names), "%m/%d/%Y"))
row.names(AB) <- Date
AB <- AB[sort.list(Date),-1]
AB"
/That looks like something I could use.
/"Second, as ts.union is not part of tseries, and is for regular time
series. I don't see how you hoped to use it."
/
I was inspired by the "get.hist.quote" function. From the help file for
"get.hist.quote"
x <- get.hist.quote(instrument = "^spc", start = "1998-01-01",
quote = "Close")
plot(x)
x <- get.hist.quote(instrument = "ibm", quote = c("Cl", "Vol"))
plot(x, main = "International Business Machines Corp")
spc <- get.hist.quote(instrument = "^spc", start = "1998-01-01")
ibm <- get.hist.quote(instrument = "ibm", start = "1998-01-01")
x <- na.remove(ts.union(spc, ibm))
plot(x, main = "IBM vs S&P 500")
This no longer works for me, BTW.
This example, downloads the data from yahoo, lines up the dates, and
It doesn't. It relies on those being regular series once get.hist.quote has finished with them. Take a closer look at get.hist.quote.
removes any NAs. I would like to be able to do the same for data that I have downloaded in my excel from a Reuters or Bloomberg terminal. I will play around further with what you have suggested later on tonight, and see if I can post back a solution. I'm in Singapore and at the end of the working day.
If you want a regular daily series with all non-trading days as NAs then the way get.hist.quote does it is the way forward. But that is not what you said you wanted ....
Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595