This post is an update to: Excel 365: NASDAQ 100 Screening Dashboard.
Instead of the NASDAQ 100 Index, the iShares S&P 100 ETF (Symbol: S.OEF) is used. The ETF seeks to track an index composed of 100 U.S. mega-cap blue-chip stocks. The index is the S&P 100. From Wikipedia: "The S&P 100 is a subset of the S&P 500 and the S&P 1500, and holds stocks that tend to be the largest and most established companies in the S&P 500. However, the S&P 100 actually includes 101 larger US company stocks due to holding two different share classes of Alphabet Inc."
The original post offered an Excel dashboard that screened the holdings of the NASDAQ 100 for market performance characteristics including the weekly, monthly, quarterly, and annual top ten percentage performers and bottom ten percentage performers. In addition, stocks with high volume trading, large net changes, and large Z-Scores are identified.
The NASDAQ 100 Index holdings have been replaced with the iShares S&P 100 ETF (Symbol: S.OEF) holdings. This Excel dashboard includes a “Search Bar” at the bottom of the dashboard.
Enter the symbol with the addition of “S.” using all capital letters. Only companies listed on the Data Tab are available.
The market performance stats returned are in the bottom row: Symbol, Company, Last Trade, NC, %NC, Open, High, Low, the percentage change using Weekly, Monthly, Quarterly, and Annual views. In addition, the current session's Volume, the Volume Ratio compared to the 21-day Moving average of the Volume, the Net Change ratio to the 21 -day Average True Range, the 21-day Moving Average, the 21-Day Standard Deviation, and the Z-Score ((Close-21 MA)/ 21-Day Standard Deviation).
The top right-hand corner lists all of the same statistics for the iShares S&P 100 ETF (Symbol: S.OEF).
Below is the image of the new Dashboard:
For all of the details regarding RTD and Excel functions used in the dashboard please review the original post: Excel 365: NASDAQ 100 Screening Dashboard.
The downloadable Excel sample is locked (to avoid accidental editing) but not password protected.
Wikipedia provides a list of the symbols in the S&P 100.
Make sure to lower your Excel Realtime Data (RTD) throttle to 100 milliseconds. Learn how to do that here.
Requires CQG Integrated Client or CQG QTrader, data enablements for the NYSE and Nasdaq stocks, and Excel 2016 or more recent locally installed, not in the Cloud.