Relabeling Overnight Median Prices in Intraday Data
Summary
The document describes a data-cleaning approach for incorporating overnight prices into high-frequency stock-index data. The example method first stores the median overnight price as an observation at 23:00 on the day trading began, then changes that record’s date to the next trading date and its time to 09:00. This represents the overnight value at the following session’s opening observation, following the treatment attributed to Hansen and Lunde (2005). The response suggests keeping dates and times in separate fields, identifying the distinct dates in the dataset, and updating the fields for records whose timestamp is 23:00 before combining them again.
This is a procedural outline rather than ready-to-run R code. It depends on the data’s structure and on correctly identifying the next relevant date; it does not discuss holidays, missing sessions, time zones, or validation of the resulting series. The example establishes the intended timestamp change, but gives no empirical comparison of alternative treatments.
Key ideas
- Separate date and time fields to make timestamp updates easier to manage.
- Identify distinct dates and use the next date for overnight observations stored at 23:00.
- Change those observations’ time to 09:00 before rebuilding the timestamp.
- The response outlines the data manipulation but does not provide executable R code or address nontrading days.
Tags
Full text
# (R-studio) Incorporating overnight returns in high-frequency data # (R-studio) Incorporating overnight returns in high-frequency data Turning here since I haven't been able to find any help online. I'm currently dealing with a high-frequency data set in R of some stock indicies and I'm currently trying to clean the data so that I will be able to work with it. So, to get to the problem, I'm trying to incorporate the overnight returns of the indicies (i.e. prices in between 15:00 and 09:00 the following day) as done by Hansen & Lunde (2005) - that is, taking the median of the overnight prices and storing it as the 09:00 observation the next day. Does anyone have any R-code on how to deal with this problem? PS. I have been able to store the overnight median price as the 23:00-observation the previous day (i.e. the day that the overnight trading started). However, I haven't been able to change the date and time of these observations so that they end up at 09:00 the next day. An example is listed below: 2006-12-29 14:30:00 17320.00 2006-12-29 14:35:00 17315.00 2006-12-29 14:40:00 17315.00 2006-12-29 14:45:00 17322.50 2006-12-29 15:00:00 17325.00 2006-12-29 23:00:00 17321.25 <- THIS OBSERVATION I WOULD LIKE TO END UP AT 09:00 THE NEXT DAY ## Answer by Iñaki Viggers (score 2) https://quant.stackexchange.com/a/44094 > Does anyone have any R-code on how to deal with this problem? It depends on what data structures you are using, but one approach is the following: - Define a two-column array to hold date and time separately. This array will have as many rows as your current array. Alternatively, cbind() these two columns to your array. - Define an array distinct_dates that will only have distinct dates in it (levels() is useful here). - In the two-column array, set the date field to the next day --which you will get from distinct_dates-- in rows where timestamp starts with '23:'. - In the two-column array, set the timestamp field to 09:00 in rows where timestamp starts with '23:'. - Set the values of original date-timestamp column by merging the fields you processed in steps 3 & 4.
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.