Here are the top rows of the files I created for Trade, Ask and Bid data:
Trade Data:
| Contract Name | Date | Time | Transaction Price | Trade Volume | Data type | Correction | Market State |
|---|---|---|---|---|---|---|---|
| F.US.EPM10 | 20070920 | 0911 | 155500 | 1 | T | R | N |
| F.US.EPM10 | 20070920 | 0911 | 155800 | 1 | T | R | N |
| F.US.EPM10 | 20070920 | 0914 | 155975 | 1 | T | R | N |
| F.US.EPM10 | 20070921 | 0848 | 155000 | 5 | T | R | N |
| F.US.EPM10 | 20070921 | 1310 | 154950 | 1 | T | R | N |
| F.US.EPM10 | 20070921 | 1341 | 154825 | 1 | T | R | N |
| F.US.EPM10 | 20070921 | 1346 | 154925 | 1 | T | R | N |
| F.US.EPM10 | 20070921 | 1353 | 155050 | 1 | T | R | N |
| F.US.EPM10 | 20070921 | 1405 | 155025 | 1 | T | R | N |
| F.US.EPM10 | 20070921 | 1414 | 155100 | 1 | T | R | N |
Bid data:
| Contract Name | Date | Time | Transaction Price | Trade Volume | Data type | Correction | Market State |
|---|---|---|---|---|---|---|---|
| F.US.EPM10 | 20070920 | 0659 | 154000 | 5 | B | R | N |
| F.US.EPM10 | 20070920 | 0911 | 155800 | 1 | B | R | N |
| F.US.EPM10 | 20070920 | 0911 | 154000 | 5 | B | R | N |
| F.US.EPM10 | 20070920 | 0914 | 155975 | 1 | B | R | N |
| F.US.EPM10 | 20070920 | 0914 | 154000 | 5 | B | R | N |
| F.US.EPM10 | 20070920 | 0916 | 154025 | 1 | B | R | N |
| F.US.EPM10 | 20070920 | 1050 | 154500 | 1 | B | R | N |
| F.US.EPM10 | 20070920 | 1310 | 154025 | 4 | B | R | N |
| F.US.EPM10 | 20070920 | 1310 | 154200 | 1 | B | R | N |
| F.US.EPM10 | 20070920 | 1312 | 154500 | 1 | B | R | N |
Ask data:
| Contract Name | Date | Time | Transaction Price | Trade Volume | Data type | Correction | Market State |
|---|---|---|---|---|---|---|---|
| F.US.EPM10 | 20070920 | 0733 | 156000 | 1 | A | R | N |
| F.US.EPM10 | 20070920 | 0831 | 155500 | 1 | A | R | N |
| F.US.EPM10 | 20070920 | 0911 | 156000 | 1 | A | R | N |
| F.US.EPM10 | 20070920 | 0911 | 155825 | 1 | A | R | N |
| F.US.EPM10 | 20070920 | 0911 | 156000 | 18 | A | R | N |
| F.US.EPM10 | 20070920 | 0914 | 156000 | 2 | A | R | N |
| F.US.EPM10 | 20070920 | 0914 | 156000 | 2 | A | R | N |
| F.US.EPM10 | 20070920 | 0914 | 156000 | 1 | A | R | N |
| F.US.EPM10 | 20070920 | 0916 | 155975 | 2 | A | R | N |
| F.US.EPM10 | 20070920 | 0959 | 156000 | 2 | A | R | N |
We can merge these files as follows:
mergedData <- rbind(rbind(tradeData, bidData),askData)
And now we probably want to order the data by date and time before showing the final result.
mergedData %>%
mutate(DateTime = ymd_hm(paste0(Date, Time))) %>%
select(`Contract Name`,
DateTime,
`Transaction Price`,
`Trade Volume`,
`Data type`,
Correction,
`Market State`
) %>%
arrange(DateTime) %>%
head(20) %>%
kable %>%
kable_styling(bootstrap_options = c("striped", "hover"),
row_label_position = "l",
full_width = F,
position = "center")| Contract Name | DateTime | Transaction Price | Trade Volume | Data type | Correction | Market State |
|---|---|---|---|---|---|---|
| F.US.EPM10 | 2007-09-20 06:59:00 | 154000 | 5 | B | R | N |
| F.US.EPM10 | 2007-09-20 07:33:00 | 156000 | 1 | A | R | N |
| F.US.EPM10 | 2007-09-20 08:31:00 | 155500 | 1 | A | R | N |
| F.US.EPM10 | 2007-09-20 09:11:00 | 155500 | 1 | T | R | N |
| F.US.EPM10 | 2007-09-20 09:11:00 | 155800 | 1 | T | R | N |
| F.US.EPM10 | 2007-09-20 09:11:00 | 155800 | 1 | B | R | N |
| F.US.EPM10 | 2007-09-20 09:11:00 | 154000 | 5 | B | R | N |
| F.US.EPM10 | 2007-09-20 09:11:00 | 156000 | 1 | A | R | N |
| F.US.EPM10 | 2007-09-20 09:11:00 | 155825 | 1 | A | R | N |
| F.US.EPM10 | 2007-09-20 09:11:00 | 156000 | 18 | A | R | N |
| F.US.EPM10 | 2007-09-20 09:14:00 | 155975 | 1 | T | R | N |
| F.US.EPM10 | 2007-09-20 09:14:00 | 155975 | 1 | B | R | N |
| F.US.EPM10 | 2007-09-20 09:14:00 | 154000 | 5 | B | R | N |
| F.US.EPM10 | 2007-09-20 09:14:00 | 156000 | 2 | A | R | N |
| F.US.EPM10 | 2007-09-20 09:14:00 | 156000 | 2 | A | R | N |
| F.US.EPM10 | 2007-09-20 09:14:00 | 156000 | 1 | A | R | N |
| F.US.EPM10 | 2007-09-20 09:16:00 | 154025 | 1 | B | R | N |
| F.US.EPM10 | 2007-09-20 09:16:00 | 155975 | 2 | A | R | N |
| F.US.EPM10 | 2007-09-20 09:59:00 | 156000 | 2 | A | R | N |
| F.US.EPM10 | 2007-09-20 10:50:00 | 154500 | 1 | B | R | N |
There we go, easy!
Iād be happy to discuss with you how your dataset differs from this one and what other data manipulations you would like to see.