Get startedGet started for free

Aggregating and combining intraday data

1. Aggregating and combining intraday data

Up to this point in the chapter, you've been working with data that is daily frequency or lower. In this video, you will learn how to solve problems specific to intraday data.

2. Timezones!

Perhaps the most significant difference with intraday data is that they're affected by timezones. Jeff Ryan introduced timezones at the beginning of chapter 5 of the xts and zoo course.

3. Timezones!

Recall that, internally, xts stores its index as the number of seconds since midnight January 1, 1970 UTC. This internal index is used when you merge xts objects. The resulting xts object will have the same timezone as the first object passed to merge().

4. Timezones!

For example, the 'london' and 'tokyo' xts objects use the same 'datetime' object for their index, but with different timezones. The result of calling merge() on these objects will only have one row because the internal index values are the same, but the timezone will depend on which object is passed to merge() first. If 'london' is first, the index will be 10AM London time on January 18th. If 'tokyo' is first, the index will be nineteen-hundred, or 7PM Tokyo time; 9 hours later.

5. Creating regular intraday data

Converting irregular intraday data to a regular dataset is similar to what you did with lower frequency data, but with a couple differences. In the next example, we will use some intraday trade data from the simulated DataCamp stock.

6. Creating regular intraday data

There likely won't be an observation exactly at the open and close, so the start() and end() functions won't return the exact times for the beginning and end of the trading day. You need to specify the from and to arguments manually when creating a regular sequence of date-times. Once you have a regular sequence of date-times, you can create a zero-width xts object.

7. Subset to trading hours

The regular date-time sequence has observations when the market was closed, so the merged result will include missing values during that time. xts' time-of-day subsetting can extract only the observations when the market was open. For example, if the market opens at 8am and closes at 6pm you can subset with the string T-zero-eight-hundred, slash, T-eighteen-hundred. Note the times must be specified in 24-hour format, and times before noon must include a leading zero.

8. Fill missing values by trading day

You also need to be careful about filling missing values. The na-dot-locf() function could carry the last value from the prior trading session forward to the first observation of the next session. You can solve this issue by using the split()-lapply()-rbind() paradigm you learned in chapter 4 of the xts and zoo course. You can use the split() function to separate your data into non-overlapping daily chunks. Then use lapply() to call na-dot-locf() on each chunk. Finally, you use do-dot-call() and rbind() to row-bind each day of data into one object.

9. Aggregate irregular intraday data

While intraday data can sometimes be sparse, it's more likely to be huge. Heavily-traded instruments often have hundreds of thousands of observations per day. You will often want to aggregate these data before doing analysis on them. The xts and zoo course taught you how to use to-dot-period(), and it's exactly what you need to aggregate dense intraday data to a lower frequency. Use the period argument to specify the aggregation periodicity, and use the k argument to specify how many periods should be included in each new observation.

10. Aggregate irregular intraday data (1)

Let's look at an example, using the simulated DC stock data.

11. Aggregate irregular intraday data (2)

If you want to aggregate the data to five minute intervals, you should set period equal to "minutes" and k equal to 5. Notice that to-dot-period() output uses the timestamp for the last observed value in each interval. If you want timestamps on each round interval, you can use align-dot-time() to round the timestamp up to the start of the next period.

12. Let's practice!

Now it's time to apply what you've learned, and practice handling intraday data in the exercises.

Create Your Free Account

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.