Importing Excel/Openoffice Dates into R
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