November 17, 2023 at 3:52 pm #72053
I created a total return index of the three defensive SPDR sector ETFs (Health Care, XLV; Consumer Staples, XLP; and Utilities, XLU) with the Custom Code Builder.
Here is the script:12345678910// Getting the Defensive Sector Total Return Data of ETFsLine1 = GETDATA(CODE=XLV:US, PRICEADJUSTMENT=Total Returns) ;Line2 = GETDATA(CODE=XLP:US, PRICEADJUSTMENT=Total Returns) ;Line3 = GETDATA(CODE=XLU:US, PRICEADJUSTMENT=Total Returns) ;// Calculating the Equal-Weighted Total Return Index of Defensive Sector ETFsLine4 = POWER(POWER=0.3333, Line1 * Line2 * Line3) ;Line4
The script works as it should, no problem.
I have also created a total return index of the three defensive sector indices (Health Care, SP500-35TR; Consumer Staples, SP500-30TR; and Utilities, SP500-55TR) with the Custom Code Builder.
The script is the following:1234// Calculating the Equal-Weighted Total Return Index of Defensive Sector IndicesPOWER(POWER=0.3333, SP500-35TR:SPI * SP500-30TR:SPI * SP500-55TR:SPI)
Again, the script works as it should, no problem.
Now I have created a Relative Comparison Chart with the base 10-year ago.
Here is the chart:
As you can see, the spread between these two total return indices is very wide at around 17.4%.
I have no idea why the spread has started to widen since 2020.
Does anyone have a suggestion as to why the spread between the two total return indices is so wide?
Thanks very much for any suggestion.
November 18, 2023 at 5:43 am #72057Mathew
- This topic was modified 2 weeks ago by Thomas.
- Topics: 41
- Replies: 2,047
- Posts: 2,088
I guess that the Total Returns data is missing on your system before 2020. When TR was introduced a couple of years ago, we did not push through a full update of everyone’s data – it can take over 10 min and if people are not interested in TR then it is a big download for no benefit. You can initiate a full replacement of the US Equities data by looking at Option 3 on this page https://www.optuma.com/kb/optuma/troubleshooting-and-faqs/troubleshooting/incomplete-charts-how-to-manually-download-missing-data
Note re the ticket you sent on the similar issue, for me with full updated data, all the values change in your WorkBook.
Because this is a Relative Comparison Chart, they will start at the same point even though the calculated one is wrong. In this type of analysis trying to rebuild an S&P index, I would always expect a small discrepancy as there are some creative rules around the index-building process. Hopefully, with a refresh of the data, the gap will become a lot smaller.
All the best
MathewNovember 19, 2023 at 9:42 am #72063
Thank you for your replay. I have done the update as you recommended and have now 22,528 files in my US Equity folder.
Regarding my Relative Comparison Chart (see above) and the workbook I uploaded in the support area, there was no change from my original data after updating the US Equity data. I have also updated the “US_Equities.idx2” file.
I have attached screenshots of the workbooks I uploaded to this post and marked those ETFs with rectangles that have the SAME values in the Price Return AND Total Return workbook.
However, since all ETFs have received dividend income, it is completely impossible that the performance values for price return and total return are the same. I have also attached screenshots with the dividend income data for the SPDS ETFs.
Here are the links to the SPDR ETFs dividend income data:
It is therefore clear that the ETFs have received dividend income and therefore the performance values for the price return and the total return cannot be identical.
For the other ETFs that have identical performance values for the price return and the total return I was not able to get the dividend income data but since these ETFs are major index ETFs it is clear that they have also received dividend income and thus the values cannot be identical for price and total return.
Perhaps there is something else I did wrong or some settings that I have not been set correctly BUT the values for the not marked ETFs and time periods are correct.
Coming back to the relative comparison chart (see above), I know of course that the spread between the two lines must increase over time, but the spread in the chart above is far too big.
Is it possible that for the years 2020, 2021 and 2022 the dividend income data for some ETFs are ignored or missing?
November 20, 2023 at 10:30 am #72091Mathew
- This reply was modified 2 weeks ago by Thomas.
- Topics: 41
- Replies: 2,047
- Posts: 2,088
Hmmm, leave it with me for a couple of days. I think there are dividends missing on some ETFs and I need to find out why.November 30, 2023 at 11:29 am #72118Matthew
- Topics: 5
- Replies: 688
- Posts: 693
Just a quick update, the data admin looked into this and has released an update to the data. If you can please go to Data > Exchanges > US Equities and click the Import EOD Data History option, this should restore any missing TR information within the Sector ETFs (there was an issue with the TR data via our provider, we’re working with them to ensure a gap like this does not occur again).November 30, 2023 at 11:22 pm #72121
Thank you for your efforts. I took your advice and updated the US stock database, and voila, all TR calculations are correct.
Here is the updated chart from above. As you can see, the distance between the two lines is now much smaller.
- You must be logged in to reply to this topic.