半成品3，谢谢。

# -*- coding: utf-8 -*-
"""
Created on Tue Apr 28 14:15:59 2020

@author: zoutianshu
"""

import pandas as pd
from WindPy import w
from zts.adj_nav import add_fund_exchange,add_stock_exchange
import datetime as dt
import numpy as np
w.start()


# wind中获取报告期持仓细节
def get_fund_holding(fund,date):
    try:
        data = w.wset("allfundhelddetail","rptdate="+date+";windcode="+fund+".OF;field=stock_code,proportiontototalstockinvestments,proportiontonetvalue")
        df_oc = pd.DataFrame(data.Data[1:],columns = data.Data[0],index = ['占股票市值','占净值']).T
    except:
        df_oc = pd.DataFrame(index = ['占股票市值','占净值']).T
    return df_oc
    
# 仓位计算
def position(df_oc_temp):
    oc = df_oc_temp.iloc[0]['占净值']/df_oc_temp.iloc[0]['占股票市值'] * 100
    return oc

def get_exposure(date):
    data = w.tdaysoffset(0, date[0:4]+"-"+date[4:6]+"-"+date[6:8], "")
    date = data.Data[0][0].strftime("%Y%m%d")
    tdy = pd.read_csv("\\\\10.180.90.60/f/daily_data/barra_model/Exposure/Exposure_"+date+".csv",index_col = 0)
    tdy = tdy.iloc[1:,:10]
    tdy = tdy.apply(pd.to_numeric, errors='ignore',downcast = 'float' )
    tdy.index = [x[0:6] for x in tdy.index]
    tdy.columns = [x[6:] for x in tdy.columns]
    return tdy


df = pd.read_excel('宽基收益率.xlsx',sheet_name = 'Sheet1')
df['成立日期'] = pd.to_datetime(df['成立日期'])
df2 = pd.read_excel('宽基收益率.xlsx',sheet_name = 'Sheet2')
df2.set_index('证券代码',inplace = True)
industry = df2[['所属申万行业名称[行业级别] 一级行业']].drop_duplicates('所属申万行业名称[行业级别] 一级行业')['所属申万行业名称[行业级别] 一级行业']
date_list = list(df.columns[4:])

fund_list_str,fund_list = add_fund_exchange(df['代码'])
df['代码'] = fund_list



###
for idate in date_list:
    date = idate.strftime("%Y%m%d")    
    
    ###
    df_distill = df[df['成立日期']<idate]
    distill_num = df_distill.shape[0]
    df_distill = df_distill.sort_values(idate,ascending = False)
    df_top20 = df_distill.iloc[:int(distill_num/5)]
    
    df_out = pd.DataFrame(index = df_top20['代码'],columns = industry.values)
    df_out ['盲区'] = np.nan;    df_out ['仓位'] = np.nan;
    
    df_exposure = get_exposure(date)
    a,b = add_stock_exchange(list(df_exposure.index))
    df_exposure.index = a
    df_out['贝塔'] = np.nan;    df_out['动量'] = np.nan;    df_out['市值'] = np.nan;    df_out['盈利'] = np.nan;    df_out['波动'] = np.nan;
    df_out['增长'] = np.nan;    df_out['价值'] = np.nan;    df_out['杠杆'] = np.nan;    df_out['流动性'] = np.nan;    df_out['非线性市值'] = np.nan;
    for ifund in df_top20['代码']:
### from file
        try:
            df_oc_temp = pd.read_csv('data\\'+date+'_'+ifund+'.csv',index_col=0)
            print(1)    
### download
        except:
            df_oc_temp = get_fund_holding(ifund,date)
            df_oc_temp.to_csv('data\\'+date+'_'+ifund+'.csv')
            print(0)
###
#仓位
###     
        try:
            df_out['仓位'].loc[ifund] = position(df_oc_temp)
###
#行业
###
            df_oc_temp['行业'] = df2[['所属申万行业名称[行业级别] 一级行业']].loc[df_oc_temp.index]
            df_oc_group = df_oc_temp.groupby('行业').sum()
            for ix in df_oc_group.index:
                df_out[ix].loc[ifund] = df_oc_group['占股票市值'].loc[ix]
            df_out ['盲区'].loc[ifund] = 100-df_oc_temp['占股票市值'].sum()
###
#风格
###
            result = pd.concat([df_oc_temp, df_exposure.loc[df_oc_temp.index]], axis=1)
            df_out ['贝塔'].loc[ifund] = (result['BETA']*result['占净值']).sum()/100
            df_out ['动量'].loc[ifund] = (result['MOMENTUM']*result['占净值']).sum()/100
            df_out ['市值'].loc[ifund] = (result['SIZE']*result['占净值']).sum()/100
            df_out ['盈利'].loc[ifund] = (result['EARNYILD']*result['占净值']).sum()/100
            df_out ['波动'].loc[ifund] = (result['RESVOL']*result['占净值']).sum()/100
            df_out ['增长'].loc[ifund] = (result['GROWTH']*result['占净值']).sum()/100
            df_out ['价值'].loc[ifund] = (result['BTOP']*result['占净值']).sum()/100
            df_out ['杠杆'].loc[ifund] = (result['LEVERAGE']*result['占净值']).sum()/100
            df_out ['流动性'].loc[ifund] = (result['LIQUIDTY']*result['占净值']).sum()/100
            df_out ['非线性市值'].loc[ifund] = (result['SIZENL']*result['占净值']).sum()/100
        except:
            pass
    
    df_out = df_out.fillna(0)
    df_out.to_excel(date+'.xlsx')