[R] Importing Excel/Openoffice Dates into R

[Prof Brian Ripley]

I am puzzled: `csv' means `comma-separated values' and those files have no 
commas in them.  You could use

A <- read.table("cboevix.csv")
B <- read.table("yenv.csv")

which looks better.  You can then merge the two dfs by

> (AB <- merge(A, B, by="row.names", all=T))
   Row.names   VIX YENV
1   1/1/1999 24.42 19.5
2  1/11/1999 25.46 21.6
3  1/12/1999 28.10 20.9
4  1/14/1999 32.98 19.3
5   1/4/1999 26.17 22.2
6   1/5/1999 24.46 23.2
7   1/6/1999 23.34 21.0
8   1/7/1999 24.37 20.2
9   1/8/1999 23.28   NA
10 1/13/1999    NA 19.1

and then convert to R's date format by 

Date <- as.POSIXct(strptime(as.character(AB$Row.names), "%m/%d/%Y"))
row.names(AB) <- Date
AB <- AB[sort.list(Date),-1]
AB
             VIX YENV
1999-01-01 24.42 19.5
1999-01-04 26.17 22.2
1999-01-05 24.46 23.2
1999-01-06 23.34 21.0
1999-01-07 24.37 20.2
1999-01-08 23.28   NA
1999-01-11 25.46 21.6
1999-01-12 28.10 20.9
1999-01-13    NA 19.1
1999-01-14 32.98 19.3

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.  You could for example use

irts(row.names(AB), as.matrix(AB))

to create an object of class "irts", or you could use the `its' package
from CRAN (which is what I would use).



On Fri, 2 Jan 2004, Ashley Davies wrote:

> Hi,
> 
> I would like to import some daily financial data from excel via csv. 
> More specifically, I would like to be able to use the ts.union function 
> from the tseries library as the dates are irregular and I need to line 
> up the dates so that I can place all the variables into one data frame.
> 
> The trouble is, how do I import the dates from excel into R? At the 
> moment I'm just importing the data via read.csv, but I don't know how to 
> bring the dates in as well.
> 
> Example: Here are two csv files.  The first file is missing Jan 13th and 
>   the second is missing the 8th.
> 
> file 1: cboevix.csv
> 	          VIX
> 1/1/1999	24.42
> 1/4/1999	26.17
> 1/5/1999	24.46
> 1/6/1999	23.34
> 1/7/1999	24.37
> 1/8/1999	23.28
> 1/11/1999	25.46
> 1/12/1999	28.1
> 1/14/1999	32.98
> 
> file 2: yenv.csv
> 
> 		YENV
> 1/1/1999	19.5
> 1/4/1999	22.2
> 1/5/1999	23.2
> 1/6/1999	21
> 1/7/1999	20.2
> 1/11/1999	21.6
> 1/12/1999	20.9
> 1/13/1999	19.1
> 1/14/1999	19.3
> 
> # Read the files in via read.csv
> A<-read.csv("cboevix.csv",skip=1,header=FALSE)
> B<-read.csv("yenv.csv",skip=1,header=FALSE)
> 
> #define variables
> VIX<-A$V2
> YENV<-B$V2
> 
> # MISSING STEP!
> #apply dates from original csv files to the variables.
> #the dates are stilling sitting in A$V1 and B$V1
> #how do I apply them to VIX and YENV?
> #????
> 
> #use ts.union function to line up the dates and create data frame 
> #"vixyen" with lined up data
> 
> vixyen<-ts.union(VIX,YENV)
> 
> Can anyone help me fill in those missing steps?  Thanks very much!
> 
> Cheers,
> 
> 

-- 
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