CODEWITHSUNDEEP
rxtszoo
Andy
Andy

Reputation: 5

Creating a simple zoo series from YYYY-MM-TT to YYYY-MM-TT

can you help me for the following question? I have a large excel-file which looks like that:

Click here to view a screenshot for the excel file

Imagine these are the stock prices of four different companies. On the first column you see the dates of a few days of 2010 in YY.MM.DD and on the first row you see the company-no. like 101, 102 ... So company 101 had a stock price of 4,555 dollar on the first day of the year.

Now I have to create a zoo series in R for the eventstudies package.

Click here to view a screenshot for the description

What's the fastest way to create a format like these? I had a lot of tries but I didn't get the right solution. The values has to look like these:

Click here to view a screenshot for the final zoo series

The date has now the format YYYY-MM-DD. As you see we have in this zoo series some values with minus. So it shows the changes from day 1 to day 2.

My final zoo series would show for company 101 a value of +1 for every day starting in 2010-01-04.

Upvotes: 0

Views: 219

Answers (3)

friendimaginary
friendimaginary

Reputation: 1

I think the easiest way is to combine as.xts() with seq.Date(), like this:

startdate <- as.Date("2010-01-01")
enddate <- as.Date("2010-12-31")
timeseries <- as.xts(x = seq.Date(from = startdate, to = enddate, by = "1 day"))

EDIT

Perhaps this is closer to what you want:

x <- readxl::read_excel("filename.xlsx")
x$X__1 <- lubridate::dmy(x$X__1)
as.zoo(x)

Upvotes: 0

Zafar
Zafar

Reputation: 2016

Simple:

library(zoo)
library(openxlsx)

sheet <- read.xlsx("<path to your excel doc.xlsx>")
dates <- as.Date(sheet[[1]], format = "%m.%d.%y") 
# if this doesn't work, replace 1 with your column name

startdate <- min(dates)
enddate <- max(dates)
dates <- seq(startdate, enddate, by = "day")
dates_zoo <- as.xts(dates)

Upvotes: 0

Melissa Key
Melissa Key

Reputation: 4551

to convert the dates, use 

new_data_variable <- as.Date(old_date_variable, "%m.%d.%y")

If you're looking to reshape the data as well, there are many options available, but I'm not certain what you need in that respect.

Upvotes: 0

Related Questions