[R-SIG-Finance] xts: Transfer/expand values to higher periodicity

[Mike]

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.

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?

> [...]
> 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?

> 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