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

[Joshua Ulrich]

On Fri, Aug 11, 2023 at 3:22 PM Mike <mike9 using posteo.nl> wrote:
>
> First I convert a higher periodicity xts (e.g. day/hour) to a lower
> one (e.g. week/day). Then I calculate values (e.g. the mean of the
> low/high) on the lower periodicity xts.
>
> Now I like to transfer and expand the calculated low periodicity
> values into the corresponding bars of the high periodicity xts.
>
> Or less abstract: Project numbers of each week into the corresponding
> time frames (of mostly 5 bars) of the original daily xts.
>
> My minimal reproducible example:
>
> data(sample_matrix)
> x.daily <- as.xts(sample_matrix)
> # Limit sample to weekdays
> x.daily <- x.daily[.indexwday(x.daily) %in% 1:5]
> x.weekly <- to.weekly(x.daily, name=NULL)
> # Example calculation
> x.weekly <- cbind (x.weekly, rowMeans(x.weekly[,2:3]))
> colnames(x.weekly) <- c('O','H','L','C','M')
>
> > 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/
> ...
>
> How can I achieve that?
>
Thanks for the reproducible example. It makes it a lot easier to help!

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.

x.daily <- as.xts(sample_matrix)
# convert to Date index
index(x.daily) <- as.Date(index(x.daily))

Now you can merge your daily series with the weekly series and use
na.locf() to fill the gaps.

x.daily.new <- merge(x.daily, x.weekly$M, fill = na.locf)

The first 3 observations are NA because there's no observation for the
first day of the week at the beginning of the series. You can fill
those values backward by setting fromLast = TRUE)

x.daily.new <- na.locf(x.daily.new, fromLast = TRUE)
x.daily.new
##                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.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
## 2007-01-15 50.61724 50.68583 50.47359 50.48912 50.08217
##        ...
## 2007-06-18 47.43470 47.56336 47.36424 47.36424 47.33332
## 2007-06-19 47.46055 47.73353 47.46055 47.67220 47.33332
## 2007-06-20 47.71126 47.81759 47.66843 47.66843 47.33332
## 2007-06-21 47.71012 47.71012 47.61106 47.62921 47.33332
## 2007-06-22 47.56849 47.59266 47.32549 47.32549 47.57154
## 2007-06-25 47.20471 47.42772 47.13405 47.42772 47.57154
## 2007-06-26 47.44300 47.61611 47.44300 47.61611 47.57154
## 2007-06-27 47.62323 47.71673 47.60015 47.62769 47.57154
## 2007-06-28 47.67604 47.70460 47.57241 47.60716 47.57154
## 2007-06-29 47.63629 47.77563 47.61733 47.66471 47.45484



> 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