[R] Importing Excel/Openoffice Dates into R

Prof Brian Ripley ripley at stats.ox.ac.uk
Fri Jan 2 11:23:01 CET 2004

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

More information about the R-help mailing list