xts: Transfer/expand values to higher periodicity
On Sun, Aug 13, 2023 at 6:45?PM Mike <mike9 at posteo.nl> wrote:
On Sat, 12 Aug 2023 Joshua Ulrich <josh.m.ulrich at gmail.com> wrote:
On Fri, Aug 11, 2023 at 3:22?PM Mike <mike9 at posteo.nl> wrote:
head(x.weekly)
O H L C M
2007-01-05 50.03978 50.42188 49.95041 50.33459 50.18615
2007-01-12 50.03555 50.35980 49.80454 50.28519 50.08217
2007-01-19 50.61724 50.77336 50.40269 50.41278 50.58802
2007-01-26 50.36008 50.43875 49.94052 50.07024 50.18963
2007-02-02 49.85624 50.53490 49.76308 50.36928 50.14899
2007-02-09 50.52389 50.89683 50.45977 50.67686 50.67830
The new x.daily.new[,5] should look like this:
Open High Low Close M
2007-01-02 50.03978 50.11778 49.95041 50.11778 50.18615\
2007-01-03 50.23050 50.42188 50.23050 50.39767 50.18615|-Values
2007-01-04 50.42096 50.42096 50.26414 50.33236 50.18615| for week
2007-01-05 50.37347 50.37347 50.22103 50.33459 50.18615/
2007-01-08 50.03555 50.10363 49.96971 49.98806 50.08217\
2007-01-09 49.99489 49.99489 49.80454 49.91333 50.08217|
2007-01-10 49.91228 50.13053 49.91228 49.97246 50.08217|-Values
2007-01-11 49.88529 50.23910 49.88529 50.23910 50.08217| for week
2007-01-12 50.21258 50.35980 50.17176 50.28519 50.08217/
2007-01-15 50.61724 50.68583 50.47359 50.48912 50.58802\
2007-01-16 50.62024 50.73731 50.56627 50.67835 50.58802|
2007-01-17 50.74150 50.77336 50.44932 50.48644 50.58802|-Values
2007-01-18 50.48051 50.60712 50.40269 50.57632 50.58802| for week
2007-01-19 50.41381 50.55627 50.41278 50.41278 50.58802/
...
Note that the index for x.daily is POSIXct even though it's daily data. You need to make sure it's Date in your case or you'll have timezone issues with this approach.
Can you explain the timezone issues I can run into a bit more? I plan to run the function to be written for that in TZ EST5EDT.
The issue was with your initial example because
x.daily <- as.xts(sample_matrix)
creates an xts object with a POSIXct index with TZ=""
to.weekly() creates an xts object with a Date index, and TZ="UTC" for
all xts objects that have an index without a time component. So you
get this result when you merge the two:
head(x.daily.new <- merge(x.daily, x.weekly$M))
## Open High Low Close M
## 2007-01-02 00:00:00 50.03978 50.11778 49.95041 50.11778 NA
## 2007-01-03 00:00:00 50.23050 50.42188 50.23050 50.39767 NA
## 2007-01-04 00:00:00 50.42096 50.42096 50.26414 50.33236 NA
## 2007-01-04 18:00:00 NA NA NA NA 50.18615
## 2007-01-05 00:00:00 50.37347 50.37347 50.22103 50.33459 NA
## 2007-01-08 00:00:00 50.03555 50.10363 49.96971 49.98806 NA
This happens any time you merge xts objects with a timezone with xts
objects that don't have a timezone (i.e. Date, yearmon, yearqtr, etc.
because they have TZ="UTC").
In the case where you want to align >= daily data with intraday data,
you need to make sure your >= daily data index is POSIXct in the same
timezone. You can keep the timezone with to.weekly() and friends by
setting drop.time = FALSE. For example
dttm.hourly <- seq(as.POSIXct(start(x.daily)), by="hour", length.out=24*30)
y.hourly <- xts(seq_along(dttm.hourly), dttm.hourly)
y.daily <- to.daily(y.hourly, name = NULL, drop.time = FALSE)
tail(y.hourly.new <- merge(y.hourly, y.daily[, "Close"], fill =
na.locf), 30)
## y.hourly Close
## 2007-01-30 18:00:00 691 672
## 2007-01-30 19:00:00 692 672
## 2007-01-30 20:00:00 693 672
## 2007-01-30 21:00:00 694 672
## 2007-01-30 22:00:00 695 672
## 2007-01-30 23:00:00 696 696
## 2007-01-31 00:00:00 697 696
## 2007-01-31 01:00:00 698 696
## 2007-01-31 02:00:00 699 696
## 2007-01-31 03:00:00 700 696
## 2007-01-31 04:00:00 701 696
## 2007-01-31 05:00:00 702 696
## 2007-01-31 06:00:00 703 696
## 2007-01-31 07:00:00 704 696
## 2007-01-31 08:00:00 705 696
## 2007-01-31 09:00:00 706 696
## 2007-01-31 10:00:00 707 696
## 2007-01-31 11:00:00 708 696
## 2007-01-31 12:00:00 709 696
## 2007-01-31 13:00:00 710 696
## 2007-01-31 14:00:00 711 696
## 2007-01-31 15:00:00 712 696
## 2007-01-31 16:00:00 713 696
## 2007-01-31 17:00:00 714 696
## 2007-01-31 18:00:00 715 696
## 2007-01-31 19:00:00 716 696
## 2007-01-31 20:00:00 717 696
## 2007-01-31 21:00:00 718 696
## 2007-01-31 22:00:00 719 696
## 2007-01-31 23:00:00 720 720
Also, as I wrote in my OP
First I convert a higher periodicity xts (e.g. day/hour) to a lower one (e.g. week/day).
the function to be written should be a general approach, which not only accepts day as high periodicity and week as low, but also other relations e.g. hour and day. Doesn't that mean to better have both indexes as POSIXct?
Yes, as I showed above.
[...] x.daily.new
Your column 'M' is correct:
## Open High Low Close M ## 2007-01-02 50.03978 50.11778 49.95041 50.11778 50.18615 ## 2007-01-03 50.23050 50.42188 50.23050 50.39767 50.18615 ## 2007-01-04 50.42096 50.42096 50.26414 50.33236 50.18615 ## 2007-01-05 50.37347 50.37347 50.22103 50.33459 50.18615
But here column 'M' should be like x.weekly['2007-01-12',] (50.08217):
## 2007-01-08 50.03555 50.10363 49.96971 49.98806 50.18615 ## 2007-01-09 49.99489 49.99489 49.80454 49.91333 50.18615 ## 2007-01-10 49.91228 50.13053 49.91228 49.97246 50.18615 ## 2007-01-11 49.88529 50.23910 49.88529 50.23910 50.18615 ## 2007-01-12 50.21258 50.35980 50.17176 50.28519 50.08217
Here column 'M' should be like x.weekly['2007-01-19',] (50.58802):
## 2007-01-15 50.61724 50.68583 50.47359 50.48912 50.08217 ## ...
But this did it: index(x.weekly) <- as.POSIXct(index(x.weekly)) m <- merge(x=x.daily, y=x.weekly$M) res <- na.locf(m, fromLast=TRUE) What do you think about this approach? Can it cause timezone issues?
It could be a problem if x.weekly was created with to.weekly(..., drop.time = TRUE) because that would remove the timezone from the result. So make sure to use drop.time = FALSE as I did in my example above. Or it might work if you provide the appropriate value for the 'tz' argument to as.POSIXct().
head(res,20)
Open High Low Close M 2007-01-02 50.03978 50.11778 49.95041 50.11778 50.18615 2007-01-03 50.23050 50.42188 50.23050 50.39767 50.18615 2007-01-04 50.42096 50.42096 50.26414 50.33236 50.18615 2007-01-05 50.37347 50.37347 50.22103 50.33459 50.18615 2007-01-08 50.03555 50.10363 49.96971 49.98806 50.08217 2007-01-09 49.99489 49.99489 49.80454 49.91333 50.08217 2007-01-10 49.91228 50.13053 49.91228 49.97246 50.08217 2007-01-11 49.88529 50.23910 49.88529 50.23910 50.08217 2007-01-12 50.21258 50.35980 50.17176 50.28519 50.08217 2007-01-15 50.61724 50.68583 50.47359 50.48912 50.58802 2007-01-16 50.62024 50.73731 50.56627 50.67835 50.58802 2007-01-17 50.74150 50.77336 50.44932 50.48644 50.58802 2007-01-18 50.48051 50.60712 50.40269 50.57632 50.58802 2007-01-19 50.41381 50.55627 50.41278 50.41278 50.58802 2007-01-22 50.36008 50.43875 50.21129 50.21129 50.18963 2007-01-23 50.03966 50.16961 50.03670 50.16961 50.18963 2007-01-24 50.10953 50.26942 50.06387 50.23145 50.18963 2007-01-25 50.20738 50.28268 50.12913 50.24334 50.18963 2007-01-26 50.16008 50.16008 49.94052 50.07024 50.18963 2007-01-29 49.85624 49.93038 49.76308 49.91875 50.14899 Mike
_______________________________________________ R-SIG-Finance at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-sig-finance -- Subscriber-posting only. If you want to post, subscribe first. -- Also note that this is not the r-help list where general R questions should go.
Joshua Ulrich | about.me/joshuaulrich FOSS Trading | www.fosstrading.com