0

Studying R (including xts and quantmod packages). There is my dataset:

str(h2)
‘zoo’ series from 2016-06-15 11:00:00 to 2016-09-15 14:00:00
  Data: num [1:928, 1:5] 67842 67486 67603 67465 67457 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:5] "X.OPEN." "X.HIGH." "X.LOW." "X.CLOSE." ...
  Index:  POSIXct[1:928], format: "2016-06-15 11:00:00" "2016-06-15 12:00:00" "2016-06-15 13:00:00" ...

first(h2, '1 day')
                    X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.
2016-06-15 11:00:00   67842   67842  67122    67488 262740
2016-06-15 12:00:00   67486   67610  67420    67603 288875
2016-06-15 13:00:00   67603   67608  67381    67466 323498
2016-06-15 14:00:00   67465   67484  67356    67455 168991
2016-06-15 15:00:00   67457   67460  67289    67361 174965
2016-06-15 16:00:00   67363   67381  67202    67317 195579
2016-06-15 17:00:00   67320   67465  67288    67397 230255
2016-06-15 18:00:00   67397   67436  67084    67099 469379
2016-06-15 19:00:00   67096   67198  66900    67058 264430
2016-06-15 20:00:00   67040   67094  66944    67092 110503
2016-06-15 21:00:00   67092   67158  66877    66992  83041
2016-06-15 22:00:00   66993   67110  66680    66909 386905
2016-06-15 23:00:00   66909   67269  66884    67126 143373

dput(first(h2, '1 day'))
structure(c(67842, 67486, 67603, 67465, 67457, 67363, 67320, 
67397, 67096, 67040, 67092, 66993, 66909, 67842, 67610, 67608, 
67484, 67460, 67381, 67465, 67436, 67198, 67094, 67158, 67110, 
67269, 67122, 67420, 67381, 67356, 67289, 67202, 67288, 67084, 
66900, 66944, 66877, 66680, 66884, 67488, 67603, 67466, 67455, 
67361, 67317, 67397, 67099, 67058, 67092, 66992, 66909, 67126, 
262740, 288875, 323498, 168991, 174965, 195579, 230255, 469379, 
264430, 110503, 83041, 386905, 143373), .Dim = c(13L, 5L), .Dimnames = list(
    NULL, c("X.OPEN.", "X.HIGH.", "X.LOW.", "X.CLOSE.", "X.VOL."
    )), index = structure(c(1465977600, 1465981200, 1465984800, 
1465988400, 1465992000, 1465995600, 1465999200, 1466002800, 1466006400, 
1466010000, 1466013600, 1466017200, 1466020800), class = c("POSIXct", 
"POSIXt"), tzone = ""), class = "zoo")

Can't figure out how to solve, for example, such task - to compare the sign of the difference (X.CLOSE-X.OPEN) at 11:00 and the difference (X.CLOSE(13:00)-X.OPEN(12:00)) on all days included in the sample.
During solving this problem I see 2 items:
1). how access to data at specific time? I.e. how for example get X.OPEN at 12:00 day I choose. I try different combinations (see code below) but no result (only title of dataset)

h2["T11:00:00/12:00:00"]
     X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.
h2["2016-06-15 11:00:00 MSK"]
     X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.
x <- h2["2016-06-15 11:00:00 MSK"]        #'zoo' series (without observations)
x
     X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.
h2["T12:00:00.000/T12:00:00.001"]
     X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.

2). Earlier I made similar algorithm in Excel and solve this problem by bruteforce (step by step checking) dataset. But R have vectorial data types and it should be faster and more convenient way to solve this task.

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
Dmitry
  • 3
  • 2
  • Welcome to Stack Overflow! Please make sure your question contains a [minimally reproducible example](http://stackoverflow.com/a/5963610/2477097), which will make it easier for community members to try to answer it. In this case, it would be useful to post the output of `dput(first(h2, '1 day'))`. Also, in general, avoid including prompts (e.g. `>`) in your code snippets, as this inhibits the ability to paste this code into a console to be run. Instead, you should paste code directly (no prompt) and comment out the output. – Alexey Shiklomanov Apr 17 '17 at 14:01
  • Thanks for your notes Alexey! I improve my question as you pointed out. – Dmitry Apr 18 '17 at 09:52

1 Answers1

0

To get data at a specific time from a time series, use window.

window(h2, start = as.POSIXct('2016-06-15 04:00'), end = as.POSIXct('2016-06-15 04:59'))
#                     X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.
# 2016-06-15 04:00:00   67842   67842  67122    67488 262740

window(h2, start = as.POSIXct('2016-06-15 06:00'), end = as.POSIXct('2016-06-15 08:00'))
#                     X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.
# 2016-06-15 06:00:00   67603   67608  67381    67466 323498
# 2016-06-15 07:00:00   67465   67484  67356    67455 168991
# 2016-06-15 08:00:00   67457   67460  67289    67361 174965

You can also access by row number.

h2[1,]
#                     X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.
# 2016-06-15 04:00:00   67842   67842  67122    67488 262740

h2[4:6,]
#                     X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL.
# 2016-06-15 07:00:00   67465   67484  67356    67455 168991
# 2016-06-15 08:00:00   67457   67460  67289    67361 174965
# 2016-06-15 09:00:00   67363   67381  67202    67317 195579

To take the difference between X.CLOSE. and X.OPEN. at all times, you can do...

h2_diff <- h2[,'X.CLOSE.'] - h2[,'X.OPEN.']
# 2016-06-15 04:00:00 2016-06-15 05:00:00 2016-06-15 06:00:00 2016-06-15 07:00:00 
#                -354                 117                -137                 -10 
# 2016-06-15 08:00:00 2016-06-15 09:00:00 2016-06-15 10:00:00 2016-06-15 11:00:00 
#                 -96                 -46                  77                -298 
# 2016-06-15 12:00:00 2016-06-15 13:00:00 2016-06-15 14:00:00 2016-06-15 15:00:00 
#                 -38                  52                -100                 -84 
# 2016-06-15 16:00:00 
#                 217

To do a lagged difference, you can use the lag function.

h2[1:3,'X.CLOSE.']
# 2016-06-15 04:00:00 2016-06-15 05:00:00 2016-06-15 06:00:00 
#               67488               67603               67466 

lag(h2[1:3,'X.CLOSE.'])
# 2016-06-15 04:00:00 2016-06-15 05:00:00 
#               67603               67466 

lag(h2[1:3,'X.CLOSE.'], -1)
# 2016-06-15 05:00:00 2016-06-15 06:00:00 
#               67488               67603

So to do X.CLOSE(n) - X.OPEN(n - 1)...

h2[,'X.CLOSE.'] - lag(h2[,'X.OPEN.'], -1)
# 2016-06-15 05:00:00 2016-06-15 06:00:00 2016-06-15 07:00:00 2016-06-15 08:00:00 
#                -239                 -20                -148                -104 
# 2016-06-15 09:00:00 2016-06-15 10:00:00 2016-06-15 11:00:00 2016-06-15 12:00:00 
#                -140                  34                -221                -339 
# 2016-06-15 13:00:00 2016-06-15 14:00:00 2016-06-15 15:00:00 2016-06-15 16:00:00 
#                  -4                 -48                -183                 133
Alexey Shiklomanov
  • 1,592
  • 13
  • 23
  • Thank you very much Alexey! If you don't mind I'd like to ask you one more question - how can I get data at time determined by myself? For example: how can I get X.OPEN at 12:00:00 of each day? Thanks a lot! – Dmitry Apr 19 '17 at 13:43
  • For extracting specific hours, see [this Stack Overflow question](http://stackoverflow.com/questions/16059965/plot-value-over-hour-of-day-with-xts-zoo-r). Also, if I answered your question, please accept the answer by clicking the grey check mark under the arrows. If you found the answer useful, you may also upvote it by clicking the up arrow. – Alexey Shiklomanov Apr 19 '17 at 13:50
  • Thanks again Alexey! Your answer really helped me – Dmitry Apr 27 '17 at 12:40