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 |
|---|---|
|
|
One common point of confusion involves traders requesting the “open candle” or “current candle” and comparing its closing price to a stock quote’s last price. It’s important to understand that candlestick data is based on exchange data is slightly delayed, while quotes for paid users are updated in real-time. As a result, the closing price of a candlestick may not exactly match the latest quoted price.
The “last” price shown in a stock quote reflects the most recent completed trade in real time. However, it does not guarantee that future trades can occur at the same price — for that, traders should look at the current bid and ask prices included with the stock 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 latest available daily candle for the stock, based on official exchange data. The last price from the quote and the close price from the daily candle may differ slightly. Quotes update in real-time for paid users, while daily candlestick data reflects exchange data with a 15 minute delay.
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.