R › Rmetrics

moving averages on specific interval and merge

Classic

List

Threaded

5 messages Options

jim green

moving averages on specific interval and merge

Greetings!

I am using quantstrat and xts to do some intraday work and come up
with this problem. the xts object temp in the following example is
attached as and rda file.

> head(temp)
A.Open A.High A.Low A.Close A.Volume
2012-02-01 08:29:00 42.47 43.76 41.410 43.76 2071
2012-02-01 09:30:00 43.38 43.38 42.970 43.15 40300
2012-02-01 09:31:00 43.14 43.28 43.130 43.28 14990
2012-02-01 09:32:00 43.27 43.37 43.270 43.37 3300
2012-02-01 09:33:00 43.37 43.50 43.370 43.48 3056
2012-02-01 09:34:00 43.49 43.50 43.396 43.44 10968

> tail(temp)
A.Open A.High A.Low A.Close A.Volume
2012-03-27 16:07:00 45.6650 45.6650 45.6650 45.6650 170
2012-03-27 16:08:00 45.6710 45.6710 45.6710 45.6710 474
2012-03-27 16:10:00 45.9131 45.9131 45.9131 45.9131 1800
2012-03-27 16:13:00 45.6952 45.6952 45.6952 45.6952 300
2012-03-27 16:15:00 45.9368 45.9368 45.9368 45.9368 791
2012-03-27 16:21:00 45.7000 45.7000 45.7000 45.7000 22000

I would like to calculate moving averages of minute volume for
specific interval and merge with the original minute ohlc data.

take 09:40:00 for example, calculate the average previous 10 days
volume between 09:39:00 to 09:40:00 and merge with exiting data.

ultimately I want to get an xts object with columns

Open High Low Close Volume Average.Volume.at.current.interval
2012-03-27 16:07:00 45.6650 45.6650 45.6650 45.6650 170 177
2012-03-27 16:08:00 45.6710 45.6710 45.6710 45.6710 474 500
...
...
..
2012-03-27 16:21:00 45.7000 45.7000 45.7000 45.7000 22000 1000

any pointers are appreciated!

Jim.

_______________________________________________
[hidden email] 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.

a.rda (218K) Download Attachment

jim green

Re: moving averages on specific interval and merge

the question is more of r-help on second thought. I'll post on r-help. sorry.

Jim.

On 10 May 2012 21:11, Jim Green <[hidden email]> wrote:

> Greetings!
>
> I am using quantstrat and xts to do some intraday work and come up
> with this problem. the xts object temp in the following example is
> attached as and rda file.
>
>> head(temp)
> A.Open A.High A.Low A.Close A.Volume
> 2012-02-01 08:29:00 42.47 43.76 41.410 43.76 2071
> 2012-02-01 09:30:00 43.38 43.38 42.970 43.15 40300
> 2012-02-01 09:31:00 43.14 43.28 43.130 43.28 14990
> 2012-02-01 09:32:00 43.27 43.37 43.270 43.37 3300
> 2012-02-01 09:33:00 43.37 43.50 43.370 43.48 3056
> 2012-02-01 09:34:00 43.49 43.50 43.396 43.44 10968
>
>> tail(temp)
> A.Open A.High A.Low A.Close A.Volume
> 2012-03-27 16:07:00 45.6650 45.6650 45.6650 45.6650 170
> 2012-03-27 16:08:00 45.6710 45.6710 45.6710 45.6710 474
> 2012-03-27 16:10:00 45.9131 45.9131 45.9131 45.9131 1800
> 2012-03-27 16:13:00 45.6952 45.6952 45.6952 45.6952 300
> 2012-03-27 16:15:00 45.9368 45.9368 45.9368 45.9368 791
> 2012-03-27 16:21:00 45.7000 45.7000 45.7000 45.7000 22000
>
>
> I would like to calculate moving averages of minute volume for
> specific interval and merge with the original minute ohlc data.
>
> take 09:40:00 for example, calculate the average previous 10 days
> volume between 09:39:00 to 09:40:00 and merge with exiting data.
>
> ultimately I want to get an xts object with columns
>
> Open High Low Close Volume Average.Volume.at.current.interval
> 2012-03-27 16:07:00 45.6650 45.6650 45.6650 45.6650 170 177
> 2012-03-27 16:08:00 45.6710 45.6710 45.6710 45.6710 474 500
> ...
> ...
> ..
> 2012-03-27 16:21:00 45.7000 45.7000 45.7000 45.7000 22000 1000
>
> any pointers are appreciated!
>
> Jim.

_______________________________________________
[hidden email] 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.

Andreas Voellenklee

Re: moving averages on specific interval and merge

Hi,

try this:

require ("TTR")
volumeSMA <- SMA(temp[,"A.Volume"], n=10)
colnames(volumeSMA) <- c("Volume.at.current.interval")
ultimateXTS <- cbind(temp, volumeSMA)

Andreas

2012/5/12 Jim Green <[hidden email]>:

> the question is more of r-help on second thought. I'll post on r-help. sorry.
>
> Jim.
>
> On 10 May 2012 21:11, Jim Green <[hidden email]> wrote:
>> Greetings!
>>
>> I am using quantstrat and xts to do some intraday work and come up
>> with this problem. the xts object temp in the following example is
>> attached as and rda file.
>>
>>> head(temp)
>> A.Open A.High A.Low A.Close A.Volume
>> 2012-02-01 08:29:00 42.47 43.76 41.410 43.76 2071
>> 2012-02-01 09:30:00 43.38 43.38 42.970 43.15 40300
>> 2012-02-01 09:31:00 43.14 43.28 43.130 43.28 14990
>> 2012-02-01 09:32:00 43.27 43.37 43.270 43.37 3300
>> 2012-02-01 09:33:00 43.37 43.50 43.370 43.48 3056
>> 2012-02-01 09:34:00 43.49 43.50 43.396 43.44 10968
>>
>>> tail(temp)
>> A.Open A.High A.Low A.Close A.Volume
>> 2012-03-27 16:07:00 45.6650 45.6650 45.6650 45.6650 170
>> 2012-03-27 16:08:00 45.6710 45.6710 45.6710 45.6710 474
>> 2012-03-27 16:10:00 45.9131 45.9131 45.9131 45.9131 1800
>> 2012-03-27 16:13:00 45.6952 45.6952 45.6952 45.6952 300
>> 2012-03-27 16:15:00 45.9368 45.9368 45.9368 45.9368 791
>> 2012-03-27 16:21:00 45.7000 45.7000 45.7000 45.7000 22000
>>
>>
>> I would like to calculate moving averages of minute volume for
>> specific interval and merge with the original minute ohlc data.
>>
>> take 09:40:00 for example, calculate the average previous 10 days
>> volume between 09:39:00 to 09:40:00 and merge with exiting data.
>>
>> ultimately I want to get an xts object with columns
>>
>> Open High Low Close Volume Average.Volume.at.current.interval
>> 2012-03-27 16:07:00 45.6650 45.6650 45.6650 45.6650 170 177
>> 2012-03-27 16:08:00 45.6710 45.6710 45.6710 45.6710 474 500
>> ...
>> ...
>> ..
>> 2012-03-27 16:21:00 45.7000 45.7000 45.7000 45.7000 22000 1000
>>
>> any pointers are appreciated!
>>
>> Jim.
>
> _______________________________________________
> [hidden email] 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.

gsee

Re: moving averages on specific interval and merge

In reply to this post by jim green

Hi Jim,

We prefer if you share with us what you tried and what you've done to
attempt to solve the problem yourself. Anyway here's one way to
start.

library(quantmod) # for the Vo function as well as xts and zoo
load("~/Downloads/a.rda")
CalcMinuteVolume <- function(x) {
# This function will calculate the average of the volume for a given minute
#of every day
mvolm <- aggregate(Vo(x), format(index(x), "%H:%M"), mean)
xts(coredata(mvolm), as.POSIXct(paste(as.Date(end(x)), index(mvolm)),
format="%Y-%m-%d %H:%M"))
}
dtemp <- to.daily(temp)
index(dtemp) <- as.Date(index(dtemp)) # in case you have an old version of xts
# make strings to use to subset the data by 10 day periods
subsets <- rollapplyr(index(dtemp), 10, function(x)
paste0(first(x), "/", last(x)))
out <- lapply(subsets, function(ss) {
CalcMinuteVolume(temp[ss])
})
names(out) <- index(dtemp)[-c(1:9)] # days 1:9 were burned to calculate 1st mean
# Now you have a list of days. e.g.
out[["2012-02-15"]]
# Now merge
temp$Average.Volume.at.current.interval <- do.call(rbind, out)
temp
########################################

Things to consider:
- You almost certainly want to throw a lag in there.
- You have a different number of rows for different days.

Also, see ?agrregate.zoo or ?tapply, ?rollapply, ?period.apply, and ?do.call

HTH,
Garrett

On Thu, May 10, 2012 at 8:11 PM, Jim Green
<[hidden email]> wrote:

jim green

Re: moving averages on specific interval and merge

Thanks Garret, it worked correctly.

I found a similar question here at
http://r.789695.n4.nabble.com/Speed-optimization-on-minutes-distribution-calculation-td929537.html
and Jeff gave a very fast solution. Just for the reference.

Now I am attempting to write a custom indicator doing what you showed
to be used in quantstrat.

Jim.

On 12 May 2012 12:54, G See <[hidden email]> wrote:

> Hi Jim,
>
> We prefer if you share with us what you tried and what you've done to
> attempt to solve the problem yourself. Anyway here's one way to
> start.
>
> library(quantmod) # for the Vo function as well as xts and zoo
> load("~/Downloads/a.rda")
> CalcMinuteVolume <- function(x) {
> # This function will calculate the average of the volume for a given minute
> #of every day
> mvolm <- aggregate(Vo(x), format(index(x), "%H:%M"), mean)
> xts(coredata(mvolm), as.POSIXct(paste(as.Date(end(x)), index(mvolm)),
> format="%Y-%m-%d %H:%M"))
> }
> dtemp <- to.daily(temp)
> index(dtemp) <- as.Date(index(dtemp)) # in case you have an old version of xts
> # make strings to use to subset the data by 10 day periods
> subsets <- rollapplyr(index(dtemp), 10, function(x)
> paste0(first(x), "/", last(x)))
> out <- lapply(subsets, function(ss) {
> CalcMinuteVolume(temp[ss])
> })
> names(out) <- index(dtemp)[-c(1:9)] # days 1:9 were burned to calculate 1st mean
> # Now you have a list of days. e.g.
> out[["2012-02-15"]]
> # Now merge
> temp$Average.Volume.at.current.interval <- do.call(rbind, out)
> temp
> ########################################
>
> Things to consider:
> - You almost certainly want to throw a lag in there.
> - You have a different number of rows for different days.
>
> Also, see ?agrregate.zoo or ?tapply, ?rollapply, ?period.apply, and ?do.call
>
> HTH,
> Garrett
>
>
> On Thu, May 10, 2012 at 8:11 PM, Jim Green
> <[hidden email]> wrote:
>> Greetings!
>>
>> I am using quantstrat and xts to do some intraday work and come up
>> with this problem. the xts object temp in the following example is
>> attached as and rda file.
>>
>>> head(temp)
>> A.Open A.High A.Low A.Close A.Volume
>> 2012-02-01 08:29:00 42.47 43.76 41.410 43.76 2071
>> 2012-02-01 09:30:00 43.38 43.38 42.970 43.15 40300
>> 2012-02-01 09:31:00 43.14 43.28 43.130 43.28 14990
>> 2012-02-01 09:32:00 43.27 43.37 43.270 43.37 3300
>> 2012-02-01 09:33:00 43.37 43.50 43.370 43.48 3056
>> 2012-02-01 09:34:00 43.49 43.50 43.396 43.44 10968
>>
>>> tail(temp)
>> A.Open A.High A.Low A.Close A.Volume
>> 2012-03-27 16:07:00 45.6650 45.6650 45.6650 45.6650 170
>> 2012-03-27 16:08:00 45.6710 45.6710 45.6710 45.6710 474
>> 2012-03-27 16:10:00 45.9131 45.9131 45.9131 45.9131 1800
>> 2012-03-27 16:13:00 45.6952 45.6952 45.6952 45.6952 300
>> 2012-03-27 16:15:00 45.9368 45.9368 45.9368 45.9368 791
>> 2012-03-27 16:21:00 45.7000 45.7000 45.7000 45.7000 22000
>>
>>
>> I would like to calculate moving averages of minute volume for
>> specific interval and merge with the original minute ohlc data.
>>
>> take 09:40:00 for example, calculate the average previous 10 days
>> volume between 09:39:00 to 09:40:00 and merge with exiting data.
>>
>> ultimately I want to get an xts object with columns
>>
>> Open High Low Close Volume Average.Volume.at.current.interval
>> 2012-03-27 16:07:00 45.6650 45.6650 45.6650 45.6650 170 177
>> 2012-03-27 16:08:00 45.6710 45.6710 45.6710 45.6710 474 500
>> ...
>> ...
>> ..
>> 2012-03-27 16:21:00 45.7000 45.7000 45.7000 45.7000 22000 1000
>>
>> any pointers are appreciated!
>>
>> Jim.
>>
>> _______________________________________________
>> [hidden email] 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.