RTI (Real-Time Indicator) functions are MarketXLS Excel functions that calculate technical indicators on intraday bars built from QuoteMedia's live stream. The cells update on their own during the session. For example, =RTI_RSI("AAPL", 5, 14) returns a 14-bar RSI on 5-minute bars, and =RTI_High("AAPL", 5, "09:30") returns the high of the first 5-minute candle of the day.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
This guide covers what you need to run them, every argument, how to address a bar by time of day, all 32 functions, what a cell is telling you when it shows #N/A or a message instead of a number, and worked examples: opening ranges, checkpoints through the day, signals on completed bars, two timeframes together and a watchlist scanner.
What you need
- The MarketXLS Windows add-in, version 9.6 or later. RTI functions run in the desktop add-in only. They are not available in Excel Online, Excel for Mac or Google Sheets.
- The QuoteMedia stream configured in MarketXLS: Settings > Data Subscriptions > QuoteMedia (see QuoteMedia setup). Real-time US data needs the Advanced or Business plan; see pricing.
- Automatic calculation in Excel: Formulas > Calculation Options > Automatic.
How the bars are built
- History first. The first time a sheet asks for a symbol, MarketXLS downloads recent 1-minute bars for it from QuoteMedia: at least the last 3 trading sessions, and up to 15 when a long interval or lookback needs more. The bars are saved in
%AppData%\MarketXLS\rti, so reopening Excel later the same day reads them from disk instead of downloading again. - Live trades on top. Trades from the stream extend the newest bar as they arrive.
- Any interval from the same bars. 5-minute, 15-minute or hourly bars are built from the 1-minute bars. Bars start at the 09:30 open, so hourly bars run 09:30-10:30, 10:30-11:30 and so on, as on a chart.
- Regular session only. Bars cover 09:30 to 16:00 US Eastern. Pre-market and after-hours trades are left out, so they do not distort the session's bars.
- Standard calculations. Every indicator is computed with TA-Lib, the standard open-source technical analysis library.
Because history is loaded at the start, an indicator shows a value within a few seconds of opening the sheet, at any time of day. Before version 9.6, an indicator had to watch the market for its whole lookback before it could show anything: a 20-bar RSI on 5-minute bars needed more than an hour and a half of live trading.
The arguments
Most RTI functions take the same arguments, in this order:
| Argument | Default | What it does |
|---|---|---|
symbol | required | Ticker, such as "AAPL", or a cell holding one. |
interval | 5 | Bar size in minutes, any whole number from 1 to 1440. |
lookback | depends on the function | Bars in the calculation, from 2 to 200. RSI, SMA, EMA and Bollinger Bands default to 20; ATR, Stochastic, ADX and MFI to 14. |
field | "Last" | The price the indicator is calculated on: "Last" (same as "Close"), "Open", "High", "Low", "Volume" or "VWAP". |
component | depends on the function | Which line of a multi-line indicator: "macd", "signal" or "histogram" for MACD; "upper", "middle" or "lower" for Bollinger Bands; "k" or "d" for Stochastic. |
index | 0 | Which bar to read: a number of bars back, or a time of day. See the next section. |
You can leave out trailing arguments. To set a later one, type the earlier ones too: =RTI_RSI("AAPL", 5, 20, "Last", 1) is the default 20-bar RSI on the last completed bar.
The index argument: bars back, or a time of day
A number counts bars back
0is the bar now forming. It changes with every trade until the bar closes.1is the last completed bar,2the one before it, and so on.
Compare 0 and 1 to catch a cross as it happens:
=RTI_RSI("AAPL", 5, 14, "Last", 1) ' RSI on the last completed 5-minute bar
Formula documentation: RTI_RSI
A time picks the bar that contains it
Give a time of day, US Eastern, and the function reads the bar that contains that time:
=RTI_High("AAPL", 5, "09:30") ' high of the first 5-minute candle
=RTI_Low("AAPL", 15, "09:30") ' low of the first 15-minute candle
=RTI_RSI("AAPL", 5, 14, "Last", "10:00") ' RSI as of the 10:00 bar
=RTI_Volume("AAPL", 5, "09:32") ' volume of the 09:30-09:35 bar
Formula documentation: RTI_High, RTI_Low, RTI_RSI, RTI_Volume
These accept the time in any of these forms:
- Text:
"09:30","9:30","9:30 AM","14:45:00", or"9.30"and"14.45". - A cell holding an Excel time, such as
=TIME(9,30,0). - A cell holding a date and time. Only the time of day is used.
A whole number is always read as bars back, so 10 means ten bars back, not 10:00. Write "10:00" for the time.
Which day a time refers to
| When you look | "09:30" means |
|---|---|
| During the trading day (09:30 to 16:00 ET, Monday to Friday) | Today's 09:30 bar |
| After the close | Today's 09:30 bar |
| Before the open | The 09:30 bar of the last trading day |
| Weekends | The last trading day's 09:30 bar, usually Friday's |
| A market holiday, after 16:00 ET | The last trading day's 09:30 bar |
During trading hours a time always means today, even if today's bar has not arrived. That way a dropped stream can never pass yesterday's value off as today's.
A time later today
If the time has not been reached yet, the cell says when it will have a value. At 11:00, =RTI_High("AAPL", 5, "15:00") shows Available from 15:00 ET, and fills in when the 15:00 bar starts. With hourly bars it shows Available from 14:30 ET, because that is when the bar containing 15:00 starts.
Times outside the session
Bars cover 09:30 to 16:00 only, so a time before 09:30 or from 16:00 on gets an immediate message: Time must be in the regular session, 09:30 to 15:59 ET.
Price and volume functions
| Function | Returns |
|---|---|
RTI_Last(symbol, [interval], [index]) | Last traded price of the bar |
RTI_Open(symbol, [interval], [index]) | Open of the bar |
RTI_High(symbol, [interval], [index]) | High of the bar |
RTI_Low(symbol, [interval], [index]) | Low of the bar |
RTI_Close(symbol, [interval], [index]) | Close of the bar. While the bar is forming, this equals RTI_Last. |
RTI_Volume(symbol, [interval], [index]) | Volume traded in the bar, partial while the bar is forming |
RTI_Time(symbol, [interval], [index]) | Start time of the bar (ET), as an Excel date-time. Format the cell as a time. |
RTI_Stream_Value(symbol, [interval], [lookback], [field], [index]) | Any of the above by name: "Last", "Open", "High", "Low", "Close", "Volume", "VWAP" or "Time" |
=RTI_Last("AAPL") ' latest price
=RTI_Close("AAPL", 5, 1) ' close of the last completed 5-minute bar
=RTI_Time("AAPL", 5, 1) ' when that bar started
Formula documentation: RTI_Last, RTI_Close, RTI_Time
Indicator functions
| Function | Default lookback | Calculation | Bars needed for a first value |
|---|---|---|---|
RTI_SMA(symbol, [interval], [lookback], [field], [index]) | 20 | Simple moving average of the last N bars | N |
RTI_EMA(symbol, [interval], [lookback], [field], [index]) | 20 | Exponential moving average over N bars | N |
RTI_RSI(symbol, [interval], [lookback], [field], [index]) | 20 | Wilder's RSI, 0 to 100 | N + 1 |
RTI_MACD(symbol, [interval], [lookback], [field], [component], [index]) | 50 | MACD 12/26/9: the MACD line (default), "signal" or "histogram" | 35 |
RTI_MACD_Signal(symbol, [interval], [lookback], [field], [index]) | 50 | MACD signal line: 9-bar EMA of the MACD line | 35 |
RTI_MACD_Histogram(symbol, [interval], [lookback], [field], [index]) | 50 | MACD line minus signal line | 35 |
RTI_BollingerBands(symbol, [interval], [lookback], [field], [component], [index]) | 20 | N-bar SMA ("middle", the default) with bands 2 standard deviations away ("upper", "lower") | N |
RTI_ATR(symbol, [interval], [lookback], [index]) | 14 | Average True Range | N + 1 |
RTI_Stochastic(symbol, [interval], [lookback], [component], [index]) | 14 | Slow stochastic: %K ("k", the default) smoothed over 3 bars, %D ("d") the 3-bar average of %K, both 0 to 100 | N + 4 |
RTI_ADX(symbol, [interval], [lookback], [index]) | 14 | Average Directional Index: trend strength, 0 to 100, not direction | 2N |
RTI_MFI(symbol, [interval], [lookback], [index]) | 14 | Money Flow Index: a volume-weighted RSI, 0 to 100 | N + 1 |
RTI_VWAP(symbol, [interval], [index]) | none | Volume-weighted average price for the session, as of the bar | 1 |
RTI_OBV(symbol, [interval], [index]) | none | On-Balance Volume | 2 |
RTI_AD(symbol, [interval], [index]) | none | Accumulation/Distribution line | 2 |
Notes on particular indicators:
- MACD always uses 12, 26 and 9 bars. Its lookback argument only sets how much history is kept, not the periods.
- VWAP restarts each session. It is QuoteMedia's own session VWAP when the stream reports one; otherwise MarketXLS calculates it from the session's bars (typical price, (high + low + close) / 3, weighted by volume).
- OBV and A/D are running totals from the first bar loaded. Their level depends on how much history is loaded, so compare their change or direction, not the number itself.
=RTI_RSI("AAPL", 5, 14) ' 14-bar RSI on 5-minute bars
=RTI_MACD("AAPL", 5, 50, "Last", "histogram") ' MACD histogram
=RTI_BollingerBands("AAPL", 5, 20, "Last", "upper")
=RTI_Stochastic("AAPL", 5, 14, "d") ' %D line
=RTI_VWAP("AAPL", 5)
Formula documentation: RTI_RSI
How far back history goes
History reaches back at most 15 trading sessions of 390 minutes each. An indicator needs its "bars needed" count of bars at your interval, so the interval times that count has to fit in those 15 sessions. A 20-bar RSI on 60-minute bars needs 21 hours of bars and is ready at once. A 20-bar RSI on 390-minute (one-session) bars needs 21 sessions, more than 15, and so never gets a value.
Time-of-day and minutes-ago functions
These work on 1-minute bars.
| Function | Returns |
|---|---|
RTI_Price_At_Min(symbol, time) | Close of the 1-minute bar starting at that time |
RTI_Volume_At_Min(symbol, time) | Volume of that 1-minute bar |
RTI_Price_Change_From_Min(symbol, time) | Latest price minus the close at that time |
RTI_Price_ChangePct_From_Min(symbol, time) | The same change as a fraction (0.0123 = 1.23%); format the cell as a percentage |
RTI_Price_X_Min_Ago(symbol, minutes) | Close of the 1-minute bar the given number of minutes ago |
RTI_PriceChange_X_Min_Ago(symbol, minutes) | Latest price minus that price |
RTI_PriceChangePct_X_Min_Ago(symbol, minutes) | The same change as a fraction |
RTI_HighestPrice_in_X_Mins(symbol, minutes) | Highest bar high over the last given minutes |
RTI_LowestPrice_in_X_Mins(symbol, minutes) | Lowest bar low over the last given minutes |
The time argument takes the same forms, and follows the same day rules, as the index argument above. minutes is a whole number from 0 to 1440, counted back from the current clock time, not from the last trade. When no bars fall in the last given minutes, at a weekend for example, the highest and lowest functions show #N/A. The minutes-ago price then returns the last price before that moment.
=RTI_Price_ChangePct_From_Min("AAPL", "09:30") ' move since the open
=RTI_HighestPrice_in_X_Mins("AAPL", 30) ' 30-minute high
Formula documentation: RTI_Price_ChangePct_From_Min, RTI_HighestPrice_in_X_Mins
RTI_Status: why a cell shows #N/A
RTI_Status(symbol, [interval], [lookback]) explains what an RTI cell is waiting for. It reports:
- whether enough bars are loaded for the lookback
- the range of bars held
- whether history loaded, is loading, or failed (with QuoteMedia's reason)
- whether the symbol is streaming, and when the last trade arrived
- whether the market is open
=RTI_Status("AAPL")
What the cell is telling you
| The cell shows | Meaning |
|---|---|
| A number | The value. It updates as trades arrive. |
#N/A | No value yet: history is still loading (a few seconds), the bar asked for has no trades, there are not enough bars, or QuoteMedia does not recognise the ticker. RTI_Status says which. |
Available from 15:00 ET | You asked for a time later today. The value appears when that bar starts. |
Time must be in the regular session, 09:30 to 15:59 ET | The time is outside the hours the bars cover. |
Index must be a bar offset (0 = bar now forming, 1 = last completed bar) or a time such as "09:30" | The index is not a whole number or a recognisable time. |
Index cell contains an error | The cell given as the index holds an error such as #N/A. |
Interval must be between 1 and 1440 minutes | Interval out of range. |
Lookback must be between 2 and 200 bars | Lookback out of range. |
Minutes must be between 0 and 1440 | Minutes out of range in a minutes-ago function. |
NA | The symbol cell is blank or holds an error. |
QuoteMedia is not configured... | Set up the stream: Settings > Data Subscriptions > QuoteMedia. |
#N/A is an Excel error on purpose. A formula such as =IF(RTI_RSI("AAPL",5,14)>70,"SELL","") then shows #N/A while the RSI loads, instead of a false SELL. To show a blank instead, wrap it: =IFNA(RTI_RSI("AAPL",5,14), "").
Worked examples
Each example uses AAPL; replace it with a cell reference such as A2 to run it down a list of tickers. Where an example compares two bars, it reads completed bars (index 1 and 2), so a signal does not flicker while the current bar is still forming.
Opening range
The opening range is the high and low of the first minutes of the session. Pick its length with the interval:
=RTI_High("AAPL", 15, "09:30") ' high of the first 15 minutes
=RTI_Low("AAPL", 15, "09:30") ' low of the first 15 minutes
=RTI_High("AAPL", 60, "09:30") ' high of the first hour
Formula documentation: RTI_High, RTI_Low
Where the price stands against it:
=IF(RTI_Last("AAPL") > RTI_High("AAPL",15,"09:30"), "ABOVE OPENING RANGE",
IF(RTI_Last("AAPL") < RTI_Low("AAPL",15,"09:30"), "BELOW OPENING RANGE", "INSIDE"))
Formula documentation: RTI_Last, RTI_High, RTI_Low
How wide the range is, as a share of the price:
=(RTI_High("AAPL",15,"09:30") - RTI_Low("AAPL",15,"09:30")) / RTI_Open("AAPL",15,"09:30")
Formula documentation: RTI_High, RTI_Low, RTI_Open
Before 09:45 the 15-minute range is still forming, so these values change until the bar closes. Before the open and at weekends they show the last session's range.
Checkpoints through the day
Prices at fixed times, and the move since each one. Put the times in a row (B1 = 10:00, C1 = 11:00, D1 = 12:00, typed as times) and the ticker in A2:
=RTI_Price_At_Min($A2, B$1) ' price at 10:00
=RTI_Price_ChangePct_From_Min($A2, B$1) ' change since 10:00, as a fraction
Formula documentation: RTI_Price_At_Min, RTI_Price_ChangePct_From_Min
Copy across for the other times. Checkpoints that have not been reached yet show Available from 11:00 ET until they are.
Volume in the first hour compared with the hour just finished:
=RTI_Volume("AAPL", 60, "09:30") ' 09:30-10:30 volume
=RTI_Volume("AAPL", 60, 1) ' volume of the last completed hourly bar
Formula documentation: RTI_Volume
Signals on completed bars
Each of these returns TRUE on the bar where the event happened, and FALSE otherwise.
RSI crosses up through 30:
=AND(RTI_RSI("AAPL",5,14,"Last",2) < 30, RTI_RSI("AAPL",5,14,"Last",1) >= 30)
Formula documentation: RTI_RSI
MACD histogram turns positive:
=AND(RTI_MACD_Histogram("AAPL",5,50,"Last",2) < 0, RTI_MACD_Histogram("AAPL",5,50,"Last",1) >= 0)
Price closes back above VWAP:
=AND(RTI_Close("AAPL",5,2) < RTI_VWAP("AAPL",5,2), RTI_Close("AAPL",5,1) > RTI_VWAP("AAPL",5,1))
Formula documentation: RTI_Close
Close outside the upper Bollinger Band:
=RTI_Close("AAPL",5,1) > RTI_BollingerBands("AAPL",5,20,"Last","upper",1)
Formula documentation: RTI_Close
When the last bar started, to stamp a signal with its time (format the cell as hh:mm):
=RTI_Time("AAPL", 5, 1)
Formula documentation: RTI_Time
Two timeframes together
A 5-minute RSI signal, taken only when the hourly ADX shows a trend:
=IF(AND(RTI_ADX("AAPL",60,14) > 25, RTI_RSI("AAPL",5,14) < 30), "OVERSOLD IN A TREND", "")
Formula documentation: RTI_RSI
The hourly ADX needs 28 hourly bars, about four sessions at seven bars a session. History covers that, so it has a value from the start.
Risk numbers
=RTI_Last("AAPL") - 2 * RTI_ATR("AAPL", 5, 14) ' stop 2 ATRs below the price
=RTI_ATR("AAPL", 5, 14) / RTI_Last("AAPL") ' ATR as a share of the price
=RTI_Last("AAPL") / RTI_VWAP("AAPL", 5) - 1 ' distance from VWAP
=RTI_HighestPrice_in_X_Mins("AAPL", 30) - RTI_LowestPrice_in_X_Mins("AAPL", 30) ' 30-minute range
Formula documentation: RTI_Last, RTI_HighestPrice_in_X_Mins, RTI_LowestPrice_in_X_Mins
A watchlist scanner
Tickers in A2:A20, and in row 2:
| Cell | Formula | Shows |
|---|---|---|
| B2 | =RTI_Last(A2) | Price |
| C2 | =RTI_Price_ChangePct_From_Min(A2, "09:30") | Move since the open |
| D2 | =RTI_Last(A2) / RTI_VWAP(A2, 5) - 1 | Distance from VWAP |
| E2 | =RTI_RSI(A2, 5, 14) | RSI (14) |
| F2 | =IF(RTI_Last(A2) > RTI_High(A2,15,"09:30"), "UP", IF(RTI_Last(A2) < RTI_Low(A2,15,"09:30"), "DOWN", "")) | Opening-range breakout |
Copy row 2 down to row 20. Format C and D as percentages and add conditional formatting to colour E above 70 and below 30. Wrap a column in IFNA(..., "") if you would rather see blanks than #N/A while history loads.
A dashboard on one symbol
Put a ticker in A2 and these formulas next to it:
| Cell | Formula | Shows |
|---|---|---|
| B2 | =RTI_Last(A2) | Price |
| C2 | =RTI_VWAP(A2, 5) | Session VWAP |
| D2 | =RTI_High(A2, 5, "09:30") | Opening-range high |
| E2 | =RTI_Low(A2, 5, "09:30") | Opening-range low |
| F2 | =RTI_RSI(A2, 5, 14) | RSI (14) |
| G2 | =RTI_MACD_Histogram(A2, 5) | MACD histogram |
| H2 | =RTI_ADX(A2, 5, 14) | ADX (14) |
| I2 | =RTI_ATR(A2, 5, 14) | ATR (14) |
| J2 | =RTI_Status(A2) | Data state |
Copy row 2 down for more tickers. Every cell for the same symbol reads the same 1-minute bars, so adding indicators does not add downloads.
Complete function list
| Function | Group |
|---|---|
RTI_Last, RTI_Open, RTI_High, RTI_Low, RTI_Close, RTI_Volume, RTI_Time, RTI_Stream_Value | Price and volume |
RTI_SMA, RTI_EMA | Moving averages |
RTI_RSI, RTI_MACD, RTI_MACD_Signal, RTI_MACD_Histogram, RTI_Stochastic | Momentum |
RTI_BollingerBands, RTI_ATR | Volatility |
RTI_ADX | Trend strength |
RTI_VWAP, RTI_OBV, RTI_MFI, RTI_AD | Volume |
RTI_Price_At_Min, RTI_Volume_At_Min, RTI_Price_Change_From_Min, RTI_Price_ChangePct_From_Min | Time of day |
RTI_Price_X_Min_Ago, RTI_PriceChange_X_Min_Ago, RTI_PriceChangePct_X_Min_Ago, RTI_HighestPrice_in_X_Mins, RTI_LowestPrice_in_X_Mins | Minutes ago |
RTI_Status | Diagnostics |
This is educational content, not investment advice.
Get started: download MarketXLS and set up the QuoteMedia stream.
