Time Series (Tables)
- Chris Harrison
- Brendan Hebert (Unlicensed)
Add a History Table
Click the History icon on the ribbon as shown below
Use the Insert History dialog to search symbols by name, exchange and symbol type
Edit a History Table
Edit existing historical data by highlighting the # symbol in the spreadsheet and then clicking the History icon on the ribbon. Make desired changes including the addition, removal or reordering of symbols and fields and then select the Insert button to insert the newly formatted data into the spreadsheet.
History Fields
A History link can contain any of the following fields:
Field | Description |
|---|---|
High | The highest price paid during the current trading session. |
Last | The last price paid during the current trading session. |
Low | The lowest price paid during the current trading session. |
Open | The first price paid during the current trading session. |
Open Interest | The total number of futures contracts traded that have not yet been liquidated either by an offsetting futures transaction or by delivery. |
Symbol | The ticker symbol. |
Volume | The total number of contracts traded during the current trading session. |
Volatility | Implied Volatility is the estimated volatility of the underlying stock over the term of the option (only available for equity options). |
Theoretical Value | The hypothetical value of the options, calculated by the Black-Scholes Option Pricing Model (only available for equity options). |
Delta | Measures the sensitivity of an option’s theoretical value to a change in the underlying price (only available for equity options). |
Gamma | Measures the rate of change in the delta for every one point move in the underlying (only available for equity options). |
Theta | The measure of the time decay of an option, the dollar amount that an option will lose each day due to the passage of time (only available for equity options). |
Vega | Measures the sensitivity of the price of an option to changes in volatility (only available for equity options). |
Rho | The rate at which the price of an option changes relative to a change in the risk-free rate of interest (only available for equity options). |
History aggregations
History aggregation / Period
The aggregation (sometimes referred to as the period) defines the amount of data represented by each bar. For example, selecting a Time aggregation of "15 minutes" results in a chart with a bar drawn at every 15 minute aggregation. The visible amount of data that is displayed on the chart is determined by the Time Range. You can use EITHER of these two settings to display data on the chart:
When you set a specific Time aggregation (period), the Time Range (visible amount of data) is set by default depending on the physical size of the chart. You may view more data (older or newer) by scrolling the time axis.
When you set a specific Time Range (visible amount of data), the aggregation (period) is set by default. For example, you've set an aggregation of 15-minute bars, but then change the Time Range to be 5 Years. The chart cannot show 5-years of 15-minute bars, so it adjusts to show you 5 years of data using monthly bars.
Change The aggregation
Click the aggregation drop-down list at the top of the chart (this appears just to the left of the Time Range button) and select from an option shown.
ORRight-click on the chart to display the context menu, highlight Time aggregation, and select from an option shown.
Aggregations for Commodities Symbols
For Intraday: Tick, Top of Book (BBO), 1 minute, 2 minute, 3 minute, 5 minute, 10 minute, 15 minute, 30 minute, hourly and custom minute query. Top of Book (BBO) contains top bid, bid size, top ask, ask size, last trade, and trade volume for both stocks and futures.
For Nearest Intraday: 1 minute nearest, 2 minute nearest, 3 minute nearest, 5 minute nearest, 10 minute nearest, 15 minute nearest, 30 minute nearest, hourly nearest and custom minute query (The results of the query will include data for the active contracts during the period requested).
For Daily / Weekly / Monthly / Quarterly / Yearly: Shows the historical trading activity only for the contract specified.
For Daily Nearest / Weekly Nearest / Monthly Nearest / Quarterly Nearest / Yearly Nearest: Builds a historical chart using the nearest contract. The nearest contract is the closest unexpired contract for the underlying commodity, where every chart bar represents the front month at that point in history.
For Daily Continue / Weekly Continue / Monthly Continue / Quarterly Continue / Yearly Continue: Builds a historical chart using the same month specified for the original contract. For example, if the symbol is ZSX13 (Soybeans November 2013), the chart is built using all previous November contracts for Soybeans (ZSX13, ZSX12, ZSX11, ZSX10 etc).
Futures Contract Roll
If one of the symbols selected is a futures contract and and either the ‘Nearest’ or ‘Continue’ aggregations have been selected, a new menu will display allowing you to select the futures roll method.
The default futures roll method is Volume and Open Interest. When the value specified is ‘Volume', a combination of volume and open interest will be used to determine when to switch from one contract to the next in the series. When ‘On Expiration’ or any of the ‘n’ Days Before options are selected, the switch from one contract to the next in the series will be based on the expiration date (and the value of the ‘’days before’ expiration date).
Adjustments
Historical data can be adjusted for both futures and stocks using the Adjustments drop down. When ‘Back adjust-continuations and nearest’ is selected, the futures price history is adjusted when switching contract months. This parameter specifies whether the contracts in the series will be adjusted based on the roll-gap between the closing prices of the current contract and the previous contract on the day of the switch.
For stock data, ‘Back-adjust equities for splits’ is selected by default. Unless unchecked, historical stock data will be adjusted for stock splits. Stocks can also be back adjusted adjusted for dividends. By default, dividend back adjust is not selected but when checked, price history is adjusted for dividends.
Padded Holiday
The Padded Holiday feature will align historical data by accounting for holidays when comparing instruments that trade on different exchanges. Below is an example of Barrick Gold traded on TSX and NYSE where the red outlined data set is not adjusted and the blue outlined data set is padded for holidays.
Equity Pre and Post Market Data
Historical extended hours equity data is also available when using History. In order to download this data, select a stock(s) or ETF(s) and for the Aggregation use one of the Intraday options. Once intraday is selected, an “Extended Hours' field will display in the Dialogue. If checked, pre and post market data will be included in the download.
Favorites
After inserting any symbol or study, the Favorites tab will save these instruments and studies (including study periods/parameters) for easy insertion later.
Upon opening Favorites, you will see a list of the last 20 symbols whose historical data was viewed. Double click, click and drag or highlight a grouping of symbols and click enter to add these symbols to the insert dialogue.
Once all symbols are selected, click on the Studies tab to see the last 20 studies applied.
Double click, click and drag or highlight any or all studies and hit enter to insert them into the dialogue.
(Note: if more than one symbol is selected and you wish to apply the same indicator to all symbols, be sure to highlight the symbols in the insert dialogue).
The Favorites dialogue can be enlarged by clicking on the pin icon in the upper right corner and then hidden by clicking the pin icon once again.