python中完整爬取股票财务信息和公司基本信息含xpath

完整代码:

完整代码花了差不多半个月时间写完,数据爬取横跨雪球网、东方财富网、同花顺,提取利润表、资产负债表、主要指标、分红、股东变化信息等,同时获取企业员工、管理人信息及主营业务、企业简介等;

过程还是比较艰辛,各种网站奇葩涉及,根本爬不到代码,还弯弯绕,最终还是突破各种难题,解决了各种bug,有代码bug调试了好几个晚上才发现bug,中间流程和数据包反复调换和代码重写,最终完成了以下代码,免费供大家学习参考!感谢各位关注!

import xlwings as xw
import requests
from bs4 import BeautifulSoup
from datetime import datetime
import json
import xlwt
import xlwings as xw
from selenium import webdriver
import time
import pandas as pd
import csv
import re
from selenium.webdriver import Chrome, ChromeOptions, ActionChains

# item_list=[]
from selenium.webdriver.common.keys import Keys

df = pd.DataFrame()

def data_a(html, numcode):  # 获取基础信息1
    # with open('rrBand.html', 'r', encoding='utf-8') as f:
    # html = BeautifulSoup(f, 'lxml')
    # html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})
    # print(html.list)
    # numcode=''
    df = pd.DataFrame()
    # print(numcode,'一')
    # print(html)
    for i, item in enumerate(html):
        # print(item)
        # print(html.list_a)
        try:
            bandNanme = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:].strip(')')
            df['序号'] = '',
            # df['股票'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:bandNanme.find('(')].strip(
            #             ')'),
            # df['代码'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('')[5:].strip(')').replace(':',''),
            # print(df[['股票','代码']])
            df['股价'] = item.find_all('div', attrs={'class', 'stock-current'})[0].text.strip('¥'),
            # print(df['股价'])
            html.list_a = item.find_all('table', attrs={'class', 'quote-info'})
            for i, item_a in enumerate(html.list_a):
                # print(item_a.find_all('span'))
                # for i in range(25):
                #     print(item_a.find_all('span')[i].text,i,sep=',')
                df['股东持股'] = '',
                df['营市比'] = '',
                # print(item_a.find_all('span')[i].text,i,sep=',')#_a.find_all('span')[18].text.strip('亿'),i,sep=','),
                # print(item_a.find_all('span')[19].text.strip('亿'))
                # df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                # df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                # df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                # print( df['总市值(亿)'])
                if numcode == '6':
                    print(numcode, '上海主板')
                    df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                elif numcode == '3':
                    print(numcode, '深圳创业板')
                    df['总市值(亿)'] = item_a.find_all('span')[11].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[16].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[21].text.strip(''),
                elif numcode == '0':
                    print(numcode, '深圳主板')
                    df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                else:
                    print('错误')
                # print(df['PB市净率'])
                print(df)
                print(str(i), "第一模块写入正常")
        except:
            print(str(i), "第一模块写入异常")
            #     # continue
    return df

# df.to_csv('fundWebd.csv', index=None, encoding='utf-8-sig',sep=',')#mode='a', header=None,index=None,
# print(df[['股价','总市值','EPS每股收益']])

def data_b(html):  # 获取基础信息2主要指标
    # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZYCWZB'
    # print('12')
    #     with open('Band.html', 'r', encoding='utf-8') as f:
    #         html = BeautifulSoup(f, 'lxml')
    #         html.list_b = html.find_all('tbody')
    df = pd.DataFrame()
    bandIncome = []
    for i, item in enumerate(html):
        # print(item)
        html.list_b_a = item.find_all('tr')
        for i, item in enumerate(html.list_b_a):
            html.list_b_a_a = item.find_all('td')
            # print(item.find_all('td'))
            for i, item in enumerate(html.list_b_a_a):
                try:
                    html.list = item.find_all('p')[0].contents[0]
                    bandIncome.append(html.list)
                    # print(bandIncome,i,sep=',')
                    # for i, item in enumerate(html.list):
                    # print(item)
                    html.list_b = item.find_all('table', attrs={'class', 'quote-info'})
                    # print(html.list_a
                    # bandNanme = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:].strip(')')
                    # df['17年利润(亿)'] = '',
                    # df['20年利润(亿)'] = '',
                    # print(str(i), "第二模块写入正常")
                    print("第二模块写入正常")
                except:
                    # continue
                    print(str(i), "第二模块写入异常")

    df['营业额'] = bandIncome[0].strip('亿'),
    df['17年营业额'] = bandIncome[3].strip('亿'),  # 2017年营业额
    df['EPS每股收益'] = bandIncome[30].strip(''),  # item_a.find_all('span')[16].text.strip(''),
    df['负债率'] = bandIncome[85].strip(''),
    df['经营现金流'] = bandIncome[50].strip(''),
    df['17年利润(亿)'] = bandIncome[13].strip('亿'),
    df['20年利润(亿)'] = bandIncome[10].strip('亿'),
    df['未分配利润'] = bandIncome[45].strip(''),
    df['公积金'] = bandIncome[40].strip(''),
    df['毛利率'] = bandIncome[75].strip(''),
    df['净利率'] = bandIncome[80].strip(''),
    df['ROA总报酬率'] = bandIncome[65].strip(''),
    df['ROE净收益率'] = bandIncome[55].strip(''),
    df['账款周期'] = bandIncome[125].strip(''),
    df['存货周转'] = bandIncome[120].strip(''),
    df['总资产周转率'] = bandIncome[145].strip(''),

    print(df)
    # for i in range(len(bandIncome)):
    #     print(bandIncome[i],i,sep=',')
    return df

def data_b_a(html):  # 获取主要指标里的季度收入
    df = pd.DataFrame()
    bandIncome = []
    for i, item in enumerate(html):
        html.list_b_a = item.find_all('td')
        # print(item.find_all('td'))
        cut_a = item.find_all('td')[7].text.strip('').find('亿')
        cut_b = item.find_all('td')[11].text.strip('').find('亿')
        df['21年季度'] = item.find_all('td')[7].text.strip('')[0:cut_a],
        df['17年季度'] = item.find_all('td')[11].text.strip('')[0:cut_b],
        print(df)
        # print('data_b_a')
        # for i in range(100):
        #     print(item.find_all('td')[i].text,i,sep=',')
        return df

def data_c(html, html1):
    # with open('Band.html','r',encoding='utf-8') as f:
    #     # url='https://xueqiu.com/snowman/S/SH601991/detail#/FHPS'
    #     html=BeautifulSoup(f,'lxml')
    #     html.list=html.find_all('tbody')
    df = pd.DataFrame()
    for i, item in enumerate(html1):
        # print(item.text)
        bandNanme = item.text
        # print(item.text.strip('')[bandNanme.find('('):].strip(')'))
        df['股票'] = item.text.strip('')[0:bandNanme.find('(')].strip(')'),
        df['代码'] = item.text.strip('')[bandNanme.find('(') + 1:].strip(')'),
        # print(df)
        # print(df['股票'].valuse, df['代码'].values)
    # df['股票'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:bandNanme.find('(')].strip(
    #             ')'),
    # df['代码'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('')[5:].strip(')').replace(':',''),
    # print(df[['股票','代码']])
    for i, item in enumerate(html):
        try:
            cut_a = item.find_all('td')[1].text.strip().find('派')
            cut_b = item.find_all('td')[1].text.strip().find('元')
            # print(cut_a,cut_b)
            # print(item)
            # print(item.find_all('td')[1].text.strip()[cut_a+1:cut_b])
            df['分红率'] = '',
            df['分红'] = item.find_all('td')[1].text.strip()[cut_a + 1:cut_b],
            print(df)
            print(str(i), "第三模块写入正常")
        except:
            print(str(i), "第三模块写入异常")
    return df

def data_d(html):
    # with open('Band.html','r',encoding='utf-8') as f:
    #     # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZCFZB'
    #     html=BeautifulSoup(f,'lxml')
    #     html.list=html.find_all('tbody')
    df = pd.DataFrame()
    # print(html)
    for i, item in enumerate(html):
        # try:
        cut_a = item.find_all('td')[7].text.strip('').find('亿')
        cut_b = item.find_all('td')[61].text.strip('').find('亿')
        # print(cut_a)
        # print(item.find_all('td')[7].text.strip('')[0: cut_a-1])
        # print(item.find_all('td')[61].text.strip('')[0: cut_a-1])
        df['货币资金'] = item.find_all('td')[7].text.strip('')[0: cut_a],
        df['存货'] = item.find_all('td')[61].text.strip('')[0: cut_b],
        # for i in range(300):
        #     print(item.find_all('p')[i],i,sep=',')
        print(df)
        print(str(i), "第四模块写入正常")
        # except:
        #     print(str(i), "第四模块写入正常")
    return df

def data_e(html):
    # with open('Band.html','r',encoding='utf-8') as f:
    #             # url='https://xueqiu.com/snowman/S/SH601600/detail#/GSLRB'
    #             html=BeautifulSoup(f,'lxml')
    #             html.list=html.find_all('tbody')
    df = pd.DataFrame()
    # print(html)
    for i, item in enumerate(html):
        try:
            cut_a = item.find_all('td')[43].text.strip('').find('+')
            cut_b = item.find_all('td')[55].text.strip('').find('+')
            cut_c = item.find_all('td')[61].text.strip('').find('+')
            df['研发费用'] = item.find_all('td')[43].text.strip(''),  # [0:cut_a],
            df['利息费用'] = item.find_all('td')[55].text.strip(''),  # [0:cut_b],
            df['利息收入'] = item.find_all('td')[61].text.strip(''),  # [0:cut_c],
            # print(item.find_all('td')[43].text.strip('')[0:cut_a-1])
            # print(df['研发费用'].values, df['利息费用'].values, df['利息收入'].values ,sep=',')
            print(df)

            # for i in range(100):
            #     print(item.find_all('td')[i].text,i,sep=',')
            print(str(i), "第五模块写入正常")
        except:
            print(str(i), "第五模块写入异常")
    return df

def data_f(html,html_a):  # 获取股东数据

    # with open('Band.html','r',encoding='utf-8') as f:
    # url='http://emweb.eastmoney.com/PC_HSF10/ShareholderResearch/Index?type=web&code=sh601601'
    # html=BeautifulSoup(f,'lxml')
    # html.list_f = html.find_all('table', attrs={'id': 'Table0'})
    # # print(numcode)
    # html.list_f_a = html.find_all('div', attrs={'style': 'padding:10px'})
    df = pd.DataFrame()
    # print(html)
    shareheld = []
    for i, item in enumerate(html):
        try:
            # html.list = item.find_all('tr')[2].contents[3]  # 股东人数
            # print(html.list)
            # html.list_a = item.find_all('tr')[10].contents[3]  # 十大股东合计占比
            # print(html.list_a)
            # for i, item in enumerate(html.list):
            #     print(item[3],i,sep=',')
            # for i, item in enumerate(html.list_a):
            df['股东人数'] = item.find_all('tr')[2].contents[3].text.strip('万').strip(','),
            df['十大流通股东持股占比'] = item.find_all('tr')[10].contents[3].text.strip('万').strip(',') + '%',
            # print(item.find_all('td',attrs={'class':'tips-dataL'}))
            # print(item.find_all('td',attrs={'class':'tips-dataL'})[0])
            # print(item.find_all('td', attrs={'class': 'tips-dataL'})[0])
            # html.list = item.find_all('td', attrs={'class': 'tips-dataL'})
            # for i, item in enumerate(html.list):
            #     # print(item.text.strip('万'),i,sep=',')
            #     # print(len(html.list))
            #     shareheld.append(item.text.strip('万').strip(','))
            # if i<=62:
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[56] + '%',
            # elif i<=71:
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[64] + '%',
            # else :
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[80] + '%',
            # print( item.find_all('tr'))
            print(str(i), '股东数据写入正常')
        except:
            print(str(i), '股东数据写入异常')
    #获取机构占比和机构明细
    org = []#机构数量
    org1 = []  # 机构数量
    for i, item in enumerate(html_a):
        # for i,item_a in enumerate(item.find_all('tr')):
            # print(item_a,i,sep=',')
            # for i, item_a_a in enumerate(item.find_all('tr')):
            #     print(item_a_a)
        # print(item.find_all('tr')[8].contents[7].text)
        # print(item.find_all('tr')[1].contents[3].text)
        df['机构占比']=item.find_all('tr')[8].contents[7].text
        org=(item.find_all('tr')[1].contents[3].text,
                       item.find_all('tr')[2].contents[3].text, item.find_all('tr')[3].contents[3].text,
                        item.find_all('tr')[4].contents[3].text,item.find_all('tr')[5].contents[3].text,
                        item.find_all('tr')[6].contents[3].text, item.find_all('tr')[7].contents[3].text,
                        item.find_all('tr')[8].contents[3].text
                       )
        df['机构数量'] =' 基金:'+org[0]+'家;'+' QFII:'+org[1]+'家;'+' 社保:'+org[2]+'家;'+' 券商:'+org[3]+'家;'+ \
                    ' 保险:' + org[4] + '家;'+' 信托:'+org[5]+'家;'+' 其他机构:'+org[6]+'家;'+' 合计:'+org[7]+'家;'

        print(df, '股东')
    return df

def data_g(html,emNumber,cashrate):#获取公司基础数据
    # with open('rrBand.html', 'w', encoding='utf-8') as f:
    #     f.write(source)
    # with open('rrBand.html', 'r', encoding='utf-8') as f:
    # html = BeautifulSoup(f, 'lxml')
    # print(html.list)
    df = pd.DataFrame()
    for i, item in enumerate(html):
        try:
            # print(item.find_all("tr"), i, sep=',')
            # for i, item_a in enumerate(item.find_all('tr')):
            #     print(item_a, i, sep=',')
            # for i in range(len(item.find_all('tr'))):
            #     print(item.find_all('tr')[18].contents[i], i, sep=',')
            # 公司名称:所属地域、所属行业、曾用名:
            basicDate1 = (
            item.find_all('tr')[0].contents[3].text.strip('\n'), item.find_all('tr')[0].contents[5].text.strip('\n'),
            item.find_all('tr')[1].contents[3].text.strip('\n'),
            item.find_all('tr')[2].contents[1].text.strip('\n')
            )
            # 主营业务、产品名称、实际控制人、董事长、办公地址、公司简介
            basicDate2 = (item.find_all('tr')[3].contents[1].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.find_all('tr')[4].contents[1].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.find_all('tr')[6].contents[1].text.replace('\n', '').replace('\t', '').replace('\r',
                                                                                                              '').replace(
                              ' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'class': 'name'})[0].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'colspan': '3'})[9].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'colspan': '3'})[10].text.replace('\n', '').replace('\t', '').replace(' ', '')
                          )

            df['板块'] = basicDate1[2].strip('').split(':')[1] ,
            df['实控人'] =item.findAll('div',attrs={'class':'tipbox_wrap mr10'})[2].findNext('span').text.replace('\n', '').replace('\t', '').replace(' ', '')#basicDate2[2],
            df['基础数据1'] = basicDate1[0] + ';' + basicDate1[1].strip('') + ';'  + \
                          basicDate1[3].strip('') + '\n',
            df['基础数据2'] = basicDate2[0] + ';' + '\n' + basicDate2[1].strip('') + ';' + '\n' + basicDate2[3].strip('') + ';' + '\n' + basicDate2[4].strip('') + ';' + '\n' + \
                          basicDate2[5].strip('') + ';',
            # 员工人数
            # data_a = item.find_all('tr')[18].contents[5].text.replace('\n', '').replace('\t', '').replace('\r','').replace(' ', '')
            df['员工人数'] =emNumber,# data_a.strip('').split(':')[1],
            df['现金收入比'] =cashrate,

            df.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
            print(df)
            print(str(i), '基础数据写入正常')
        except:
            print(str(i), '基础数据写入异常')
    return df

# 写入csv中
if __name__ == "__main__":
    # 创建一个workbook

    app = xw.App(visible=False, add_book=False)
    wb = app.books.open('fundWebd.xlsx')
    # 创建一个worksheet
    sh = wb.sheets['worksheet']
    rng = [i for i in sh.range("c:c").value if i != None]  # 单元格内容
    j = sh.range('a1').expand('table').rows.count  # 序号
    app.display_alerts = False
    app.screen_updating = False
    # rng = sh.range('a1').expand('table')
    # nrows = rng.rows.count
    # a = sh.range(f'a1:a{nrows}').value
    # a = [ i for i in sht.range(a:a).value if i != None]

    # 打开网页
    opt = ChromeOptions()  # 创建Chrome参数对象
    opt.headless = False  # True#              # 把Chrome设置成可视化无界面模式,
    driver = Chrome(options=opt)
    # driver = webdriver.Chrome()
    df_a = []
    df_b = []
    df_b_a = []
    df_c = []
    df_d = []
    df_e = []
    df_f = []

    for i in range(len(rng) - 1):
        print(str(i), rng[i + 1], '第' + str(i + 1) + '只股票开始写入')  # rng[i+1]
        numcode = rng[i + 1].strip('')[2:3]
        time1 = time.time()  # 计算耗时
        try:

            bandcode = rng[i + 1]  # 'SH601600'
            bandcode_a = rng[i + 1][2:]  # 'SH601600'
            xueqiu_url = 'https://xueqiu.com/S/' + bandcode  # 雪球网基础数据'https://c.runoob.com/'#很好的ide工具
            xueqiu_url_a = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/ZYCWZB'  # 主要指标
            xueqiu_url_c = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/FHPS'  # 分红
            xueqiu_url_d = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/ZCFZB'  # 存货
            xueqiu_url_e = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/GSLRB'  # 研发、利息收入
            xueqiu_url_f = 'http://emweb.eastmoney.com/PC_HSF10/ShareholderResearch/Index?type=web&code=' + bandcode  # 主要指标
            tonghuashun_url = 'http://basic.10jqka.com.cn/' + bandcode_a + '/company.html#stockpage'  #
            if numcode == '6':
                eastwealth_url = 'https://emh5.eastmoney.com/html/index.html?fc=' + bandcode_a + '01&fn=&color=w#/cwfx' #
            else:
                eastwealth_url = 'https://emh5.eastmoney.com/html/index.html?fc=' + bandcode_a + '02&fn=&color=w#/cwfx'  #
            # DFCF_url='http://emweb.eastmoney.com/PC_HSF10/OperationsRequired/Index?type=web&code=SH601600'
            # k=0.5+0.3#网页间隔实际控制

            # 基础数据1加载
            driver.get(xueqiu_url)  # 加载网址
            # time.sleep(1)  # 休眠1秒
            source = driver.page_source  # 获取网页内容
            html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
            # time.sleep(1)  # 休眠1秒
            html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})
            numcode = rng[i + 1].strip('')[2:3]
            # print(numcode)
            df_a = data_a(html.list, numcode)  # 执行语句块
            # df_a.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            time.sleep(0.3)  # 休眠1秒

            # 基础数据2加载主要指教
            driver.back()  # 加载网址
            time.sleep(0.3)
            # driver.get(xueqiu_url)  # 加载网址
            # time.sleep(0.5)  # 休眠1秒
            # ActionChains(driver).key_down(Keys.CONTROL).send_keys("w").key_up(Keys.CONTROL).perform()#关闭标签
            # driver.find_element_by_xpath(".//div[contains(@class,'stock-links')]/ul[6]/li[2]/a").click()#加载主要指标
            driver.get(xueqiu_url_a)  # 加载网址
            time.sleep(3)  # 休眠1秒
            # driver.find_elements_by_class_name('btn active').click()
            # driver.find_element_by_xpath(".//*[@id='header']/div[1]/div/form/input[2]").click()
            source = driver.page_source  # 获取网页内容
            html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
            # print(html)
            # time.sleep(1)  # 休眠1秒
            html.list_b_a = html.find_all('tbody')
            df_b_a = data_b_a(html.list_b_a)  # 执行语句块
            # print(html.list_b_a)
            # 执行后点击任务
            # time.sleep(1)
            driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
            # print(driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").text)  # /span[contains(@class,'btn')]
            time.sleep(1.5)  # 休眠4秒
            source = driver.page_source  # 获取网页内容
            html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
            # print(html)
            # time.sleep(1)  # 休眠1秒
            html.list_b = html.find_all('tbody')
            df_b = data_b(html.list_b)  # 执行语句块
            time.sleep(0.2)

            # 基础数据三加载分红
            driver.back()  # 加载网址
            time.sleep(0.5)
            driver.get(xueqiu_url_c)  # 加载网址
            time.sleep(2)
            source = driver.page_source  # 获取网页内容
            html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
            # print(html)
            # time.sleep(1)  # 休眠1秒
            html.list_c = html.find_all('tbody')
            html.list_c2 = html.find_all('div', attrs={'stock-info-name'})
            # print(html.list_c2)
            df_c = data_c(html.list_c, html.list_c2)  # 执行语句块
            # print(html)
            time.sleep(0.2)

            # 基础数据四加载资产负债表,,存货、货币资金
            driver.get(xueqiu_url)  # 加载网址
            time.sleep(0.5)
            driver.get(xueqiu_url_d)  # 加载网址
            time.sleep(2.5)
            driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
            time.sleep(1.3)
            source = driver.page_source  # 获取网页内容
            html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
            # print(html)
            # time.sleep(1)  # 休眠1秒
            html.list_d = html.find_all('tbody')
            df_d = data_d(html.list_d)  # 执行语句块
            # df_d.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            time.sleep(0.2)

            # 基础数据五加载利润表
            driver.get(xueqiu_url_f)  # 加载网址
            time.sleep(0.5)
            driver.get(xueqiu_url_e)  # xueqiu_url_e
            time.sleep(2.5)
            driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
            time.sleep(1.3)
            source = driver.page_source  # 获取网页内容
            html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
            # print(html)
            # time.sleep(1)  # 休眠1秒
            html.list_e = html.find_all('tbody')
            df_e = data_e(html.list_e)  # 执行语句块
            # df_e.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            # time.sleep(1)
            # with open('rrBand.html','w',encoding='utf-8') as f:#写入网页
            # f.write(source)

            # 股东数据加载第六模块
            driver.get(xueqiu_url_f)  # 加载网址
            time.sleep(0.5)  # 休眠1秒
            source = driver.page_source  # 获取网页内容
            html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
            # time.sleep(1)  # 休眠1秒
            # html.list_f = html.find_all('tbody')
            html.list_f = html.find_all('table',attrs={'id':'Table0'})
            # print(numcode)
            html.list_f_a = html.find_all('div', attrs={'style': 'padding:10px'})
            df_f = data_f(html.list_f,html.list_f_a)  #执行语句块
            # df_f.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False) # ,orient="values")
            # time.sleep(k)  # 休眠1秒

            # 公司基础数据加载第七模块
            driver.get(eastwealth_url)  # 加载现金收入比
            time.sleep(3)  # 休眠1秒
            cashrate_1 = driver.find_element_by_xpath(".//div[contains(@class,'item-data')]/p[28]").text
            cashrate_2 = driver.find_element_by_xpath(".//div[contains(@class,'item-data')]/p[29]").text
            cashrate=cashrate_1+'/'+cashrate_2#获取现金收入比
            driver.get(tonghuashun_url)  # 加载网址
            source = driver.page_source  # 获取网页内容
            time.sleep(0.3)  # 休眠1秒
            # 员工人数
            emNumber = driver.find_element_by_xpath(
                ".//table[contains(@class,'m_table ggintro managelist')]/tbody/tr[7]/td[3]/span").text
            print(emNumber,'员工人数')
            html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
            html.list_g = html.find_all('div', attrs={'class': 'm_box company_overview company_detail'})
            # print(html.list_g)
            df_g = data_g(html.list_g,emNumber,cashrate)  # 执行语句块
            # df_g.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
            time.sleep(1.5)  # 休眠1秒

            # #单独写入excel模块
            # df = pd.concat([df, df_g], axis=0)  # 加入基础数据列
            # df.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
            # time.sleep(k+0.5)  # 休眠1秒
            # with open('fundWebdTest.json', 'r', encoding='utf-8') as f:
            #     data = json.load(f)
            #     # print(data[0]['股票'])
            # for i in range(len(data)):  # 写入数据
            #     sh.cells[i + 1, 38].value = data[i]['实控人']  # 实控人

        except:
            continue
            # print(str(i), "当页数据操作失败")
         
            # with open('rrBand.html', 'r', encoding='utf-8') as f:
            #     html = BeautifulSoup(f, 'lxml')
            #     html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})
            #     df_a = data_a(html.list)  # 执行语句块
            # with open('Band.html', 'r', encoding='utf-8') as f:
            #     html = BeautifulSoup(f, 'lxml')
            #     html.list_b = html.find_all('tbody')
            #     df_b = data_b(html.list_b)  # 执行语句块
            # with open('Band.html', 'r', encoding='utf-8') as f:
            #      html = BeautifulSoup(f, 'lxml')
            #      html.list_b = html.find_all('tbody')
            # with open('Band.html','r',encoding='utf-8') as f:
            #     # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZCFZB'
            #     html=BeautifulSoup(f,'lxml')
            #     html.list=html.find_all('tbody')
            # with open('Band.html','r',encoding='utf-8') as f:
            # # url='https://xueqiu.com/snowman/S/SH601600/detail#/GSLRB'
            # html=BeautifulSoup(f,'lxml')
            # html.list=html.find_all('tbody')

        # 以下为写入模板
        # df=pd.concat([df_a,df_b],axis=1)#列合并,axis=0表示按行合并df = df_a.append(df_b)
        # print(df_c,'测试')
        df1 = pd.concat([df_a, df_b_a], axis=1)  # 按列合并
        # print(df1)
        df2 = pd.concat([df1, df_b], axis=1)  # 按列合并
        # print(df1)
        df3 = pd.concat([df2, df_c], axis=1)  # 按列合并
        # print(df2)
        df4 = pd.concat([df3, df_d], axis=1)  # 按列合并
        # print(df3)
        df5 = pd.concat([df4, df_e], axis=1)  # 按列合并
        print(df5)
        df6 = pd.concat([df5, df_f], axis=1)  # 按列合并
        print(df6)
        df7 = pd.concat([df6, df_g], axis=1)  # 按列合并
        print(df7)
        df = pd.concat([df7, df], axis=0)  # 加入基础数据列
        print(df)
        time2 = time.time()  # 计算耗时
        print("总耗时:{}".format(time2 - time1))

        df.to_csv("fundWebd.csv", mode="a+", header=None, index=None, encoding="utf-8-sig", sep=',')  # 提前写入vsv文件
        # item_list.append(df)
        # print(item_list)
        #写入json数据
        df.to_json('fundWebd.json', orient='records', indent=1, force_ascii=False)  # ,orient="values")
        # with open('fundWebd.json','r',encoding='utf-8') as f:
        #     data = json.load(f)
        # item_list.append(data)
        # with open('.fund.json', 'w', encoding='utf-8')as f:
        #     json.dump(item_list,f, indent=1, ensure_ascii=False)
        time.sleep(0.5)


    with open('fundWebd.json', 'r', encoding='utf-8') as f:
        data = json.load(f)
        # print(data[0]['股票'])
    
    time.sleep(0.5)
    bandN = ['序号', '股票', '代码', '股价', '总市值(亿)', '股东持股', '营业额', 'EPS每股收益', '分红', '分红率', '营市比', 'PE市盈率',
                 'PB市净率', '负债率', '经营现金流', '货币资金', '存货', '利息费/收', '17年利润(亿)', '20年利润(亿)', '利润复增率', '营业额复合增长率',
                 '季度增长率', '现金收入比', 'PEG', '未分配利润', '公积金', '毛利率', '净利率', 'ROA总报酬率', 'ROE净收益率', '账款周期', '存货周转', '总资产周转率']
    for i in range(len(data)):  # 写入数据
        try:
        #     print(len(data))
        #     print(data[i][bandN[4]].find('万'))
        #     print(data[i][bandN[4]].find('亿'))
            sh.cells[i + 1, 0].value = i + 1
            sh.cells[i + 1, 1].value = data[i][bandN[1]]
            sh.cells[i + 1, 2].value = data[i][bandN[2]]
            sh.cells[i + 1, 3].value = data[i][bandN[3]]  # 股价
            sh.cells[i + 1, 4].value = data[i][bandN[4]]#总市值(亿)
            # print(data[i]['十大流通股东持股占比'][0:1])
            if data[i]['十大流通股东持股占比'][0:1]!='-':
                if data[i][bandN[4]].find('万') > 0:#市值过万亿
                    sh.cells[i + 1, 5].value = '=(' + data[i][bandN[4]].strip('万') + '*10000*100000000-(' + data[i][bandN[4]].strip('万') + '*10000/' + data[i][bandN[3]] + ')*' + \
                        data[i]['十大流通股东持股占比'] + '*' + data[i][bandN[3]] + '*100000000)/(' + data[i]['股东人数'] + '*10000)/10000'#股东持股
                else:
                    sh.cells[i + 1, 5].value = '=(' + data[i][bandN[4]] + '*100000000-(' + data[i][bandN[4]] + '/' + \
                                               data[i][bandN[3]] + ')*' + \
                                               data[i]['十大流通股东持股占比'] + '*' + data[i][bandN[3]] + '*100000000)/(' + \
                                               data[i]['股东人数'] + '*10000)/10000'  # 股东持股
            else:
                sh.cells[i + 1, 5].value =''
            sh.cells[i + 1, 6].value = data[i]['营业额']  # 营业额
            sh.cells[i + 1, 7].value = data[i][bandN[7]]
            sh.cells[i + 1, 8].value = data[i][bandN[8]]  # 分红
            # sh.cells[i+1, 9].value = data[i][bandN[9]]#分红率
            # sh.cells[i+1, 10].value = data[i][bandN[10]]#营市比
            sh.cells[i + 1, 11].value = data[i][bandN[11]]
            sh.cells[i + 1, 12].value = data[i][bandN[12]]
            sh.cells[i + 1, 13].value = data[i][bandN[13]] + '%'  # 负债率
            sh.cells[i + 1, 14].value = data[i][bandN[14]]  # 经营现金流
            sh.cells[i + 1, 15].value = data[i][bandN[15]]  # 货币资金
            sh.cells[i + 1, 16].value = data[i][bandN[16]]  # 存货
            sh.cells[i + 1, 17].value = data[i]['利息费用'] + ' /' + data[i]['利息收入']  # 利息费/收
            sh.cells[i + 1, 18].value = data[i]['17年利润(亿)']  # 17年利润(亿)
            sh.cells[i + 1, 19].value = data[i]['20年利润(亿)']  # 20年利润(亿)
            # sh.cells[i + 1, 20].value = data[i][bandN[20]]  # 利润复增率
            sh.cells[i + 1, 21].value = '=EXP(LN(' + data[i]['营业额'] + '/' + data[i]['17年营业额'] + ')/3)-1'  # 营业额复合增长率 sh.cells[i + 1, 5].value =''
            sh.cells[i + 1, 22].value = '=EXP(LN(' + data[i]['21年季度'] + ' /' + data[i]['17年季度'] + ')/3)-1'  # 季度增长率
            sh.cells[i + 1, 23].value = data[i]['现金收入比']  # 现金收入比
            sh.cells[i + 1, 25].value = data[i][bandN[25]]  # 未分配利润
            sh.cells[i + 1, 26].value = data[i][bandN[26]]  # 公积金
            sh.cells[i + 1, 27].value = data[i][bandN[27]] + '%'  # 毛利率
            sh.cells[i + 1, 28].value = data[i][bandN[28]] + '%'  # 净利率
            sh.cells[i + 1, 29].value = data[i][bandN[29]] + '%'  # ROA总报酬率
            sh.cells[i + 1, 30].value = data[i][bandN[30]] + '%'  # ROE净收益率
            sh.cells[i + 1, 31].value = data[i][bandN[31]]  # 账款周期
            sh.cells[i + 1, 32].value = data[i][bandN[32]]  # 存货周转
            sh.cells[i + 1, 33].value = data[i][bandN[33]] + '%'  # 总资产周转率
            sh.cells[i + 1, 34].value = data[i]['研发费用'] + '/' + data[i][bandN[6]]  # 研发/收入比
            sh.cells[i + 1, 35].value = data[i]['板块'] # 板块
            sh.cells[i + 1, 36].value = data[i]['基础数据1'] +data[i]['基础数据2'] # 公司简介
            sh.cells[i + 1, 37].value = '='+data[i]['营业额']+'/'+data[i]['员工人数']+'*100000000/10000' # 人均创收
            sh.cells[i + 1, 38].value = data[i]['实控人'] # 实控人
            sh.cells[i + 1, 39].value = data[i]['机构数量'] # 机构数量
            sh.cells[i + 1, 40].value = data[i]['机构占比'] # 基金占比

            print(str(i), 'excel写入正常')
        except:
            # continue
            print(str(i), 'excel写入错误')


    try:
        wb.save('fundWebd.xlsx')
        wb.close()
        app.quit()
        # 获得当前窗口句柄
        sreach_windows = driver.current_window_handle
        driver.quit()
        # 获得当前所有打开的窗口的句柄
        all_handles = driver.window_handles
        for handle in all_handles:
            driver.switch_to.window(handle)
            driver.close()
            time.sleep(2)
        # driver.close()
        # driver.quit()
    except:
        print('有错误代码')




增加类模块:

import xlwings as xw
import requests
from bs4 import BeautifulSoup
from datetime import datetime
import json
import xlwt
import xlwings as xw
from selenium import webdriver
import time
import pandas as pd
import csv
import re
from selenium.webdriver import Chrome, ChromeOptions, ActionChains
import multiprocessing

# item_list=[]
from selenium.webdriver.common.keys import Keys

df = pd.DataFrame()
def data_a(html, numcode):  # 获取基础信息1
    # with open('rrBand.html', 'r', encoding='utf-8') as f:
    # html = BeautifulSoup(f, 'lxml')
    # html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})
    # print(html.list)
    # numcode=''
    df = pd.DataFrame()
    # print(numcode,'一')
    # print(html)
    for i, item in enumerate(html):
        # print(item)
        # print(html.list_a)
        try:
            bandNanme = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:].strip(')')
            df['序号'] = '',
            # df['股票'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:bandNanme.find('(')].strip(
            #             ')'),
            # df['代码'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('')[5:].strip(')').replace(':',''),
            # print(df[['股票','代码']])
            df['股价'] = item.find_all('div', attrs={'class', 'stock-current'})[0].text.strip('¥'),
            # print(df['股价'])
            html.list_a = item.find_all('table', attrs={'class', 'quote-info'})
            for i, item_a in enumerate(html.list_a):
                # print(item_a.find_all('span'))
                # for i in range(25):
                #     print(item_a.find_all('span')[i].text,i,sep=',')
                df['股东持股'] = '',
                df['营市比'] = '',
                # print(item_a.find_all('span')[i].text,i,sep=',')#_a.find_all('span')[18].text.strip('亿'),i,sep=','),
                # print(item_a.find_all('span')[19].text.strip('亿'))
                # df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                # df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                # df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                # print( df['总市值(亿)'])
                if numcode == '6':
                    print(numcode, '上海主板')
                    df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                elif numcode == '3':
                    print(numcode, '深圳创业板')
                    df['总市值(亿)'] = item_a.find_all('span')[11].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[16].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[21].text.strip(''),
                elif numcode == '0':
                    print(numcode, '深圳主板')
                    df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                else:
                    print('错误')
                # print(df['PB市净率'])
                print(df)
                print(str(i), "第一模块写入正常")
        except:
            print(str(i), "第一模块写入异常")
            #     # continue
    return df

def data_b(html):  # 获取基础信息2主要指标
    # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZYCWZB'
    # print('12')
    #     with open('Band.html', 'r', encoding='utf-8') as f:
    #         html = BeautifulSoup(f, 'lxml')
    #         html.list_b = html.find_all('tbody')
    df = pd.DataFrame()
    bandIncome = []
    for i, item in enumerate(html):
        # print(item)
        html.list_b_a = item.find_all('tr')
        for i, item in enumerate(html.list_b_a):
            html.list_b_a_a = item.find_all('td')
            # print(item.find_all('td'))
            for i, item in enumerate(html.list_b_a_a):
                try:
                    html.list = item.find_all('p')[0].contents[0]
                    bandIncome.append(html.list)
                    # print(bandIncome,i,sep=',')
                    # for i, item in enumerate(html.list):
                    # print(item)
                    html.list_b = item.find_all('table', attrs={'class', 'quote-info'})
                    # print(html.list_a
                    # bandNanme = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:].strip(')')
                    # df['17年利润(亿)'] = '',
                    # df['20年利润(亿)'] = '',
                    # print(str(i), "第二模块写入正常")
                    print("第二模块写入正常")
                except:
                    # continue
                    print(str(i), "第二模块写入异常")

    df['营业额'] = bandIncome[0].strip('亿'),
    df['17年营业额'] = bandIncome[3].strip('亿'),  # 2017年营业额
    df['EPS每股收益'] = bandIncome[30].strip(''),  # item_a.find_all('span')[16].text.strip(''),
    df['负债率'] = bandIncome[85].strip(''),
    df['经营现金流'] = bandIncome[50].strip(''),
    df['17年利润(亿)'] = bandIncome[13].strip('亿'),
    df['20年利润(亿)'] = bandIncome[10].strip('亿'),
    df['未分配利润'] = bandIncome[45].strip(''),
    df['公积金'] = bandIncome[40].strip(''),
    df['毛利率'] = bandIncome[75].strip(''),
    df['净利率'] = bandIncome[80].strip(''),
    df['ROA总报酬率'] = bandIncome[65].strip(''),
    df['ROE净收益率'] = bandIncome[55].strip(''),
    df['账款周期'] = bandIncome[125].strip(''),
    df['存货周转'] = bandIncome[120].strip(''),
    df['总资产周转率'] = bandIncome[145].strip(''),

    print(df)
    # for i in range(len(bandIncome)):
    #     print(bandIncome[i],i,sep=',')
    return df
# 获取主要指标里的季度收入
def data_b_a(html):  # 获取主要指标里的季度收入
    df = pd.DataFrame()
    bandIncome = []
    for i, item in enumerate(html):
        html.list_b_a = item.find_all('td')
        # print(item.find_all('td'))
        cut_a = item.find_all('td')[7].text.strip('').find('亿')
        cut_b = item.find_all('td')[11].text.strip('').find('亿')
        df['21年季度'] = item.find_all('td')[7].text.strip('')[0:cut_a],
        df['17年季度'] = item.find_all('td')[11].text.strip('')[0:cut_b],
        print(df)
        # print('data_b_a')
        # for i in range(100):
        #     print(item.find_all('td')[i].text,i,sep=',')
        return df

def data_c(html, html1):
    # with open('Band.html','r',encoding='utf-8') as f:
    #     # url='https://xueqiu.com/snowman/S/SH601991/detail#/FHPS'
    #     html=BeautifulSoup(f,'lxml')
    #     html.list=html.find_all('tbody')
    df = pd.DataFrame()
    for i, item in enumerate(html1):
        # print(item.text)
        bandNanme = item.text
        # print(item.text.strip('')[bandNanme.find('('):].strip(')'))
        df['股票'] = item.text.strip('')[0:bandNanme.find('(')].strip(')'),
        df['代码'] = item.text.strip('')[bandNanme.find('(') + 1:].strip(')'),
        # print(df)
        # print(df['股票'].valuse, df['代码'].values)
    # df['股票'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:bandNanme.find('(')].strip(
    #             ')'),
    # df['代码'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('')[5:].strip(')').replace(':',''),
    # print(df[['股票','代码']])
    for i, item in enumerate(html):
        try:
            cut_a = item.find_all('td')[1].text.strip().find('派')
            cut_b = item.find_all('td')[1].text.strip().find('元')
            # print(cut_a,cut_b)
            # print(item)
            # print(item.find_all('td')[1].text.strip()[cut_a+1:cut_b])
            df['分红率'] = '',
            df['分红'] = item.find_all('td')[1].text.strip()[cut_a + 1:cut_b],
            print(df)
            print(str(i), "第三模块写入正常")
        except:
            print(str(i), "第三模块写入异常")
    return df

def data_d(html):
    # with open('Band.html','r',encoding='utf-8') as f:
    #     # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZCFZB'
    #     html=BeautifulSoup(f,'lxml')
    #     html.list=html.find_all('tbody')
    df = pd.DataFrame()
    # print(html)
    for i, item in enumerate(html):
        # try:
        cut_a = item.find_all('td')[7].text.strip('').find('亿')
        cut_b = item.find_all('td')[61].text.strip('').find('亿')
        # print(cut_a)
        # print(item.find_all('td')[7].text.strip('')[0: cut_a-1])
        # print(item.find_all('td')[61].text.strip('')[0: cut_a-1])
        df['货币资金'] = item.find_all('td')[7].text.strip('')[0: cut_a],
        df['存货'] = item.find_all('td')[61].text.strip('')[0: cut_b],
        # for i in range(300):
        #     print(item.find_all('p')[i],i,sep=',')
        print(df)
        print(str(i), "第四模块写入正常")
        # except:
        #     print(str(i), "第四模块写入正常")
    return df

def data_e(html):
    # with open('Band.html','r',encoding='utf-8') as f:
    #             # url='https://xueqiu.com/snowman/S/SH601600/detail#/GSLRB'
    #             html=BeautifulSoup(f,'lxml')
    #             html.list=html.find_all('tbody')
    df = pd.DataFrame()
    # print(html)
    for i, item in enumerate(html):
        try:
            cut_a = item.find_all('td')[43].text.strip('').find('+')
            cut_b = item.find_all('td')[55].text.strip('').find('+')
            cut_c = item.find_all('td')[61].text.strip('').find('+')
            df['研发费用'] = item.find_all('td')[43].text.strip(''),  # [0:cut_a],
            df['利息费用'] = item.find_all('td')[55].text.strip(''),  # [0:cut_b],
            df['利息收入'] = item.find_all('td')[61].text.strip(''),  # [0:cut_c],
            # print(item.find_all('td')[43].text.strip('')[0:cut_a-1])
            # print(df['研发费用'].values, df['利息费用'].values, df['利息收入'].values ,sep=',')
            print(df)

            # for i in range(100):
            #     print(item.find_all('td')[i].text,i,sep=',')
            print(str(i), "第五模块写入正常")
        except:
            print(str(i), "第五模块写入异常")
    return df

def data_f(html,html_a):  # 获取股东数据

    # with open('Band.html','r',encoding='utf-8') as f:
    # url='http://emweb.eastmoney.com/PC_HSF10/ShareholderResearch/Index?type=web&code=sh601601'
    # html=BeautifulSoup(f,'lxml')
    # html.list_f = html.find_all('table', attrs={'id': 'Table0'})
    # # print(numcode)
    # html.list_f_a = html.find_all('div', attrs={'style': 'padding:10px'})
    df = pd.DataFrame()
    # print(html)
    shareheld = []
    for i, item in enumerate(html):
        try:
            # html.list = item.find_all('tr')[2].contents[3]  # 股东人数
            # print(html.list)
            # html.list_a = item.find_all('tr')[10].contents[3]  # 十大股东合计占比
            # print(html.list_a)
            # for i, item in enumerate(html.list):
            #     print(item[3],i,sep=',')
            # for i, item in enumerate(html.list_a):
            df['股东人数'] = item.find_all('tr')[2].contents[3].text.strip('万').strip(','),
            df['十大流通股东持股占比'] = item.find_all('tr')[10].contents[3].text.strip('万').strip(',') + '%',
            # print(item.find_all('td',attrs={'class':'tips-dataL'}))
            # print(item.find_all('td',attrs={'class':'tips-dataL'})[0])
            # print(item.find_all('td', attrs={'class': 'tips-dataL'})[0])
            # html.list = item.find_all('td', attrs={'class': 'tips-dataL'})
            # for i, item in enumerate(html.list):
            #     # print(item.text.strip('万'),i,sep=',')
            #     # print(len(html.list))
            #     shareheld.append(item.text.strip('万').strip(','))
            # if i<=62:
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[56] + '%',
            # elif i<=71:
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[64] + '%',
            # else :
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[80] + '%',
            # print( item.find_all('tr'))
            print(str(i), '股东数据写入正常')
        except:
            print(str(i), '股东数据写入异常')
    #获取机构占比和机构明细
    org = []#机构数量
    org1 = []  # 机构数量
    for i, item in enumerate(html_a):

        # for i,item_a in enumerate(item.find_all('tr')):
            # print(item_a,i,sep=',')
            # for i, item_a_a in enumerate(item.find_all('tr')):
            #     print(item_a_a)
        # print(item.find_all('tr')[8].contents[7].text)
        # print(item.find_all('tr')[1].contents[3].text)
        df['机构占比']=item.find_all('tr')[8].contents[7].text
        org=(item.find_all('tr')[1].contents[3].text,
                       item.find_all('tr')[2].contents[3].text, item.find_all('tr')[3].contents[3].text,
                        item.find_all('tr')[4].contents[3].text,item.find_all('tr')[5].contents[3].text,
                        item.find_all('tr')[6].contents[3].text, item.find_all('tr')[7].contents[3].text,
                        item.find_all('tr')[8].contents[3].text
                       )
        df['机构数量'] =' 基金:'+org[0]+'家;'+' QFII:'+org[1]+'家;'+' 社保:'+org[2]+'家;'+' 券商:'+org[3]+'家;'+ \
                    ' 保险:' + org[4] + '家;'+' 信托:'+org[5]+'家;'+' 其他机构:'+org[6]+'家;'+' 合计:'+org[7]+'家;'

        print(df, '股东')
    return df

def data_g(html,emNumber,cashrate):#获取公司基础数据
    # with open('rrBand.html', 'w', encoding='utf-8') as f:
    #     f.write(source)
    # with open('rrBand.html', 'r', encoding='utf-8') as f:
    # html = BeautifulSoup(f, 'lxml')
    # print(html.list)
    df = pd.DataFrame()
    for i, item in enumerate(html):
        try:
            # print(item.find_all("tr"), i, sep=',')
            # for i, item_a in enumerate(item.find_all('tr')):
            #     print(item_a, i, sep=',')
            # for i in range(len(item.find_all('tr'))):
            #     print(item.find_all('tr')[18].contents[i], i, sep=',')
            # 公司名称:所属地域、所属行业、曾用名:
            basicDate1 = (
            item.find_all('tr')[0].contents[3].text.strip('\n'), item.find_all('tr')[0].contents[5].text.strip('\n'),
            item.find_all('tr')[1].contents[3].text.strip('\n'),
            item.find_all('tr')[2].contents[1].text.strip('\n')
            )
            # 主营业务、产品名称、实际控制人、董事长、办公地址、公司简介
            basicDate2 = (item.find_all('tr')[3].contents[1].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.find_all('tr')[4].contents[1].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.find_all('tr')[6].contents[1].text.replace('\n', '').replace('\t', '').replace('\r',
                                                                                                              '').replace(
                              ' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'class': 'name'})[0].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'colspan': '3'})[9].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'colspan': '3'})[10].text.replace('\n', '').replace('\t', '').replace(' ', '')
                          )

            df['板块'] = basicDate1[2].strip('').split(':')[1] ,
            df['实控人'] =item.findAll('div',attrs={'class':'tipbox_wrap mr10'})[2].findNext('span').text.replace('\n', '').replace('\t', '').replace(' ', '')#basicDate2[2],
            df['基础数据1'] = basicDate1[0] + ';' + basicDate1[1].strip('') + ';'  + \
                          basicDate1[3].strip('') + '\n',
            df['基础数据2'] = basicDate2[0] + ';' + '\n' + basicDate2[1].strip('') + ';' + '\n' + basicDate2[3].strip('') + ';' + '\n' + basicDate2[4].strip('') + ';' + '\n' + \
                          basicDate2[5].strip('') + ';',
            # 员工人数
            # data_a = item.find_all('tr')[18].contents[5].text.replace('\n', '').replace('\t', '').replace('\r','').replace(' ', '')
            df['员工人数'] =emNumber,# data_a.strip('').split(':')[1],
            df['现金收入比'] =cashrate,

            df.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
            print(df)
            print(str(i), '基础数据写入正常')
        except:
            print(str(i), '基础数据写入异常')
    return df
#定义加载类
class web:
    def __init__(self, url, numcode):
        self.url = url
        self.numcode = numcode
    def  web_a(self):#主要指标
        # 基础数据1加载
        driver.get(self.url)  # 加载网址
        # time.sleep(1)  # 休眠1秒
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # time.sleep(1)  # 休眠1秒
        html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})

        return html.list

    # 基础数据2加载主要指教
    def web_b(self):  # # 基础数据2加载主要指教
        # driver.back()  # 加载网址
        driver.refresh()
        time.sleep(0.3)
        # ActionChains(driver).key_down(Keys.CONTROL).send_keys("w").key_up(Keys.CONTROL).perform()#关闭标签
        # driver.find_element_by_xpath(".//div[contains(@class,'stock-links')]/ul[6]/li[2]/a").click()#加载主要指标
        driver.get(self.url)  # 加载网址
        time.sleep(3)  # 休眠1秒
        # driver.find_elements_by_class_name('btn active').click()
        # driver.find_element_by_xpath(".//*[@id='header']/div[1]/div/form/input[2]").click()
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # print(html)
        # time.sleep(1)  # 休眠1秒
        html.list_b_a = html.find_all('tbody')
        df_b_a = data_b_a(html.list_b_a)  # 执行语句块
        # print(html.list_b_a)
        # 执行后点击任务
        # time.sleep(1)
        driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
        # print(driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").text)  # /span[contains(@class,'btn')]
        time.sleep(1.5)  # 休眠4秒
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # print(html)
        # time.sleep(1)  # 休眠1秒
        html.list_b = html.find_all('tbody')
        df_b = data_b(html.list_b)  # 执行语句块
        # time.sleep(0.2)
        return df_b_a,df_b


    # 基础数据三加载分红
    def  web_c(self):# 基础数据三加载分红
        driver.back()  # 加载网址
        time.sleep(0.5)
        driver.get(self.url)  # 加载网址
        time.sleep(2.5)
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # print(html)
        # time.sleep(1)  # 休眠1秒
        html.list_c = html.find_all('tbody')#获取分红
        html.list_c2 = html.find_all('div', attrs={'stock-info-name'})#获取股票代码和名称
        return html.list_c ,html.list_c2

    # 基础数据四加载资产负债表,,存货、货币资金
    def  web_d(self,url):# 基础数据四加载资产负债表,,存货、货币资金
        driver.get(url)  # 加载网址
        time.sleep(0.5)
        driver.get(self.url)  # 加载网址
        time.sleep(3)
        driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
        time.sleep(1.5)
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # print(html)
        # time.sleep(1)  # 休眠1秒
        html.list_d = html.find_all('tbody')
        return html.list_d

    # 基础数据五加载利润表
    def web_e(self,url):# 基础数据五加载利润表
        driver.get(url)  # 加载网址
        time.sleep(0.3)
        driver.get(self.url)  # xueqiu_url_e
        time.sleep(3)
        driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
        time.sleep(1.5)
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # time.sleep(1)  # 休眠1秒
        html.list_e = html.find_all('tbody')
        # df_e = data_e(html.list_e)  # 执行语句块
        return html.list_e

    # 股东数据加载第六模块
    def web_f(self):# 股东数据加载第六模块
        driver.get(self.url)  # 加载网址
        time.sleep(1.5)  # 休眠1秒
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # time.sleep(1)  # 休眠1秒
        # html.list_f = html.find_all('tbody')
        html.list_f = html.find_all('table', attrs={'id': 'Table0'})
        # print(numcode)
        html.list_f_a = html.find_all('div', attrs={'style': 'padding:10px'})
        return html.list_f,html.list_f_a

   #公司基础数据加载第七模块加载现金收入比和员工人数
    def web_g(self,url):
        driver.get(url)  # 加载现金收入比
        driver.refresh()
        time.sleep(0.3)  # 休眠1秒
        driver.get(self.url)  # 加载现金收入比
        time.sleep(3.5)  # 休眠1秒
        cashrate_1 = driver.find_element_by_xpath(".//div[contains(@class,'item-data')]/p[28]").text
        cashrate_2 = driver.find_element_by_xpath(".//div[contains(@class,'item-data')]/p[29]").text
        cashrate = cashrate_1 + '/' + cashrate_2  # 获取现金收入比
        driver.get(url)  # 加载网址
        source = driver.page_source  # 获取网页内容
        time.sleep(0.5)  # 休眠1秒
        # 员工人数
        emNumber = driver.find_element_by_xpath(
            ".//table[contains(@class,'m_table ggintro managelist')]/tbody/tr[7]/td[3]/span").text
        print(emNumber, '员工人数')
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        html.list_g = html.find_all('div', attrs={'class': 'm_box company_overview company_detail'})
        # print(html.list_g)
        df_g = data_g(html.list_g, emNumber, cashrate)  # 执行语句块
        # df_g.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
        time.sleep(0.5)  # 休眠1秒
        return df_g

    def run(self):
        pf=multiprocessing.Process(target=self.web_e)
        pf.start()
        pf.join()


if __name__ == "__main__":
    # 创建一个workbook

    app = xw.App(visible=False, add_book=False)
    wb = app.books.open('fundWebd.xlsx')
    # 创建一个worksheet
    sh = wb.sheets['worksheet']
    rng = [i for i in sh.range("c:c").value if i != None]  # 单元格内容
    j = sh.range('a1').expand('table').rows.count  # 序号
    app.display_alerts = False
    app.screen_updating = False

    # rng = sh.range('a1').expand('table')
    # nrows = rng.rows.count
    # a = sh.range(f'a1:a{nrows}').value
    # a = [ i for i in sht.range(a:a).value if i != None]
    ''''''
    # 打开网页
    opt = ChromeOptions()  # 创建Chrome参数对象
    opt.headless =  False  #  True# True#              # 把Chrome设置成可视化无界面模式,
    driver = Chrome(options=opt)
    # driver = webdriver.Chrome()
    df_a = []
    df_b = []
    df_b_a = []
    df_c = []
    df_d = []
    df_e = []
    df_f = []

    for i in range(len(rng) - 1):
        print(str(i), rng[i + 1], '第' + str(i + 1) + '只股票开始写入')  # rng[i+1]
        numcode = rng[i + 1].strip('')[2:3]
        time1 = time.time()  # 计算耗时
        try:

            bandcode = rng[i + 1]  # 'SH601600'
            bandcode_a = rng[i + 1][2:]  # 'SH601600'
            xueqiu_url = 'https://xueqiu.com/S/' + bandcode  # 雪球网基础数据'https://c.runoob.com/'#很好的ide工具
            xueqiu_url_a = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/ZYCWZB'  # 主要指标
            xueqiu_url_c = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/FHPS'  # 分红
            xueqiu_url_d = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/ZCFZB'  # 存货
            xueqiu_url_e = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/GSLRB'  # 研发、利息收入
            xueqiu_url_f = 'http://emweb.eastmoney.com/PC_HSF10/ShareholderResearch/Index?type=web&code=' + bandcode  # 主要指标
            tonghuashun_url = 'http://basic.10jqka.com.cn/' + bandcode_a + '/company.html#stockpage'  #
            if numcode == '6':
                eastwealth_url = 'https://emh5.eastmoney.com/html/index.html?fc=' + bandcode_a + '01&fn=&color=w#/cwfx' #
            else:
                eastwealth_url = 'https://emh5.eastmoney.com/html/index.html?fc=' + bandcode_a + '02&fn=&color=w#/cwfx'  #
            # DFCF_url='http://emweb.eastmoney.com/PC_HSF10/OperationsRequired/Index?type=web&code=SH601600'
            # k=0.5+0.3#网页间隔实际控制
            ''''''
            # 主要指标
            myWeb=web(xueqiu_url,numcode)#调用类
            html=myWeb.web_a()
            # numcode = rng[i + 1].strip('')[2:3]
            # print(numcode)
            df_a = data_a(html, numcode)  # 执行语句块html.list
            # df_a.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            time.sleep(0.3)  # 休眠1秒

            # 基础数据2加载主要指教
            myWeb = web(xueqiu_url_a, numcode)  # 调用类
            df_dfb  = myWeb.web_b()
            df_b_a=df_dfb[0]#获取季度信息
            df_b=df_dfb[1]#获取年度消息
            ''''''

            # 基础数据三加载分红
            myWeb = web(xueqiu_url_c, numcode)  # 调用类
            html = myWeb.web_c()
            df_c = data_c(html[0], html[1])  # 执行语句块
            time.sleep(0.2)
            ''''''
            # 基础数据四加载资产负债表,,存货、货币资金
            myWeb = web(xueqiu_url_d, numcode)  # 调用类
            html = myWeb.web_d(xueqiu_url)
            df_d = data_d(html)  # 执行语句块
            # df_d.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            time.sleep(0.2)

            # 基础数据五加载利润表
            myWeb = web(xueqiu_url_e, numcode)  # 调用类
            html = myWeb.web_e(xueqiu_url_f)
            df_e = data_e(html)  # 执行语句块
            # df_e.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            # time.sleep(1)
            # with open('rrBand.html','w',encoding='utf-8') as f:#写入网页
            # f.write(source)
            time.sleep(0.3)  # 休眠1秒

            # 股东数据加载第六模块
            myWeb = web(xueqiu_url_f, numcode)  # 调用类
            html = myWeb.web_f()
            # html=myWeb.run()
            df_f = data_f(html[0],html[1])  #执行语句块
            # df_f.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False) # ,orient="values")
            time.sleep(0.5)  # 休眠1秒
            ''''''
            # 公司基础数据加载第七模块加载现金收入比和员工人数
            myWeb = web(eastwealth_url, numcode) #东方财富 # 调用类
            df_g = myWeb.web_g(tonghuashun_url)#同花顺
            time.sleep(1.5)  # 休眠1秒

            # #单独写入excel模块
            # df1= pd.concat([df_c, df_g], axis=1)  # 加入基础数据列
            # df = pd.concat([df, df1], axis=0)  # 加入基础数据列
            # df.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
            # time.sleep(0.5)  # 休眠1秒
            # time2 = time.time()  # 计算耗时
            # print("总耗时:{}".format(time2 - time1))
            # with open('fundWebdTest.json', 'r', encoding='utf-8') as f:
            #     data = json.load(f)
            #     # print(data[0]['股票'])
            # for i in range(len(data)):  # 写入数据
            #     sh.cells[i + 1, 1].value = data[i]['股票']#股票名称
            #     sh.cells[i + 1, 2].value = data[i]['代码']#股票代码
            #     sh.cells[i + 1, 23].value = data[i]['现金收入比']  # 实控人
            #     sh.cells[i + 1, 36].value = data[i]['基础数据1'] + data[i]['基础数据2']  # 公司简介
        except:
            continue
            # print(str(i), "当页数据操作失败")

            # with open('rrBand.html', 'r', encoding='utf-8') as f:
            #     html = BeautifulSoup(f, 'lxml')
            #     html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})
            #     df_a = data_a(html.list)  # 执行语句块
            # with open('Band.html', 'r', encoding='utf-8') as f:
            #     html = BeautifulSoup(f, 'lxml')
            #     html.list_b = html.find_all('tbody')
            #     df_b = data_b(html.list_b)  # 执行语句块
            # with open('Band.html', 'r', encoding='utf-8') as f:
            #      html = BeautifulSoup(f, 'lxml')
            #      html.list_b = html.find_all('tbody')
            # with open('Band.html','r',encoding='utf-8') as f:
            #     # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZCFZB'
            #     html=BeautifulSoup(f,'lxml')
            #     html.list=html.find_all('tbody')
            # with open('Band.html','r',encoding='utf-8') as f:
            # # url='https://xueqiu.com/snowman/S/SH601600/detail#/GSLRB'
            # html=BeautifulSoup(f,'lxml')
            # html.list=html.find_all('tbody')
        ''''''
        
        # 以下为写入模板
        # df=pd.concat([df_a,df_b],axis=1)#列合并,axis=0表示按行合并df = df_a.append(df_b)
        # print(df_c,'测试')
        df1 = pd.concat([df_a, df_b_a], axis=1)  # 按列合并
        # print(df1)
        df2 = pd.concat([df1, df_b], axis=1)  # 按列合并
        # print(df1)
        df3 = pd.concat([df2, df_c], axis=1)  # 按列合并
        # print(df2)
        df4 = pd.concat([df3, df_d], axis=1)  # 按列合并
        # print(df3)
        df5 = pd.concat([df4, df_e], axis=1)  # 按列合并
        # print(df5)
        df6 = pd.concat([df5, df_f], axis=1)  # 按列合并
        # print(df6)
        df7 = pd.concat([df6, df_g], axis=1)  # 按列合并
        # print(df7)
        df = pd.concat([df7, df], axis=0)  # 加入基础数据列
        print(df)
        time.sleep(0.5)
        time2 = time.time()  # 计算耗时
        print("总耗时:{}".format(time2 - time1))

        df.to_csv("fundWebd.csv", mode="a+", header=None, index=None, encoding="utf-8-sig", sep=',')  # 提前写入vsv文件
        # item_list.append(df)
        # print(item_list)
        #写入json数据
        df.to_json('fundWebd.json', orient='records', indent=1, force_ascii=False)  # ,orient="values")
        # with open('fundWebd.json','r',encoding='utf-8') as f:
        #     data = json.load(f)
        # item_list.append(data)
        # with open('.fund.json', 'w', encoding='utf-8')as f:
        #     json.dump(item_list,f, indent=1, ensure_ascii=False)
        time.sleep(0.5)

    ''''''
    with open('fundWebd.json', 'r', encoding='utf-8') as f:
        data = json.load(f)
        # print(data[0]['股票'])
    
    time.sleep(0.5)
    bandN = ['序号', '股票', '代码', '股价', '总市值(亿)', '股东持股', '营业额', 'EPS每股收益', '分红', '分红率', '营市比', 'PE市盈率',
                 'PB市净率', '负债率', '经营现金流', '货币资金', '存货', '利息费/收', '17年利润(亿)', '20年利润(亿)', '利润复增率', '营业额复合增长率',
                 '季度增长率', '现金收入比', 'PEG', '未分配利润', '公积金', '毛利率', '净利率', 'ROA总报酬率', 'ROE净收益率', '账款周期', '存货周转', '总资产周转率']
    for i in range(len(data)):  # 写入数据
        try:
        #     print(len(data))
        #     print(data[i][bandN[4]].find('万'))
        #     print(data[i][bandN[4]].find('亿'))
            sh.cells[i + 1, 0].value = i + 1
            sh.cells[i + 1, 1].value = data[i][bandN[1]]
            sh.cells[i + 1, 2].value = data[i][bandN[2]]
            sh.cells[i + 1, 3].value = data[i][bandN[3]]  # 股价
            sh.cells[i + 1, 4].value = data[i][bandN[4]]#总市值(亿)
            # print(data[i]['十大流通股东持股占比'][0:1])
            if data[i]['十大流通股东持股占比'][0:1]!='-':
                if data[i][bandN[4]].find('万') > 0:#市值过万亿
                    sh.cells[i + 1, 5].value = '=(' + data[i][bandN[4]].strip('万') + '*10000*100000000-(' + data[i][bandN[4]].strip('万') + '*10000/' + data[i][bandN[3]] + ')*' + \
                        data[i]['十大流通股东持股占比'] + '*' + data[i][bandN[3]] + '*100000000)/(' + data[i]['股东人数'] + '*10000)/10000'#股东持股
                else:
                    sh.cells[i + 1, 5].value = '=(' + data[i][bandN[4]] + '*100000000-(' + data[i][bandN[4]] + '/' + \
                                               data[i][bandN[3]] + ')*' + \
                                               data[i]['十大流通股东持股占比'] + '*' + data[i][bandN[3]] + '*100000000)/(' + \
                                               data[i]['股东人数'] + '*10000)/10000'  # 股东持股
            else:
                sh.cells[i + 1, 5].value =''
            sh.cells[i + 1, 6].value = data[i]['营业额']  # 营业额
            sh.cells[i + 1, 7].value = data[i][bandN[7]]
            sh.cells[i + 1, 8].value = data[i][bandN[8]]  # 分红
            # sh.cells[i+1, 9].value = data[i][bandN[9]]#分红率
            # sh.cells[i+1, 10].value = data[i][bandN[10]]#营市比
            sh.cells[i + 1, 11].value = data[i][bandN[11]]
            sh.cells[i + 1, 12].value = data[i][bandN[12]]
            sh.cells[i + 1, 13].value = data[i][bandN[13]] + '%'  # 负债率
            sh.cells[i + 1, 14].value ='='+data[i][bandN[14]]+'/'+ data[i][bandN[3]]  # 经营现金流
            sh.cells[i + 1, 15].value = data[i][bandN[15]]  # 货币资金
            sh.cells[i + 1, 16].value = data[i][bandN[16]]  # 存货
            sh.cells[i + 1, 17].value = data[i]['利息费用'] + ' /' + data[i]['利息收入']  # 利息费/收
            sh.cells[i + 1, 18].value = data[i]['17年利润(亿)']  # 17年利润(亿)
            sh.cells[i + 1, 19].value = data[i]['20年利润(亿)']  # 20年利润(亿)
            # sh.cells[i + 1, 20].value = data[i][bandN[20]]  # 利润复增率
            sh.cells[i + 1, 21].value = '=EXP(LN(' + data[i]['营业额'] + '/' + data[i]['17年营业额'] + ')/3)-1'  # 营业额复合增长率 sh.cells[i + 1, 5].value =''
            sh.cells[i + 1, 22].value = '=EXP(LN(' + data[i]['21年季度'] + ' /' + data[i]['17年季度'] + ')/3)-1'  # 季度增长率
            sh.cells[i + 1, 23].value = data[i]['现金收入比']  # 现金收入比
            sh.cells[i + 1, 25].value = data[i][bandN[25]]  # 未分配利润
            sh.cells[i + 1, 26].value = data[i][bandN[26]]  # 公积金
            sh.cells[i + 1, 27].value = data[i][bandN[27]] + '%'  # 毛利率
            sh.cells[i + 1, 28].value = data[i][bandN[28]] + '%'  # 净利率
            sh.cells[i + 1, 29].value = data[i][bandN[29]] + '%'  # ROA总报酬率
            sh.cells[i + 1, 30].value = data[i][bandN[30]] + '%'  # ROE净收益率
            sh.cells[i + 1, 31].value = data[i][bandN[31]]  # 账款周期
            sh.cells[i + 1, 32].value = data[i][bandN[32]]  # 存货周转
            sh.cells[i + 1, 33].value = data[i][bandN[33]]   # 总资产周转率
            sh.cells[i + 1, 34].value = data[i]['研发费用'] + '/' + data[i][bandN[6]]  # 研发/收入比
            sh.cells[i + 1, 35].value = data[i]['板块'] # 板块
            sh.cells[i + 1, 36].value = data[i]['基础数据1'] +data[i]['基础数据2'] # 公司简介
            sh.cells[i + 1, 37].value = '='+data[i]['营业额']+'/'+data[i]['员工人数']+'*100000000/10000' # 人均创收
            sh.cells[i + 1, 38].value = data[i]['实控人'] # 实控人
            sh.cells[i + 1, 39].value = data[i]['机构数量'] # 机构数量
            sh.cells[i + 1, 40].value = data[i]['机构占比'] # 基金占比

            print(str(i), 'excel写入正常')
        except:
            # continue
            print(str(i), 'excel写入错误')


    try:
        wb.save('fundWebd.xlsx')
        wb.close()
        app.quit()
        ''''''
        # 获得当前窗口句柄
        sreach_windows = driver.current_window_handle
        driver.quit()
        # 获得当前所有打开的窗口的句柄
        all_handles = driver.window_handles
        for handle in all_handles:
            driver.switch_to.window(handle)
            driver.close()
            time.sleep(2)
        driver.close()
        driver.quit()
        ''''''
    except:
        print('有错误代码')




因东方财富手机网页禁用调整:

import xlwings as xw
import requests
from bs4 import BeautifulSoup
from datetime import datetime
import json
import xlwt
import xlwings as xw
from selenium import webdriver
import time
import pandas as pd
import csv
import re
from selenium.webdriver import Chrome, ChromeOptions, ActionChains
import multiprocessing

# item_list=[]
from selenium.webdriver.common.keys import Keys

df = pd.DataFrame()


def data_a(html, numcode):  # 获取基础信息1
    # with open('rrBand.html', 'r', encoding='utf-8') as f:
    # html = BeautifulSoup(f, 'lxml')
    # html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})
    # print(html.list)
    # numcode=''
    df = pd.DataFrame()
    # print(numcode,'一')
    # print(html)
    for i, item in enumerate(html):
        # print(item)
        # print(html.list_a)
        try:
            bandNanme = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:].strip(')')
            df['序号'] = '',
            # df['股票'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:bandNanme.find('(')].strip(
            #             ')'),
            # df['代码'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('')[5:].strip(')').replace(':',''),
            # print(df[['股票','代码']])
            df['股价'] = item.find_all('div', attrs={'class', 'stock-current'})[0].text.strip('¥'),
            # print(df['股价'])
            html.list_a = item.find_all('table', attrs={'class', 'quote-info'})
            for i, item_a in enumerate(html.list_a):
                # print(item_a.find_all('span'))
                # for i in range(25):
                #     print(item_a.find_all('span')[i].text,i,sep=',')
                df['股东持股'] = '',
                df['营市比'] = '',
                # print(item_a.find_all('span')[i].text,i,sep=',')#_a.find_all('span')[18].text.strip('亿'),i,sep=','),
                # print(item_a.find_all('span')[19].text.strip('亿'))
                # df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                # df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                # df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                # print( df['总市值(亿)'])
                if numcode == '6':
                    print(numcode, '上海主板')
                    df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                elif numcode == '3':
                    print(numcode, '深圳创业板')
                    df['总市值(亿)'] = item_a.find_all('span')[11].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[16].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[21].text.strip(''),
                elif numcode == '0':
                    print(numcode, '深圳主板')
                    df['总市值(亿)'] = item_a.find_all('span')[19].text.strip('亿'),
                    df['PE市盈率'] = item_a.find_all('span')[10].text.strip(''),
                    df['PB市净率'] = item_a.find_all('span')[15].text.strip(''),
                else:
                    print('错误')
                # print(df['PB市净率'])
                print(df)
                print(str(i), "第一模块写入正常")
        except:
            print(str(i), "第一模块写入异常")
            #     # continue
    return df


def data_b(html):  # 获取基础信息2主要指标
    # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZYCWZB'
    # print('12')
    #     with open('Band.html', 'r', encoding='utf-8') as f:
    #         html = BeautifulSoup(f, 'lxml')
    #         html.list_b = html.find_all('tbody')
    df = pd.DataFrame()
    bandIncome = []
    for i, item in enumerate(html):
        # print(item)
        html.list_b_a = item.find_all('tr')
        for i, item in enumerate(html.list_b_a):
            html.list_b_a_a = item.find_all('td')
            # print(item.find_all('td'))
            for i, item in enumerate(html.list_b_a_a):
                try:
                    html.list = item.find_all('p')[0].contents[0]
                    bandIncome.append(html.list)
                    # print(bandIncome,i,sep=',')
                    # for i, item in enumerate(html.list):
                    # print(item)
                    html.list_b = item.find_all('table', attrs={'class', 'quote-info'})
                    # print(html.list_a
                    # bandNanme = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:].strip(')')
                    # df['17年利润(亿)'] = '',
                    # df['20年利润(亿)'] = '',
                    # print(str(i), "第二模块写入正常")
                    print("第二模块写入正常")
                except:
                    # continue
                    print(str(i), "第二模块写入异常")

    df['营业额'] = bandIncome[0].strip('亿'),
    df['17年营业额'] = bandIncome[3].strip('亿'),  # 2017年营业额
    df['EPS每股收益'] = bandIncome[30].strip(''),  # item_a.find_all('span')[16].text.strip(''),
    df['负债率'] = bandIncome[85].strip(''),
    df['经营现金流'] = bandIncome[50].strip(''),
    df['17年利润(亿)'] = bandIncome[13].strip('亿'),
    df['20年利润(亿)'] = bandIncome[10].strip('亿'),
    df['未分配利润'] = bandIncome[45].strip(''),
    df['公积金'] = bandIncome[40].strip(''),
    df['毛利率'] = bandIncome[75].strip(''),
    df['净利率'] = bandIncome[80].strip(''),
    df['ROA总报酬率'] = bandIncome[65].strip(''),
    df['ROE净收益率'] = bandIncome[55].strip(''),
    df['账款周期'] = bandIncome[125].strip(''),
    df['存货周转'] = bandIncome[120].strip(''),
    df['总资产周转率'] = bandIncome[145].strip(''),

    print(df)
    # for i in range(len(bandIncome)):
    #     print(bandIncome[i],i,sep=',')
    return df


# 获取主要指标里的季度收入
def data_b_a(html):  # 获取主要指标里的季度收入
    df = pd.DataFrame()
    bandIncome = []
    for i, item in enumerate(html):
        html.list_b_a = item.find_all('td')
        # print(item.find_all('td'))
        cut_a = item.find_all('td')[7].text.strip('').find('亿')
        cut_b = item.find_all('td')[11].text.strip('').find('亿')
        df['21年季度'] = item.find_all('td')[7].text.strip('')[0:cut_a],
        df['17年季度'] = item.find_all('td')[11].text.strip('')[0:cut_b],
        print(df)
        # print('data_b_a')
        # for i in range(100):
        #     print(item.find_all('td')[i].text,i,sep=',')
        return df


def data_c(html, html1):
    # with open('Band.html','r',encoding='utf-8') as f:
    #     # url='https://xueqiu.com/snowman/S/SH601991/detail#/FHPS'
    #     html=BeautifulSoup(f,'lxml')
    #     html.list=html.find_all('tbody')
    df = pd.DataFrame()
    for i, item in enumerate(html1):
        # print(item.text)
        bandNanme = item.text
        # print(item.text.strip('')[bandNanme.find('('):].strip(')'))
        df['股票'] = item.text.strip('')[0:bandNanme.find('(')].strip(')'),
        df['代码'] = item.text.strip('')[bandNanme.find('(') + 1:].strip(')'),
        # print(df)
        # print(df['股票'].valuse, df['代码'].values)
    # df['股票'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('()')[0:bandNanme.find('(')].strip(
    #             ')'),
    # df['代码'] = item.find_all('div', attrs={'class', 'stock-name'})[0].text.strip('')[5:].strip(')').replace(':',''),
    # print(df[['股票','代码']])
    for i, item in enumerate(html):
        try:
            cut_a = item.find_all('td')[1].text.strip().find('派')
            cut_b = item.find_all('td')[1].text.strip().find('元')
            # print(cut_a,cut_b)
            # print(item)
            # print(item.find_all('td')[1].text.strip()[cut_a+1:cut_b])
            df['分红率'] = '',
            df['分红'] = item.find_all('td')[1].text.strip()[cut_a + 1:cut_b],
            print(df)
            print(str(i), "第三模块写入正常")
        except:
            print(str(i), "第三模块写入异常")
    return df


def data_d(html):
    # with open('Band.html','r',encoding='utf-8') as f:
    #     # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZCFZB'
    #     html=BeautifulSoup(f,'lxml')
    #     html.list=html.find_all('tbody')
    df = pd.DataFrame()
    # print(html)
    for i, item in enumerate(html):
        # try:
        cut_a = item.find_all('td')[7].text.strip('').find('亿')
        cut_b = item.find_all('td')[61].text.strip('').find('亿')
        # print(cut_a)
        # print(item.find_all('td')[7].text.strip('')[0: cut_a-1])
        # print(item.find_all('td')[61].text.strip('')[0: cut_a-1])
        df['货币资金'] = item.find_all('td')[7].text.strip('')[0: cut_a],
        df['存货'] = item.find_all('td')[61].text.strip('')[0: cut_b],
        # for i in range(300):
        #     print(item.find_all('p')[i],i,sep=',')
        print(df)
        print(str(i), "第四模块写入正常")
        # except:
        #     print(str(i), "第四模块写入正常")
    return df


def data_e(html):
    # with open('Band.html','r',encoding='utf-8') as f:
    #             # url='https://xueqiu.com/snowman/S/SH601600/detail#/GSLRB'
    #             html=BeautifulSoup(f,'lxml')
    #             html.list=html.find_all('tbody')
    df = pd.DataFrame()
    # print(html)
    for i, item in enumerate(html):
        try:
            cut_a = item.find_all('td')[43].text.strip('').find('+')
            cut_b = item.find_all('td')[55].text.strip('').find('+')
            cut_c = item.find_all('td')[61].text.strip('').find('+')
            df['研发费用'] = item.find_all('td')[43].text.strip(''),  # [0:cut_a],
            df['利息费用'] = item.find_all('td')[55].text.strip(''),  # [0:cut_b],
            df['利息收入'] = item.find_all('td')[61].text.strip(''),  # [0:cut_c],
            # print(item.find_all('td')[43].text.strip('')[0:cut_a-1])
            # print(df['研发费用'].values, df['利息费用'].values, df['利息收入'].values ,sep=',')
            print(df)

            # for i in range(100):
            #     print(item.find_all('td')[i].text,i,sep=',')
            print(str(i), "第五模块写入正常")
        except:
            print(str(i), "第五模块写入异常")
    return df


def data_f(html, html_a):  # 获取股东数据

    # with open('Band.html','r',encoding='utf-8') as f:
    # url='http://emweb.eastmoney.com/PC_HSF10/ShareholderResearch/Index?type=web&code=sh601601'
    # html=BeautifulSoup(f,'lxml')
    # html.list_f = html.find_all('table', attrs={'id': 'Table0'})
    # # print(numcode)
    # html.list_f_a = html.find_all('div', attrs={'style': 'padding:10px'})
    df = pd.DataFrame()
    # print(html)
    shareheld = []
    for i, item in enumerate(html):
        try:
            # html.list = item.find_all('tr')[2].contents[3]  # 股东人数
            # print(html.list)
            # html.list_a = item.find_all('tr')[10].contents[3]  # 十大股东合计占比
            # print(html.list_a)
            # for i, item in enumerate(html.list):
            #     print(item[3],i,sep=',')
            # for i, item in enumerate(html.list_a):
            df['股东人数'] = item.find_all('tr')[2].contents[3].text.strip('万').strip(','),
            df['十大流通股东持股占比'] = item.find_all('tr')[10].contents[3].text.strip('万').strip(',') + '%',
            # print(item.find_all('td',attrs={'class':'tips-dataL'}))
            # print(item.find_all('td',attrs={'class':'tips-dataL'})[0])
            # print(item.find_all('td', attrs={'class': 'tips-dataL'})[0])
            # html.list = item.find_all('td', attrs={'class': 'tips-dataL'})
            # for i, item in enumerate(html.list):
            #     # print(item.text.strip('万'),i,sep=',')
            #     # print(len(html.list))
            #     shareheld.append(item.text.strip('万').strip(','))
            # if i<=62:
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[56] + '%',
            # elif i<=71:
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[64] + '%',
            # else :
            #     df['股东人数'] = shareheld[0],
            #     df['十大流通股东持股占比'] = shareheld[80] + '%',
            # print( item.find_all('tr'))
            print(str(i), '股东数据写入正常')
        except:
            print(str(i), '股东数据写入异常')
    # 获取机构占比和机构明细
    org = []  # 机构数量
    org1 = []  # 机构数量
    for i, item in enumerate(html_a):
        # for i,item_a in enumerate(item.find_all('tr')):
        # print(item_a,i,sep=',')
        # for i, item_a_a in enumerate(item.find_all('tr')):
        #     print(item_a_a)
        # print(item.find_all('tr')[8].contents[7].text)
        # print(item.find_all('tr')[1].contents[3].text)
        df['机构占比'] = item.find_all('tr')[8].contents[7].text
        org = (item.find_all('tr')[1].contents[3].text,
               item.find_all('tr')[2].contents[3].text, item.find_all('tr')[3].contents[3].text,
               item.find_all('tr')[4].contents[3].text, item.find_all('tr')[5].contents[3].text,
               item.find_all('tr')[6].contents[3].text, item.find_all('tr')[7].contents[3].text,
               item.find_all('tr')[8].contents[3].text
               )
        df['机构数量'] = ' 基金:' + org[0] + '家;' + ' QFII:' + org[1] + '家;' + ' 社保:' + org[2] + '家;' + ' 券商:' + org[
            3] + '家;' + \
                     ' 保险:' + org[4] + '家;' + ' 信托:' + org[5] + '家;' + ' 其他机构:' + org[6] + '家;' + ' 合计:' + org[7] + '家;'

        print(df, '股东')
    return df


def data_g(html, emNumber, cashrate):  # 获取公司基础数据
    # with open('rrBand.html', 'w', encoding='utf-8') as f:
    #     f.write(source)
    # with open('rrBand.html', 'r', encoding='utf-8') as f:
    # html = BeautifulSoup(f, 'lxml')
    # print(html.list)
    df = pd.DataFrame()
    for i, item in enumerate(html):
        try:
            # print(item.find_all("tr"), i, sep=',')
            # for i, item_a in enumerate(item.find_all('tr')):
            #     print(item_a, i, sep=',')
            # for i in range(len(item.find_all('tr'))):
            #     print(item.find_all('tr')[18].contents[i], i, sep=',')
            # 公司名称:所属地域、所属行业、曾用名:
            basicDate1 = (
                item.find_all('tr')[0].contents[3].text.strip('\n'),
                item.find_all('tr')[0].contents[5].text.strip('\n'),
                item.find_all('tr')[1].contents[3].text.strip('\n'),
                item.find_all('tr')[2].contents[1].text.strip('\n')
            )
            # 主营业务、产品名称、实际控制人、董事长、办公地址、公司简介
            basicDate2 = (item.find_all('tr')[3].contents[1].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.find_all('tr')[4].contents[1].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.find_all('tr')[6].contents[1].text.replace('\n', '').replace('\t', '').replace('\r',
                                                                                                              '').replace(
                              ' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'class': 'name'})[0].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'colspan': '3'})[9].text.replace('\n', '').replace('\t', '').replace(' ', ''),
                          item.findNext('table', attrs={'class': 'm_table ggintro managelist'}).findAll('td', attrs={
                              'colspan': '3'})[10].text.replace('\n', '').replace('\t', '').replace(' ', '')
                          )

            df['板块'] = basicDate1[2].strip('').split(':')[1],
            df['实控人'] = item.findAll('div', attrs={'class': 'tipbox_wrap mr10'})[2].findNext('span').text.replace('\n',
                                                                                                                  '').replace(
                '\t', '').replace(' ', '')  # basicDate2[2],
            df['基础数据1'] = basicDate1[0] + ';' + basicDate1[1].strip('') + ';' + \
                          basicDate1[3].strip('') + '\n',
            df['基础数据2'] = basicDate2[0] + ';' + '\n' + basicDate2[1].strip('') + ';' + '\n' + basicDate2[3].strip(
                '') + ';' + '\n' + basicDate2[4].strip('') + ';' + '\n' + \
                          basicDate2[5].strip('') + ';',
            # 员工人数
            # data_a = item.find_all('tr')[18].contents[5].text.replace('\n', '').replace('\t', '').replace('\r','').replace(' ', '')
            df['员工人数'] = emNumber,  # data_a.strip('').split(':')[1],
            df['现金收入比'] = cashrate,

            df.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
            print(df)
            print(str(i), '基础数据写入正常')
        except:
            print(str(i), '基础数据写入异常')
    return df


# 定义加载类
class web:
    def __init__(self, url, numcode):
        self.url = url
        self.numcode = numcode

    def web_a(self):  # 主要指标
        # 基础数据1加载
        driver.get(self.url)  # 加载网址
        # time.sleep(1)  # 休眠1秒
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # time.sleep(1)  # 休眠1秒
        html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})
        return html.list

    # 基础数据2加载主要指教
    def web_b(self):  # # 基础数据2加载主要指教
        # driver.back()  # 加载网址
        driver.refresh()
        time.sleep(0.3)
        # ActionChains(driver).key_down(Keys.CONTROL).send_keys("w").key_up(Keys.CONTROL).perform()#关闭标签
        # driver.find_element_by_xpath(".//div[contains(@class,'stock-links')]/ul[6]/li[2]/a").click()#加载主要指标
        driver.get(self.url)  # 加载网址
        time.sleep(3)  # 休眠1秒
        # driver.find_elements_by_class_name('btn active').click()
        # driver.find_element_by_xpath(".//*[@id='header']/div[1]/div/form/input[2]").click()
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # print(html)
        # time.sleep(1)  # 休眠1秒
        html.list_b_a = html.find_all('tbody')
        df_b_a = data_b_a(html.list_b_a)  # 执行语句块
        # print(html.list_b_a)
        # 执行后点击任务
        # time.sleep(1)
        driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
        # print(driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").text)  # /span[contains(@class,'btn')]
        time.sleep(1.5)  # 休眠4秒
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # print(html)
        # time.sleep(1)  # 休眠1秒
        html.list_b = html.find_all('tbody')
        df_b = data_b(html.list_b)  # 执行语句块
        # time.sleep(0.2)
        return df_b_a, df_b

    # 基础数据三加载分红
    def web_c(self):  # 基础数据三加载分红
        driver.back()  # 加载网址
        time.sleep(0.5)
        driver.get(self.url)  # 加载网址
        time.sleep(2.5)
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # print(html)
        # time.sleep(1)  # 休眠1秒
        html.list_c = html.find_all('tbody')  # 获取分红
        html.list_c2 = html.find_all('div', attrs={'stock-info-name'})  # 获取股票代码和名称
        return html.list_c, html.list_c2

    # 基础数据四加载资产负债表,,存货、货币资金
    def web_d(self, url):  # 基础数据四加载资产负债表,,存货、货币资金
        driver.get(url)  # 加载网址
        time.sleep(0.5)
        driver.get(self.url)  # 加载网址
        time.sleep(3)
        driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
        time.sleep(1.5)
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # print(html)
        # time.sleep(1)  # 休眠1秒
        html.list_d = html.find_all('tbody')
        return html.list_d

    # 基础数据五加载利润表
    def web_e(self, url):  # 基础数据五加载利润表
        driver.get(url)  # 加载网址
        time.sleep(0.3)
        driver.get(self.url)  # xueqiu_url_e
        time.sleep(3)
        driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
        time.sleep(1.5)
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # time.sleep(1)  # 休眠1秒
        html.list_e = html.find_all('tbody')
        # df_e = data_e(html.list_e)  # 执行语句块
        return html.list_e

    # 股东数据加载第六模块
    def web_f(self):  # 股东数据加载第六模块
        driver.get(self.url)  # 加载网址
        time.sleep(1.5)  # 休眠1秒
        source = driver.page_source  # 获取网页内容
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        # time.sleep(1)  # 休眠1秒
        # html.list_f = html.find_all('tbody')
        html.list_f = html.find_all('table', attrs={'id': 'Table0'})
        # print(numcode)
        html.list_f_a = html.find_all('div', attrs={'style': 'padding:10px'})
        return html.list_f, html.list_f_a

    # 公司基础数据加载第七模块加载现金收入比和员工人数
    def web_g(self, url):
        driver.get(url)  # 加载现金收入比
        driver.refresh()
        time.sleep(0.3)  # 休眠1秒
        '''
        driver.get(self.url)  # 加载现金收入比
        time.sleep(3.5)  # 休眠1秒
        cashrate_1 = driver.find_element_by_xpath(".//div[contains(@class,'item-data')]/p[28]").text
        cashrate_2 = driver.find_element_by_xpath(".//div[contains(@class,'item-data')]/p[29]").text
        '''
        cashrate = ''#cashrate_1 + '/' + cashrate_2  # 获取现金收入比#东方财富手机网页禁用
        # driver.get(url)  # 加载网址

        source = driver.page_source  # 获取网页内容
        time.sleep(0.5)  # 休眠1秒
        # 员工人数
        emNumber = driver.find_element_by_xpath(
            ".//table[contains(@class,'m_table ggintro managelist')]/tbody/tr[7]/td[3]/span").text
        print(emNumber, '员工人数')
        html = BeautifulSoup(source, 'html.parser')  # 获取网页内容
        html.list_g = html.find_all('div', attrs={'class': 'm_box company_overview company_detail'})
        # print(html.list_g)
        df_g = data_g(html.list_g, emNumber, cashrate)  # 执行语句块
        # df_g.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
        time.sleep(0.5)  # 休眠1秒
        return df_g

    def run(self):
        pf = multiprocessing.Process(target=self.web_e)
        pf.start()
        pf.join()


if __name__ == "__main__":
    # 创建一个workbook

    app = xw.App(visible=False, add_book=False)
    wb = app.books.open('fundWebd.xlsx')
    # 创建一个worksheet
    sh = wb.sheets['worksheet']
    rng = [i for i in sh.range("c:c").value if i != None]  # 单元格内容
    j = sh.range('a1').expand('table').rows.count  # 序号
    app.display_alerts = False
    app.screen_updating = False

    # rng = sh.range('a1').expand('table')
    # nrows = rng.rows.count
    # a = sh.range(f'a1:a{nrows}').value
    # a = [ i for i in sht.range(a:a).value if i != None]

    # 打开网页
    opt = ChromeOptions()  # 创建Chrome参数对象
    opt.headless = False  # True# True#              # 把Chrome设置成可视化无界面模式,
    driver = Chrome(options=opt)
    # driver = webdriver.Chrome()
    df_a = []
    df_b = []
    df_b_a = []
    df_c = []
    df_d = []
    df_e = []
    df_f = []

    for i in range(len(rng) - 1):
        print(str(i), rng[i + 1], '第' + str(i + 1) + '只股票开始写入')  # rng[i+1]
        numcode = rng[i + 1].strip('')[2:3]
        time1 = time.time()  # 计算耗时
        try:

            bandcode = rng[i + 1]  # 'SH601600'
            bandcode_a = rng[i + 1][2:]  # 'SH601600'
            xueqiu_url = 'https://xueqiu.com/S/' + bandcode  # 雪球网基础数据'https://c.runoob.com/'#很好的ide工具
            xueqiu_url_a = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/ZYCWZB'  # 主要指标
            xueqiu_url_c = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/FHPS'  # 分红
            xueqiu_url_d = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/ZCFZB'  # 存货
            xueqiu_url_e = 'https://xueqiu.com/snowman/S/' + bandcode + '/detail#/GSLRB'  # 研发、利息收入
            xueqiu_url_f = 'http://emweb.eastmoney.com/PC_HSF10/ShareholderResearch/Index?type=web&code=' + bandcode  # 主要指标
            tonghuashun_url = 'http://basic.10jqka.com.cn/' + bandcode_a + '/company.html#stockpage'  #
            if numcode == '6':
                eastwealth_url = 'https://emh5.eastmoney.com/html/hsf10.html?fc=' + bandcode_a + '01&color=w#/cwfx'  #
            else:
                eastwealth_url = 'https://emh5.eastmoney.com/html/hsf10.html?fc=' + bandcode_a + '02&color=w#/cwfx'  #
            print(eastwealth_url,'东方财富网址')
            # DFCF_url='http://emweb.eastmoney.com/PC_HSF10/OperationsRequired/Index?type=web&code=SH601600'
            # k=0.5+0.3#网页间隔实际控制
            ''''''
            # 主要指标
            myWeb = web(xueqiu_url, numcode)  # 调用类
            html = myWeb.web_a()
            # numcode = rng[i + 1].strip('')[2:3]
            # print(numcode)
            df_a = data_a(html, numcode)  # 执行语句块html.list
            # df_a.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            time.sleep(0.3)  # 休眠1秒

            # 基础数据2加载主要指教
            myWeb = web(xueqiu_url_a, numcode)  # 调用类
            df_dfb = myWeb.web_b()
            df_b_a = df_dfb[0]  # 获取季度信息
            df_b = df_dfb[1]  # 获取年度消息
            ''''''

            # 基础数据三加载分红
            myWeb = web(xueqiu_url_c, numcode)  # 调用类
            html = myWeb.web_c()
            df_c = data_c(html[0], html[1])  # 执行语句块
            time.sleep(0.2)
            ''''''
            # 基础数据四加载资产负债表,,存货、货币资金
            myWeb = web(xueqiu_url_d, numcode)  # 调用类
            html = myWeb.web_d(xueqiu_url)
            df_d = data_d(html)  # 执行语句块
            # df_d.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            time.sleep(0.2)

            # 基础数据五加载利润表
            myWeb = web(xueqiu_url_e, numcode)  # 调用类
            html = myWeb.web_e(xueqiu_url_f)
            df_e = data_e(html)  # 执行语句块
            # df_e.to_json('fundWebdTest.json', orient='records', force_ascii=False)  # ,orient="values")
            # time.sleep(1)
            # with open('rrBand.html','w',encoding='utf-8') as f:#写入网页
            # f.write(source)
            time.sleep(0.3)  # 休眠1秒

            # 股东数据加载第六模块
            myWeb = web(xueqiu_url_f, numcode)  # 调用类
            html = myWeb.web_f()
            # html=myWeb.run()
            df_f = data_f(html[0], html[1])  # 执行语句块
            # df_f.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False) # ,orient="values")
            time.sleep(0.5)  # 休眠1秒
            '''
            # 公司基础数据加载第七模块加载现金收入比和员工人数
            myWeb = web(eastwealth_url, numcode)  # 东方财富 # 调用类
            df_g = myWeb.web_g(tonghuashun_url)  # 同花顺
            time.sleep(1.5)  # 休眠1秒
            '''
            # 公司基础数据加载第七模块加载现金收入比和员工人数
            myWeb = web(eastwealth_url, numcode)  # 东方财富 # 调用类
            df_g = myWeb.web_g(tonghuashun_url)  # 同花顺
            time.sleep(1.5)  # 休眠1秒
            # #单独写入excel模块
            # df1= pd.concat([df_c, df_g], axis=1)  # 加入基础数据列
            # df = pd.concat([df, df1], axis=0)  # 加入基础数据列
            # df.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False)  # ,orient="value
            # time.sleep(0.5)  # 休眠1秒
            # time2 = time.time()  # 计算耗时
            # print("总耗时:{}".format(time2 - time1))
            # with open('fundWebdTest.json', 'r', encoding='utf-8') as f:
            #     data = json.load(f)
            #     # print(data[0]['股票'])
            # for i in range(len(data)):  # 写入数据
            #     sh.cells[i + 1, 1].value = data[i]['股票']#股票名称
            #     sh.cells[i + 1, 2].value = data[i]['代码']#股票代码
            #     sh.cells[i + 1, 23].value = data[i]['现金收入比']  # 实控人
            #     sh.cells[i + 1, 36].value = data[i]['基础数据1'] + data[i]['基础数据2']  # 公司简介
        except:
            continue
            # print(str(i), "当页数据操作失败")

            # with open('rrBand.html', 'r', encoding='utf-8') as f:
            #     html = BeautifulSoup(f, 'lxml')
            #     html.list = html.find_all('div', attrs={'class': 'container-sm float-left stock__main'})
            #     df_a = data_a(html.list)  # 执行语句块
            # with open('Band.html', 'r', encoding='utf-8') as f:
            #     html = BeautifulSoup(f, 'lxml')
            #     html.list_b = html.find_all('tbody')
            #     df_b = data_b(html.list_b)  # 执行语句块
            # with open('Band.html', 'r', encoding='utf-8') as f:
            #      html = BeautifulSoup(f, 'lxml')
            #      html.list_b = html.find_all('tbody')
            # with open('Band.html','r',encoding='utf-8') as f:
            #     # url='https://xueqiu.com/snowman/S/SH600282/detail#/ZCFZB'
            #     html=BeautifulSoup(f,'lxml')
            #     html.list=html.find_all('tbody')
            # with open('Band.html','r',encoding='utf-8') as f:
            # # url='https://xueqiu.com/snowman/S/SH601600/detail#/GSLRB'
            # html=BeautifulSoup(f,'lxml')
            # html.list=html.find_all('tbody')
        ''''''
        # 以下为写入模板
        # df=pd.concat([df_a,df_b],axis=1)#列合并,axis=0表示按行合并df = df_a.append(df_b)
        # print(df_c,'测试')
        df1 = pd.concat([df_a, df_b_a], axis=1)  # 按列合并
        # print(df1)
        df2 = pd.concat([df1, df_b], axis=1)  # 按列合并
        # print(df1)
        df3 = pd.concat([df2, df_c], axis=1)  # 按列合并
        # print(df2)
        df4 = pd.concat([df3, df_d], axis=1)  # 按列合并
        # print(df3)
        df5 = pd.concat([df4, df_e], axis=1)  # 按列合并
        # print(df5)
        df6 = pd.concat([df5, df_f], axis=1)  # 按列合并
        # print(df6)
        ''''''
        df7 = pd.concat([df6, df_g], axis=1)  # 按列合并
        # print(df7)
        df = pd.concat([df7, df], axis=0)  # 加入基础数据列
        ''''''
        # df = pd.concat([df6, df], axis=0)  # 加入基础数据列
        print(df)
        time.sleep(0.5)
        time2 = time.time()  # 计算耗时
        print("总耗时:{}".format(time2 - time1))

        df.to_csv("fundWebd.csv", mode="a+", header=None, index=None, encoding="utf-8-sig", sep=',')  # 提前写入vsv文件
        # item_list.append(df)
        # print(item_list)
        # 写入json数据
        df.to_json('fundWebd.json', orient='records', indent=1, force_ascii=False)  # ,orient="values")
        # with open('fundWebd.json','r',encoding='utf-8') as f:
        #     data = json.load(f)
        # item_list.append(data)
        # with open('.fund.json', 'w', encoding='utf-8')as f:
        #     json.dump(item_list,f, indent=1, ensure_ascii=False)
        time.sleep(0.5)

    with open('fundWebd.json', 'r', encoding='utf-8') as f:
        data = json.load(f)
        # print(data[0]['股票'])

    time.sleep(0.5)
    bandN = ['序号', '股票', '代码', '股价', '总市值(亿)', '股东持股', '营业额', 'EPS每股收益', '分红', '分红率', '营市比', 'PE市盈率',
             'PB市净率', '负债率', '经营现金流', '货币资金', '存货', '利息费/收', '17年利润(亿)', '20年利润(亿)', '利润复增率', '营业额复合增长率',
             '季度增长率', '现金收入比', 'PEG', '未分配利润', '公积金', '毛利率', '净利率', 'ROA总报酬率', 'ROE净收益率', '账款周期', '存货周转', '总资产周转率']
    for i in range(len(data)):  # 写入数据
        try:
            #     print(len(data))
            #     print(data[i][bandN[4]].find('万'))
            #     print(data[i][bandN[4]].find('亿'))
            sh.cells[i + 1, 0].value = i + 1
            sh.cells[i + 1, 1].value = data[i][bandN[1]]
            sh.cells[i + 1, 2].value = data[i][bandN[2]]
            sh.cells[i + 1, 3].value = data[i][bandN[3]]  # 股价
            sh.cells[i + 1, 4].value = data[i][bandN[4]]  # 总市值(亿)
            # print(data[i]['十大流通股东持股占比'][0:1])
            if data[i]['十大流通股东持股占比'][0:1] != '-':
                if data[i][bandN[4]].find('万') > 0:  # 市值过万亿
                    sh.cells[i + 1, 5].value = '=(' + data[i][bandN[4]].strip('万') + '*10000*100000000-(' + data[i][
                        bandN[4]].strip('万') + '*10000/' + data[i][bandN[3]] + ')*' + \
                                               data[i]['十大流通股东持股占比'] + '*' + data[i][bandN[3]] + '*100000000)/(' + \
                                               data[i]['股东人数'] + '*10000)/10000'  # 股东持股
                else:
                    sh.cells[i + 1, 5].value = '=(' + data[i][bandN[4]] + '*100000000-(' + data[i][bandN[4]] + '/' + \
                                               data[i][bandN[3]] + ')*' + \
                                               data[i]['十大流通股东持股占比'] + '*' + data[i][bandN[3]] + '*100000000)/(' + \
                                               data[i]['股东人数'] + '*10000)/10000'  # 股东持股
            else:
                sh.cells[i + 1, 5].value = ''
            sh.cells[i + 1, 6].value = data[i]['营业额']  # 营业额
            sh.cells[i + 1, 7].value = data[i][bandN[7]]
            sh.cells[i + 1, 8].value = data[i][bandN[8]]  # 分红
            # sh.cells[i+1, 9].value = data[i][bandN[9]]#分红率
            # sh.cells[i+1, 10].value = data[i][bandN[10]]#营市比
            sh.cells[i + 1, 11].value = data[i][bandN[11]]
            sh.cells[i + 1, 12].value = data[i][bandN[12]]
            sh.cells[i + 1, 13].value = data[i][bandN[13]] + '%'  # 负债率
            sh.cells[i + 1, 14].value = data[i][bandN[14]]  # 经营现金流
            sh.cells[i + 1, 15].value = data[i][bandN[15]]  # 货币资金
            sh.cells[i + 1, 16].value = data[i][bandN[16]]  # 存货
            sh.cells[i + 1, 17].value = data[i]['利息费用'] + ' /' + data[i]['利息收入']  # 利息费/收
            sh.cells[i + 1, 18].value = data[i]['17年利润(亿)']  # 17年利润(亿)
            sh.cells[i + 1, 19].value = data[i]['20年利润(亿)']  # 20年利润(亿)
            # sh.cells[i + 1, 20].value = data[i][bandN[20]]  # 利润复增率
            sh.cells[i + 1, 21].value = '=EXP(LN(' + data[i]['营业额'] + '/' + data[i][
                '17年营业额'] + ')/3)-1'  # 营业额复合增长率 sh.cells[i + 1, 5].value =''
            sh.cells[i + 1, 22].value = '=EXP(LN(' + data[i]['21年季度'] + ' /' + data[i]['17年季度'] + ')/3)-1'  # 季度增长率
            sh.cells[i + 1, 23].value = data[i]['现金收入比']  # 现金收入比#来源东方财务手机网页禁用

            sh.cells[i + 1, 25].value = data[i][bandN[25]]  # 未分配利润
            sh.cells[i + 1, 26].value = data[i][bandN[26]]  # 公积金
            sh.cells[i + 1, 27].value = data[i][bandN[27]] + '%'  # 毛利率
            sh.cells[i + 1, 28].value = data[i][bandN[28]] + '%'  # 净利率
            sh.cells[i + 1, 29].value = data[i][bandN[29]] + '%'  # ROA总报酬率
            sh.cells[i + 1, 30].value = data[i][bandN[30]] + '%'  # ROE净收益率
            sh.cells[i + 1, 31].value = data[i][bandN[31]]  # 账款周期
            sh.cells[i + 1, 32].value = data[i][bandN[32]]  # 存货周转
            sh.cells[i + 1, 33].value = data[i][bandN[33]]  # 总资产周转率
            sh.cells[i + 1, 34].value = data[i]['研发费用'] + '/' + data[i][bandN[6]]  # 研发/收入比
            sh.cells[i + 1, 35].value = data[i]['板块']  # 板块
            sh.cells[i + 1, 36].value = data[i]['基础数据1'] + data[i]['基础数据2']  # 公司简介
            sh.cells[i + 1, 37].value = '=' + data[i]['营业额'] + '/' + data[i]['员工人数'] + '*100000000/10000'  # 人均创收
            sh.cells[i + 1, 38].value = data[i]['实控人']  # 实控人
            sh.cells[i + 1, 39].value = data[i]['机构数量']  # 机构数量
            sh.cells[i + 1, 40].value = data[i]['机构占比']  # 基金占比

            print(str(i), 'excel写入正常')
        except:
            # continue
            print(str(i), 'excel写入错误')

    ''''''
    try:
        wb.save('fundWebd.xlsx')
        wb.close()
        app.quit()
        # 获得当前窗口句柄
        sreach_windows = driver.current_window_handle
        driver.quit()
        # 获得当前所有打开的窗口的句柄
        all_handles = driver.window_handles
        for handle in all_handles:
            driver.switch_to.window(handle)
            driver.close()
            time.sleep(2)
        # driver.close()
        # driver.quit()
    except:
        print('有错误代码')




评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

品尚公益团队

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值