Data cleaning

Data cleaning of high-frequency data is a necessary step in all finance and financial econometrics applications. The reason is that most data providers like tick data offer raw instead of preprocessed data. The upside is that you can follow the cleaning process yourself instead of relying on another party. In this vignette, we will address a common workflow that makes use of both trades and quotes data on a microsecond resolution.

Cleaning tick data on trades

We start by loading the highfrequency package and printing the head of one example data set that contains trade data for one stock on two days.

library(highfrequency)
head(sampleTDataRaw)
#>                     DT EX SYMBOL SYM_SUFFIX TR_SCOND SIZE  PRICE TR_CORR
#> 1: 2018-01-02 05:01:21  P    XXX         NA      FTI    2 157.80       0
#> 2: 2018-01-02 05:23:50  P    XXX         NA      FTI    3 157.80       0
#> 3: 2018-01-02 05:23:50  P    XXX         NA      FTI    1 157.80       0
#> 4: 2018-01-02 07:11:54  P    XXX         NA        T  130 158.00       0
#> 5: 2018-01-02 07:23:12  P    XXX         NA       TI   30 158.10       0
#> 6: 2018-01-02 07:28:44  P    XXX         NA       TI   30 158.28       0
#>    TR_SEQNUM TR_SOURCE TR_RF
#> 1:     48101         C      
#> 2:     48701         C      
#> 3:     48801         C      
#> 4:     67901         C      
#> 5:     72101         C      
#> 6:     73301         C

A quick look via summary into the data let’s us see that there are trades listed in the data set out of the bounds of the regular trading hours that are 9:30 to 16:00 on regular work days.

summary(sampleTDataRaw[, c("DT", "SIZE", "PRICE")])
#>        DT                           SIZE              PRICE      
#>  Min.   :2018-01-02 05:01:21   Min.   :     1.0   Min.   :155.4  
#>  1st Qu.:2018-01-02 12:50:07   1st Qu.:    29.0   1st Qu.:156.4  
#>  Median :2018-01-02 15:59:06   Median :   100.0   Median :156.7  
#>  Mean   :2018-01-03 00:33:21   Mean   :   132.7   Mean   :156.8  
#>  3rd Qu.:2018-01-03 12:21:06   3rd Qu.:   100.0   3rd Qu.:157.1  
#>  Max.   :2018-01-03 19:55:37   Max.   :443901.0   Max.   :159.4

Next, we will use one of three main cleaning functions called tradesCleanup. It implements the steps laid out in the seminal paper of Barndorff-Nielsen et al. (2009). It processes the given data by

deleting entries with a price reported as zero,
selecting trades from a single exchange (“N” stands for New York Stock Exchange (NYSE))
filtering for the sales condition to be either “E” or “F”, corresponding to cases of regular trade records and
summarizing trades that have the same time stamp. The default is to take the median price and summed-up volume of the different trades.

tDataCleaned <- tradesCleanup(tDataRaw = sampleTDataRaw, exchange = "N")

When using tradesCleanup, the return value is a list of two. It contains a report about the number of observations after each cleaning step and the cleaned data itself.

tDataCleaned$report
#>          initialObservations        removedFromZeroTrades 
#>                        77263                            0 
#>  removedOutsideExchangeHours removedFromSelectingExchange 
#>                          451                        65625 
#>       removedFromCorrections    removedFromSalesCondition 
#>                            0                         3032 
#>    removedFromMergeTimestamp            finalObservations 
#>                         2015                         6140

summary(tDataCleaned$tData[, c("DT", "SIZE", "PRICE")])
#>        DT                           SIZE            PRICE      
#>  Min.   :2018-01-02 09:30:00   Min.   :   1.0   Min.   :155.4  
#>  1st Qu.:2018-01-02 12:22:48   1st Qu.: 100.0   1st Qu.:156.4  
#>  Median :2018-01-02 15:59:10   Median : 100.0   Median :156.7  
#>  Mean   :2018-01-03 00:18:24   Mean   : 173.1   Mean   :156.9  
#>  3rd Qu.:2018-01-03 12:12:08   3rd Qu.: 200.0   3rd Qu.:157.1  
#>  Max.   :2018-01-03 15:59:59   Max.   :6608.0   Max.   :159.4

As just one example, one can see that all trades that had time stamps outside the opening hours of the NYSE. The report of the cleaning procedure shows us that most of the observations are removed either due to originating from another exchange or having irregular sales conditions.

As a last step for cleaning tick data on trades it is advised to check whether compatible quotes have been made before the recorded transaction. For doing so, we first need to implement a similar cleaning procedure for quotes data.

Cleaning tick data on quotes

The highfrequency function also brings a similarly-named function for cleaning quotes data, quotesCleanup. Here, the cleaning steps are

removing quotes with zero price,
selecting a single exchange,
removing entries that have extraordinarily large spreads
merging quotes that happen on the same time and
removing quotes that may be seen to be outliers.

qDataCleaned <- quotesCleanup(qDataRaw = sampleQDataRaw, exchange = "N")

quotesCleanup also provides a list with two elements, a report and the cleaned data.

qDataCleaned$report
#>          initialObservations        removedFromZeroQuotes 
#>                       131401                           96 
#>  removedOutsideExchangeHours removedFromSelectingExchange 
#>                          776                        36109 
#>    removedFromNegativeSpread       removedFromLargeSpread 
#>                            0                            0 
#>    removedFromMergeTimestamp              removedOutliers 
#>                        47856                            0 
#>            finalObservations 
#>                        46564

summary(qDataCleaned$qData[, c("DT", "OFR", "OFRSIZ", "BID", "BIDSIZ", "MIDQUOTE")])
#>        DT                           OFR            OFRSIZ       
#>  Min.   :2018-01-02 09:30:00   Min.   :155.4   Min.   :  1.000  
#>  1st Qu.:2018-01-02 12:21:55   1st Qu.:156.5   1st Qu.:  1.000  
#>  Median :2018-01-02 15:54:41   Median :156.7   Median :  2.000  
#>  Mean   :2018-01-03 00:03:51   Mean   :156.9   Mean   :  4.211  
#>  3rd Qu.:2018-01-03 12:12:14   3rd Qu.:157.2   3rd Qu.:  4.000  
#>  Max.   :2018-01-03 15:59:59   Max.   :159.4   Max.   :417.000  
#>       BID            BIDSIZ           MIDQUOTE    
#>  Min.   :155.4   Min.   :  1.000   Min.   :155.4  
#>  1st Qu.:156.4   1st Qu.:  1.000   1st Qu.:156.4  
#>  Median :156.7   Median :  2.000   Median :156.7  
#>  Mean   :156.9   Mean   :  4.304   Mean   :156.9  
#>  3rd Qu.:157.1   3rd Qu.:  4.000   3rd Qu.:157.1  
#>  Max.   :159.4   Max.   :426.000   Max.   :159.4

Cleaning transaction data using cleaned quotes

Given our freshly cleaned trades and quotes data, we now want to filter for trades that are proceeded by a compatible set of quotes. Currently, the tradesCleanupUsingQuotes function only supports submitting trades and quotes data for one day only, Hence, we filter our data set to one of the days.

tqDataCleaned <- tradesCleanupUsingQuotes(tData = tDataCleaned$tData[as.Date(DT) == "2018-01-02"], 
                                           qData = qDataCleaned$qData[as.Date(DT) == "2018-01-02"])
tqDataCleaned
#>                        DT SYMBOL    BID    OFR QUOTEEX QU_COND     QU_SEQNUM
#>    1: 2018-01-02 09:30:00    XXX 158.39 158.50       N       R 5.645787e-317
#>    2: 2018-01-02 09:30:00    XXX 158.39 158.58       N       R 5.655669e-317
#>    3: 2018-01-02 09:30:00    XXX 158.39 158.58       N       R 5.734423e-317
#>    4: 2018-01-02 09:30:00    XXX 158.39 158.58       N       R 5.734423e-317
#>    5: 2018-01-02 09:30:00    XXX 158.39 158.58       N       R 5.734423e-317
#>   ---                                                                       
#> 3165: 2018-01-02 15:59:57    XXX 157.04 157.06       N       R 8.475940e-315
#> 3166: 2018-01-02 15:59:58    XXX 157.00 157.04       N       R 8.480610e-315
#> 3167: 2018-01-02 15:59:59    XXX 157.02 157.04       N       R 8.484741e-315
#> 3168: 2018-01-02 15:59:59    XXX 157.02 157.03       N       R 8.485014e-315
#> 3169: 2018-01-02 15:59:59    XXX 157.02 157.03       N       R 8.487702e-315
#>       NATBBO_IND QU_CANCEL QU_SOURCE SYM_SUFFIX BIDSIZ OFRSIZ MIDQUOTE   PRICE
#>    1:          A        NA         C         NA      1     18  158.445 158.500
#>    2:          G        NA         C         NA      1      1  158.485 158.500
#>    3:          A        NA         C         NA      1      1  158.485 158.485
#>    4:          A        NA         C         NA      1      1  158.485 158.485
#>    5:          A        NA         C         NA      1      1  158.485 158.485
#>   ---                                                                         
#> 3165:          A        NA         C         NA      1      3  157.050 157.050
#> 3166:          U        NA         C         NA      1     13  157.020 157.040
#> 3167:          G        NA         C         NA     12    103  157.030 157.020
#> 3168:          G        NA         C         NA      4     18  157.025 157.020
#> 3169:          A        NA         C         NA      6     86  157.025 157.020
#>       SIZE NUMTRADES EX i.SYM_SUFFIX COND CORR TR_SEQNUM TR_SOURCE TR_RF
#>    1:   50         1  N           NA    I    0    272301         C      
#>    2: 1805         1  N           NA         0    273801         C      
#>    3:    4         1  N           NA    I    0    301001         C      
#>    4:    1         1  N           NA    I    0    301401         C      
#>    5:   72         1  N           NA    I    0    301501         C      
#>   ---                                                                   
#> 3165:  300         1  N           NA    F    0 143882001         C      
#> 3166: 1200         1  N           NA         0 143960301         C      
#> 3167:  500         2  N           NA    F    0 144041001         C      
#> 3168:  400         1  N           NA    F    0 144043101         C      
#> 3169:   62         1  N           NA    I    0 144070401         C

Here, we see that once more half of the trades are filtered out due to non-plausibility.

Data cleaning

Cleaning tick data on trades

Cleaning tick data on quotes

Cleaning transaction data using cleaned quotes

References