Python选基金(爬虫+策略)

之前买基金都瞎买的，最近突然想可以用python来试试，综合基金类型、持仓、收益率、基金经理多维度综合考虑，看看能不能帮忙选比较优质的基金出来。

整体策略:

一、数据准备

1.1获得基金经理信息

```

import requests

import time

import re

import pymysql

conn=pymysql.connect(host='xxxxxxxx',user='xxxxx',password='xxxxx',database='xxx',charset="utf8")

cur=conn.cursor()

#获得基金经理信息

def getfundmanageinfo():

    for i in range(54):

        print('正在写入第{}页'.format(i))

        url='http://fund.eastmoney.com/Data/FundDataPortfolio_Interface.aspx?dt=14&mc=returnjson&ft=all&pn=20&pi='+str(i)+'&sc=abbname&st=asc'

        r=requests.get(url)

        fundmanagelist=re.findall(re.compile('\[(.*)\]',re.S),r.text)[0].strip('[').strip(']').replace('"','').split("],[")

        #"30634044","艾定飞","80053204","华商基金","007685,007853","华商电子行业量化股票,华商计算机行业量化股票","938","73.86%","007685","华商电子行业量化股票","5.41亿元","73.86%"

        for j in fundmanagelist:

            funmanagername=j.split(',')[1]

            company=j.split(',')[3]

            fundinfo=j.split(',')[4:-6]

            fundcode='##'.join(fundinfo[:int(len(fundinfo)/2)]) #用两个#把基金代码拼接成字符串

            fundname='##'.join(fundinfo[int(len(fundinfo)/2):])

            workday=j.split(',')[-6]

            fundscale=j.split(',')[-2]

            bestprofit=j.split(',')[-1]

            bestfundcode=j.split(',')[-4]

            bestfundname=j.split(',')[-3]

            sql="insert into fundmanagerinfo values('{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(funmanagername,company,str(fundcode),str(fundname),workday,fundscale,bestprofit,bestfundcode,bestfundname)

            cur.execute(sql)

            conn.commit()

if __name__ == '__main__':

    getfundmanageinfo()

```

1.2筛选从业年限超过10年的基金经理，并且最佳收益率>100%，按照业绩排名

```

select * from fundmanagerinfo

where workday >3650 and cast(left(bestprofit,5) as float)>100

order by cast(left(bestprofit,5) as float) desc

```

结果:

1.3然后选择这些基金经理持有的基金代码出来到fundcodeanalysis表，作为基金池

```

create  table fundcodeanalysis as

select DISTINCT funmanagername ,substring_index(substring_index(a.fundcode ,'##',b.help_topic_id + 1),'##' ,-1) as fundcode

,substring_index(substring_index(a.fundname,'##',b.help_topic_id + 1),'##' ,-1) as fundname

from (select * from fundmanagerinfo where workday >3650 and cast(left(bestprofit,5) as float)>100

order by cast(left(bestprofit,5) as float) desc)

a 

JOIN mysql.help_topic b ON b.help_topic_id <(length(a.fundcode)-length( replace(a.fundcode,'##','')) + 1)

```

表如下:

1.4采集这些基金基础信息

采用3个进程，跑了好几个小时才跑完，如果电脑配置够好，建议多点进程

```

from multiprocessing import Pool

def getfundinfo(fundcode):

    baseurl='https://fundf10.eastmoney.com/F10DataApi.aspx?type=lsjz&code='+str(fundcode)+'&sdate=2001-12-18&edate=2022-05-18&per=20&page=1'

    r=requests.get(baseurl)

    pagenum=re.findall(re.compile('pages:(.*?),curpage',re.S),r.text)[0]

    for i in range(1,int(pagenum)+1):

        print('正在获取基金{}的第{}页,总{}页'.format(fundcode,i,pagenum))

        url='https://fundf10.eastmoney.com/F10DataApi.aspx?type=lsjz&code='+str(fundcode)+'&sdate=2001-12-18&edate=2022-05-18&per=20&page='+str(i)

        k=requests.get(url)

        dates=re.findall(re.compile('<tr><td>(.*?)</td><td',re.S),k.text)

        netvalues=re.findall(re.compile("<td class='tor bold'>(.*?)</td><td",re.S),k.text)

        accvalues=re.findall(re.compile("</td><td class='tor bold'>.*?</td><td class='tor bold'>(.*?)</td><td class='tor bold ",re.S),k.text)

        #grouths=re.findall(re.compile("<td class='tor bold red'>(.*?)</td><td|<td class='tor bold grn'>(.*?)</td><td",re.S),k.text)

        df=pd.DataFrame([dates,netvalues,accvalues]).T

        df.columns=['dates','netvalues','accvalues']

        for j in range(df.shape[0]):

            rows=df.iloc[j,:]

            date=rows[0]

            netvalue=rows[1]

            accvalue=rows[2]

            try:

                sql="insert into fundinfoanalysis values('{}','{}','{}','{}')".format(fundcode,date,netvalue,accvalue)

                cur.execute(sql)

                conn.commit()

            except Exception as e:

                print(e)

if __name__ == '__main__':

    pool=Pool(processes=3)

    #getfundmanageinfo()

    fundcode_df=pd.read_sql('select fundcode from fundcodeanalysis',conn)

    pool.map(getfundinfo,list(fundcode_df.fundcode)) #多进程爬取，注意getfundinfo函数没有括号

    pool.close()

    pool.join()

```

二、选择策略

2.1获得上证指数每天数据作为对比，网址在这里，直接下载数据就行

2.2以大盘指数筛选基金

选择上涨指数比较平稳的区间作为依据，如果大盘不动，收益上涨就证明了基金经理的水平比较好

观察期  20190301-20200630    上证2994.005 ~  2984.6741 变化率-0.33%

验证区  20201201-20210531    上证3451.9384 ~ 3615.4773 变化率 4.73%

2.21筛选观察期变化率大于上证指数的基金,因为上证是负数，所以大于0就可以了，从里面筛选排名前30%的基金出来

```

select * FROM (

select a.fundcode ,concat(round((end_value/start_value-1)*100,1),'%') as 收益率,

round(PERCENT_RANK()over(order by end_value/start_value-1 desc)*100,0) as 排名

from

(select  fundcode ,accvalue as start_value from  `fundinfoanalysis`where dates='2019-03-01' )a

left join

(select  fundcode ,accvalue as end_value from  `fundinfoanalysis`where dates='2020-06-30') b

on a.fundcode=b.fundcode

where cast(end_value as float)/cast(start_value as float)-1>0

and start_value is not null

order by round(cast(end_value as float)/cast(start_value as float)-1,3)*100 desc

)x

where 排名<30

```

结果

2.22看看这些基金在验证区是否依然大于上证指数，然后再筛选前30%的出来

```

select * FROM (

select a.fundcode ,concat(round((end_value/start_value-1)*100,1),'%') as 收益率,

round(PERCENT_RANK()over(order by end_value/start_value-1 desc)*100,0) as 排名

from

(select  x.fundcode ,accvalue as start_value from  `fundinfoanalysis` x inner join funcodetop30 y on x.fundcode =y.fundcode where dates='2020-12-01' )a

left join

(select  x.fundcode ,accvalue as end_value from  `fundinfoanalysis` x inner join funcodetop30 y on x.fundcode =y.fundcode where dates='2021-05-31') b

on a.fundcode=b.fundcode

where end_value/start_value-1>0.047

and start_value is not null

order by round(cast(end_value as float)/cast(start_value as float)-1,3)*100 desc

)x

where 排名<30

```

最后从8000多基金里面，剩下27支基金了

好像有点多，再筛一下，两次观察区间都保持在前30名的基金

```

SELECT * from fundcodefinal a

left join (select fundcode ,profit ,RANK()over(order by profit desc) rn from funcodetop30)

b on a.fundcode =b.fundcode

where b.rn<30

```

现在就剩下10支了，接着分析这10支基金的持仓股票详情

```

def getfundstockdetails(fundcode):

    print('正在获取基金{}的持仓股票明细'.format(fundcode))

    url="http://fundf10.eastmoney.com/FundArchivesDatas.aspx?type=jjcc&code={}&topline=10&year=&month=&rt=0.5032668912422176".format(fundcode)

    r=requests.get(url)

    stockcodes=re.findall(re.compile("</td><td><a href='.*?'>(.*?)</a></td><td class='tol'>",re.S),r.text)

    stocknames=re.findall(re.compile("</td><td class='tol'><a href='.*?'>(.*?)</a></td><td class='tor'>",re.S),r.text)

    percents=re.findall(re.compile("股吧.*?</a></td><td class='tor'>(.*?)</td><td class='tor'>",re.S),r.text)

    holding_nums=re.findall(re.compile("行情.*?</a></td><td class='tor'>.*?</td><td class='tor'>(.*?)</td><td class='tor'>",re.S),r.text)

    holding_values=re.findall(re.compile("行情.*?</a></td><td class='tor'>.*?</td><td class='tor'>.*?</td><td class='tor'>(.*?)</td></tr>",re.S),r.text)

    df=pd.DataFrame([stockcodes,stocknames,percents,holding_nums,holding_values]).T

    df.columns=['stockcode','stockname','percent','holding_num','holding_value']

    for j in range(df.shape[0]):

        rows=df.iloc[j,:]

        stockcode=rows[0]

        stockname=rows[1]

        percent=rows[2]

        holding_num=rows[3]

        holding_value=rows[4]

        try:

            sql="insert into fundinfoanalysisstockdetails values('{}','{}','{}','{}','{}','{}')".format(fundcode,stockcode,stockname,percent,holding_num,holding_value)

            cur.execute(sql)

            conn.commit()

        except Exception as e:

            print(e)

if __name__ == '__main__':

    pool=Pool(processes=3) #开启3个进程，不填就是默认最大进程数

    # #getfundmanageinfo()

    # fundcode_df=pd.read_sql('select distinct fundcode from fundcodeanalysis',conn)

    fundfinal=pd.read_sql('SELECT distinct a.fundcode from fundcodefinal a left join (select fundcode ,profit ,RANK()over(order by profit desc) rn from funcodetop30)b on a.fundcode =b.fundcode where b.rn<30',conn)

    pool.map(getfundstockdetails,list(fundfinal.fundcode)) #多进程，注意getfundinfo函数没有括号

    pool.close()

    pool.join()

```

最终结果

想玩股票的，可以看看这些股票，毕竟是人家基金经理精心挑选出来的

时间有限，以上过程仅限于个人探索，不构成投资建议。另外策略方面也可以再深入研究，例如不同类型基金配比，一部分用来追求高收益，一部分求稳保证最终盈利就行。还有可以定投策略也可以加入。

鉴于有的人懒得看代码，所以我准备了完整代码:

```

import requests

import pandas as pd

import re

import pymysql

from multiprocessing import Pool

conn=pymysql.connect(host='xxxxxx',user='xxx',password='xxxx',database='xxx',charset="utf8")

cur=conn.cursor()

#获得所有基金代码

def getfundCode():

    url = 'http://fund.eastmoney.com/js/fundcode_search.js'

    r = requests.get(url)

    fundcodelist=re.findall(re.compile('\[(.*)\]',re.S),r.text)[0].strip('[').strip(']').replace('"','').split("],[")

    fundcodes=[] #获得所有基金代码存为列表

    for i in fundcodelist:

        #000001,HXCZHH,华夏成长混合,混合型

        fundcode=i.split(',')[0]

        fundnameen=i.split(',')[1]

        fundnamecn=i.split(',')[2]

        fundtype=i.split(',')[3]

        # fundinfo='{},{},{},{}\n'.format(fundcode,fundnameen,fundnamecn,fundtype)

        # with open('fundinfo.csv', 'a+', encoding='gbk') as f:

        #    f.write(fundinfo) #存到本地

        fundcodes.append(fundcode)

    return fundcodes

#获得基金经理信息

def getfundmanageinfo():

    for i in range(54):

        print('正在写入第{}页'.format(i))

        url='http://fund.eastmoney.com/Data/FundDataPortfolio_Interface.aspx?dt=14&mc=returnjson&ft=all&pn=20&pi='+str(i)+'&sc=abbname&st=asc'

        r=requests.get(url)

        fundmanagelist=re.findall(re.compile('\[(.*)\]',re.S),r.text)[0].strip('[').strip(']').replace('"','').split("],[")

        #"30634044","艾定飞","80053204","华商基金","007685,007853","华商电子行业量化股票,华商计算机行业量化股票","938","73.86%","007685","华商电子行业量化股票","5.41亿元","73.86%"

        for j in fundmanagelist:

            funmanagername=j.split(',')[1]

            company=j.split(',')[3]

            fundinfo=j.split(',')[4:-6]

            fundcode='##'.join(fundinfo[:int(len(fundinfo)/2)]) #用两个#把基金代码拼接成字符串

            fundname='##'.join(fundinfo[int(len(fundinfo)/2):])

            workday=j.split(',')[-6]

            fundscale=j.split(',')[-2]

            bestprofit=j.split(',')[-1]

            bestfundcode=j.split(',')[-4]

            bestfundname=j.split(',')[-3]

            sql="insert into fundmanagerinfo values('{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(funmanagername,company,str(fundcode),str(fundname),workday,fundscale,bestprofit,bestfundcode,bestfundname)

            cur.execute(sql)

            conn.commit()

def getfundinfo(fundcode):

    baseurl='https://fundf10.eastmoney.com/F10DataApi.aspx?type=lsjz&code='+str(fundcode)+'&sdate=2001-12-18&edate=2022-05-18&per=20&page=1'

    print(baseurl)

    r=requests.get(baseurl)

    pagenum=re.findall(re.compile('pages:(.*?),curpage',re.S),r.text)[0]

    for i in range(1,int(pagenum)+1):

        print('正在获取基金{}的第{}页,总{}页'.format(fundcode,i,pagenum))

        url='https://fundf10.eastmoney.com/F10DataApi.aspx?type=lsjz&code='+str(fundcode)+'&sdate=2001-12-18&edate=2022-05-18&per=20&page='+str(i)

        k=requests.get(url)

        dates=re.findall(re.compile('<tr><td>(.*?)</td><td',re.S),k.text)

        netvalues=re.findall(re.compile("<td class='tor bold'>(.*?)</td><td",re.S),k.text)

        accvalues=re.findall(re.compile("</td><td class='tor bold'>.*?</td><td class='tor bold'>(.*?)</td><td class='tor bold ",re.S),k.text)

        #grouths=re.findall(re.compile("<td class='tor bold red'>(.*?)</td><td|<td class='tor bold grn'>(.*?)</td><td",re.S),k.text)

        df=pd.DataFrame([dates,netvalues,accvalues]).T

        df.columns=['dates','netvalues','accvalues']

        for j in range(df.shape[0]):

            rows=df.iloc[j,:]

            date=rows[0]

            netvalue=rows[1]

            accvalue=rows[2]

            try:

                sql="insert into fundinfoanalysis values('{}','{}','{}','{}')".format(fundcode,date,netvalue,accvalue)

                cur.execute(sql)

                conn.commit()

            except Exception as e:

                print(e)

def getfundstockdetails(fundcode):

    print('正在获取基金{}的持仓股票明细'.format(fundcode))

    url="http://fundf10.eastmoney.com/FundArchivesDatas.aspx?type=jjcc&code={}&topline=10&year=&month=&rt=0.5032668912422176".format(fundcode)

    r=requests.get(url)

    stockcodes=re.findall(re.compile("</td><td><a href='.*?'>(.*?)</a></td><td class='tol'>",re.S),r.text)

    stocknames=re.findall(re.compile("</td><td class='tol'><a href='.*?'>(.*?)</a></td><td class='tor'>",re.S),r.text)

    percents=re.findall(re.compile("股吧.*?</a></td><td class='tor'>(.*?)</td><td class='tor'>",re.S),r.text)

    holding_nums=re.findall(re.compile("行情.*?</a></td><td class='tor'>.*?</td><td class='tor'>(.*?)</td><td class='tor'>",re.S),r.text)

    holding_values=re.findall(re.compile("行情.*?</a></td><td class='tor'>.*?</td><td class='tor'>.*?</td><td class='tor'>(.*?)</td></tr>",re.S),r.text)

    df=pd.DataFrame([stockcodes,stocknames,percents,holding_nums,holding_values]).T

    df.columns=['stockcode','stockname','percent','holding_num','holding_value']

    for j in range(df.shape[0]):

        rows=df.iloc[j,:]

        stockcode=rows[0]

        stockname=rows[1]

        percent=rows[2]

        holding_num=rows[3]

        holding_value=rows[4]

        try:

            sql="insert into fundinfoanalysisstockdetails values('{}','{}','{}','{}','{}','{}')".format(fundcode,stockcode,stockname,percent,holding_num,holding_value)

            cur.execute(sql)

            conn.commit()

        except Exception as e:

            print(e)

if __name__ == '__main__':

    pool=Pool(processes=3) #开启3个进程，不填就是默认最大进程数

    # #getfundmanageinfo()

    # fundcode_df=pd.read_sql('select distinct fundcode from fundcodeanalysis',conn)

    fundfinal=pd.read_sql('SELECT distinct a.fundcode from fundcodefinal a left join (select fundcode ,profit ,RANK()over(order by profit desc) rn from funcodetop30)b on a.fundcode =b.fundcode where b.rn<30',conn)

    pool.map(getfundstockdetails,list(fundfinal.fundcode)) #多进程，注意getfundinfo函数没有括号

    pool.close()

    pool.join()

```

代码太乱，可以看这里https://blog.csdn.net/lbship/article/details/117985012