[R-SIG-Finance] xts: Transfer/expand values to higher periodicity
Joshua Ulrich
jo@h@m@u|r|ch @end|ng |rom gm@||@com
Mon Aug 14 21:32:35 CEST 2023
On Sun, Aug 13, 2023 at 6:45 PM Mike <mike9 using posteo.nl> wrote:
>
> On Sat, 12 Aug 2023 Joshua Ulrich <josh.m.ulrich using gmail.com> wrote:
>
> > On Fri, Aug 11, 2023 at 3:22 PM Mike <mike9 using 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 using 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
More information about the R-SIG-Finance
mailing list