Using the xlq2 Com interface to Norgate Data and VBA, a spreadsheet showing current S&P 1500 components can be built in 3 to 5 seconds.
MV$B – Market value in billions = shares outstanding * closing price. Most of the time market value of a common stock is the same as market capitalization. MV is a slightly better practical choice because market capitalization can be misleading if a company has multiple common stock classes in play, such as GOOG and GOOGL. Personally, taking time trying to get any fundamental number exactly right seems like a fool’s errand.
GICS – S&P (SPGI) assigns every security a code based on the Global Industry Classification Standard. At the simplest level, the GICS code contains clear and comprehensive sector and industry information.
SIe and Sub-Industry. The 8 digit GICS code identifies the sub-industry of the security. The SIe is the element number of the GICS sub-industry array. The SIe and the sub-industry description are inserted by the VBA code.
The SIe is stored for future direct access to the GICS structure. A major performance goal of the prototype is to minimize array lookups.
A copy of the S&P 1500 spreadsheet can be downloaded below.
The GICSArray spreadsheet can be downloaded below. This is more stable than the S&P 1500 list. It was last changed in March, 2023.
MV$B is arguably the single most useful fundamental number for an equity. The fact that market value can be summed gives the developer the opportunity to practice basic logical skills on the GICS structure.
The basic technical problem is to go from GICSArray to GICSSumm.
Only the Sector and Industry Groups are shown. The entire GICSSumm spreadsheet for 8/11 can be downloaded below. As long as the sub-industry element is known from GICSsp1500, GICSSumm can be built instantaneously without any array lookups.
