Thanks for the chart on market capitalizations on Twitter (https://twitter.com/Optuma/status/1452923394945601538?) and your forum post (https://forum.optuma.com/topic/twitter-charts/page/2/#post-66377).
Regarding market capitalization, I have a question about the stock weights in the S&P 500 and Nasdaq 100.
Is it possible to use the “DATAFIELD” function to calculate the percentage weighting of the market capitalization of each individual stock in the S&P 500 and Nasdaq 100 or at least for some of them?
It should be possible to display the current market capitalization percentage weights in the form of a watchlist column as well as a chart showing the history of the market capitalization weights.
If this is possible, please post the scripts in your Twitter forum post (https://forum.optuma.com/topic/twitter-charts) so that all the information is in one place.
I did not want to post this post in your Twitter forum topic so that the Twitter forum post is a “clean” Optuma topic.
Thank you Darren for your fabulous work.
There are two ways you could do this. The first would be to find a source for the official weightings for the NASDAQ 100 / SP500 Constituents, then import those as EDF’s (if available on your edition): https://help.optuma.com/kb/faq.php?id=725
The other option would be to build a custom breadth code that sums all of the constituents market caps together. You can then set up a custom column in the Watchlist which references the breadth code and the current market cap value to find the current weightings.
An example of the script you could then use would be:
//Reference Market Cap Breadth Code
V1 = GETDATA(CODE=NASDAQ100 TOTAL MARKET CAP:Market Breadth) ;
//Reference Current Market Cap Value
V2 = DATAFIELD(FEED=FD, FIELD=MarketCapitalization, LATESTONLY=True);
V2 / V1
The final set up would look like this:
Here’s an example of importing the weights in a .csv file eg for the SPDR Consumer Discretionary ETF ($XLY) from the Sate Street ETF website:
The file would need to be formatted to include just the ticker and weight columns, and then imported as an External Data Field:
Current XLY weights attached, but the file would need to be updated manually and then reloaded:
XLY-Weights.csv (905 Bytes)
AM i right to sum all of the constituents market caps (SPX) together?
Because i want to show the cap weight that you post on this post, thanks
Yes you would do that, but first you would need to download the historical daily market cap data for each constituent (fundamental data is only downloaded when the chart is opened).
The best way to do this is to open a watchlist of the S&P500 and below a price chart add a Data Field tool set to Market Cap. Then click the Cycle Codes icon in the watchlist title bar to automatically scroll down the list to download the data in the chart (right-click the cycle codes icon to set the speed to something like 6 seconds).
Once all 500 have been cycled through and the market cap data has been downloaded then run the breadth calculation to sum the values and then add the watchlist column to calculate the percentage.
can I also update the fundamental data using the “Select Security” window and then right click on “US Equities” and select “Download Fundamental Data”?
Thanks Darren and Merry Christmas and Happy New Year!
Hi and Happy New Year to all,
a short reminder to my above post about how to update the fundamental data. Am I right that I can update the fundamental data also with the “Download Fundamental Data” function?
Posting the above post on December 24 was surely not the best date/idea.
I’m not sure - I’ll have to test it later. Did you try it?
I made some testing about the fundamental data update.
Here are my results:
When I click on “Download Fundamental Data” function in the bottom left corner I get the message “Requesting Fundamental Data for US Equities” and shortly after that the message “Processing Fundamental Data for US Equities” and again shortly after that the message “Importing Fundamental Data for US Equities …”
After the download was completed I have looked at the file “fundata.db” in the folder “C:\ProgramData\Optuma Data” and in the column “Date modified” in Windows File Explorer I see the date and time of the last time the file was modified and it shows the time when I made the download a minute or so ago.
As far as I am able to understand this update issue, the fundamental data should be updated to the current values when I use the "Download Fundamental Data” function since the file “fundata.db” shows in Windows File Explorer exactly the date and time when I clicked the "Download Fundamental Data” function.
Hi Optuma team,
I have replicated the procedure to calculate the total sum of the market capitalization of the S&P 500 using the Optuma Breadth engine.
Here are my script for the Breadth engine:
Here is a screen shot of all the other settings:
In “Date Range” it is necessary to use “Last Week”. If not Optuma calculates nothing and the file that the Breadth engine creates is empty.
I then have created a script to calculate the %-weight of the S&P 500 components.
Here is the script:
//Reference Market Cap Breadth Code
Var1 = GETDATA(CODE=SPXTOTALCAP:Market Breadth);
//Reference Current Market Cap Value
Var2 = DATAFIELD(FEED=FD, FIELD=MarketCapitalization, LATESTONLY=True);
Var2 / Var1
Here is the screenshot:
All the scripts work fine. BUT the cap weights are not the same as the cap weights you can download from
…SPY: SPDR® S&P 500® ETF Trust
There are some great differences in the %-cap weights between the Optuma calculations and the SPY %-cap weights. This is especially true for the two Alphabet companies (GOOG and GOOGL).
I have attached and Excel file with the %-cap weights of the SPY as of 01/03/2022 as well as my Optuma workbook and a PDF file of the Optuma workbook %-cap weights.
Can you please verify my calculations/scripts and inform me about the sometimes great differences between the SPY %-cap weights and the Optuma %-cap weights?
Optuma-SPX-Weights-as-of-01-03-2022.pdf (1.94 MB)
SPY-Weights-as-of-01-03-2022.xlsx (30.3 KB)
Optuma-SPX-Weights-as-of-01-03-2022.owb (206 KB)
ADDENDUM to Replay # 67121 AND #67123 (my last two posts above)
I made some further testing about the update of the fundamental data using your recommended “Cycle Code” update techniques you have described above in Replay #67005.
The “Cycle Code” update technique has not triggered any fundamental data update window in the bottom left corner of the screen and also the file information of the file “fundata.db” in Windows File Explorer didn’t change using the “Cycle Code” technique.
Concerning the total market cap value calculated with the Breadth engine there seems to be some problems.
Using the data as of 01/04/2022 the S&P 500 components symbol list watchlist shows a total market cap value of 44,985,676.0 whereas the chart of the total market cap value created with the data of the Breadth engine shows a value of 45,053,525.0 (45.053525). See my two screen shots.
Because of that in the S&P 500 components watchlist (see above) the sum of the total weights is NOT 100% it is 99.85%.
I have run the Breadth engine manually too, to make sure that the data are current.
There seems to be some problems with the fundamental data entered in the “Market Capitalization” data field (see e.g. the two Alphabet companies) and some problems with the Breadth engine to calculate the total sum of the market cap.
Sorry for that in the early days of the new year.
With regards to Google and the different SPY weights, both GOOG and GOOGL are members of the SPX index so are included in the list, but our data provider includes total market cap for each one (ie counted twice).
The only way to include them once would be to run the calculation on a .csv list with only one listed. Also, try running it on the current S&P500 members only so that old members aren’t included.
Downloading the Market Cap data by cycling down a watchlist will not trigger a data download message as the data is requested on a stock-by-stock basis.