Constructing Spread OHLC Bars from Two Assets

# How to correctly build a spread candlestick chart between two assets (avoid inconsistent OHLC)


# How to correctly build a spread candlestick chart between two assets (avoid inconsistent OHLC)












I just wondering how charting platforms plot spreads between two assets. For example, if we want two create spread chart between APPL / AMZN we can substract or divide each asset:

if we divide two assets:

appl(OHLC) / amzn(OHLC) appl(OHLC) - amzn(OHLC)

how avoid that spread close price was lower to the low or higher to the high to build a synthetic OHLC spread series.

I would like to create synthetic curtom spread series but i am not sure how to get OHLC spread series for create indicators from that series like if it was a single asset OHLC series.

## Answer by Attack68 (score 1)

https://quant.stackexchange.com/a/58625

The Open and the Close will be consistent timing and will be valid.

The High and Low of each instrument will not be time consistent. In particular it will be especially erroneous to divide the appl(H) by amzn(L) to get the highest ratio or the appl(L) by amzn(H).

The likelihood that both prices were at the highs at similar times and the lows at similar times will be greater but in general the accuracy will be far from 100%.

Without more granular data there is no more you can do than make assumptions and live with a weak model.

## Answer by Sergei Rodionov (score 0)

https://quant.stackexchange.com/a/61680

With respect to TradingView specifically, this feature is called spread charts. Irrespective of the aggregation period selected on the chart, they take 1-minute instrument bars and produce a series of 1-minute spread bars. Longer duration spread bars (1-day on your screenshot) are then calculated from the 1-minute spread bars.

The expression is evaluated on the server which means that access to 1-minute instrument bars would be necessary to reproduce this calculation on the client.

It's not clear what part of the OHLC bar they use when generating 1-minute spread bars. Most likely Open or Close, as highs and lows within each period would be misaligned on time.

Whether it's Open or Close, the calculated spread is approximate due to smoothing. The chart below shows raw trades for two assets: A and B (A=SPY rescaled and B=QQQ). These are actual round lot trades recorded at 2021-03-11 10:45:00 US/Eastern.

The blue line shows raw spread `A/B` which is based on last trade price. Each time a trade comes in, the ratio is recalculated based on last trade price. The green and orange lines show `A[O]/B[O]` and `A[C]/B[C]` for 1-minute periods. At 10:50 both spread bars are quite away from the raw spread, which illustrates how 1-minute bar spreads hide volatility that occurs inside the bar.

If you have access to VWAP bars, `A[VWAP]/B[VWAP]` (violet series) would provide a better estimate of the raw spread.

Reducing the bar period to less than 1-minute is another setting that minimizes smoothing. Example using 10-second bars:

So keep in mind this information when viewing OHLC spreads in the Data Window. The values are approximate.

If it's necessary to calculate the exact spread bars, one can create a sliding window in SQL:

```
SELECT datetime,
  LAST(CASE symbol WHEN 'SPY' THEN price ELSE null END) last_A,
  LAST(CASE symbol WHEN 'QQQ' THEN price ELSE null END) last_B,
  ROUND(last_A/last_B, 6) AS AB
FROM atsd_trade
WHERE symbol IN ('SPY', 'QQQ')
AND datetime BETWEEN '2021-03-11 09:30:00' AND '2021-03-11 16:00:00'
WITH ROW_NUMBER(1 ORDER BY time, trade_num) >= 0
ORDER BY datetime, trade_num
```

```
| datetime                         | last_A | last_B |       AB |
|----------------------------------|-------:|-------:|---------:|
| ...                              |        |        |          |
| 2021-03-11T10:45:00.603000-05:00 | 393.46 | 316.94 | 1.241434 |
| 2021-03-11T10:45:00.603000-05:00 | 393.46 | 316.94 | 1.241434 |
| 2021-03-11T10:45:00.605000-05:00 | 393.47 | 316.94 | 1.241465 |
| 2021-03-11T10:45:00.605000-05:00 | 393.47 | 316.94 | 1.241465 |
| 2021-03-11T10:45:00.605000-05:00 | 393.46 | 316.94 | 1.241434 |
| 2021-03-11T10:45:00.605000-05:00 | 393.47 | 316.94 | 1.241465 |
| 2021-03-11T10:45:00.606000-05:00 | 393.47 | 316.94 | 1.241465 |
```

With this analytical window technique, it's then possible to calculate the exact spread bars for any period, 1-minute, 1-hour, 1-day, etc.

```
SELECT datetime, 
  ROUND(MIN(ratio_AB), 6) AS AB_low, 
  ROUND(MAX(ratio_AB), 6) AS AB_high 
FROM (
SELECT datetime,
  LAST(CASE symbol WHEN 'SPY' THEN price ELSE null END) last_A,
  LAST(CASE symbol WHEN 'QQQ' THEN price ELSE null END) last_B,
  last_A/last_B AS ratio_AB
FROM atsd_trade
WHERE symbol IN ('SPY', 'QQQ')
AND datetime BETWEEN '2021-03-11 09:30:00' AND '2021-03-11 16:00:00'
WITH ROW_NUMBER(1 ORDER BY time, trade_num) >= 0
ORDER BY datetime, trade_num
) GROUP BY PERIOD(1 MINUTE) -- or 1-hour, or 1-day
```

```
| datetime            |   AB_low |  AB_high |
|---------------------|---------:|---------:|
| 2021-03-11 10:45:00 | 1.241214 | 1.242576 |
| 2021-03-11 10:46:00 | 1.241756 | 1.242613 |
| 2021-03-11 10:47:00 | 1.242365 | 1.242878 |
| 2021-03-11 10:48:00 | 1.241907 | 1.242808 |
| 2021-03-11 10:49:00 | 1.241197 | 1.242087 |
```