[R] Importing Excel/Openoffice Dates into R

[Prof Brian Ripley]

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