How do I get European tick size or historical intraday opening and closing prices?

Question

I'm looking for each European stock, the tick size. Given that obtaining this information directly is difficult (I don't think that databases, even the ones for academic purposes provides this information), I'm looking for an indirect way to do it. The assumption would be to look for the 1 minute (or even less) intraday difference of opening and closing price. Indeed, if I look to the minimum difference, each month, I should obtain the minimum price increment, namely the tick size. I tried with bloomberg, but the thing is that is not helpful for bulkydownload. Indeed I have thousands of stock in my dataset and I have to look for the last 4 years.

I'm wondering how can I tackle this issue and if someone has some solution. Thanks in advance.

Answer 1

To illustrate the approach using the difference between neighboring candle prices:

1. Export intraday OHLC candles and check the difference between close and open, as well as between open and previous close.

• c_minus_o is the price difference between close and open of the same candle
• o_minus_c is the price difference between open and the close of the previous candle. This one is the delta in prices between two consecutive trades.

| datetime | symbol  |   open() | close() | c_minus_o | o_minus_c |
|----------|---------|---------:|--------:|----------:|----------:|
| 09:30:00 | AVA.US  |    40.64 |   40.61 |     -0.03 |           |
| 09:31:00 | AVA.US  |   40.425 |  40.425 |         0 |    -0.185 |
| 09:32:00 | AVA.US  |   40.435 |   40.48 |     0.045 |      0.01 |
| 09:33:00 | AVA.US  |    40.56 |  40.555 |    -0.005 |      0.08 |
| 09:34:00 | AVA.US  |    40.58 |   40.49 |     -0.09 |     0.025 |
| 09:35:00 | AVA.US  |     40.5 |    40.5 |         0 |      0.01 |
| 09:38:00 | AVA.US  |    40.42 |   40.33 |     -0.09 |     -0.08 |
| 09:39:00 | AVA.US  |    40.46 |   40.46 |         0 |      0.13 |
| 09:40:00 | AVA.US  |  40.5118 |   40.52 |    0.0082 |    0.0518 |
| ...      |         |          |         |           |           |
| 09:30:00 | AVAV.US |   111.37 |  110.85 |     -0.52 |           |
| 09:37:00 | AVAV.US |   110.79 |  110.71 |     -0.08 |     -0.06 |
| 09:39:00 | AVAV.US |    110.7 |   110.7 |         0 |     -0.01 |
| 09:40:00 | AVAV.US | 111.7459 |  111.35 |   -0.3959 |    1.0459 |
| 09:42:00 | AVAV.US |  111.395 | 111.345 |     -0.05 |     0.045 |
| 09:44:00 | AVAV.US |   111.53 |  111.56 |      0.03 |     0.185 |
| 09:46:00 | AVAV.US | 111.9926 |  111.95 |   -0.0426 |    0.4326 |
| 09:48:00 | AVAV.US |   111.68 |   111.7 |      0.02 |     -0.27 |
| 09:49:00 | AVAV.US |   111.25 |  110.86 |     -0.39 |     -0.45 |

I used the LAG function in SQL which is analogous to relative cell references in Excel.

SELECT datetime, symbol, open(), close(), 
  close()-open() AS c_minus_o, open()-LAG(close()) AS o_minus_c
FROM atsd_trade
WHERE class = 'STOCK_USA' AND symbol LIKE 'AVA%'
AND datetime BETWEEN '2021-02-26' and '2021-02-27'
GROUP BY exchange, class, symbol, PERIOD(1 minute)
WITH ROW_NUMBER(symbol ORDER BY datetime) > 0

2. Calculate minimum and a lower percentile of absolute differences for each symbol. Ignore zero differences.

| symbol  | count(*) | min_c_minus_o | min_o_minus_c | p1_c_minus_o | p1_o_minus_c |
|---------|---------:|--------------:|--------------:|-------------:|-------------:|
| AVA.US  |      330 |        0.0025 |        0.0001 |      0.00285 |        0.005 |
| AVAV.US |      209 |        0.0093 |          0.01 |     0.009396 |         0.01 |

Here I used an inline view to calculate aggregates on top of inner query results.

SELECT symbol, count(*), 
MIN(abs(c_minus_o)) AS min_c_minus_o, MIN(abs(o_minus_c)) AS min_o_minus_c,
PERCENTILE(1, abs(c_minus_o)) AS p1_c_minus_o, PERCENTILE(1, abs(o_minus_c)) AS p1_o_minus_c
FROM (
SELECT datetime, symbol, open(), close(), 
  CASE WHEN close()-open() = 0 THEN NULL ELSE close()-open() END AS c_minus_o,
  CASE WHEN open()-LAG(close()) = 0 THEN NULL ELSE open()-LAG(close()) END AS o_minus_c
FROM atsd_trade
WHERE class = 'STOCK_USA' AND symbol LIKE 'AVA%'
AND datetime BETWEEN '2021-02-26' and '2021-02-27'
GROUP BY exchange, class, symbol, PERIOD(1 minute)
WITH ROW_NUMBER(symbol ORDER BY datetime) > 0
    ) GROUP BY symbol

I like how p1_o_minus_c is turning out to show a reasonable estimate. As you increase the interval from one day to several months or even quarters, the percentiles should stabilize even more. Keep in mind that the calculus maybe affected by splits.