History

Add Historical Data

Click on the History icon in the ribbon to pull up the Insert History dialogue.

Use the Insert History dialogue to search for symbols by name, exchange or symbol type similarly to what we’ve done for Quotes and Charts previously.

Select your Fields

Once the desired symbols are moved over to the insert pane (double click or click and drag over), you will want to select the Fields needed in the time series.

By clicking on the Edit Fields button you will see that History fields are split into three subgroups: Prices & Volume, Options and Options Overview. Fields can be added to the history query by checking the box next to the field, clicking and dragging the field name into the selected column area or selecting all by checking the box next to the subgroups name.

History Fields

Next you will want to select any and all Historical fields to customize the historical query to your exact requirements.

Field	Description
Name	Underlying company name or commodity
Open	Opening price for the day
High	The highest trade price for the day
Low	The lowest trade price for the day
Close	The last traded price
Sale Condition	A special condition associated with the trade
Symbol	The ticker symbol
Volume	The total number of shares or futures contracts traded for the day
OI	The total number of futures contracts or options traded that have not yet been liquidated either by an offsetting futures transaction or by delivery
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)
Bid	The highest price a buyer of a security or instrument is willing to pay
Ask	The lowest price a seller of a security or instrument is willing to offer
Implied Volatility	The ATM average of the implied volatility of the nearest monthly contract
IV Change	The underlying asset's change in implied volatility for the current trading session (only available for equity options)
IV Rank	The current IV compared to the highest and lowest values over the past 1-year. If IV Rank is 100% this means the IV is at its highest level over the past 1-year, and can signify the market is overbought (only available for equity options)
IV Percentile	The percentage of days with IV closing below the current IV value over the prior 1-year. A high IV Percentile means the current IV is at a higher level than for most of the past year. This would occur after a period of significant price movement, and a high IV Percentile can often predict a coming market reversal in price (only available for equity options)
Put/Call Volume Ratio	The total Put/Call volume ratio for all option contracts (across all expiration dates). A high put/call ratio can signify the market is oversold as more traders are buying puts rather than calls, and a low put/call ratio can signify the market is overbought as more traders are buying calls rather than puts (only available for equity options)
Options Volume	The total volume for all option contracts (across all expiration dates) traded during the current session (only available for equity options)
Put/Call Open Interest Ratio	The put/call open interest ratio for all options contracts across all expiration dates (only available for equity options)
Total OI	The total open interest for all option contracts across all expiration dates (only available for equity options)

History Aggregation

Once you click on the Aggregation drop down you will see all of the available periods that can be selected.

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 query using the nearest contract. The nearest contract is the closest unexpired contract for the underlying commodity based on a combination of volume and open interest.
Daily Continue / Weekly Continue / Monthly Continue / Quarterly Continue / Yearly Continue: Builds a historical query using the same month specified for the original contract. For example, if the symbol is ZSX13 (Soybeans November 2013), all previous November contracts for Soybeans (ZSX13, ZSX12, ZSX11, ZSX10 etc) are queried.

(Note: Historical Tick data is limited to the last 30 days)

Equity Sale Conditions

For ‘Tick’ and ‘Top of Book (BBO)’, below are the sale condition codes for NYSE/NYSE Arca and NASDAQ.

@ – Regular Sale
B – Average Price Trade
C – Cash Trade (Same Day Clearing)
E – Automatic Execution
F – Inter-market Sweep Order
H – Price Variation Trade
I – Odd Lot Trade
K – Rule 127 (NYSE Only) or Rule 155 (NYSE American only)
L – Sold Last (Late Reporting)
M – Market Center Official Close
N – Next Day Trade (Next Day Clearing)
O – Market Center Opening Trade
P – Prior Reference Price
Q – Market Center Official Open
R – Seller
T – Extended Hours Trade
U – Extended Hours Sold (Out Of Sequence)
V – Contingent Trade
X – Cross Trade
Z – Sold (Out Of Sequence)
4 – Derivatively Priced
5 – Market Center Reopening Trade
6 – Market Center Closing Trade
7 – Qualified Contingent Trade
8 – Reserved
9 – Corrected Consolidated Close Price as per Listing Market

Sale Condition Nasdaq - Denotes the sale condition associated with a trade (utp binnary 1.5 page 43)

@ – Regular Sale
A – Acquisition
B – Bunched Trade
C – Cash Sale
D – Distribution
E – Placeholder
F – Intermarket Sweep
G – Bunched Sold Trade
H – Price Variation Trade
I – Odd Lot Trade
K – Rule 155 Trade (AMEX)
L – Sold Last
M – Market Center Official Close
N – Next Day
O – Opening Prints
P – Prior Reference Price
Q – Market Center Official Open3
R – Seller
S – Split Trade
T – Form T
U – Extended trading hours (Sold Out of Sequence)
V – Contingent Trade
W – Average Price Trade
X – Cross Trade
Y – Yellow Flag Regular Trade
Z – Sold (out of sequence)
1 – Stopped Stock (Regular Trade)
4 – Derivatively priced
5 – Re-Opening Prints
6 – Closing Prints
7 – Qualified Contingent Trade
8 – Placeholder For 611 Exempt
9 – Corrected Consolidated Close (per listing market)

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).

Next you will want to choose your sort option. Ascending will display the data starting with the first data point and descending will start with the most recent data point (default is descending).

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.

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 “n” date. For example, you 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 either horizontally or vertically in the spreadsheet.

Edit History

Once inserted, you can edit a Historical query by highlighting the Time Series anchor cell and then clicking on the History icon in 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.

(Note: Historical data queries are capped at Excel’s maximum limit in terms of the total number of rows and columns in a worksheet at 1,048,576).

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.

History Refresh

By default, Barchart for Excel’s historical data refreshes automatically when the sheet becomes active. This auto refresh can be turned off by clicking on the Refresh icon and selecting the ‘Turn refresh off’ option.

Once turned off, this preference will be saved to your Barchart for Excel account.

History Removal

Historical data inserted into a worksheet can be quickly removed by highlighting the Time Series anchor cell, Clicking on the History icon and selecting the Clear button.