python量化投资实战-分析基金数据

数据库设计

CREATE TABLE `fund_base_data` (
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
	`fundcode` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '基金代码' COLLATE 'utf8_unicode_ci',
	`name` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '基金名称' COLLATE 'utf8_unicode_ci',
	`jzrq` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '净值日期' COLLATE 'utf8_unicode_ci',
	`dwjz` DECIMAL(10,8) NOT NULL DEFAULT '0.00000000' COMMENT '当日净值',
	`gsz` DECIMAL(10,8) NOT NULL DEFAULT '0.00000000' COMMENT '估算净值',
	`gszzl` DECIMAL(10,8) NOT NULL DEFAULT '0.00000000' COMMENT '估算涨跌百分比',
	`gztime` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '估值时间' COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `fundcode_jzrq` (`fundcode`, `jzrq`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1935
;

获取实盘数据并写入数据库

import sys

import requests
import json
import re
from decimal import Decimal
import MySQLdb
from fund import mysql_utils

'''
基金详细信息：http://fund.eastmoney.com/pingzhongdata/001186.js?v=20160518155842

http://fund.eastmoney.com/js/fundcode_search.js
所有基金名称列表代码

http://fund.eastmoney.com/js/jjjz_gs.js?dt=1463791574015
所有基金公司名称列表代码
 
 array(7) {
	  ["fundcode"]=>"519983"           //基金代码
	  ["name"]=>"长信量化先锋混合A"    //基金名称
	  ["jzrq"]=>"2018-09-21"           //净值日期
	  ["dwjz"]=>"1.2440"               //当日净值
	  ["gsz"]=>"1.2388"                //估算净值
	  ["gszzl"]=>"-0.42"               //估算涨跌百分比 即-0.42%
	  ["gztime"]=>"2018-09-25 15:00"   //估值时间
	}
 
'''


def get_data(code):
    url = "http://fundgz.1234567.com.cn/js/%s.js" % code
    # 浏览器头
    headers = {'content-type': 'application/json',
               'User-Agent': 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:22.0) Gecko/20100101 Firefox/22.0'}

    r = requests.get(url, headers=headers)
    # 返回信息
    content = None
    if r.ok:
        content = r.text
        # content = """jsonpgz({"fundcode":"501019","name":"国泰国证航天军工指数","jzrq":"2020-08-13","dwjz":"1.2327","gsz":"1.2690","gszzl":"2.95","gztime":"2020-08-14 15:00"});"""

    # 正则表达式
    pattern = r'^jsonpgz\((.*)\)'
    # 查找结果
    if content:
        search = re.findall(pattern, content)
        # 遍历结果
        for i in search:
            dict = json.loads(i)
            # print(data,type(data))
            # print("基金: {},收益率: {}".format(data['name'], data['gsz']))
            return dict
    else:
        return None

def get_index():
    list =['s_sh000001']
    url = "http://hq.sinajs.cn/list={}".format("s_sh000047")
    # 浏览器头
    headers = {'content-type': 'application/json',
               'User-Agent': 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:22.0) Gecko/20100101 Firefox/22.0'}

    r = requests.get(url, headers=headers)
    # 返回信息
    content = None
    if r.ok:
        content = r.text

    return content

if __name__ == '__main__':


    # list = ["161724", "005969", "161032"]

    print(get_index())

    list = [
        "003494",
        "006058",
        "000136",
        "005001",
        "519712",
        "519697",
        "005267",
        "002593",
        "100026",
        "007016",
        "008383",
        "001868",
        "010235",
        "001371",
        "001182",
        "003625",
        "001879",
        "310318",
        "110003",
        "180003",
        "110020",
        "161725",
        "008592",
        "003956",
        "008382",
        "005969",
        "003003",
        "001930",
        "160213",
        "161724",
        "001643",
        "519760",
        "006401",
        "151002",
        "007412",
        "001951",
        "005968",
        "004997",
        "007844",
        "161721",
        "000433",
        "008089",
        "161032",
        "240022",
        "398051",
        "003096",
        "002939",
        "005911",
        "009087",
        "750003",
        "000320",
        "004952",
        "163402",
        "161715",
        "110025",
        "005660",
        "002910",
        "001103",
        "610002",
        "008634",
        "163407",
        "007230",
        "002903",
        "005224",
        "008397",
        "004433",
        "163411",
        "001603",
        "180031",
        "163417",
        "006308",
        "110011",
        "002079",
        "160632",
        "163406",
        "005491",
        "001691",
        "001875",
        "260108",
        "005827",
    ]

    mysql_config = {
        "host": "localhost",
        "port": 3306,
        "userName": "root",
        "password": "root",
        "dbName": "finance",
        "charsets": "UTF8"
    }

    dbUtil = mysql_utils.DBUtil(mysql_config)
    # print("对象实例后的属性：" + json.dumps(dbUtil.__dict__))

    list2 = ['003003',
             '001930',
             '160213',
             '007844',
             '006308',
             '001691',
             ]
    for code in list:
        try:
            dict = get_data(code)
            if not dict:
                print("fail code ->{}".format(code))
            else:
                sql = """INSERT INTO fund_base_data(fundcode,
                                                                 name, jzrq, dwjz, gsz,gszzl,gztime)
                                                                 VALUES ('{}','{}','{}',{},{},{},'{}')""".format(
                    dict['fundcode'],
                    dict['name'],
                    dict['jzrq'],
                    dict['dwjz'],
                    dict['gsz'],
                    dict['gszzl'],
                    dict['gztime'])
                dbUtil.save(sql)
        except Exception as e:
            error_msg = sys.exc_info()
            print(str(error_msg))
            print("fail code ->{}".format(code))
            pass
        continue

筛选出连续回撤n天的基金

SELECT name,count(*) as days_count
FROM (
SELECT *
	,row_number() over (partition by name order by jzrq asc) as rn
FROM fund_base_data
WHERE gszzl < 0
) t
group by name,DATE_ADD(jzrq,interval -rn day)
HAVING days_count = n;

筛选出最佳回撤最多的基金

SELECT NAME,SUM(gszzl) FROM fund_base_data GROUP BY NAME HAVING SUM(gszzl) < -6 ORDER BY SUM(gszzl);