A few times we discussed using Tongdaxin and Guoren.com, and today we use the choice platform to compare the long-term performance of stocks and funds.
After entering the choice, find the stock data browser, find “All A Shares” in the lower left corner, select “Annual Closing Price” in “Selected Indicators”, select from 2011 to September 30, 2022, and choose the method of reinstatement ” and then reinstate the rights. Select “First Listing Date” and “Delisting Date” in “Securities Information”, save the template, and export the excel sheet.
This table cannot be used directly, and a step of “cleaning data” needs to be done. Taking this table as an example, there is a lot of redundant data. For example, some B shares starting with 2 and 9 are also exported for unknown reasons, and there are also some NEEQ stocks starting with 8, which must be deleted.
There are also some erroneous data in the post-resumption price. For example, the post-resumption price before the initial listing date and the post-resumption price after the delisting date are not the price in the life of the stock and must be excluded.
Some platforms have not been carefully verified, and the input data is wrong. No matter how correct your calculations are, the results you get are definitely wrong, so for us, manual verification and as much as possible to wash the wrong data, It is a required course for a data worker.
Then create a new “return rate” sheet to calculate the annual rate of return of each stock. The formula is very simple = the re-weighted price after the current year/the re-weighted price after the previous year -1, but the cases where these two prices are blank must be excluded. ,details make a difference.
Then calculate the data in the above table, taking 2012 as an example, the quantity greater than 0: =COUNTIFS(yield!C:C,”>0″) The quantity less than 0:=COUNTIFS(yield!C:C,”<0″ ) is equal to 0 Quantity: =COUNTIFS(Yield!C:C,”=0″) Total:=SUM(B2:B4) Win Rate:=B2/B5 Average:=AVERAGE(Yield!C:C) Median :=MEDIAN(Yield! C:C) Then copy the data in the 2012 column to 2013-2022 (as of September 30, 2022)
Using a similar approach, we got this table for the mixed funds above.
Then we compare the performance of stocks and hybrid funds over the past 10+ years. Let’s look at the winning rate first. From 2012 to 2021, the winning rate of hybrid funds is at least 4.34% higher than that of stocks, and the highest is 63.29% higher, and the average is 22.94% higher. On September 30, it underperformed by 11.61%.
Looking at the arithmetic average, mixed funds outperformed stocks in 7 years and underperformed in 4 years; the median also outperformed in 7 years and underperformed in 4 years. Overall, hybrid funds outperformed stocks.
Today’s example is to illustrate the importance of cleaning data and the importance of boundary conditions. The method of finding bugs is to start with the original data to find the errors of individual cases, and then comprehensively draw inferences from other facts to correct them. It is very important to cultivate this ability, and the direct use of data without discrimination is likely to have bugs.
Yesterday, the outer disk rebounded strongly. Today, the Hang Seng Index soared 5.90%. It is said that it will rise first and then fall, so let’s wait patiently.
This topic has 13 discussions in Snowball, click to view.
Snowball is an investor’s social network, and smart investors are here.
Click to download Snowball mobile client http://xueqiu.com/xz ]]>
This article is reproduced from: http://xueqiu.com/6146592061/232139929
This site is for inclusion only, and the copyright belongs to the original author.