2
$\begingroup$

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.

enter image description here

Improve this question
$\endgroup$

2 Answers 2

Reset to default
1
$\begingroup$

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.

Improve this answer
$\endgroup$
5
  • $\begingroup$ Thanks @Attack68, so how charting software plot spreads. I've edited my question with some screenshot. $\endgroup$
    – Vince
    Oct 12, 2020 at 19:33
  • $\begingroup$ well they either have more granular data that is more accurate, or they make a subjective decision and plot it to the user regardless of its accuracy. The interesting factor would be comparing this to true data or even another platforms charts. $\endgroup$
    – Attack68 ♦
    Oct 12, 2020 at 19:39
  • $\begingroup$ Thanks @Attack68. What i am doing is, (appl[O] - amzn[O]), (appl[H] - amzn[L]) ,(appl[H] - amzn[H], appl[C] - amzn[C]. But at the and i have observations with (appl[C] - amzn[C]) > (appl[H] - amzn[H]) that obviously is not posible. $\endgroup$
    – Vince
    Oct 12, 2020 at 19:57
  • $\begingroup$ "The Open and the Close will be consistent timing and will be valid." - I suspect this depends on how open and close are calculated. If they're calculated based on first and last trade within a given time window, it's possible they're not simultaneous - depends on the arrival times of the trades. If they're calculated based on mid prices then they should be consistent. More of an issue for less liquid instruments, of course. I wouldn't assume the provider is necessarily doing something sensible. $\endgroup$
    – user42108
    Nov 12, 2020 at 13:16
  • $\begingroup$ The nub of the answer above is correct... You cannot be certain that the highs and lows coincide (or even that high coincides with high not low, though that is less of an issue with stocks than multi-asset!) Short answer is you can guesstimate the relative high/low using MLE; but it's a HORRIBLE process that adds almost zero new information to just saying stock-vs-stock, the peaks and troughs probably coincided. Sorry ;-( $\endgroup$
    – demully
    Mar 11, 2021 at 23:56
0
$\begingroup$

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.

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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 |
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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