Moving Through The GICS Structure With Excel VBA

The GICS logical structure is remarkably good and amendable to simple but powerful customized lower level logic. This post continues the discussion from S&P 1500 GICS Codes.

IT is not considered research and development by either Wall Street Broker Dealers or the myriad small financial data firms on the web catering to amateurs. Consequently, the full analytical potential of the GICS structure is not widely understood. It seems that only the most wealthy and sophisticated market practitioners attempt to deal with it.

While it is true that idiots are unable to competently deal with the perceived complexities, they are not all that difficult to address if one avoids panic.

Once the GICSSumm and GICSsp1500 worksheets are conceived; it is possible to practice and evaluate basic data manipulation techniques.

Sub-Industry View

The study starts at the top of the GICS structure and works it’s way down to the S&P 1500 components of a specific sub-industry. This focused view of the sub-industry is a very useful starting structure for more detailed analysis of the associated equities.

It should go without saying that financial data manipulation recommendations from firms with the market value of MSCI and SPGI should be treated with respect.

Notes On VBA Coding

VBA for Excel Office 365 and 2021 finally supports an array sort as part of their major dynamic array functions upgrade. Ironically, most of the other dynamic array functions require worksheet cell ranges and don’t work with arrays, making them of dubious value to algorithm developers. However, the new functions are interesting and probably will be enhanced in the future.

After loading the SP1500 worksheet into an array and sorting it, only a single lookup (.XMatch) is required to produce the study. All other addressing issues can be resolved with arithmetic.

siGICS = 40203040

With Application.WorksheetFunction
aGrpX = .Sort(aGrpX, 3)
xw = .XMatch(siGICS, .Index(aGrpX, 0, 3))
End With

.Index is critical for making two dimensional arrays easily usable. Thinking “row, column” while studying these things helped me a lot.

Sector View

This type of operation is called an explosion. The Financial sector is comprised of three industry groups: Banks, Financial Services, and Insurance. Capital Markets is a euphemism for Wall Street with Financial Exchanges & Data one of the three associated sub-industries.

The explosion logic requires three lookups in the GICSSumm array structure before simple arithmetic can be used resolve any addressing issues.

I started in IT as a computer operator in the 70s and it seemed self evident at that time that any competent coder should make an effort to get skilled in explosion logic because it occurs so frequently. It really surprises me that over 99% of professionals apparently don’t think that way.

In the next post, I’ll look at applying return and statistical analysis to GICS.

Leave a Reply