Get answers and suggestions for various questions from here

Look for only "chicken", the income is guaranteed


I believe that people who have mixed up in the workplace will have their own small savings. According to the current bank interest rate, the rate of return is so low. The rate of purchase of bank demand and wealth management products cannot catch up with the M2 circulation. It is not equal to losing money. In addition to the traditional channel of the bank, we can also choose P2P, bonds, funds, stocks and other means. Among them, for the professionals, letting the money lie on the fund will be a good way to balance risks and benefits. In terms of the types of funds, there are various types of stock funds, hybrid funds, index funds, bond funds, QDII funds, etc. In the face of Linda’s full-fledged fund, I believe that everyone will be a bit confused. In fact, the fund’s selection has no such thing. Three elements, timing, picking index (active base picking, passive base pick index), selection basis (fund base).

At the right time, it is affected by the macro environment, monetary policy and current political events. This article does not elaborate. To pick an index, you need to look at valuation and growth. This article does not elaborate. According to the historical data, we can look at the long-term performance of this base. We can also compare the growth and decline of the same period index to judge the growth or resilience of this base. We can also refer to the cost rate.
For the operation of the selection base, I will use the data analysis method to find out the funds that perform well in QDII. This time, through the collection of the historical data of QDII on the Tiantian Fund website, the keywords are as follows: the fund's rise and fall in 2014-2018, the fund's four points from 2014 to 2018 Ranking. In addition, from the Yahoo Finance, calculate the rise and fall of the S&P 500 and the Nasdaq from 2014 to 2018.

I. Asking questions
1. Find out the 2014 to 2018 (the data for 2018 is the first week of January to June, the same below), the QDII fund with excellent or good position in the quartile
2. Find out the growth of the bull market Well, or the QDII fund that can resist the decline in the bear market.
2. Data cleaning
1) Use Gooseeker's ready-made reptile tool to crawl the data that will be used on the Tiantian Fund. In the beginning, I spent a lot of time to understand the operation logic of this tool. According to my experience, it is recommended that beginners should master the "Collection of Web Page Data", "Collection List Data", "Collection of Web Sites for Hierarchical Collection", how to merge multi-level acquisition results in excel "and" how to run reptile populations "and other articles ( ).
In this learning process, there will be many problems, take the initiative to find time to find the tutorial and the message in the forum can generally be resolved.
If the reader wants to use this tool to obtain data of other websites or public numbers in the future, you can download the templates made by the predecessors, but some templates need to be recharged (the most practical collection rules, data mining methods, software tools - collection) Guest GooSeeker ).

Set the search, the overall operation of this tool refers to the following figure (source collection):

The following picture shows the list of QDII funds on the Tiantian Fund website.

According to this webpage information, with the help of the collection tool, the author turned it into the following table 1.

The following picture shows the specific information content of each QDII fund webpage.

Based on the above, the author also uses the collection to aggregate it into a form 2.

Next, use the vlookup function in Excel to splicing the two tables according to the "URL" field in Table 1 and the "fullpath" field in Table 2. As shown below.

2) Hide the fields that are not needed in the original table, such as clueid, fullpath, realpath, etc., adjust the column width and add missing column names.
3) Remove duplicate values ​​based on the fullpath field.

4) Missing value processing. According to the number of rows in Table 1, the deduction of the title line has a total of 134 funds, but the number of rows in Table 2 is only 120 rows, that is, 14 fund records are lost. We can find out which fund information is crawling failed under the member center of the collection group -> rule management column, refer to the following figure.

In addition, some of the funds in the table also have abnormal values. After analysis, these funds were established after 2014. The missing data of some years led to the failure of the crawl.

In order to obtain accurate statistical results, it is recommended to manually complete the missing fund content.
5) Consistent processing. Using the excel's split function, the fund's gain field from 2014 to 2018 is uniformly converted into numerical data.
Fields with numeric values ​​in the table, actually numeric fields in the form of strings, which have an impact on statistical analysis of the data. To do this, we can convert the relevant fields to a number format.

Specify a field first, use the copy function, then select "Paste Special" and paste it as a value.

Use the break function to convert the data type of the specified field from text to numeric.

Third, build the model
Before building the analysis model, the author first from the Yahoo Finance website ( The value of the S&P 500 and Nasdaq from 2014 to 2018 is used as a horizontal reference for the annual fund's rise and fall.
1. Find out the QDII funds that are in excellent or good position in the quartile rankings from 2014 to 2018.

The following figure shows the descriptive statistics of the excel function. The author selected the 2018 increase as an example.

2. Find out the QDII fund that has grown well in the bull market and is resistant to the bear market.
According to the numerical floating range of the S&P 500 and Nasdaq indices from 2014 to 2018, 2017 is the year with the biggest increase, and 2015 is the worst year. Below we will make a data perspective for further analysis based on the data of these two years.

The chart below shows the top 20 funds in 2017. From the data on the map, the foundations of mixed-type, small-cap, and emerging-market styles have achieved good returns. Huabao Overseas China Growth Mix, Bank of Communications CSI Overseas China Internet Index has a standard deviation of more than 2.5% in the past three years, and has high volatility, suitable for investors with positive growth style. (Shangtou Morgan China Century Renminbi was established in 2016 and lacks historical data for analysis.)

The chart below shows the top 20 funds in 2015. According to the data on the chart, some overseas bond funds still achieved good returns in the case of poor performance in the US stock market. Among them, the Cathay Pacific Nasdaq 100 Index, the GF Nasdaq 100 Index, the ICBC Global Select did not enter the top 20 of the fund's gains in 2017, but it also belongs to the fund's quartile ranking from 2014 to 2018. In a better position, ICBC Global Select has a standard deviation of 1.5%-1.7% in the past 1-3 years, which is a fund with less volatility, and may be appropriately concerned for investors with risk-biased conservatives.

The author also analyzed the data from January 1st to June 10th, 2018. The following picture shows the top 20 funds in 2018. According to the data on the map, this year's oil-based funds have a higher income. This is also related to the rapid rise of the crude oil index.

The above is a simple analysis of the first use of excel. Due to lack of experience, there are still many imperfections. Welcome to criticize and correct. Now that US stocks are at a historically high level, friends who want to invest in US stocks or QDII funds are advised to wait patiently for the right time. After all, Trump is a person who does not follow the common sense, and may give us a surprise at any time. In the purchase method of the fund, it is recommended to use the smart tool to use the fixed investment method, and first enter the all in due to impulsiveness.