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/lusongno1/article/details/113829405?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-1&spm=1001.2101.3001.4242