import csv
import os
import time

import numpy as np
import pandas as pd
root='/Users/yumingwu/Downloads/Stk_Day_QFQ/'
root1='/Users/yumingwu/Desktop/数据/集合竞价/'
path = os.listdir(root1)

path.sort()
print(path)
guben = pd.read_excel('../全部A股.xlsx')
with open('./'+'集合竞价数据昨日涨幅'+'.csv','w',encoding='gbk',newline='') as csfile:
    writer = csv.writer(csfile)
    writer.writerow(['代码', '日期', '昨日成交量', '昨日成交额', '昨日换手率', '10日涨幅', '流通市值','开盘价','明日开盘价','涨幅','盈利','价格','totalBuyVol'	,'totalBuyAmount','cancelBuyVol','cancelBuyAmount','preBuyVol','cirVol','rate','buyRate','cancelRate','昨日涨幅'])
    for spath in path:

        if spath.find('xlsx')==-1:
            continue
        print(spath)
        data = pd.read_excel(root1 + spath)
        day = pd.to_datetime(spath[0:8])
        columns = data.columns

        if 'preBuyVol' in columns:

            for index, item in data.iterrows():

                # preBuyVol=item['preBuyVol']
                print(item['preBuyVol'])
                code = item['code']
                if (code.find('pre ') == -1):
                    continue
                code = code[len(code) - 6:len(code)]
                gubencode = 0
                if (int(code) > 600000):
                    gubencode = code + ".SH"
                    code = 'SH' + code + '.csv'


                else:
                    gubencode = code + ".SZ"
                    code = 'SZ' + code + '.csv'

                gubenitem = guben[guben['证券代码'] == gubencode]
                if len(gubenitem) == 0:
                    continue
                gubenitem=gubenitem.iloc[0]
                codeData = pd.read_csv(root + code, encoding='gbk')
                codeData['时间']=pd.to_datetime(codeData['时间'])

                codeDatap =codeData[codeData['时间']<=day]
                code0 = codeDatap.iloc[-1]
                codeDatap = codeData[codeData['时间'] < day]
                if len(codeDatap) < 20:
                    continue
                codeDatap=codeDatap.iloc[len(codeDatap)-11:len(codeDatap)]
                minprice= codeDatap['开盘价'].min()
                codeDatan =codeData[codeData['时间']>day]
                if(len(codeDatan)==0):
                    continue

                code3 = codeDatap.iloc[-1]
                code4 = codeDatap.iloc[-2]
                code1 = codeDatan.iloc[0]

                writer.writerow(
                    [code0['代码'],
                     code0['时间'],
                     code3['成交量(股)'],
                     code3['成交额(元)'],
                     code3['成交量(股)']/gubenitem['流通A股'],
                     code3['收盘价']/minprice,
                     code3['收盘价']*gubenitem['流通A股']/100000000,
                     code0['开盘价'],
                     code1['开盘价'],
                     code1['开盘价']/code0['开盘价']-1,
                     50000*(code1['开盘价'] / code0['开盘价'] - 1-0.0014),
                     item['price'],
                     item['totalBuyVol'],
                     item['totalBuyAmount'],
                     item['cancelBuyVol'],
                     item['cancelBuyAmount'],
                     item['preBuyVol'],
                     item['cirVol'],
                     item['rate'],
                     item['totalBuyVol']/item['cirVol'],
                     item['cancelBuyVol'] / item['cirVol'],
                     code3['收盘价'] / code4['收盘价']-1
                     ])



        else:
          pass


