mfm-platform/msci.py at master · geraybos/mfm-platform · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Apr 21 13:51:39 2017

@author: lishiwang
"""
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from pandas import Series, DataFrame, Panel
from datetime import datetime
import os
import statsmodels.api as sm
import copy
from matplotlib.backends.backend_pdf import PdfPages
from cvxopt import solvers, matrix

from data import data
from strategy_data import strategy_data
from position import position
from strategy import strategy
from backtest import backtest
from barra_base import barra_base
from db_engine import db_engine
from database import database

class msci_stocks():
    def __init__(self, *, startdate):
        stock_list = pd.read_excel('msci list.xlsx', sheetname=1)
        self.stock_isin = stock_list['ISIN']
        self.db = database(start_date=startdate)
        self.db.initialize_jydb()
        self.db.initialize_sq()

    def get_stock_code(self):
        # 创建包含isin代码的sql string
        isin_sql_str = ''
        for i in self.stock_isin.iteritems():
            if type(i[1]) != str and np.isnan(i[1]):
                continue
            isin_sql_str += "'" + str(i[1]) + "'"
            if i[0] <self.stock_isin.size - 1:
                isin_sql_str += ", "
        sql_query = "select distinct SecuCode from SecuMain where ISIN in (" + isin_sql_str + ") order by SecuCode"
        self.msci_stockcode = self.db.jydb_engine.get_original_data(sql_query)
        self.msci_stockcode = self.msci_stockcode.iloc[:, 0]
        pass

    def get_price_data(self):
        self.db.get_trading_days()
        self.db.get_labels()
        self.db.get_ClosePrice_adj()
        self.db.get_index_price()
        # 获取收益数据
        closeprice_adj = self.db.data.stock_price.ix['ClosePrice_adj']
        self.stock_return = np.log(closeprice_adj/closeprice_adj.shift(1)).fillna(0)
        self.benchmark_return = np.log(self.db.data.benchmark_price.div(self.db.data.benchmark_price.shift(1).reindex(
                major_axis=self.db.data.stock_price.major_axis))).fillna(0)

    def get_msci_stocks_stats(self):
        # 等权持有的收益
        self.port_value_ew = np.exp(self.stock_return.reindex(columns=self.msci_stockcode).cumsum()).mean(1)
        self.benchmark_value = np.exp(self.benchmark_return.cumsum(1))
#        self.port_value_ew = self.stock_return.reindex(columns=self.msci_stockcode).cumsum().mean(1)
#        self.benchmark_value = self.benchmark_return.cumsum(1)
        f = plt.figure()
        ax = f.add_subplot(1,1,1)
        ax.plot(self.port_value_ew, label='stocks')
        ax.plot(self.benchmark_value.ix['ClosePrice_zz500', :, 0], label='zz500')
        ax.plot(self.benchmark_value.ix['ClosePrice_hs300', :, 0], label='hs300')
        ax.plot(self.benchmark_value.ix['ClosePrice_zz800', :, 0], label='zz800')
        ax.plot(self.benchmark_value.ix['ClosePrice_sz50', :, 0], label='sz50')
        ax.legend(loc='best')

if __name__ == '__main__':
    msci_s = msci_stocks(startdate='2017-03-02')
    msci_s.get_stock_code()
    msci_s.get_price_data()
    msci_s.get_msci_stocks_stats()