R - Next highest value in a time series

Question

A relatively simple question, but one I can't seem to find any examples.

I have simple forex price data which is in a 2 column xts object called subx1:

Datetime, Price   
2016-09-01 00:00:01, 1.11563  
2016-09-01 00:00:01, 1.11564  
2016-09-01 00:00:02, 1.11564  
2016-09-01 00:00:03, 1.11565

... and so forth.

I'm trying to find the first time after 2pm when the price goes higher than the pre-2pm high which is held in another object's column called daypeakxts$before2.High and

Where a sample of daypeakxts is:

Date, before2.High  
2016-09-01, 1.11567    
2016-09-02, 1.11987

This is a bad example of what I'm trying to do:

subxresult <- index(subx1, subx1$datetime > daypeakxts$before2.High)

... so I'm looking to discover a datetime for a price using a conditional statement with a day's value in another xts object.

Try using something like `which.max(time > 2PM & value > earlierValue)` with the appropriate translations. — lmo, Apr 25 '17 at 15:48
Thank you. I had to convert to a regular data.frame and then tried: subxresult <- which.max(subx1$dt == daypeakxts$date & subx$price > daypeakxts$before2.High) but received an error : "longer object length is not a multiple of shorter object length" I'm using a date in one instance (for the highest pre-2pm price for that day) but a date-time for the main time series. Both are in POSIX format though. — nycrefugee, Apr 25 '17 at 16:31

score 2 · Accepted Answer · edited May 23 '17 at 12:18

You didn't provide enough data for a reproducible example, so I'm going to use some daily data that comes with the xts package.

library(xts)
data(sample_matrix)
x <- as.xts(sample_matrix, dateForamt = "Date")

# Aggregate and find the high for each week
Week.High <- apply.weekly(x, function(x) max(x$High))

# Finding the pre-2pm high would be something like:
# Pre.2pm.High <- apply.daily(x["T00:00/T14:00"], function(x) max(x$High))

# Merge the period high with the original data, and
# fill NA with the last observation carried forward
y <- merge(x, Week.High, fill = na.locf)

# Lag the period high, so it aligns with the following period
y$Week.High <- lag(y$Week.High)

# Find the first instance where the next period's high
# is higher than the previous period's high
y$First.Higher <- apply.weekly(y, function(x) which(x$High > x$Week.High)[1])

I tried your solution. It worked for you example - and have pretty much adapted. — nycrefugee, Apr 29 '17 at 20:51

R - Next highest value in a time series

1 Answers1

Linked