0
$\begingroup$

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.

$\endgroup$
2
  • $\begingroup$ Order tick size and trade increment are different things. While exchanges may specify the minimum price increment for orders, the trades may be executed in finer increments due to price improvements, negotiated trades, auctions. If you still opt for a statistical estimate, choose a lower percentile instead of minimum, minimum is not a robust measure. $\endgroup$ Mar 1 at 11:52
  • $\begingroup$ Hi Sergei. Yes I noticed this issue and indeed it was puzzling to me why some price changes where even lower than the theoretical tick size. Indeed I noticed that if I look to the Mode of the absolute difference between opening and closing price, I should obtain the tick size. I'm just wondering how can I automate this process for a bulk download $\endgroup$
    – Marco
    Mar 1 at 13:52
0
$\begingroup$

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
  1. 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.

$\endgroup$
6
  • $\begingroup$ I really appreciate your clarifications and many thanks, Sergei. Just few doubts if you don't mind: 1.How di you export Export intraday OHLC? So far, I only tried manually with Bloomberg exporting few stocks using excel adds in. Nevertheless the issue is twofold. Firstly, I don't see a way to pull out bulky data (I have thousands of stock). Secondly, I have to go back to 2017, and it seems that Bloomberg doesn't allow you to go back more than 140 days. 2. What di you suggest in order to have a proxy of tick size each month? Looking to the mode of o_minus_c each month? $\endgroup$
    – Marco
    Mar 1 at 16:00
  • $\begingroup$ I don't have experience with Bloomberg export functionality but you can check with their support. Once you have the data in CSV files - the problem is 90% solved. I'm not a big fan of mode for numeric types. It's a nice characteristics for categorical values, but for numbers - percentile is a better choice. $\endgroup$ Mar 1 at 16:47
  • $\begingroup$ Unfortunately I'm quite sure I can't go as back as 2017 with Bloomberg. I'm wondering which source did you use for pull out the data (using also the code that you metnioned). If you can just give me a tip on that it would be great. $\endgroup$
    – Marco
    Mar 1 at 16:58
  • $\begingroup$ For US stocks I'm using a data feed provided by broker. It's proprietary but you need European equities anyways which I don't know where to source other than Reuters/Bloomberg. Try searching this site, once in a while someone posts a question on free intraday data - it's a common pain point. $\endgroup$ Mar 1 at 17:13
  • $\begingroup$ Many thanks Sergei. I really appreciate your help ! $\endgroup$
    – Marco
    Mar 1 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.