I am working with an intraday trading database. I have a TAQ (Trade and Quote) database, so for each tick I have the date (POSIXct class), the type (BID, ASK, TRADE), the price and the size. I need to add the BID and ASK depth just before the TRADE.
Here is an example database:
df = structure(data.frame(date = as.POSIXct(c(rep("2017-12-13 09:15:00", 2), rep("2017-12-13 09:30:00", 2), "2017-12-13 09:31:00", "2017-12-13 09:31:01",
rep("2017-12-13 09:32:00", 2), "2017-12-13 09:33:00", rep("2017-12-13 09:35:00", 3), rep("2017-12-13 09:38:00", 4),
"2017-12-13 09:40:00", "2017-12-13 09:41:00", "2017-12-13 09:41:05","2017-12-13 09:41:10", "2017-12-13 09:43:00",
"2017-12-13 09:43:05", "2017-12-13 09:45:00", "2017-12-13 09:45:50", "2017-12-13 09:46:30"), tz=""),
type = structure(c("ASK", rep("BID", 2), "TRADE", rep("BID", 3), "ASK", "TRADE", "ASK", "TRADE", "ASK", rep("BID", 2),
rep("TRADE", 2), "BID", rep("ASK", 3), rep("TRADE", 2), "ASK", "BID", "TRADE"), class = "character"),
price = structure( c(1915, rep(1905, 3), rep(1910, 2), 1905, 1920, 1910, rep(1915, 3),
rep(1905, 4), 1910, rep(1920, 5), 1915, 1905, 1910), class = "numeric"),
size = structure(c(rep(1000, 3), 2000, rep(1000, 4), 2000, rep(1000, 3), rep(2000, 2), 1000, 1500,
rep(1000, 8), 2000), class = "numeric")), class = "data.frame")
And the expected output is:
date type price size BID_size ASK_size
1 2017-12-13 09:15:00 ASK 1915 1000 NA NA
2 2017-12-13 09:15:00 BID 1905 1000 NA NA
3 2017-12-13 09:30:00 BID 1905 1000 NA NA
4 2017-12-13 09:30:00 TRADE 1905 2000 2000 1000
5 2017-12-13 09:31:00 BID 1910 1000 NA NA
6 2017-12-13 09:31:01 BID 1910 1000 NA NA
7 2017-12-13 09:32:00 BID 1905 1000 NA NA
8 2017-12-13 09:32:00 ASK 1920 1000 NA NA
9 2017-12-13 09:33:00 TRADE 1910 2000 2000 1000
10 2017-12-13 09:35:00 ASK 1915 1000 NA NA
11 2017-12-13 09:35:00 TRADE 1915 1000 1000 1000
12 2017-12-13 09:35:00 ASK 1915 1000 NA NA
13 2017-12-13 09:38:00 BID 1905 2000 NA NA
14 2017-12-13 09:38:00 BID 1905 2000 NA NA
15 2017-12-13 09:38:00 TRADE 1905 1000 4000 1000
16 2017-12-13 09:38:00 TRADE 1905 1500 3000 1000
17 2017-12-13 09:40:00 BID 1910 1000 NA NA
18 2017-12-13 09:41:00 ASK 1920 1000 NA NA
19 2017-12-13 09:41:05 ASK 1920 1000 NA NA
20 2017-12-13 09:41:10 ASK 1920 1000 NA NA
21 2017-12-13 09:43:00 TRADE 1920 1000 1000 3000
22 2017-12-13 09:43:05 TRADE 1920 1000 1000 2000
23 2017-12-13 09:45:00 ASK 1915 1000 NA NA
24 2017-12-13 09:45:50 BID 1905 1000 NA NA
25 2017-12-13 09:46:30 TRADE 1910 2000 1000 1000
I was trying to create an indicator between trades, find the type with the same price as TRADE and sum all rows, finally for the other quote find the best BID/ASK, but my programming skills with POSIXct objects and movements between rows are too basic. I hope that you can help me. Thanks a lot.