One of the ways users get confused using STOCKDATA is by trying to get quote attributes with historical candlestick requests or by expecting to get a candlestick with a real-time request. It helps to understand the difference between candles vs quotes when it comes to data. Candlestick data is a summary of trades that did take place in the past while quote data is pricing data for trades that could take place in the future.
| Stock Quote Attributes | Stock Candlestick Attributes |
|---|---|
|
|
Perhaps the biggest confusion relates to traders who are requesting the “open candle” or “current candle” and referring to the closing price on this candle. This candle, whether it is the daily or intraday candle, will have its “close” price match the a stock quote’s “last” price. These traders are under the impression that requesting the current candlestick can be the equivalent of a stock quote. This is not the case. The “last” price that is included with a stock quote is historical data and represents the last trade that took place. There is no guarantee that subsequent trades can also be executed at the same price. The only way to know what price a future trade can be executed at is by looking at the stock’s current quote.
How To Get Quote Data Using STOCKDATA
To get abbreviated quote data, just use =STOCKDATA("AAPL") and you’ll get the midpoint price (between the bid and the ask). For a complete top of book level 1 quote, add the “all” attribute =STOCKDATA("AAPL","ALL") and the formula will output all the attributes shown above (including the last price). Only use 2 parameters. By not requesting any date at all, the system knows you want a quote. If you use any date parameter, even requesting today’s price, the formula will output a candlestick and not a quote.
How to Get Candle Data Using STOCKDATA
To get a candle you need to include at least one date in the formula. For example, today’s daily candle can be requested by using =STOCKDATA("AAPL","ALL","TODAY"). This will output the current daily candle for the stock. Notice how in the example the last price of the quote and the close price of the daily candle are the same. This is the only data point between the two stockdata outputs that will be the same.
When To Use Candles vs Quotes
If you need historical data about a stock or you want to graph your stock on a chart, candle data is the data you’re looking for. Quote data will only tell you how much a stock is worth at this exact moment (i.e. the second you open your spreadsheet). If you want to know how much the stock was trading for at any point in the past, then candle data is what you need.
However, if you are thinking about opening a new trade or closing an existing trade, you need to use quotes, since this will represent the price you will receive if you open or close a trade in the stock in question. Candlestick data (even the current candle) is not as accurate as quote data. And in thinly traded stocks there can be large differences in the open candle’s closing price and the bid/ask prices of the current quote. The only scenario where the last price will match closely with the bid/ask is on extremely liquid tickers like AAPL or SPY. Even then, we don’t recommend using open candle’s closing price, since it is a bad practice and there’s no good reason to do so.
Market Data offers both candles and quotes, so please make sure you use the appropriate data for your use-case.
