import requests
import re 
import pandas as pd
import os
import fitz
import time
import openpyxl
import matplotlib.pyplot as plt

xlsx = '证券行业.xlsx'

df = pd.read_excel(xlsx)

exf = openpyxl.load_workbook(xlsx)
sheet = exf.active
C2 = sheet['C2']
C = sheet['C']

links = [c.value for c in C]

links_1 = links[1:-1]
links_2 = ''.join(links_1)

sample  = '=HYPERLINK("http://news.windin.com/ns/bulletin.php?code=D996983CA8F2&id=123597048&type=1","方正证券:2020年年度报告摘要")")'

p = re.compile('"(.*?)","(.*?)"')
list_of_tuple = p.findall(links_2)

df2 = pd.DataFrame({'link':[t[0] for t in list_of_tuple], 'f_name':[t[1] for t in list_of_tuple]})

df2.to_csv('证券行业.csv')



df = pd.read_csv('证券行业.csv')
p = re.compile('(?<=\d{4}(年度))')
f_names = [p.sub('年年度报告', f) for f in df.f_name]
df['f_name'] = f_names; del p,f_names

def filter_links(words,df,include=True):
    ls = []
    for word in words:
        if include:
            ls.append([word in f for f in df.f_name])
        else:
            ls.append([word not in f for f in df.f_name])
    index = []
    for r in range(len(df)):
        flag  = not include
        for c in range(len(words)):
            if include:
                ls.append([word not in f for f in df.f_name])
                index=[]
                for r in range(len(df)):
                    flag=not include
                    for c in range(len(words)):
                        if include:
                            flag = flag or ls[c][r]
                        else:
                            flag = flag and ls[c][r]
                    index.append(flag)
                df2=df[index]
                return(df2)
                
df_all = filter_links(['摘要','问询函','审计','财务','风险','债券'],df,include=[False])
df_orig = filter_links(['(','('],df_all,include=[False])
df_updt = filter_links(['(','(',],df_all,include=[True])
df_updt = filter_links(['取消'], df_updt,include=[False])


def sub_with_update(df_updt,df_orig):
    df_newest = df_orig.copy()
    index_orig=[]
    index_updt=[]
    for i,f in enumerate(df_orig.f_name):
        for j,fn in enumerate(df_updt.f_name):
            if f in fn:
             index_orig.append(i)
             index_updt.append(j)
    for n in range(len(index_orig)):
        i = index_orig[n]
        j = index_updt[n]
        df_orig.iloc[i,-2] = df_updt.iloc[j,-2]
    return(df_newest)

df_newest  = sub_with_update(df_updt,df_orig)
df_all.sort_values(by=['f_name'],inplace=True,ignore_index=True)
df_newest['公司简称'] = [f[:4] for f in df_newest.f_name]

counts = df_newest['公司简称'].value_counts()

ten_company = []
for cn in counts.index[:10]:
    ten_company.append(filter_links([cn],df_newest))
    
if not os.path.exists('10companies'):
    os.makedirs('10companies')
    
for df_com in ten_company:
    cn=df_com['公司简称'].iloc[0]
    df_com.to_csv('10companies/%s.csv' % cn)
    
ten_csv=os.listdir('10companies')
    
os.chdir('C:/Users/lenovo/Desktop/python/10companies')
f1=os.listdir()


links= []
f_names=[]
links = df['link']; f_names = df['f_name']

# for f2 in f1:
#     f3 = pd.read_csv(f2)
#     for link in f3['link']:
#         links.append(link)
#     for f_name in f3['f_name']:
#         f_names.append(f_name)
        
def get_PDF_url(url):
    r = requests.get(url);r.encoding = 'utf-8'; html = r.text
    r.close() # 已获取html内容，结束connection
    p = re.compile('<a href=(.*?)\s.*?>(.*?)</a>', re.DOTALL)
    a = p.search(html) # 因第一个<a>即是目标标签，故用search
    if a is None:
        Warning('没有找到下载链接。请手动检查链接：%s' % url)
        return()
    else:
        href = a.group(1); fname = a.group(2).strip()
    href = r.url[:26] + href # 形成完整的链接
    return((href,fname))

hrefs=[];fnames=[]

for link in links:
    href,fname = get_PDF_url(link)
    hrefs.append(href)
    fnames.append(fname)
    df_final_links=pd.DataFrame({'href':hrefs,'fname':fnames})
    df_final_links.to_csv('证券links.csv')
    
    
df_final_links=pd.read_csv('C:/Users/lenovo/Desktop/python/10companies/证券links.csv')
f_names=df_final_links['fname']
hrefs=df_final_links['href']
for i in range(len(hrefs)):
    href=hrefs[i];f_name=f_names[i]
    r = requests.get(href, allow_redirects=True)
    open('%s' %f_name, 'wb').write(r.content)
    time.sleep(10)
r.close()

---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
<ipython-input-3-485877a11c07> in <module>
      1 xlsx = '证券行业.xlsx'
      2 
----> 3 df = pd.read_excel(xlsx)
      4 
      5 exf = openpyxl.load_workbook(xlsx)

E:\anaconda3\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
    294                 )
    295                 warnings.warn(msg, FutureWarning, stacklevel=stacklevel)
--> 296             return func(*args, **kwargs)
    297 
    298         return wrapper

E:\anaconda3\lib\site-packages\pandas\io\excel\_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols)
    302 
    303     if not isinstance(io, ExcelFile):
--> 304         io = ExcelFile(io, engine=engine)
    305     elif engine and engine != io.engine:
    306         raise ValueError(

E:\anaconda3\lib\site-packages\pandas\io\excel\_base.py in __init__(self, path_or_buffer, engine)
    865         self._io = stringify_path(path_or_buffer)
    866 
--> 867         self._reader = self._engines[engine](self._io)
    868 
    869     def __fspath__(self):

E:\anaconda3\lib\site-packages\pandas\io\excel\_xlrd.py in __init__(self, filepath_or_buffer)
     20         err_msg = "Install xlrd >= 1.0.0 for Excel support"
     21         import_optional_dependency("xlrd", extra=err_msg)
---> 22         super().__init__(filepath_or_buffer)
     23 
     24     @property

E:\anaconda3\lib\site-packages\pandas\io\excel\_base.py in __init__(self, filepath_or_buffer)
    351             self.book = self.load_workbook(filepath_or_buffer)
    352         elif isinstance(filepath_or_buffer, str):
--> 353             self.book = self.load_workbook(filepath_or_buffer)
    354         elif isinstance(filepath_or_buffer, bytes):
    355             self.book = self.load_workbook(BytesIO(filepath_or_buffer))

E:\anaconda3\lib\site-packages\pandas\io\excel\_xlrd.py in load_workbook(self, filepath_or_buffer)
     35             return open_workbook(file_contents=data)
     36         else:
---> 37             return open_workbook(filepath_or_buffer)
     38 
     39     @property

E:\anaconda3\lib\site-packages\xlrd\__init__.py in open_workbook(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows)
    109     else:
    110         filename = os.path.expanduser(filename)
--> 111         with open(filename, "rb") as f:
    112             peek = f.read(peeksz)
    113     if peek == b"PK\x03\x04": # a ZIP file

FileNotFoundError: [Errno 2] No such file or directory: '证券行业.xlsx'

%matplotlib inline
data=pd.read_excel('C:/Users/lenovo/Desktop/python/data.xlsx')

plt.rcParams['font.sans-serif'] = ['SimHei']  # 显示中文(windows)
plt.rcParams['axes.unicode_minus'] = False   # 用来正常显示负号
fig = plt.figure(figsize=(15,9), dpi=100)
ax = fig.add_subplot(111)    
ax.plot(data['date'],data['东吴证券'],color='red', label='东吴证券')
ax.plot(data['date'],data['东兴证券'],color='yellow', label='东兴证券')
ax.plot(data['date'],data['哈投股份'],color='green', label='哈投股份')
ax.plot(data['date'],data['辽宁成大'],color='black', label='辽宁成大')
ax.plot(data['date'],data['南京证券'],color='grey', label='南京证券')
ax.plot(data['date'],data['天风证券'],color='pink', label='天风证券')
ax.plot(data['date'],data['五矿资本'],color='Navy', label='五矿资本')
ax.plot(data['date'],data['中泰证券'],color='Gold', label='中泰证券')
ax.plot(data['date'],data['西南证券'],color='Orange', label='西南证券')
ax.plot(data['date'],data['长江证券'],color='Maroon', label='长江证券')
plt.xlabel('年份', fontsize=14)    # X轴标签
plt.ylabel("百万", fontsize=16)         # Y轴标签
ax.legend()                              # 图例
plt.title("利润总额", fontsize=25, color='black', pad=20)
plt.gcf().autofmt_xdate()
plt.show()

data=pd.read_excel('C:/Users/lenovo/Desktop/python/data.xlsx').set_index('date')
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['东吴证券','东兴证券','哈投股份','辽宁成大','南京证券','天风证券', '五矿资本', '中泰证券', '西南证券', '长江证券']
data0=data.iloc[8]
plt.barh(range(len(data0)), data0, tick_label=companies_name, color='#6699CC')
plt.title('2016年利润总额对比（单位：百万元)')
plt.show()

plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['东吴证券','东兴证券','哈投股份','辽宁成大','南京证券','天风证券', '五矿资本', '中泰证券', '西南证券', '长江证券']
data1=data.iloc[9]
plt.barh(range(len(data1)), data1, tick_label=companies_name, color='#6699CC')
plt.title('2017年利润总额对比（单位：百万元)')
plt.show()

plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['东吴证券','东兴证券','哈投股份','辽宁成大','南京证券','天风证券', '五矿资本', '中泰证券', '西南证券', '长江证券']
data2=data.iloc[7]
plt.barh(range(len(data2)), data2, tick_label=companies_name, color='#6699CC')
plt.title('2018年利润总额对比（单位：百万元)')
plt.show()

plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['东吴证券','东兴证券','哈投股份','辽宁成大','南京证券','天风证券', '五矿资本', '中泰证券', '西南证券', '长江证券']
data3=data.iloc[6]
plt.barh(range(len(data3)), data3 , tick_label=companies_name, color='#6699CC')
plt.title('2019年利润总额对比（单位：百万元)')
plt.show()

plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['东吴证券','东兴证券','哈投股份','辽宁成大','南京证券','天风证券', '五矿资本', '中泰证券', '西南证券', '长江证券']
data4=data.iloc[5]
plt.barh(range(len(data4)), data4, tick_label=companies_name, color='#6699CC')
plt.title('2020年利润总额对比（单位：百万元)')
plt.show()