|
完整代码：
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):#获取公司基础数据
# 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.find_all('tr')[8].contents[1].text.replace('\n', '').replace('\t', '').replace('\r',
'').replace(
' ', ''),
item.find_all('tr')[23].contents[1].text.replace('\n', '').replace('\t', '').replace('\r',
'').replace(
' ', ''),
item.find_all('tr')[24].contents[1].text.replace('\n', '').replace('\t', '').replace('\r',
'').replace(
' ', '')
)
df['板块'] = basicDate1[2].strip('').split('：')[1] ,
df['实控人'] = 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['员工人数'] = data_a.strip('').split('：')[1],
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]
try:
bandcode = rng[i + 1] # '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/002352/company.html#stockpage' #
# DFCF_url='http://emweb.eastmoney.com/PC_HSF10/OperationsRequired/Index?type=web&code=SH601600'
k=0.5#网页间隔实际控制
# 基础数据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(k) # 休眠1秒
# 基础数据2加载主要指教
driver.back() # 加载网址
time.sleep(k)
# 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(k*4+0.5) # 休眠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(k+0.7) # 休眠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(1)
# 基础数据三加载分红
# driver.back() # 加载网址
# time.sleep(0.5)
driver.get(xueqiu_url_c) # 加载网址
time.sleep(k*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'})
# print(html.list_c2)
df_c = data_c(html.list_c, html.list_c2) # 执行语句块
# print(html)
# time.sleep(1)
# 基础数据四加载资产负债表
driver.get(xueqiu_url) # 加载网址
time.sleep(k-0.2)
driver.get(xueqiu_url_d) # 加载网址
time.sleep(k*2.5+0.1)
driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
time.sleep(k+0.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(1)
# 基础数据五加载利润表
driver.get(xueqiu_url_f) # 加载网址
time.sleep(k-0.2)
driver.get(xueqiu_url_e) # xueqiu_url_e
time.sleep(k*2.5+0.2+0.1)
driver.find_element_by_xpath(".//div[contains(@class,'stock-info-btn-list')]/span[2]").click()
time.sleep(k+0.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(k-0.2) # 休眠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+0.5) # 休眠1秒
# 公司基础数据加载第七模块
driver.get(tonghuashun_url) # 加载网址
source = driver.page_source # 获取网页内容
time.sleep(k) # 休眠1秒
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) # 执行语句块
# df_g.to_json('fundWebdTest.json', orient='records', indent=1, force_ascii=False) # ,orient="value
time.sleep(k + 0.5) # 休眠1秒
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)
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.8)
with open('fundWebd.json', 'r', encoding='utf-8') as f:
data = json.load(f)
# print(data[0]['股票'])
time.sleep(0.8)
bandN = ['序号', '股票', '代码', '股价', '总市值(亿)', '股东持股', '营业额', 'EPS每股收益', '分红', '分红率', '营市比', 'PE市盈率',
'PB市净率', '负债率', '经营现金流', '货币资金', '存货', '利息费/收', '17年利润(亿)', '20年利润(亿)', '利润复增率', '营业额复合增长率',
'季度增长率', '现金收入比', 'PEG', '未分配利润', '公积金', '毛利率', '净利率', 'ROA总报酬率', 'ROE净收益率', '账款周期', '存货周转', '总资产周转率']
for i in range(len(data)): # 写入数据
try:
print(len(data))
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]!='-':
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' # data[i]['十大流通股东持股占比']+' /'+ data[i]['股东人数']#round(float(data[i]['总市值(亿)'])/float(data[i]['股价']),2)#股东持股
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 = round(float(data[i]['利息费用'])/float(data[i]['利息收入']),2) # 利息费/收
sh.cells[i + 1, 17].value = data[i]['利息费用'] + ' /' + data[i]['利息收入'] # 利息费/收
# sh.cells[i + 20, 17].value =data[i]['利息费用']
# sh.cells[i + 20, 18].value =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, 22].value = '=EXP(LN(' + data[i]['21年季度'] + ' /' + data[i]['17年季度'] + ')/3)-1' # 季度增长率
# sh.cells[i + 1, 23].value = data[i][bandN[23]] # 现金收入比
# sh.cells[i + 1, 24].value = data[i][bandN[24]] # PEG
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, 32].value = data[i][bandN[32]] # 存货周转
# sh.cells[i + 1, 32].value = data[i][bandN[32]] # 存货周转
# sh.cells[i + 1, 32].value = data[i][bandN[32]] # 存货周转
# sh.cells[i + 1, 43].value =round(float(data[i]['研发费用'])/float(data[i][bandN[6]]),4) # 研发/收入比
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(i)
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('有错误代码')
|