"""
Created on Wed Dec 16 01:17:48 2020

@author: zym
"""

import os
import pandas as pd
import numpy as np
import xlwt



months = ['2015-01','2015-02','2015-03','2015-04','2015-05','2015-06','2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
            '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
            '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
            '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
            '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
            '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']



# 读取上证A股月度数据

print("\n开始读取上证A股月度数据")

stkcds = []              # 所有股票的证券代码 Stkcd
stocks_Mclsprc = []      # 股票交易月份及对应的月收盘价 Trdmnt Mclsprc


fileName = os.getcwd() + "\\上证A股月度数据.xlsx"
df = pd.DataFrame(pd.read_excel(fileName))

for i in range(len(df)):

    s = df.Stkcd[i]
    t = {}
    t.clear()


    if(s not in stkcds):

        # print(s)

        stkcds.append(s)
        t['Stkcd'] = s

        j = i
        
        Mclsprc = []
        Mclsprc.clear()

        while(j<len(df) and df.Stkcd[j]==s):

            month_Mclsprc = []
            month_Mclsprc.clear()
            month_Mclsprc.append(df.Trdmnt[j])
            month_Mclsprc.append(df.Mclsprc[j])
            Mclsprc.append(month_Mclsprc)
            j = j+1
        
        t['Mclsprc'] = Mclsprc
        stocks_Mclsprc.append(t)

print("读取完成")


# print(len(stkcds))            # 1533
# print(len(stocks_Mclsprc))    # 1533
# print(stocks_Mclsprc[0])

# stocks_Mclsprc是一个包含若干dict的list  存储读入的A股月度数据
# 每一个dict代表一个股票 以 stocks_Mclsprc[0] 为例 如下所示
# {'Stkcd': 600000, 'Mclsprc': [['2015-01', 14.47], ['2015-02', 14.53], ['2015-03', 15.79], ['2015-04', 18.07], ['2015-05', 17.04], ['2015-06', 16.96], ['2015-07', 15.07], ['2015-08', 14.96], ['2015-09', 16.63], ['2015-10', 16.39], ['2015-11', 18.65], ['2015-12', 18.27], ['2016-01', 16.9], ['2016-02', 18.45], ['2016-03', 17.93], ['2016-04', 17.83], ['2016-05', 18.29], ['2016-06', 15.57], ['2016-07', 15.7], ['2016-08', 16.48], ['2016-09', 16.49], ['2016-10', 16.27], ['2016-11', 17.16], ['2016-12', 16.21], ['2017-01', 16.74], ['2017-02', 16.59], ['2017-03', 16.01], ['2017-04', 15.21], ['2017-05', 12.84], ['2017-06', 12.65], ['2017-07', 13.36], ['2017-08', 12.71], ['2017-09', 12.87], ['2017-10', 12.61], ['2017-11', 12.91], ['2017-12', 12.59], ['2018-01', 13.17], ['2018-02', 12.46], ['2018-03', 11.65], ['2018-04', 11.61], ['2018-05', 10.55], ['2018-06', 9.56], ['2018-07', 10.17], ['2018-08', 10.33], ['2018-09', 10.62], ['2018-10', 10.98], ['2018-11', 10.71], ['2018-12', 9.8], ['2019-01', 10.73], ['2019-02', 11.74], ['2019-03', 11.28], ['2019-04', 11.97], ['2019-05', 11.13], ['2019-06', 11.68], ['2019-07', 11.87], ['2019-08', 11.28], ['2019-09', 11.84], ['2019-10', 12.51], ['2019-11', 11.91], ['2019-12', 12.37], ['2020-01', 11.35], ['2020-02', 10.85], ['2020-03', 10.15], ['2020-04', 10.63], ['2020-05', 10.57], ['2020-06', 10.58]]}



# 从上证A股月度数据读取每个月可用的股票 用于后续选股 

stkcds_every_month = {}

for i in range(len(months)):
    m = months[i]
    stkcds_every_month[m] = []       # 初始化

stkcds_tmp = []  # 记录第一次出现的股票

for i in range(len(df)):

    m = df.Trdmnt[i]
    s = df.Stkcd[i]
    s = str(s)

    # 股票在第一次出现时不用计入 因为后续计算月度收益率时需要同时用到本月收益和上月收益 所以从第二次开始出现的股票才是可用的
    # 例如 603278 最早的数据是 2017-11 但实际可用的数据从 2017-12 开始

    if(s not in stkcds_tmp):    

        stkcds_tmp.append(s)

    else:     # 从第二次出现时 开始计入当月可用股票

        if(s not in stkcds_every_month[m]):
            stkcds_every_month[m].append(s)



# 计算每只股票的月度收益率
# 股票月度收益率 = (本月收盘价-上月收盘价)/上月收盘价
# Mincomes是一个包含若干dict的dict  后续读取股票月度收益率时较为方便 只需要股票的证券代码和交易月份即可读

print("\n开始计算每只股票的月度收益率")

Mincomes = {}

for i in range(len(stkcds)):

    s = stocks_Mclsprc[i]['Stkcd']
    s = str(s)
    n = len(stocks_Mclsprc[i]['Mclsprc'])

    Mincomes_one_stock = {}
    Mincomes_one_stock.clear()
    
    for j in range(n-1):

        last_month_price = stocks_Mclsprc[i]['Mclsprc'][j][1]
        this_month = stocks_Mclsprc[i]['Mclsprc'][j+1][0]
        this_month_price = stocks_Mclsprc[i]['Mclsprc'][j+1][1]
        month_income = (this_month_price-last_month_price)/last_month_price    # 月度收益率
        Mincomes_one_stock[this_month] = month_income

    Mincomes[s] = Mincomes_one_stock

print("计算完成")

# print(len(Mincomes))  # 1533
# print(Mincomes['600000'])
# {'2015-02': 0.004146510020732462, '2015-03': 0.08671713695801789, '2015-04': 0.14439518682710584, '2015-05': -0.057000553403431166, '2015-06': -0.004694835680751074, '2015-07': -0.11143867924528304, '2015-08': -0.007299270072992663, '2015-09': 0.11163101604278061, '2015-10': -0.01443174984966918, '2015-11': 0.13788895668090287, '2015-12': -0.02037533512064338, '2016-01': -0.07498631636562676, '2016-02': 0.091715976331361, '2016-03': -0.028184281842818407, '2016-04': -0.005577244841048602, '2016-05': 0.02579921480650594, '2016-06': -0.14871514488791684, '2016-07': 0.008349389852279962, '2016-08': 0.049681528662420454, '2016-09': 0.0006067961165047336, '2016-10': -0.013341419041843473, '2016-11': 0.05470190534726494, '2016-12': -0.05536130536130532, '2017-01': 0.03269586674892027, '2017-02': -0.008960573476702425, '2017-03': -0.03496081977094625, '2017-04': -0.04996876951905063, '2017-05': -0.1558185404339251, '2017-06': -0.014797507788161956, '2017-07': 0.05612648221343866, '2017-08': -0.04865269461077834, '2017-09': 0.012588512981903882, '2017-10': -0.020202020202020186, '2017-11': 0.023790642347343436, '2017-12': -0.024786986831913268, '2018-01': 0.04606830818109611, '2018-02': -0.053910402429764546, '2018-03': -0.06500802568218302, '2018-04': -0.0034334763948498646, '2018-05': -0.0913006029285098, '2018-06': -0.09383886255924172, '2018-07': 0.06380753138075307, '2018-08': 0.015732546705998048, '2018-09': 0.02807357212003864, '2018-10': 0.03389830508474588, '2018-11': -0.02459016393442619, '2018-12': -0.08496732026143791, '2019-01': 0.09489795918367343, '2019-02': 0.09412861136999065, '2019-03': -0.03918228279386719, '2019-04': 0.06117021276595756, '2019-05': -0.07017543859649121, '2019-06': 0.04941599281221913, '2019-07': 0.01626712328767119, '2019-08': -0.04970513900589721, '2019-09': 0.04964539007092203, '2019-10': 0.056587837837837836, '2019-11': -0.04796163069544362, '2019-12': 0.03862300587741386, '2020-01': -0.08245755860953918, '2020-02': -0.04405286343612335, '2020-03': -0.06451612903225801, '2020-04': 0.04729064039408871, '2020-05': -0.005644402634054609, '2020-06': 0.0009460737937558928}
# print(Mincomes['600010']['2017-07'])   
# 0.28310502283105027




# 读取上证指数月度数据
# 市场月度收益率已在xlsx中计算完成 直接读取
# 市场月度收益率 = (本月收盘指数-上月收盘指数)/上月收盘指数

print("\n开始读取上证指数月度数据")

Mincomes_market = {}

fileName = os.getcwd() + "\\上证指数月度数据.xlsx"
df = pd.DataFrame(pd.read_excel(fileName))

for i in range(len(df)):

    if(i>0):  # 跳过2015-01

        month = df.Month[i]
        income = df.Mincome[i]
        Mincomes_market[month] = income

print("读取完成")

# print(Mincomes_market)
# print(Mincomes_market['2017-07'])
# 0.025247562434473726



# 计算每只股票的超额收益率 AR
# 股票超额收益率 = 股票月度收益率-市场月度收益率

print("\n开始计算每只股票的超额收益率")

Abnormal = {}   # 结构同 Mincomes

for i in range(len(stkcds)):

    s = stkcds[i]
    s = str(s)
    
    Abnormal_one_stock = {}
    Abnormal_one_stock.clear()

    for j in range(len(months)):

        if(j>0):

            m = months[j]
            if(m in Mincomes[s]):
                mincome1 = Mincomes[s][m]
                mincome_market1 = Mincomes_market[m]
                a = mincome1 - mincome_market1
                Abnormal_one_stock[m] = a

    Abnormal[s] = Abnormal_one_stock

print("计算完成")

# print(Abnormal['600000'])
# print(Abnormal['600010']['2017-07'])  
# 0.25785746039657653 







# 选取 5% 还是 10% 作为输家/赢家组合

# select = 0.05
# select = 0.1



# 形成期 

# formation_period = ['2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12']
# formation_period = ['2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
#                     '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06']
# formation_period = ['2015-02','2015-03','2015-04','2015-05','2015-06','2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07']
# formation_period = ['2015-02','2015-03','2015-04','2015-05','2015-06','2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
#                     '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06']
# formation_period = ['2015-02','2015-03','2015-04','2015-05','2015-06','2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12']
# formation_period = ['2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12']
# formation_period = ['2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
#                     '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
# formation_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
# formation_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                     '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06']




# 检验期 

# test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
# test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                     '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12']
# test_period = ['2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06']
# test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12']
# test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
# test_period = ['2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
# test_period = ['2016-08','2016-09','2016-10','2016-11','2016-12',
#                      '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06']
# test_period = ['2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06']
# test_period = ['2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
# test_period = ['2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']




# 最优结果 1

formation_period = ['2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12']
test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
                '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
                '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
                '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
select = 0.05


# 最优结果 2

# formation_period = ['2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
#                     '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
# test_period = ['2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06']
# select = 0.1




formation = len(formation_period)
test = len(test_period)

split = test_period[0]

print("\n形成期:" + formation_period[0] + " ~ " + formation_period[-1] + "    共"+str(formation)+"个月")
print("检验期:" +  test_period[0] + " ~ " +test_period[-1]+ "    共"+str(test)+"个月")

period = str(formation)+"_"+str(test)+"_"+split+"_"+str(select)



# 取形成期和检验期可用股票的交集

sf = stkcds_every_month[formation_period[0]] # 形成期可用股票
# print(len(sf))

for i in range(formation):

    mtemp = formation_period[i]
    stemp = stkcds_every_month[mtemp]
    sf = list(set(stemp).intersection(set(sf))) 
    # print(len(sf))

st = stkcds_every_month[test_period[0]] # 检验期可用股票
# print(len(st))

for i in range(test):

    mtemp = test_period[i]
    stemp = stkcds_every_month[mtemp]
    st = list(set(stemp).intersection(set(st))) 
    # print(len(st))

st = list(set(sf).intersection(set(st)))  # 取交集
sp = st

# print("\n")
# print(len(sp))



# 根据得到的股票 计算形成期的累计超额收益率 记录检验期的超额收益率

stocks_this_period = []    # 格式是一个包含若干dict的list

for i in range(len(sp)):

    one_stock = {}
    one_stock.clear()
    s = sp[i]

    one_stock['Stkcd'] = s
    one_stock['Abnormal_formation'] = 0   # 形成期的累计超额收益率

    for j in range(formation):

        mj = formation_period[j]
        one_stock['Abnormal_formation'] = one_stock['Abnormal_formation']+ Abnormal[s][mj] 

    one_stock['Abnormal_test'] = []   # 检验期每个月的超额收益率

    for j in range(test):

        mj = test_period[j]
        one_stock['Abnormal_test'].append(Abnormal[s][mj])


    stocks_this_period.append(one_stock)



# 按形成期的累计超额收益率从小到大排序

stocks_this_period = sorted(stocks_this_period, key=lambda k: k['Abnormal_formation']) 
l = len(stocks_this_period)


# 排名前10%(5%)为输家组合 后10%(5%)为赢家组合

# num = int(0.1*l)
# num = int(0.05*l)
num = int(select*l)
loser = stocks_this_period[0:num]
winner = stocks_this_period[l-num:l]



# 将一个投资组合S及相关指标写入EXCEL

def write_Excel(S,name,period):

    filepath = (os.getcwd()+"\\分组\\"+period) 
    isExists=os.path.exists(filepath)
    if not isExists:
        os.makedirs(filepath)
    excelpath = (os.getcwd()+"\\分组\\"+period+"\\"+name+".xls") 
    workbook = xlwt.Workbook(encoding='utf-8')
    sheet = workbook.add_sheet('Sheet1',cell_overwrite_ok=True)
    headlist=['Stkcd','Formation']
    row=0
    col=0
    for head in headlist:
        sheet.write(row,col,head)
        col=col+1
    n = len(S)
    for i in range(n):
        sheet.write(i+1,0,int(S[i]['Stkcd']))
        sheet.write(i+1,1,float(S[i]['Abnormal_formation']))
        # sheet.write(i+1,2,float(S[i]['Abnormal_test']))

    workbook.save(excelpath) 



# 将输家组合和赢家组合写入xls

write_Excel(loser,"loser",period)
write_Excel(winner,"winner",period)


# print(winner[0])
# print(Abnormal['600131']['2017-01'])  



# 计算一个股票在检验期每个月的累计超额收益率 并加入 one_stock 中

def CAR_stock(S):

    num = len(S)

    for i in range(num):

        one_stock = S[i]
        ar = one_stock['Abnormal_test']
        CAR_test = []
        CAR_test.clear()

        for j in range(test):

            length = j+1
            car = 0

            for t in range(length):
                car = car + ar[t]

            CAR_test.append(car)
        
        S[i]['CAR_test'] = CAR_test
    
    return S



# 计算一个投资组合在检验期每个月的累计超额收益率

def CAR_portfolio(S):

    num = len(S)
    CAR = []

    for i in range(test):

        car_point = 0

        for j in range(num):

            car_point = car_point + S[j]['CAR_test'][i]
        
        car_point = car_point/num
        CAR.append(car_point)
    
    return CAR




loser_num = len(loser)
loser = CAR_stock(loser)
loser_CAR = CAR_portfolio(loser)

winner_num = len(winner)
winner = CAR_stock(winner)
winner_CAR = CAR_portfolio(winner)



# for i in range(test):

#     print(str(loser_CAR[i])+"  "+str(winner_CAR[i]))



result = {'loser':loser_CAR,'winner':winner_CAR}
result = pd.DataFrame(result)



path = (os.getcwd()+"\\结果\\"+period+".xls")
result.to_excel(path)




"""
Created on Wed Dec 16 01:17:48 2020

@author: gy
"""

# 画图

import matplotlib.pyplot as plt


plt.plot(test_period, loser_CAR, color='blue')
plt.plot(test_period, winner_CAR, color='red')
plt.yticks(fontsize=8)
plt.xticks(rotation=90,fontsize=5)

t = "Formation: " + formation_period[0] + " ~ " + formation_period[-1] + "   " + str(formation)+" months" + "\nTest: " +  test_period[0] + " ~ " +test_period[-1]+ "   "+str(test)+" months"+"\nSplit: "+split + "    "+str(int(select*100))+"%"
plt.title(t,fontsize=10)

path = (os.getcwd()+"\\结果\\"+period+".png")
plt.savefig(path)

plt.show()
f6e043101889db18dad31493ff4362d24ae09394
<mask token>


def write_Excel(S, name, period):
    filepath = os.getcwd() + '\\分组\\' + period
    isExists = os.path.exists(filepath)
    if not isExists:
        os.makedirs(filepath)
    excelpath = os.getcwd() + '\\分组\\' + period + '\\' + name + '.xls'
    workbook = xlwt.Workbook(encoding='utf-8')
    sheet = workbook.add_sheet('Sheet1', cell_overwrite_ok=True)
    headlist = ['Stkcd', 'Formation']
    row = 0
    col = 0
    for head in headlist:
        sheet.write(row, col, head)
        col = col + 1
    n = len(S)
    for i in range(n):
        sheet.write(i + 1, 0, int(S[i]['Stkcd']))
        sheet.write(i + 1, 1, float(S[i]['Abnormal_formation']))
    workbook.save(excelpath)


<mask token>


def CAR_stock(S):
    num = len(S)
    for i in range(num):
        one_stock = S[i]
        ar = one_stock['Abnormal_test']
        CAR_test = []
        CAR_test.clear()
        for j in range(test):
            length = j + 1
            car = 0
            for t in range(length):
                car = car + ar[t]
            CAR_test.append(car)
        S[i]['CAR_test'] = CAR_test
    return S


def CAR_portfolio(S):
    num = len(S)
    CAR = []
    for i in range(test):
        car_point = 0
        for j in range(num):
            car_point = car_point + S[j]['CAR_test'][i]
        car_point = car_point / num
        CAR.append(car_point)
    return CAR


<mask token>

<mask token>
print('\n开始读取上证A股月度数据')
<mask token>
for i in range(len(df)):
    s = df.Stkcd[i]
    t = {}
    t.clear()
    if s not in stkcds:
        stkcds.append(s)
        t['Stkcd'] = s
        j = i
        Mclsprc = []
        Mclsprc.clear()
        while j < len(df) and df.Stkcd[j] == s:
            month_Mclsprc = []
            month_Mclsprc.clear()
            month_Mclsprc.append(df.Trdmnt[j])
            month_Mclsprc.append(df.Mclsprc[j])
            Mclsprc.append(month_Mclsprc)
            j = j + 1
        t['Mclsprc'] = Mclsprc
        stocks_Mclsprc.append(t)
print('读取完成')
<mask token>
for i in range(len(months)):
    m = months[i]
    stkcds_every_month[m] = []
<mask token>
for i in range(len(df)):
    m = df.Trdmnt[i]
    s = df.Stkcd[i]
    s = str(s)
    if s not in stkcds_tmp:
        stkcds_tmp.append(s)
    elif s not in stkcds_every_month[m]:
        stkcds_every_month[m].append(s)
print('\n开始计算每只股票的月度收益率')
<mask token>
for i in range(len(stkcds)):
    s = stocks_Mclsprc[i]['Stkcd']
    s = str(s)
    n = len(stocks_Mclsprc[i]['Mclsprc'])
    Mincomes_one_stock = {}
    Mincomes_one_stock.clear()
    for j in range(n - 1):
        last_month_price = stocks_Mclsprc[i]['Mclsprc'][j][1]
        this_month = stocks_Mclsprc[i]['Mclsprc'][j + 1][0]
        this_month_price = stocks_Mclsprc[i]['Mclsprc'][j + 1][1]
        month_income = (this_month_price - last_month_price) / last_month_price
        Mincomes_one_stock[this_month] = month_income
    Mincomes[s] = Mincomes_one_stock
print('计算完成')
print('\n开始读取上证指数月度数据')
<mask token>
for i in range(len(df)):
    if i > 0:
        month = df.Month[i]
        income = df.Mincome[i]
        Mincomes_market[month] = income
print('读取完成')
print('\n开始计算每只股票的超额收益率')
<mask token>
for i in range(len(stkcds)):
    s = stkcds[i]
    s = str(s)
    Abnormal_one_stock = {}
    Abnormal_one_stock.clear()
    for j in range(len(months)):
        if j > 0:
            m = months[j]
            if m in Mincomes[s]:
                mincome1 = Mincomes[s][m]
                mincome_market1 = Mincomes_market[m]
                a = mincome1 - mincome_market1
                Abnormal_one_stock[m] = a
    Abnormal[s] = Abnormal_one_stock
print('计算完成')
<mask token>
print('\n形成期:' + formation_period[0] + ' ~ ' + formation_period[-1] +
    '    共' + str(formation) + '个月')
print('检验期:' + test_period[0] + ' ~ ' + test_period[-1] + '    共' + str(
    test) + '个月')
<mask token>
for i in range(formation):
    mtemp = formation_period[i]
    stemp = stkcds_every_month[mtemp]
    sf = list(set(stemp).intersection(set(sf)))
<mask token>
for i in range(test):
    mtemp = test_period[i]
    stemp = stkcds_every_month[mtemp]
    st = list(set(stemp).intersection(set(st)))
<mask token>
for i in range(len(sp)):
    one_stock = {}
    one_stock.clear()
    s = sp[i]
    one_stock['Stkcd'] = s
    one_stock['Abnormal_formation'] = 0
    for j in range(formation):
        mj = formation_period[j]
        one_stock['Abnormal_formation'] = one_stock['Abnormal_formation'
            ] + Abnormal[s][mj]
    one_stock['Abnormal_test'] = []
    for j in range(test):
        mj = test_period[j]
        one_stock['Abnormal_test'].append(Abnormal[s][mj])
    stocks_this_period.append(one_stock)
<mask token>


def write_Excel(S, name, period):
    filepath = os.getcwd() + '\\分组\\' + period
    isExists = os.path.exists(filepath)
    if not isExists:
        os.makedirs(filepath)
    excelpath = os.getcwd() + '\\分组\\' + period + '\\' + name + '.xls'
    workbook = xlwt.Workbook(encoding='utf-8')
    sheet = workbook.add_sheet('Sheet1', cell_overwrite_ok=True)
    headlist = ['Stkcd', 'Formation']
    row = 0
    col = 0
    for head in headlist:
        sheet.write(row, col, head)
        col = col + 1
    n = len(S)
    for i in range(n):
        sheet.write(i + 1, 0, int(S[i]['Stkcd']))
        sheet.write(i + 1, 1, float(S[i]['Abnormal_formation']))
    workbook.save(excelpath)


write_Excel(loser, 'loser', period)
write_Excel(winner, 'winner', period)


def CAR_stock(S):
    num = len(S)
    for i in range(num):
        one_stock = S[i]
        ar = one_stock['Abnormal_test']
        CAR_test = []
        CAR_test.clear()
        for j in range(test):
            length = j + 1
            car = 0
            for t in range(length):
                car = car + ar[t]
            CAR_test.append(car)
        S[i]['CAR_test'] = CAR_test
    return S


def CAR_portfolio(S):
    num = len(S)
    CAR = []
    for i in range(test):
        car_point = 0
        for j in range(num):
            car_point = car_point + S[j]['CAR_test'][i]
        car_point = car_point / num
        CAR.append(car_point)
    return CAR


<mask token>
result.to_excel(path)
<mask token>
plt.plot(test_period, loser_CAR, color='blue')
plt.plot(test_period, winner_CAR, color='red')
plt.yticks(fontsize=8)
plt.xticks(rotation=90, fontsize=5)
<mask token>
plt.title(t, fontsize=10)
<mask token>
plt.savefig(path)
plt.show()

<mask token>
months = ['2015-01', '2015-02', '2015-03', '2015-04', '2015-05', '2015-06',
    '2015-07', '2015-08', '2015-09', '2015-10', '2015-11', '2015-12',
    '2016-01', '2016-02', '2016-03', '2016-04', '2016-05', '2016-06',
    '2016-07', '2016-08', '2016-09', '2016-10', '2016-11', '2016-12',
    '2017-01', '2017-02', '2017-03', '2017-04', '2017-05', '2017-06',
    '2017-07', '2017-08', '2017-09', '2017-10', '2017-11', '2017-12',
    '2018-01', '2018-02', '2018-03', '2018-04', '2018-05', '2018-06',
    '2018-07', '2018-08', '2018-09', '2018-10', '2018-11', '2018-12',
    '2019-01', '2019-02', '2019-03', '2019-04', '2019-05', '2019-06',
    '2019-07', '2019-08', '2019-09', '2019-10', '2019-11', '2019-12',
    '2020-01', '2020-02', '2020-03', '2020-04', '2020-05', '2020-06']
print('\n开始读取上证A股月度数据')
stkcds = []
stocks_Mclsprc = []
fileName = os.getcwd() + '\\上证A股月度数据.xlsx'
df = pd.DataFrame(pd.read_excel(fileName))
for i in range(len(df)):
    s = df.Stkcd[i]
    t = {}
    t.clear()
    if s not in stkcds:
        stkcds.append(s)
        t['Stkcd'] = s
        j = i
        Mclsprc = []
        Mclsprc.clear()
        while j < len(df) and df.Stkcd[j] == s:
            month_Mclsprc = []
            month_Mclsprc.clear()
            month_Mclsprc.append(df.Trdmnt[j])
            month_Mclsprc.append(df.Mclsprc[j])
            Mclsprc.append(month_Mclsprc)
            j = j + 1
        t['Mclsprc'] = Mclsprc
        stocks_Mclsprc.append(t)
print('读取完成')
stkcds_every_month = {}
for i in range(len(months)):
    m = months[i]
    stkcds_every_month[m] = []
stkcds_tmp = []
for i in range(len(df)):
    m = df.Trdmnt[i]
    s = df.Stkcd[i]
    s = str(s)
    if s not in stkcds_tmp:
        stkcds_tmp.append(s)
    elif s not in stkcds_every_month[m]:
        stkcds_every_month[m].append(s)
print('\n开始计算每只股票的月度收益率')
Mincomes = {}
for i in range(len(stkcds)):
    s = stocks_Mclsprc[i]['Stkcd']
    s = str(s)
    n = len(stocks_Mclsprc[i]['Mclsprc'])
    Mincomes_one_stock = {}
    Mincomes_one_stock.clear()
    for j in range(n - 1):
        last_month_price = stocks_Mclsprc[i]['Mclsprc'][j][1]
        this_month = stocks_Mclsprc[i]['Mclsprc'][j + 1][0]
        this_month_price = stocks_Mclsprc[i]['Mclsprc'][j + 1][1]
        month_income = (this_month_price - last_month_price) / last_month_price
        Mincomes_one_stock[this_month] = month_income
    Mincomes[s] = Mincomes_one_stock
print('计算完成')
print('\n开始读取上证指数月度数据')
Mincomes_market = {}
fileName = os.getcwd() + '\\上证指数月度数据.xlsx'
df = pd.DataFrame(pd.read_excel(fileName))
for i in range(len(df)):
    if i > 0:
        month = df.Month[i]
        income = df.Mincome[i]
        Mincomes_market[month] = income
print('读取完成')
print('\n开始计算每只股票的超额收益率')
Abnormal = {}
for i in range(len(stkcds)):
    s = stkcds[i]
    s = str(s)
    Abnormal_one_stock = {}
    Abnormal_one_stock.clear()
    for j in range(len(months)):
        if j > 0:
            m = months[j]
            if m in Mincomes[s]:
                mincome1 = Mincomes[s][m]
                mincome_market1 = Mincomes_market[m]
                a = mincome1 - mincome_market1
                Abnormal_one_stock[m] = a
    Abnormal[s] = Abnormal_one_stock
print('计算完成')
formation_period = ['2016-01', '2016-02', '2016-03', '2016-04', '2016-05',
    '2016-06', '2016-07', '2016-08', '2016-09', '2016-10', '2016-11', '2016-12'
    ]
test_period = ['2017-01', '2017-02', '2017-03', '2017-04', '2017-05',
    '2017-06', '2017-07', '2017-08', '2017-09', '2017-10', '2017-11',
    '2017-12', '2018-01', '2018-02', '2018-03', '2018-04', '2018-05',
    '2018-06', '2018-07', '2018-08', '2018-09', '2018-10', '2018-11',
    '2018-12', '2019-01', '2019-02', '2019-03', '2019-04', '2019-05',
    '2019-06', '2019-07', '2019-08', '2019-09', '2019-10', '2019-11',
    '2019-12', '2020-01', '2020-02', '2020-03', '2020-04', '2020-05', '2020-06'
    ]
select = 0.05
formation = len(formation_period)
test = len(test_period)
split = test_period[0]
print('\n形成期:' + formation_period[0] + ' ~ ' + formation_period[-1] +
    '    共' + str(formation) + '个月')
print('检验期:' + test_period[0] + ' ~ ' + test_period[-1] + '    共' + str(
    test) + '个月')
period = str(formation) + '_' + str(test) + '_' + split + '_' + str(select)
sf = stkcds_every_month[formation_period[0]]
for i in range(formation):
    mtemp = formation_period[i]
    stemp = stkcds_every_month[mtemp]
    sf = list(set(stemp).intersection(set(sf)))
st = stkcds_every_month[test_period[0]]
for i in range(test):
    mtemp = test_period[i]
    stemp = stkcds_every_month[mtemp]
    st = list(set(stemp).intersection(set(st)))
st = list(set(sf).intersection(set(st)))
sp = st
stocks_this_period = []
for i in range(len(sp)):
    one_stock = {}
    one_stock.clear()
    s = sp[i]
    one_stock['Stkcd'] = s
    one_stock['Abnormal_formation'] = 0
    for j in range(formation):
        mj = formation_period[j]
        one_stock['Abnormal_formation'] = one_stock['Abnormal_formation'
            ] + Abnormal[s][mj]
    one_stock['Abnormal_test'] = []
    for j in range(test):
        mj = test_period[j]
        one_stock['Abnormal_test'].append(Abnormal[s][mj])
    stocks_this_period.append(one_stock)
stocks_this_period = sorted(stocks_this_period, key=lambda k: k[
    'Abnormal_formation'])
l = len(stocks_this_period)
num = int(select * l)
loser = stocks_this_period[0:num]
winner = stocks_this_period[l - num:l]


def write_Excel(S, name, period):
    filepath = os.getcwd() + '\\分组\\' + period
    isExists = os.path.exists(filepath)
    if not isExists:
        os.makedirs(filepath)
    excelpath = os.getcwd() + '\\分组\\' + period + '\\' + name + '.xls'
    workbook = xlwt.Workbook(encoding='utf-8')
    sheet = workbook.add_sheet('Sheet1', cell_overwrite_ok=True)
    headlist = ['Stkcd', 'Formation']
    row = 0
    col = 0
    for head in headlist:
        sheet.write(row, col, head)
        col = col + 1
    n = len(S)
    for i in range(n):
        sheet.write(i + 1, 0, int(S[i]['Stkcd']))
        sheet.write(i + 1, 1, float(S[i]['Abnormal_formation']))
    workbook.save(excelpath)


write_Excel(loser, 'loser', period)
write_Excel(winner, 'winner', period)


def CAR_stock(S):
    num = len(S)
    for i in range(num):
        one_stock = S[i]
        ar = one_stock['Abnormal_test']
        CAR_test = []
        CAR_test.clear()
        for j in range(test):
            length = j + 1
            car = 0
            for t in range(length):
                car = car + ar[t]
            CAR_test.append(car)
        S[i]['CAR_test'] = CAR_test
    return S


def CAR_portfolio(S):
    num = len(S)
    CAR = []
    for i in range(test):
        car_point = 0
        for j in range(num):
            car_point = car_point + S[j]['CAR_test'][i]
        car_point = car_point / num
        CAR.append(car_point)
    return CAR


loser_num = len(loser)
loser = CAR_stock(loser)
loser_CAR = CAR_portfolio(loser)
winner_num = len(winner)
winner = CAR_stock(winner)
winner_CAR = CAR_portfolio(winner)
result = {'loser': loser_CAR, 'winner': winner_CAR}
result = pd.DataFrame(result)
path = os.getcwd() + '\\结果\\' + period + '.xls'
result.to_excel(path)
<mask token>
plt.plot(test_period, loser_CAR, color='blue')
plt.plot(test_period, winner_CAR, color='red')
plt.yticks(fontsize=8)
plt.xticks(rotation=90, fontsize=5)
t = 'Formation: ' + formation_period[0] + ' ~ ' + formation_period[-1
    ] + '   ' + str(formation) + ' months' + '\nTest: ' + test_period[0
    ] + ' ~ ' + test_period[-1] + '   ' + str(test
    ) + ' months' + '\nSplit: ' + split + '    ' + str(int(select * 100)) + '%'
plt.title(t, fontsize=10)
path = os.getcwd() + '\\结果\\' + period + '.png'
plt.savefig(path)
plt.show()

<mask token>
import os
import pandas as pd
import numpy as np
import xlwt
months = ['2015-01', '2015-02', '2015-03', '2015-04', '2015-05', '2015-06',
    '2015-07', '2015-08', '2015-09', '2015-10', '2015-11', '2015-12',
    '2016-01', '2016-02', '2016-03', '2016-04', '2016-05', '2016-06',
    '2016-07', '2016-08', '2016-09', '2016-10', '2016-11', '2016-12',
    '2017-01', '2017-02', '2017-03', '2017-04', '2017-05', '2017-06',
    '2017-07', '2017-08', '2017-09', '2017-10', '2017-11', '2017-12',
    '2018-01', '2018-02', '2018-03', '2018-04', '2018-05', '2018-06',
    '2018-07', '2018-08', '2018-09', '2018-10', '2018-11', '2018-12',
    '2019-01', '2019-02', '2019-03', '2019-04', '2019-05', '2019-06',
    '2019-07', '2019-08', '2019-09', '2019-10', '2019-11', '2019-12',
    '2020-01', '2020-02', '2020-03', '2020-04', '2020-05', '2020-06']
print('\n开始读取上证A股月度数据')
stkcds = []
stocks_Mclsprc = []
fileName = os.getcwd() + '\\上证A股月度数据.xlsx'
df = pd.DataFrame(pd.read_excel(fileName))
for i in range(len(df)):
    s = df.Stkcd[i]
    t = {}
    t.clear()
    if s not in stkcds:
        stkcds.append(s)
        t['Stkcd'] = s
        j = i
        Mclsprc = []
        Mclsprc.clear()
        while j < len(df) and df.Stkcd[j] == s:
            month_Mclsprc = []
            month_Mclsprc.clear()
            month_Mclsprc.append(df.Trdmnt[j])
            month_Mclsprc.append(df.Mclsprc[j])
            Mclsprc.append(month_Mclsprc)
            j = j + 1
        t['Mclsprc'] = Mclsprc
        stocks_Mclsprc.append(t)
print('读取完成')
stkcds_every_month = {}
for i in range(len(months)):
    m = months[i]
    stkcds_every_month[m] = []
stkcds_tmp = []
for i in range(len(df)):
    m = df.Trdmnt[i]
    s = df.Stkcd[i]
    s = str(s)
    if s not in stkcds_tmp:
        stkcds_tmp.append(s)
    elif s not in stkcds_every_month[m]:
        stkcds_every_month[m].append(s)
print('\n开始计算每只股票的月度收益率')
Mincomes = {}
for i in range(len(stkcds)):
    s = stocks_Mclsprc[i]['Stkcd']
    s = str(s)
    n = len(stocks_Mclsprc[i]['Mclsprc'])
    Mincomes_one_stock = {}
    Mincomes_one_stock.clear()
    for j in range(n - 1):
        last_month_price = stocks_Mclsprc[i]['Mclsprc'][j][1]
        this_month = stocks_Mclsprc[i]['Mclsprc'][j + 1][0]
        this_month_price = stocks_Mclsprc[i]['Mclsprc'][j + 1][1]
        month_income = (this_month_price - last_month_price) / last_month_price
        Mincomes_one_stock[this_month] = month_income
    Mincomes[s] = Mincomes_one_stock
print('计算完成')
print('\n开始读取上证指数月度数据')
Mincomes_market = {}
fileName = os.getcwd() + '\\上证指数月度数据.xlsx'
df = pd.DataFrame(pd.read_excel(fileName))
for i in range(len(df)):
    if i > 0:
        month = df.Month[i]
        income = df.Mincome[i]
        Mincomes_market[month] = income
print('读取完成')
print('\n开始计算每只股票的超额收益率')
Abnormal = {}
for i in range(len(stkcds)):
    s = stkcds[i]
    s = str(s)
    Abnormal_one_stock = {}
    Abnormal_one_stock.clear()
    for j in range(len(months)):
        if j > 0:
            m = months[j]
            if m in Mincomes[s]:
                mincome1 = Mincomes[s][m]
                mincome_market1 = Mincomes_market[m]
                a = mincome1 - mincome_market1
                Abnormal_one_stock[m] = a
    Abnormal[s] = Abnormal_one_stock
print('计算完成')
formation_period = ['2016-01', '2016-02', '2016-03', '2016-04', '2016-05',
    '2016-06', '2016-07', '2016-08', '2016-09', '2016-10', '2016-11', '2016-12'
    ]
test_period = ['2017-01', '2017-02', '2017-03', '2017-04', '2017-05',
    '2017-06', '2017-07', '2017-08', '2017-09', '2017-10', '2017-11',
    '2017-12', '2018-01', '2018-02', '2018-03', '2018-04', '2018-05',
    '2018-06', '2018-07', '2018-08', '2018-09', '2018-10', '2018-11',
    '2018-12', '2019-01', '2019-02', '2019-03', '2019-04', '2019-05',
    '2019-06', '2019-07', '2019-08', '2019-09', '2019-10', '2019-11',
    '2019-12', '2020-01', '2020-02', '2020-03', '2020-04', '2020-05', '2020-06'
    ]
select = 0.05
formation = len(formation_period)
test = len(test_period)
split = test_period[0]
print('\n形成期:' + formation_period[0] + ' ~ ' + formation_period[-1] +
    '    共' + str(formation) + '个月')
print('检验期:' + test_period[0] + ' ~ ' + test_period[-1] + '    共' + str(
    test) + '个月')
period = str(formation) + '_' + str(test) + '_' + split + '_' + str(select)
sf = stkcds_every_month[formation_period[0]]
for i in range(formation):
    mtemp = formation_period[i]
    stemp = stkcds_every_month[mtemp]
    sf = list(set(stemp).intersection(set(sf)))
st = stkcds_every_month[test_period[0]]
for i in range(test):
    mtemp = test_period[i]
    stemp = stkcds_every_month[mtemp]
    st = list(set(stemp).intersection(set(st)))
st = list(set(sf).intersection(set(st)))
sp = st
stocks_this_period = []
for i in range(len(sp)):
    one_stock = {}
    one_stock.clear()
    s = sp[i]
    one_stock['Stkcd'] = s
    one_stock['Abnormal_formation'] = 0
    for j in range(formation):
        mj = formation_period[j]
        one_stock['Abnormal_formation'] = one_stock['Abnormal_formation'
            ] + Abnormal[s][mj]
    one_stock['Abnormal_test'] = []
    for j in range(test):
        mj = test_period[j]
        one_stock['Abnormal_test'].append(Abnormal[s][mj])
    stocks_this_period.append(one_stock)
stocks_this_period = sorted(stocks_this_period, key=lambda k: k[
    'Abnormal_formation'])
l = len(stocks_this_period)
num = int(select * l)
loser = stocks_this_period[0:num]
winner = stocks_this_period[l - num:l]


def write_Excel(S, name, period):
    filepath = os.getcwd() + '\\分组\\' + period
    isExists = os.path.exists(filepath)
    if not isExists:
        os.makedirs(filepath)
    excelpath = os.getcwd() + '\\分组\\' + period + '\\' + name + '.xls'
    workbook = xlwt.Workbook(encoding='utf-8')
    sheet = workbook.add_sheet('Sheet1', cell_overwrite_ok=True)
    headlist = ['Stkcd', 'Formation']
    row = 0
    col = 0
    for head in headlist:
        sheet.write(row, col, head)
        col = col + 1
    n = len(S)
    for i in range(n):
        sheet.write(i + 1, 0, int(S[i]['Stkcd']))
        sheet.write(i + 1, 1, float(S[i]['Abnormal_formation']))
    workbook.save(excelpath)


write_Excel(loser, 'loser', period)
write_Excel(winner, 'winner', period)


def CAR_stock(S):
    num = len(S)
    for i in range(num):
        one_stock = S[i]
        ar = one_stock['Abnormal_test']
        CAR_test = []
        CAR_test.clear()
        for j in range(test):
            length = j + 1
            car = 0
            for t in range(length):
                car = car + ar[t]
            CAR_test.append(car)
        S[i]['CAR_test'] = CAR_test
    return S


def CAR_portfolio(S):
    num = len(S)
    CAR = []
    for i in range(test):
        car_point = 0
        for j in range(num):
            car_point = car_point + S[j]['CAR_test'][i]
        car_point = car_point / num
        CAR.append(car_point)
    return CAR


loser_num = len(loser)
loser = CAR_stock(loser)
loser_CAR = CAR_portfolio(loser)
winner_num = len(winner)
winner = CAR_stock(winner)
winner_CAR = CAR_portfolio(winner)
result = {'loser': loser_CAR, 'winner': winner_CAR}
result = pd.DataFrame(result)
path = os.getcwd() + '\\结果\\' + period + '.xls'
result.to_excel(path)
<mask token>
import matplotlib.pyplot as plt
plt.plot(test_period, loser_CAR, color='blue')
plt.plot(test_period, winner_CAR, color='red')
plt.yticks(fontsize=8)
plt.xticks(rotation=90, fontsize=5)
t = 'Formation: ' + formation_period[0] + ' ~ ' + formation_period[-1
    ] + '   ' + str(formation) + ' months' + '\nTest: ' + test_period[0
    ] + ' ~ ' + test_period[-1] + '   ' + str(test
    ) + ' months' + '\nSplit: ' + split + '    ' + str(int(select * 100)) + '%'
plt.title(t, fontsize=10)
path = os.getcwd() + '\\结果\\' + period + '.png'
plt.savefig(path)
plt.show()

"""
Created on Wed Dec 16 01:17:48 2020

@author: zym
"""

import os
import pandas as pd
import numpy as np
import xlwt



months = ['2015-01','2015-02','2015-03','2015-04','2015-05','2015-06','2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
            '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
            '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
            '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
            '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
            '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']



# 读取上证A股月度数据

print("\n开始读取上证A股月度数据")

stkcds = []              # 所有股票的证券代码 Stkcd
stocks_Mclsprc = []      # 股票交易月份及对应的月收盘价 Trdmnt Mclsprc


fileName = os.getcwd() + "\\上证A股月度数据.xlsx"
df = pd.DataFrame(pd.read_excel(fileName))

for i in range(len(df)):

    s = df.Stkcd[i]
    t = {}
    t.clear()


    if(s not in stkcds):

        # print(s)

        stkcds.append(s)
        t['Stkcd'] = s

        j = i
        
        Mclsprc = []
        Mclsprc.clear()

        while(j<len(df) and df.Stkcd[j]==s):

            month_Mclsprc = []
            month_Mclsprc.clear()
            month_Mclsprc.append(df.Trdmnt[j])
            month_Mclsprc.append(df.Mclsprc[j])
            Mclsprc.append(month_Mclsprc)
            j = j+1
        
        t['Mclsprc'] = Mclsprc
        stocks_Mclsprc.append(t)

print("读取完成")


# print(len(stkcds))            # 1533
# print(len(stocks_Mclsprc))    # 1533
# print(stocks_Mclsprc[0])

# stocks_Mclsprc是一个包含若干dict的list  存储读入的A股月度数据
# 每一个dict代表一个股票 以 stocks_Mclsprc[0] 为例 如下所示
# {'Stkcd': 600000, 'Mclsprc': [['2015-01', 14.47], ['2015-02', 14.53], ['2015-03', 15.79], ['2015-04', 18.07], ['2015-05', 17.04], ['2015-06', 16.96], ['2015-07', 15.07], ['2015-08', 14.96], ['2015-09', 16.63], ['2015-10', 16.39], ['2015-11', 18.65], ['2015-12', 18.27], ['2016-01', 16.9], ['2016-02', 18.45], ['2016-03', 17.93], ['2016-04', 17.83], ['2016-05', 18.29], ['2016-06', 15.57], ['2016-07', 15.7], ['2016-08', 16.48], ['2016-09', 16.49], ['2016-10', 16.27], ['2016-11', 17.16], ['2016-12', 16.21], ['2017-01', 16.74], ['2017-02', 16.59], ['2017-03', 16.01], ['2017-04', 15.21], ['2017-05', 12.84], ['2017-06', 12.65], ['2017-07', 13.36], ['2017-08', 12.71], ['2017-09', 12.87], ['2017-10', 12.61], ['2017-11', 12.91], ['2017-12', 12.59], ['2018-01', 13.17], ['2018-02', 12.46], ['2018-03', 11.65], ['2018-04', 11.61], ['2018-05', 10.55], ['2018-06', 9.56], ['2018-07', 10.17], ['2018-08', 10.33], ['2018-09', 10.62], ['2018-10', 10.98], ['2018-11', 10.71], ['2018-12', 9.8], ['2019-01', 10.73], ['2019-02', 11.74], ['2019-03', 11.28], ['2019-04', 11.97], ['2019-05', 11.13], ['2019-06', 11.68], ['2019-07', 11.87], ['2019-08', 11.28], ['2019-09', 11.84], ['2019-10', 12.51], ['2019-11', 11.91], ['2019-12', 12.37], ['2020-01', 11.35], ['2020-02', 10.85], ['2020-03', 10.15], ['2020-04', 10.63], ['2020-05', 10.57], ['2020-06', 10.58]]}



# 从上证A股月度数据读取每个月可用的股票 用于后续选股 

stkcds_every_month = {}

for i in range(len(months)):
    m = months[i]
    stkcds_every_month[m] = []       # 初始化

stkcds_tmp = []  # 记录第一次出现的股票

for i in range(len(df)):

    m = df.Trdmnt[i]
    s = df.Stkcd[i]
    s = str(s)

    # 股票在第一次出现时不用计入 因为后续计算月度收益率时需要同时用到本月收益和上月收益 所以从第二次开始出现的股票才是可用的
    # 例如 603278 最早的数据是 2017-11 但实际可用的数据从 2017-12 开始

    if(s not in stkcds_tmp):    

        stkcds_tmp.append(s)

    else:     # 从第二次出现时 开始计入当月可用股票

        if(s not in stkcds_every_month[m]):
            stkcds_every_month[m].append(s)



# 计算每只股票的月度收益率
# 股票月度收益率 = (本月收盘价-上月收盘价)/上月收盘价
# Mincomes是一个包含若干dict的dict  后续读取股票月度收益率时较为方便 只需要股票的证券代码和交易月份即可读

print("\n开始计算每只股票的月度收益率")

Mincomes = {}

for i in range(len(stkcds)):

    s = stocks_Mclsprc[i]['Stkcd']
    s = str(s)
    n = len(stocks_Mclsprc[i]['Mclsprc'])

    Mincomes_one_stock = {}
    Mincomes_one_stock.clear()
    
    for j in range(n-1):

        last_month_price = stocks_Mclsprc[i]['Mclsprc'][j][1]
        this_month = stocks_Mclsprc[i]['Mclsprc'][j+1][0]
        this_month_price = stocks_Mclsprc[i]['Mclsprc'][j+1][1]
        month_income = (this_month_price-last_month_price)/last_month_price    # 月度收益率
        Mincomes_one_stock[this_month] = month_income

    Mincomes[s] = Mincomes_one_stock

print("计算完成")

# print(len(Mincomes))  # 1533
# print(Mincomes['600000'])
# {'2015-02': 0.004146510020732462, '2015-03': 0.08671713695801789, '2015-04': 0.14439518682710584, '2015-05': -0.057000553403431166, '2015-06': -0.004694835680751074, '2015-07': -0.11143867924528304, '2015-08': -0.007299270072992663, '2015-09': 0.11163101604278061, '2015-10': -0.01443174984966918, '2015-11': 0.13788895668090287, '2015-12': -0.02037533512064338, '2016-01': -0.07498631636562676, '2016-02': 0.091715976331361, '2016-03': -0.028184281842818407, '2016-04': -0.005577244841048602, '2016-05': 0.02579921480650594, '2016-06': -0.14871514488791684, '2016-07': 0.008349389852279962, '2016-08': 0.049681528662420454, '2016-09': 0.0006067961165047336, '2016-10': -0.013341419041843473, '2016-11': 0.05470190534726494, '2016-12': -0.05536130536130532, '2017-01': 0.03269586674892027, '2017-02': -0.008960573476702425, '2017-03': -0.03496081977094625, '2017-04': -0.04996876951905063, '2017-05': -0.1558185404339251, '2017-06': -0.014797507788161956, '2017-07': 0.05612648221343866, '2017-08': -0.04865269461077834, '2017-09': 0.012588512981903882, '2017-10': -0.020202020202020186, '2017-11': 0.023790642347343436, '2017-12': -0.024786986831913268, '2018-01': 0.04606830818109611, '2018-02': -0.053910402429764546, '2018-03': -0.06500802568218302, '2018-04': -0.0034334763948498646, '2018-05': -0.0913006029285098, '2018-06': -0.09383886255924172, '2018-07': 0.06380753138075307, '2018-08': 0.015732546705998048, '2018-09': 0.02807357212003864, '2018-10': 0.03389830508474588, '2018-11': -0.02459016393442619, '2018-12': -0.08496732026143791, '2019-01': 0.09489795918367343, '2019-02': 0.09412861136999065, '2019-03': -0.03918228279386719, '2019-04': 0.06117021276595756, '2019-05': -0.07017543859649121, '2019-06': 0.04941599281221913, '2019-07': 0.01626712328767119, '2019-08': -0.04970513900589721, '2019-09': 0.04964539007092203, '2019-10': 0.056587837837837836, '2019-11': -0.04796163069544362, '2019-12': 0.03862300587741386, '2020-01': -0.08245755860953918, '2020-02': -0.04405286343612335, '2020-03': -0.06451612903225801, '2020-04': 0.04729064039408871, '2020-05': -0.005644402634054609, '2020-06': 0.0009460737937558928}
# print(Mincomes['600010']['2017-07'])   
# 0.28310502283105027




# 读取上证指数月度数据
# 市场月度收益率已在xlsx中计算完成 直接读取
# 市场月度收益率 = (本月收盘指数-上月收盘指数)/上月收盘指数

print("\n开始读取上证指数月度数据")

Mincomes_market = {}

fileName = os.getcwd() + "\\上证指数月度数据.xlsx"
df = pd.DataFrame(pd.read_excel(fileName))

for i in range(len(df)):

    if(i>0):  # 跳过2015-01

        month = df.Month[i]
        income = df.Mincome[i]
        Mincomes_market[month] = income

print("读取完成")

# print(Mincomes_market)
# print(Mincomes_market['2017-07'])
# 0.025247562434473726



# 计算每只股票的超额收益率 AR
# 股票超额收益率 = 股票月度收益率-市场月度收益率

print("\n开始计算每只股票的超额收益率")

Abnormal = {}   # 结构同 Mincomes

for i in range(len(stkcds)):

    s = stkcds[i]
    s = str(s)
    
    Abnormal_one_stock = {}
    Abnormal_one_stock.clear()

    for j in range(len(months)):

        if(j>0):

            m = months[j]
            if(m in Mincomes[s]):
                mincome1 = Mincomes[s][m]
                mincome_market1 = Mincomes_market[m]
                a = mincome1 - mincome_market1
                Abnormal_one_stock[m] = a

    Abnormal[s] = Abnormal_one_stock

print("计算完成")

# print(Abnormal['600000'])
# print(Abnormal['600010']['2017-07'])  
# 0.25785746039657653 







# 选取 5% 还是 10% 作为输家/赢家组合

# select = 0.05
# select = 0.1



# 形成期 

# formation_period = ['2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12']
# formation_period = ['2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
#                     '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06']
# formation_period = ['2015-02','2015-03','2015-04','2015-05','2015-06','2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07']
# formation_period = ['2015-02','2015-03','2015-04','2015-05','2015-06','2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
#                     '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06']
# formation_period = ['2015-02','2015-03','2015-04','2015-05','2015-06','2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12']
# formation_period = ['2015-07','2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12']
# formation_period = ['2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
#                     '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
# formation_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
# formation_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                     '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06']




# 检验期 

# test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
# test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                     '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12']
# test_period = ['2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06']
# test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12']
# test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
# test_period = ['2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
#                 '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
# test_period = ['2016-08','2016-09','2016-10','2016-11','2016-12',
#                      '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06']
# test_period = ['2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06']
# test_period = ['2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
# test_period = ['2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
#                 '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']




# 最优结果 1

formation_period = ['2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12']
test_period = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12',
                '2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
                '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06','2019-07','2019-08','2019-09','2019-10','2019-11','2019-12',
                '2020-01','2020-02','2020-03','2020-04','2020-05','2020-06']
select = 0.05


# 最优结果 2

# formation_period = ['2015-08','2015-09','2015-10','2015-11','2015-12',
#                     '2016-01','2016-02','2016-03','2016-04','2016-05','2016-06','2016-07','2016-08','2016-09','2016-10','2016-11','2016-12',
#                     '2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
# test_period = ['2018-01','2018-02','2018-03','2018-04','2018-05','2018-06','2018-07','2018-08','2018-09','2018-10','2018-11','2018-12',
#                 '2019-01','2019-02','2019-03','2019-04','2019-05','2019-06']
# select = 0.1




formation = len(formation_period)
test = len(test_period)

split = test_period[0]

print("\n形成期:" + formation_period[0] + " ~ " + formation_period[-1] + "    共"+str(formation)+"个月")
print("检验期:" +  test_period[0] + " ~ " +test_period[-1]+ "    共"+str(test)+"个月")

period = str(formation)+"_"+str(test)+"_"+split+"_"+str(select)



# 取形成期和检验期可用股票的交集

sf = stkcds_every_month[formation_period[0]] # 形成期可用股票
# print(len(sf))

for i in range(formation):

    mtemp = formation_period[i]
    stemp = stkcds_every_month[mtemp]
    sf = list(set(stemp).intersection(set(sf))) 
    # print(len(sf))

st = stkcds_every_month[test_period[0]] # 检验期可用股票
# print(len(st))

for i in range(test):

    mtemp = test_period[i]
    stemp = stkcds_every_month[mtemp]
    st = list(set(stemp).intersection(set(st))) 
    # print(len(st))

st = list(set(sf).intersection(set(st)))  # 取交集
sp = st

# print("\n")
# print(len(sp))



# 根据得到的股票 计算形成期的累计超额收益率 记录检验期的超额收益率

stocks_this_period = []    # 格式是一个包含若干dict的list

for i in range(len(sp)):

    one_stock = {}
    one_stock.clear()
    s = sp[i]

    one_stock['Stkcd'] = s
    one_stock['Abnormal_formation'] = 0   # 形成期的累计超额收益率

    for j in range(formation):

        mj = formation_period[j]
        one_stock['Abnormal_formation'] = one_stock['Abnormal_formation']+ Abnormal[s][mj] 

    one_stock['Abnormal_test'] = []   # 检验期每个月的超额收益率

    for j in range(test):

        mj = test_period[j]
        one_stock['Abnormal_test'].append(Abnormal[s][mj])


    stocks_this_period.append(one_stock)



# 按形成期的累计超额收益率从小到大排序

stocks_this_period = sorted(stocks_this_period, key=lambda k: k['Abnormal_formation']) 
l = len(stocks_this_period)


# 排名前10%(5%)为输家组合 后10%(5%)为赢家组合

# num = int(0.1*l)
# num = int(0.05*l)
num = int(select*l)
loser = stocks_this_period[0:num]
winner = stocks_this_period[l-num:l]



# 将一个投资组合S及相关指标写入EXCEL

def write_Excel(S,name,period):

    filepath = (os.getcwd()+"\\分组\\"+period) 
    isExists=os.path.exists(filepath)
    if not isExists:
        os.makedirs(filepath)
    excelpath = (os.getcwd()+"\\分组\\"+period+"\\"+name+".xls") 
    workbook = xlwt.Workbook(encoding='utf-8')
    sheet = workbook.add_sheet('Sheet1',cell_overwrite_ok=True)
    headlist=['Stkcd','Formation']
    row=0
    col=0
    for head in headlist:
        sheet.write(row,col,head)
        col=col+1
    n = len(S)
    for i in range(n):
        sheet.write(i+1,0,int(S[i]['Stkcd']))
        sheet.write(i+1,1,float(S[i]['Abnormal_formation']))
        # sheet.write(i+1,2,float(S[i]['Abnormal_test']))

    workbook.save(excelpath) 



# 将输家组合和赢家组合写入xls

write_Excel(loser,"loser",period)
write_Excel(winner,"winner",period)


# print(winner[0])
# print(Abnormal['600131']['2017-01'])  



# 计算一个股票在检验期每个月的累计超额收益率 并加入 one_stock 中

def CAR_stock(S):

    num = len(S)

    for i in range(num):

        one_stock = S[i]
        ar = one_stock['Abnormal_test']
        CAR_test = []
        CAR_test.clear()

        for j in range(test):

            length = j+1
            car = 0

            for t in range(length):
                car = car + ar[t]

            CAR_test.append(car)
        
        S[i]['CAR_test'] = CAR_test
    
    return S



# 计算一个投资组合在检验期每个月的累计超额收益率

def CAR_portfolio(S):

    num = len(S)
    CAR = []

    for i in range(test):

        car_point = 0

        for j in range(num):

            car_point = car_point + S[j]['CAR_test'][i]
        
        car_point = car_point/num
        CAR.append(car_point)
    
    return CAR




loser_num = len(loser)
loser = CAR_stock(loser)
loser_CAR = CAR_portfolio(loser)

winner_num = len(winner)
winner = CAR_stock(winner)
winner_CAR = CAR_portfolio(winner)



# for i in range(test):

#     print(str(loser_CAR[i])+"  "+str(winner_CAR[i]))



result = {'loser':loser_CAR,'winner':winner_CAR}
result = pd.DataFrame(result)



path = (os.getcwd()+"\\结果\\"+period+".xls")
result.to_excel(path)




"""
Created on Wed Dec 16 01:17:48 2020

@author: gy
"""

# 画图

import matplotlib.pyplot as plt


plt.plot(test_period, loser_CAR, color='blue')
plt.plot(test_period, winner_CAR, color='red')
plt.yticks(fontsize=8)
plt.xticks(rotation=90,fontsize=5)

t = "Formation: " + formation_period[0] + " ~ " + formation_period[-1] + "   " + str(formation)+" months" + "\nTest: " +  test_period[0] + " ~ " +test_period[-1]+ "   "+str(test)+" months"+"\nSplit: "+split + "    "+str(int(select*100))+"%"
plt.title(t,fontsize=10)

path = (os.getcwd()+"\\结果\\"+period+".png")
plt.savefig(path)

plt.show()