Chart
Add a Chart
Click on the Chart icon in the ribbon to pull up the Insert Chart dialogue.
The symbol search functionality here is exactly the same as that of the Insert Quote dialogue. Enter in the ticker if known or use the exchange tree or the Exchange or instrument drop downs.
Historical Aggregation
Once you click on the Aggregation drop down you will see all of the available periods that can be selected.
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 Fetch options or a Date Range.
For Intraday: Tick, 1 minute, 2 minute, 3 minute, 5 minute, 10 minute, 15 minute, 30 minute, hourly and custom minute query.
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.
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).
Now you will want to choose your sort option. The historical data that builds charts automatically populates when creating a chart and sorting gives you the option to view this data in ascending or descending order. Ascending will display the data starting with the first data point and descending will start with the most recent data point.
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.
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.
The Fetch Default time periods can be found here. Using the Last option, you can opt to view the last n days or bars from a selected date. For example, I could choose to display the last 100 days from December 2nd, 2019 rather than just the last 100 days from today's date.
Using the Date Range option, you can customize the start and end dates by clicking on the chart icons and selecting your specific dates.
The View option will allow you to display the historical data for the chart either horizontally or vertically in the spreadsheet. Lastly you will want to select the Chart (bar) type.
Once all fields and options are selected click Insert and the chart will render in the spreadsheet with the corresponding historical data.
Edit a Chart
You can edit a chart’s data by clicking on the Chart anchor cell and then clicking on Chart in the ribbon.
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.
Chart Removal
Charts inserted into a worksheet can be quickly removed by highlighting the Chart anchor cell, Clicking on the Chart icon and selecting the Clear button.