# 这应当是本Notebook唯一可编辑的Cell
# 运行前确保Wind处于运行状态，否则WindPy包可能无法使用
import numpy as np
import pandas as pd
import itertools as it
import datetime as dt
import os, shelve, zipfile, math, shutil, glob, win32com.client, ftplib
import WindPy as wp
# wp.w.stop()
wp.w.start()

tdate = '20171211'
ldate = wp.w.tdaysoffset(-1, tdate, "").Times[0].strftime('%Y%m%d')  # 前一交易日
# b4ldate = wp.w.tdaysoffset(-2, tdate, "").Times[0].strftime('%Y%m%d')  # 前前交易日
ndate = wp.w.tdaysoffset(+1, tdate, "").Times[0].strftime('%Y%m%d')  # 后一交易日

rootwd = r'E:\BDSync\K3\T11-基金运行'  # change E to D when working on OA-JINGYF(SWSMU Laptop)!
# rootwd = r'D:\Justyre\BDSync\K3\T11-基金运行'  # root working directory when on JUSTYRE-THINKER
finfo = pd.DataFrame({'沪深300价值':['310398', '000919', 0.90, 0.95, 0.80, '90000000'], \
                      '电子':['163116', '399811', 0.85, 0.95, 1.0, '0022_000'], \
                      '传媒':['163117', '399810', 0.85, 0.95, 1.0, '0023_000'], \
                      '医药生物':['163118', '000808', 0.85, 0.95, 1.0, '0024_000'], \
                      '新兴健康':['163119', '930629', 0.80, 0.95, 1.0, '26']}, 
                     index = ['基金代码', '指数代码', '最低仓位', '最高仓位', '关联股权重系数', '主动组合代码'],
                    columns = ['沪深300价值', '电子', '传媒', '医药生物', '新兴健康'])
wd = os.path.join(rootwd, 'data')

# thresholds
wtcut = 1  # 权重门槛（100制），初值1%

# 人工确认三个交易日是否正确
print('T-1=', ldate, 'T=', tdate, 'T+1=', ndate)

q = []
for idxid in finfo.values.tolist()[1]:  # 对各指数代码循环

    # Step 1: 取到weightnextday.xls
#     # tesbeg:待IT完成迁移后启用tesbeg-tesfin. Justyre 20171115
#     # 从公司FTP(\\172.16.3.151, 目前映射为本地Z盘)收集weightnextday的zip并解压其中的xls至wd
#     srcdir = os.path.join(r'Z:\Yewu\中证指数', tdate, idxid, r'weight_for_next_trading_day')
#     fname = idxid+'weightnextday'+ldate+'.zip'
#     srcf = os.path.join(srcdir, fname)
#     shutil.copy2(srcf, wd)  # copy2() attempts to preserve all file metadata
#     with zipfile.ZipFile(os.path.join(wd, fname), 'r') as zf:
#         zf.extract(idxid+'weightnextday'+ldate+'.xls', path=wd)
#         print(fund+'的指数'+idxid+'weightnextday解压缩完成，无错误')
#     # tesfin
    
    # Step 2: 读入weightnextday.xls
    qzb = pd.read_excel(os.path.join(wd, idxid+'weightnextday'+ldate+'.xls'), \
                        usecols=[1,4,5,13,15], names=['idxid','gpdm','gpmc','kpj','calcap'])  # 权重表weightnextday
    qzb.idxid = [str(a).rjust(6,'0') for a in qzb.idxid]
    qzb.gpdm = [str(a).rjust(6,'0') for a in qzb.gpdm]
    qzb['zsqz'] = qzb.calcap / sum(qzb.calcap) * 100  # 基于计算用市值列计算的权重（100制），比直接取权重列更精确
      # 中证此表内含的勾稽关系：计算用股本=round(总股本*归档后自由流通比例,0)，计算用市值=round(计算用股本*调整后开盘参考价*权重因子,0)
    qzb = qzb.drop('calcap', axis=1)
    
    # Step 3: 保留权重>=wtcut且ldate停牌者
    q1 = qzb[qzb.zsqz >= wtcut].copy()
    windgpdm=[a + '.SH' if a[0]=='6' else a + '.SZ' for a in q1.gpdm]
    a = 'tradeDate=' + ldate
    q1['jyzt'] = wp.w.wss(windgpdm, "trade_status", a).Data[0]
    q2 = q1[[True if a[0]=='停' else False for a in q1.jyzt]]
    q.append(q2)

q = pd.concat(q, axis=0)
q.index = q.idxid

f1 = finfo.transpose()
f1['基金简称'] = f1.index
f1.index = f1.指数代码

q['基金简称'] = f1.loc[q.index,'基金简称']
q = q.reset_index(drop=True)
q  # 得到该股票序列后，Wind财经新闻NEWS搜索各股票名，栏目选“基金”，观察有无估值调整的信息

q.to_excel(os.path.join(wd,tdate+'危股榜.xlsx'))

?str.replace()