This is perhaps my most ambitious shared Google Sheet. It is similar to the S&P500 sheet, but with more data.
Here’s the methodology:
I used this document to get a list of the stocks which make up the Russell 2000. This is only updated once a year in June. By my count it listed 1,975 companies:
https://www.russell.com/documents/indexes/membership/members…
I manually copied and pasted the data from this document into a Google Sheet. I used the function documented in my Spreadsheet tip of the day in a previous post to parse out the ticker symbols and names.
I then downloaded lists of all the stocks on the NASDAQ, NYSE and AMEX exchanges from the NASDAQ site here:
http://www.nasdaq.com/screening/company-list.aspx
and consolidated them into a single StockInfo tab on my Google Sheet. I used this to look up the Sector and Industry data for each stock using Google Sheet functions.
The Google Sheet is here:
https://docs.google.com/spreadsheets/d/1QVc15-LuowWMj0bX86wL…
The Data tab is the central repository for all the stock data. It has the following columns:
A - The stock name and ticker as it came from the Russell 2000 document
B - Stock ticker - obtained from column A using a Google function - see row 1 for the functions. I did a copy/paste values after I had set up the data, to eliminate too many function calls.
C - Stock name - obtained from column A using a Google function
D - The row number of the ticker from column B in the StockInfo sheet
=match(B2,StockInfo!$A$1:$A$6714,0)
E - Stock Sector - =index(NDaqSector,D2)
F - Stock Industry - =index(NDaqIndustry,D2)
Note the range names NDaqSector and NDaqIndustry were what I named them originally. They actually include stocks from all 3 exchanges - I was too lazy to change the range names.
G - Mkt Cap $mill - =Googlefinance(B2,“marketcap”)/1000000
H - Mkt Cap Rank - =rank(G2,G$2:G$1899)+countif(G$2:G2,G2)-1
I - Current Price - =googlefinance(B2)
J - Today’s % chg - =googlefinance(B2,“changepct”)
K - 52 week high - =googlefinance(B2,“high52”)
L - % off high - =I2/K2-1
M - Rank % off high - =rank(L2,L$2:L$1899,1)+countif(L$2:L2,L2)-1
N - 52 week low - =googlefinance(B2,“low52”)
O - % above low - =I2/N2-1
P - Rank % above low - =rank(O2,O$2:O$1899)+countif(O$2:O2,O2)-1
R - Price 12/31/14 - =index(googlefinance(B2,“close”, “12/31/14”),2,2)
S - Price 12/31/15 - =index(googlefinance(B2,“close”, “12/31/15”),2,2)
T - % chg 2015 - -11.7%
U - Rank % Chg 2015 - =rank(T2,T$2:T$1899)+countif(T$2:T2,T2)-1
W - % Chg YTD - =I2/S2-1
X - Rank % Chg YTD - =rank(W2,W$2:W$1899)+countif(W$2:W2,W2)-1
Note that in columns R & S, I converted most of the formulas to values using edit copy/paste values. I find that when you are obtaining a large number of stock prices on specific dates, the GoogleFinance() function returns a lot of #NA’s somewhat randomly, so once you have obtained the correct value it is better to lock it down by pasting in the value.
As I was working my way through the 12/31/14 and 12/31/15 prices, I found a number of companies which had been acquired, merged, filed for bankruptcy or otherwise had no current stock quotes, so I moved them down to the bottom of the data sheet - these are in rows 1904 - 1980 - a total of 77 companies and needless to say, they can’t be included in the other tables. Also there were a significant number of companies which started trading after 12/31/14, so I set their 12/31/14 price to the price on the first day of trading and noted that date as a comment in the cell.
On the Rankings tab, I have included the following 4 tables:
Sorted by Market Cap
Sorted by % off 52 week high
Sorted by 2015 % gain
Sorted by YTD % gain
These tables are set up similarly to the S&P 500 tables, so look at my post on that if you need to understand how these work. They are dynamic tables and will update automatically as market prices change.
On the Sector/Industry Summaries tab, I started to include summaries by Sector and Industry. I am having a problem with the formula for the Industry section, so I have left that unfinished - I had a number of finicky issues with the various formulas where they wouldn’t work unless I explicitly specified additional parameters which should have already been the default. If anyone sees the problem with the formulas in D16, E16 and D17, which are reporting Array arguments to SUMIFS are of different size., let me know.
The average Russell 2000 stock is 39.4% off it’s high. Energy is off 67.1% and Health Care is off 53.8%. Other sectors are shown below.
Sector # of Total Mkt Cap Avg % Off High
Cos $mill
Total Russell 2000 1898 $1,693,078.9 -39.4%
Basic Industries 98 $75,053.9 -46.2%
Capital Goods 164 $134,294.8 -37.4%
Consumer Durables 56 $55,309.3 -30.6%
Consumer Non-Durables 81 $88,043.6 -35.5%
Consumer Services 346 $352,695.8 -36.3%
Energy 71 $33,371.1 -67.1%
Finance 336 $310,206.4 -25.9%
Health Care 306 $210,425.1 -53.8%
Miscellaneous 60 $57,258.0 -39.9%
Public Utilities 67 $87,431.3 -23.2%
Technology 256 $233,619.5 -40.9%
Transportation 44 $44,361.0 -49.4%
John
If you found this post useful, please click the “Recommend It!” link at the top right. Thank you.