code: import datetime
  import requests
  import pandas as pd
  import pickle
  import time
  import urllib
  import os
  from io import StringIO
  import numpy as np
  import warnings
  import os
  import datetime
  import time
  from tqdm import tnrange, tqdm_notebook
  from requests.exceptions import ConnectionError
  from requests.exceptions import ReadTimeout
  import ipywidgets as widgets
  
  import pip
  
  def import_or_install(package):
      try:
          __import__(package)
      except ImportError:
          print('Please install lxml(pip install lxml)')
  
  import_or_install("lxml")
  
  headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}
  
  date_range_record_file = os.path.join('history', 'date_range.pickle')
  
  
  def requests_get(*args1, **args2):
      i = 3
      while i >= 0:
          try:
              return requests.get(*args1, **args2)
          except (ConnectionError, ReadTimeout) as error:
              print(error)
              print('retry one more time after 60s', i, 'times left')
              time.sleep(60)
          i -= 1
      return pd.DataFrame()
  
  ### ----------
  ###   Helper
  ### ----------
  
  def otc_date_str(date):
      """將datetime.date轉換成民國曆
  
      Args:
          date (datetime.date): 西元歷的日期
  
      Returns:
          str: 民國歷日期 ex: 109/01/01
      """
      return str(date.year - 1911) + date.strftime('%Y/%m/%d')[4:]
  
  
  def combine_index(df, n1, n2):
  
      """將dataframe df中的股票代號與股票名稱合併
  
      Keyword arguments:
  
      Args:
          df (pandas.DataFrame): 此dataframe含有column n1, n2
          n1 (str): 股票代號
          n2 (str): 股票名稱
  
      Returns:
          df (pandas.DataFrame): 此dataframe的index為「股票代號+股票名稱」
      """
  
      return df.set_index(df[n1].astype(str).str.replace(' ', '') + \
          ' ' + df[n2].astype(str).str.replace(' ', '')).drop([n1, n2], axis=1)
  
  def crawl_benchmark(date):
  
      date_str = date.strftime('%Y%m%d')
      res = requests_get("https://www.twse.com.tw/exchangeReport/MI_5MINS_INDEX?response=csv&date=" +
                         date_str + "&_=1544020420045")
  
      # 利用 pandas 將資料整理成表格
  
      if len(res.text) < 10:
          return pd.DataFrame()
  
      df = pd.read_csv(StringIO(res.text.replace("=","")), header=1, index_col='時間')
  
      # 資料處理
  
      df = df.dropna(how='all', axis=0).dropna(how='all', axis=1)
      df.index = pd.to_datetime(date.strftime('%Y %m %d ') + pd.Series(df.index))
      df = df.apply(lambda s: s.astype(str).str.replace(",", "").astype(float))
      df = df.reset_index().rename(columns={'時間':'date'})
      df['stock_id'] = '台股指數'
      return df.set_index(['stock_id', 'date'])
  
  def crawl_capital():
      res = requests_get('https://dts.twse.com.tw/opendata/t187ap03_L.csv', headers=headers)
      res.encoding = 'utf-8'
      df = pd.read_csv(StringIO(res.text))
      time.sleep(5)
      res = requests_get('https://dts.twse.com.tw/opendata/t187ap03_O.csv', headers=headers)
      res.encoding = 'utf-8'
      df = df.append(pd.read_csv(StringIO(res.text)))
  
      df['date'] = pd.to_datetime(str(datetime.datetime.now().year) + df['出表日期'].str[3:])
      df.set_index([df['公司代號'].astype(str) + ' ' + df['公司簡稱'].astype(str), 'date'], inplace=True)
      df.index.levels[0].name = '股票名稱'
      return df
  
  
  def interest():
      res = requests_get('https://www.twse.com.tw/exchangeReport/TWT48U_ALL?response=open_data', headers=headers)
      res.encoding = 'utf-8'
      df = pd.read_csv(StringIO(res.text))
  
      time.sleep(5)
  
      res = requests_get('https://www.tpex.org.tw/web/stock/exright/preAnnounce/prepost_result.php?l=zh-tw&o=data', headers=headers)
      res.encoding = 'utf-8'
      df = df.append(pd.read_csv(StringIO(res.text)))
  
      df['date'] = df['除權息日期'].str.replace('年', '/').str.replace('月', '/').str.replace('日', '')
      df['date'] = pd.to_datetime(str(datetime.datetime.now().year) + df['date'].str[3:])
      df = df.set_index([df['股票代號'].astype(str) + ' ' + df['名稱'].astype(str), 'date'])
      return df
  
  
  def preprocess(df, date):
      df = df.dropna(axis=1, how='all').dropna(axis=0, how='all')
      df.columns = df.columns.str.replace(' ', '')
      df.index.name = 'stock_id'
      df.columns.name = ''
      df['date'] = pd.to_datetime(date)
      df = df.reset_index().set_index(['stock_id', 'date'])
      df = df.apply(lambda s: s.astype(str).str.replace(',',''))
  
      return df
  
  
  
  def bargin_twe(date):
      datestr = date.strftime('%Y%m%d')
      
      res = requests_get('https://www.twse.com.tw/fund/T86?response=csv&date='\
                         +datestr+'&selectType=ALLBUT0999')
      try:
          df = pd.read_csv(StringIO(res.text.replace('=','')), header=1)
      except:
          print('holiday')
          return pd.DataFrame()
      
      df = combine_index(df, '證券代號', '證券名稱')
      df = preprocess(df, date)
      return df
  
  def bargin_otc(date):
      datestr = otc_date_str(date)
      
      url = 'https://www.tpex.org.tw/web/stock/3insti/daily_trade/3itrade_hedge_result.php?l=zh-tw&o=csv&se=EW&t=D&d='+datestr+'&s=0,asc'
      res = requests_get(url, headers=headers)
      try:
          df = pd.read_csv(StringIO(res.text), header=1)
      except:
          print('holiday')
          return pd.DataFrame()
  
      df = combine_index(df, '代號', '名稱')
      df = preprocess(df, date)
      return df
  
  def price_twe(date):
      date_str = date.strftime('%Y%m%d')
      res = requests_get('https://www.twse.com.tw/exchangeReport/MI_INDEX?response=csv&date='+date_str+'&type=ALLBUT0999', headers=headers, )
  
      if res.text == '':
          print('holiday')
          return pd.DataFrame()
  
      header = np.where(list(map(lambda l: '證券代號' in l, res.text.split('\n')[:200])))[0][0]
  
      df = pd.read_csv(StringIO(res.text.replace('=','')), header=header-1)
      df = combine_index(df, '證券代號', '證券名稱')
      df = preprocess(df, date)
      return df
  
  def price_otc(date):
      datestr = otc_date_str(date)
      link = 'https://www.tpex.org.tw/web/stock/aftertrading/daily_close_quotes/stk_quote_download.php?l=zh-tw&d='+datestr+'&s=0,asc,0'
      res = requests_get(link, headers=headers)
      df = pd.read_csv(StringIO(res.text), header=2)
  
      if len(df) < 30:
          print('holiday')
          return pd.DataFrame()
  
      df = combine_index(df, '代號', '名稱')
      df = preprocess(df, date)
      df = df[df['成交筆數'].str.replace(' ', '') != '成交筆數']
      return df
  
  def pe_twe(date):
      datestr = date.strftime('%Y%m%d')
      res = requests_get('https://www.twse.com.tw/exchangeReport/BWIBBU_d?response=csv&date='+datestr+'&selectType=ALL', headers=headers)
      try:
          df = pd.read_csv(StringIO(res.text), header=1)
      except:
          print('holiday')
          return pd.DataFrame()
  
      df = combine_index(df, '證券代號', '證券名稱')
      df = preprocess(df, date)
      return df
  
  def pe_otc(date):
      datestr = otc_date_str(date)
      res = requests_get('https://www.tpex.org.tw/web/stock/aftertrading/peratio_analysis/pera_result.php?l=zh-tw&o=csv&charset=UTF-8&d='+datestr+'&c=&s=0,asc', headers=headers)
      try:
          df = pd.read_csv(StringIO(res.text), header=3)
          df = combine_index(df, '股票代號', '名稱')
          df = preprocess(df, date)
      except:
          print('holiday')
          return pd.DataFrame()
  
      return df
  
  def month_revenue(name, date):
  
      year = date.year - 1911
      month = (date.month+10)%12+1
      if month == 12:
          year -= 1
      url = 'https://mops.twse.com.tw/nas/t21/%s/t21sc03_%d_%d.html' % (name, year, month)
      print(url)
      res = requests_get(url, headers=headers)
      res.encoding = 'big5'
  
      try:
          dfs = pd.read_html(StringIO(res.text), encoding='big-5')
      except:
          print('MONTH ' + name + ': cannot parse ' + str(date))
          return pd.DataFrame()
  
      df = pd.concat([df for df in dfs if df.shape[1] <= 11 and df.shape[1] > 5])
  
      if 'levels' in dir(df.columns):
          df.columns = df.columns.get_level_values(1)
      else:
          df = df[list(range(0,10))]
          column_index = df.index[(df[0] == '公司代號')][0]
          df.columns = df.iloc[column_index]
  
      df = df.loc[:,~df.columns.isnull()]
      df = df.loc[~pd.to_numeric(df['當月營收'], errors='coerce').isnull()]
      df = df[df['公司代號'] != '合計']
      df = combine_index(df, '公司代號', '公司名稱')
      df = preprocess(df, datetime.date(date.year, date.month, 10))
      return df.drop_duplicates()
  
  def crawl_split_twe():
  
      res = requests_get('https://www.twse.com.tw/exchangeReport/TWTAVU?response=csv&_=1537824706232', headers=headers)
  
      df = pd.read_csv(StringIO(res.text),header=1)
      df = df.dropna(how='all', axis=1).dropna(thresh=3, axis=0)
  
      def process_date(s):
          return pd.to_datetime(str(datetime.datetime.now().year) + s.str[3:])
  
      df['停止買賣日期'] = process_date(df['停止買賣日期'])
      df['恢復買賣日期'] = process_date(df['恢復買賣日期'])
      df['股票代號'] = df['股票代號'].astype(int).astype(str)
      df['stock_id'] = df['股票代號'] + ' ' + df['名稱']
      df['date'] = df['恢復買賣日期']
      df = df.set_index(['stock_id', 'date'])
  
      return df
  
  
  def crawl_split_otc():
      res = requests_get("https://www.tpex.org.tw/web/stock/exright/decap/decap_download.php?l=zh-tw&d=107/09/21&s=0,asc,0", headers=headers)
      df = pd.read_csv(StringIO(res.text), header=1)
      df = df.dropna(thresh=5, axis=0)
      df['stock_id'] = df['代號'] + ' ' + df['名稱']
      def process_date(s):
          ss = s.astype(int).astype(str)
          return pd.to_datetime(str(datetime.datetime.now().year) + '/' + ss.str[3:5] + '/' + ss.str[5:])
  
      df['停止買賣日期'] = process_date(df['停止買賣日期'])
      df['恢復買賣日期'] = process_date(df['恢復買賣日期'])
      df['date'] = df['恢復買賣日期']
      df = df.rename(columns={'代號':'股票代號'})
      df = df.set_index(['stock_id', 'date'])
      return df
  
  import io
  import json
  import requests
  import datetime
  import pandas as pd
  
  def crawl_twse_divide_ratio():
  
      datestr = datetime.datetime.now().strftime('%Y%m%d')
      res = requests_get("https://www.twse.com.tw/exchangeReport/TWT49U?response=csv&strDate=20040101&endDate="+datestr+"&_=1551532565786")
  
      df = pd.read_csv(io.StringIO(res.text.replace("=", "")), header=1)
  
      df = df.dropna(thresh=5).dropna(how='all', axis=1)
  
      df = df[~df['資料日期'].isnull()]
  
      # set stock id
      df['stock_id'] = df['股票代號'] + ' ' + df['股票名稱']
  
      # set dates
      df = df[~df['資料日期'].isnull()]
      years = df['資料日期'].str.split('年').str[0].astype(int) + 1911
      years.loc[df['資料日期'].str[3] != '年'] = np.nan
      years.loc[years > datetime.datetime.now().year] = np.nan
      years.ffill(inplace=True)
      dates = years.astype(int).astype(str) +'/'+ df['資料日期'].str.split('年').str[1].str.replace('月', '/').str.replace('日', '')
      df['date'] = pd.to_datetime(dates, errors='coerce')
  
      # convert to float
      float_name_list = ['除權息前收盤價', '除權息參考價', '權值+息值', '漲停價格',
                         '跌停價格', '開盤競價基準', '減除股利參考價' , '最近一次申報每股 (單位)淨值',
                         '最近一次申報每股 (單位)盈餘']
  
      df[float_name_list] = df[float_name_list].astype(str).apply(lambda s:s.str.replace(',', '')).astype(float)
  
  
      df['twse_divide_ratio'] = df['除權息前收盤價'] / df['開盤競價基準']
      return df.set_index(['stock_id', 'date'])
  
  def crawl_otc_divide_ratio():
  
      y = datetime.datetime.now().year
      m = datetime.datetime.now().month
      d = datetime.datetime.now().day
  
      y = str(y-1911)
      m = str(m) if m > 9 else '0' + str(m)
      d = str(d) if d > 9 else '0' + str(d)
  
      datestr = '%s/%s/%s' % (y,m,d)
      res_otc = requests_get('https://www.tpex.org.tw/web/stock/exright/dailyquo/exDailyQ_result.php?l=zh-tw&d=097/01/02&ed=' + datestr + '&_=1551594269115')
  
      df = pd.DataFrame(json.loads(res_otc.text)['aaData'])
      df.columns = ['除權息日期', '代號', '名稱', '除權息前收盤價', '除權息參考價',
                        '權值', '息值',"權+息值","權/息","漲停價格","跌停價格","開盤競價基準",
                        "減除股利參考價","現金股利", "每千股無償配股", "-", "現金增資股數", "現金增資認購價",
                        "公開承銷股數", "員工認購股數","原股東認購數", "按持股比例千股認購"]
  
  
      float_name_list = [ '除權息前收盤價', '除權息參考價',
                            '權值', '息值',"權+息值","漲停價格","跌停價格","開盤競價基準",
                            "減除股利參考價","現金股利", "每千股無償配股", "現金增資股數", "現金增資認購價",
                            "公開承銷股數", "員工認購股數","原股東認購數", "按持股比例千股認購"
      ]
      df[float_name_list] = df[float_name_list].astype(str).apply(lambda s:s.str.replace(',', '')).astype(float)
  
      # set stock id
      df['stock_id'] = df['代號'] + ' ' + df['名稱']
  
      # set dates
      dates = df['除權息日期'].str.split('/')
      dates = (dates.str[0].astype(int) + 1911).astype(str) + '/' + dates.str[1] + '/' + dates.str[2]
      df['date'] = pd.to_datetime(dates)
  
      df['otc_divide_ratio'] = df['除權息前收盤價'] / df['開盤競價基準']
      return df.set_index(['stock_id', 'date'])
  
  
  def crawl_twse_cap_reduction():
  
      datestr = datetime.datetime.now().strftime('%Y%m%d')
      res3 = requests_get("https://www.twse.com.tw/exchangeReport/TWTAUU?response=csv&strDate=20110101&endDate=" + datestr + "&_=1551597854043")
      df = pd.read_csv(io.StringIO(res3.text), header=1)
      df = df.dropna(thresh=5).dropna(how='all',axis=1)
      dates = (df['恢復買賣日期'].str.split('/').str[0].astype(int) + 1911).astype(str) + df['恢復買賣日期'].str[3:]
      df['date'] = pd.to_datetime(dates, errors='coerce')
      df['stock_id'] = df['股票代號'].astype(int).astype(str) + ' ' + df['名稱']
      df.head()
  
      df['twse_cap_divide_ratio'] = df['停止買賣前收盤價格']/df['開盤競價基準']
  
      return df.set_index(['stock_id', 'date'])
  
  def crawl_otc_cap_reduction():
  
      y = datetime.datetime.now().year
      m = datetime.datetime.now().month
      d = datetime.datetime.now().day
  
      y = str(y-1911)
      m = str(m) if m > 9 else '0' + str(m)
      d = str(d) if d > 9 else '0' + str(d)
  
      datestr = '%s/%s/%s' % (y,m,d)
      res4 = requests_get("https://www.tpex.org.tw/web/stock/exright/revivt/revivt_result.php?l=zh-tw&d=102/01/01&ed="+datestr+"&_=1551611342446")
  
      df = pd.DataFrame(json.loads(res4.text)['aaData'])
  
      name = ['恢復買賣日期', '股票代號', '股票名稱', '最後交易之收盤價格',
              '減資恢復買賣開始日參考價格', '漲停價格', '跌停價格', '開始交易基準價', '除權參考價', '減資源因', '詳細資料']
  
      float_name_list = ['最後交易之收盤價格', '減資恢復買賣開始日參考價格', '漲停價格', '跌停價格', '開始交易基準價', '除權參考價']
      df.columns = name
      df[float_name_list] = df[float_name_list].astype(str).apply(lambda s:s.str.replace(',', '')).astype(float)
      df['stock_id'] = df['股票代號'] + ' ' + df['股票名稱']
      dates = (df['恢復買賣日期'].astype(str).str[:-4].astype(int) + 1911).astype(str) + df['恢復買賣日期'].astype(str).str[-4:]
      df['date'] = pd.to_datetime(dates)
      df['date'] = pd.to_datetime(dates, errors='coerce')
  
      df['otc_cap_divide_ratio'] = df['最後交易之收盤價格'] / df['開始交易基準價']
  
      return df.set_index(['stock_id', 'date'])
  
  
  
  
  o2tp = {'成交股數':'成交股數',
          '成交筆數':'成交筆數',
          '成交金額(元)':'成交金額',
          '收盤':'收盤價',
          '開盤':'開盤價',
          '最低':'最低價',
          '最高':'最高價',
          '最後買價':'最後揭示買價',
          '最後賣價':'最後揭示賣價',
        }
  
  o2tpe = {
      '殖利率(%)':'殖利率(%)',
      '本益比':'本益比',
      '每股股利':'股利年度',
      '股價淨值比':'股價淨值比',
  }
  
  o2tb = {
      '外資及陸資(不含外資自營商)-買進股數':'外陸資買進股數(不含外資自營商)',
      '外資及陸資買股數': '外陸資買進股數(不含外資自營商)',
      
      '外資及陸資(不含外資自營商)-賣出股數':'外陸資賣出股數(不含外資自營商)',
      '外資及陸資賣股數': '外陸資賣出股數(不含外資自營商)',
      
      '外資及陸資(不含外資自營商)-買賣超股數':'外陸資買賣超股數(不含外資自營商)',
      '外資及陸資淨買股數': '外陸資買賣超股數(不含外資自營商)',
      
      '外資自營商-買進股數':'外資自營商買進股數',
      '外資自營商-賣出股數':'外資自營商賣出股數',
      '外資自營商-買賣超股數':'外資自營商買賣超股數',
      '投信-買進股數':'投信買進股數',
      '投信買進股數': '投信買進股數',
      '投信-賣出股數': '投信賣出股數',
      '投信賣股數': '投信賣出股數',
      
      '投信-買賣超股數':'投信買賣超股數',
      '投信淨買股數': '投信買賣超股數',
      
      '自營商(自行買賣)-買進股數':'自營商買進股數(自行買賣)',
      '自營商(自行買賣)買股數':'自營商買進股數(自行買賣)',
      
      '自營商(自行買賣)-賣出股數':'自營商賣出股數(自行買賣)',
      '自營商(自行買賣)賣股數':'自營商賣出股數(自行買賣)',
      
      '自營商(自行買賣)-買賣超股數': '自營商買賣超股數(自行買賣)',
      '自營商(自行買賣)淨買股數': '自營商買賣超股數(自行買賣)',
      
      '自營商(避險)-買進股數':'自營商買進股數(避險)',
      '自營商(避險)買股數': '自營商買進股數(避險)',
      '自營商(避險)-賣出股數':'自營商賣出股數(避險)',
      '自營商(避險)賣股數': '自營商賣出股數(避險)',
      '自營商(避險)-買賣超股數': '自營商買賣超股數(避險)',
      '自營商(避險)淨買股數': '自營商買賣超股數(避險)',
      
  }
  
  o2tm = {n:n for n in ['當月營收', '上月營收', '去年當月營收', '上月比較增減(%)', '去年同月增減(%)', '當月累計營收', '去年累計營收',
         '前期比較增減(%)']}
  
  def merge(twe, otc, t2o):
      t2o2 = {k:v for k,v in t2o.items() if k in otc.columns}
      otc = otc[list(t2o2.keys())]
      otc = otc.rename(columns=t2o2)
      twe = twe[otc.columns & twe.columns]
  
      return twe.append(otc)
  
  
  def crawl_price(date):
      dftwe = price_twe(date)
      time.sleep(5)
      dfotc = price_otc(date)
      if len(dftwe) != 0 and len(dfotc) != 0:
          df = merge(dftwe, dfotc, o2tp)
          return df
      else:
          return pd.DataFrame()
  
  
  def crawl_bargin(date):
      dftwe = bargin_twe(date)
      dfotc = bargin_otc(date)
      if len(dftwe) != 0 and len(dfotc) != 0:
          return merge(dftwe, dfotc, o2tb)
      else:
          return pd.DataFrame()
  
  
  def crawl_monthly_report(date):
      dftwe = month_revenue('sii', date)
      time.sleep(5)
      dfotc = month_revenue('otc', date)
      if len(dftwe) != 0 and len(dfotc) != 0:
          return merge(dftwe, dfotc, o2tm)
      else:
          return pd.DataFrame()
  
  def crawl_pe(date):
  
      dftwe = pe_twe(date)
      dfotc = pe_otc(date)
      if len(dftwe) != 0 and len(dfotc) != 0:
          return merge(dftwe, dfotc, o2tpe)
      else:
          return pd.DataFrame()
  
  out = widgets.Output(layout={'border': '1px solid black'})
  
  @out.capture()
  def update_table(table_name, crawl_function, dates):
  
      if dates:
          if len(dates) == 0:
              print("該時間段沒有可以爬取之資料")
              return
          print('start crawl ' + table_name + ' from ', dates[0] , 'to', dates[-1])
      else:
          print('起始、結束日期有點怪怪的，請重新選擇一下喔')
          return
      
  
      df = pd.DataFrame()
      dfs = {}
  
      progress = tqdm_notebook(dates, )
  
      for d in progress:
  
          print('crawling', d)
          progress.set_description('crawl' + table_name + str(d))
  
          data = crawl_function(d)
  
          if data is None or len(data) == 0:
              print('fail, check if ' + str(d) + ' is a holiday')
  
          # update multiple dataframes
          elif isinstance(data, dict):
              if len(dfs) == 0:
                  dfs = {i:pd.DataFrame() for i in data.keys()}
  
              for i, d in data.items():
                  dfs[i] = dfs[i].append(d)
  
          # update single dataframe
          else:
              df = df.append(data)
              print('success')
  
          time.sleep(5)
  
  
  
      if df is not None and len(df) != 0:
          to_pickle(df, table_name)
  
      if len(dfs) != 0:
          for i, d in dfs.items():
              print('saveing df', d.head(), len(d))
              if len(d) != 0:
                  print('save df', d.head())
                  to_pickle(df, table_name)
                  
  import datetime
  from dateutil.relativedelta import relativedelta
  
  def check_monthly_revenue():
      
      df = pd.read_pickle("history/tables/monthly_report.pkl")
      
      if df.loc['1101 台泥', '2017-10-10']['當月營收'] == '8387381':
          print("fix monthly report errors")
          df = df.reset_index()
          df['date'] = [d + relativedelta(months=1) for d in df['date']]
          df.set_index(['stock_id', 'date'], inplace=True)
          df.to_pickle("history/tables/monthly_report.pkl")
          print("done")
          commit("monthlu_report")
  
  import pickle
  def to_pickle(df, name):
      
      if not os.path.isdir('history'):
          os.mkdir('history')
  
      if not os.path.isdir(os.path.join('history', 'tables')):
          os.mkdir(os.path.join('history', 'tables'))
  
  
      fname = os.path.join('history', 'tables', name + '.pkl')
      newfname = os.path.join('history', 'tables', 'new' + name + '.pkl')
      
      # refine patch for monthly revenue
      
      if name == 'monthly_report' :
          check_monthly_revenue()
  
      if os.path.isfile(fname):
          old_df = pd.read_pickle(fname)
          old_df = old_df.append(df, sort=False)
  
          old_df = old_df[~old_df.index.duplicated(keep='last')]
          old_df = old_df.sort_index()
          old_df.to_pickle(newfname)
          os.remove(fname)
          os.rename(newfname, fname)
      else:
          df = df[~df.index.duplicated(keep='last')]
          df.to_pickle(fname)
          old_df = df
          
      if not os.path.isfile(date_range_record_file):
          pickle.dump({}, open(date_range_record_file, 'wb'))
  
      dates = pickle.load(open(date_range_record_file, 'rb'))
      dates[name] = (old_df.index.levels[1][0], old_df.index.levels[1][-1])
      pickle.dump(dates, open(date_range_record_file, 'wb'))
      
      commit(name)
  
  
  from datetime import date
  from dateutil.rrule import rrule, DAILY, MONTHLY
  
  def date_range(start_date, end_date):
      return [dt.date() for dt in rrule(DAILY, dtstart=start_date, until=end_date)]
  
  def month_range(start_date, end_date):
      return [dt.date() for dt in rrule(MONTHLY, dtstart=start_date, until=end_date)]
  
  def season_range(start_date, end_date):
  
      if isinstance(start_date, datetime.datetime):
          start_date = start_date.date()
  
      if isinstance(end_date, datetime.datetime):
          end_date = end_date.date()
  
      ret = []
      for year in range(start_date.year-1, end_date.year+1):
          ret += [  datetime.date(year, 5, 15),
                  datetime.date(year, 8, 14),
                  datetime.date(year, 11, 14),
                  datetime.date(year+1, 3, 31)]
      ret = [r for r in ret if start_date < r < end_date]
  
      return ret
  
  import ipywidgets as widgets
  from IPython.display import display
  
  def table_date_range(table_name):
      if os.path.isfile(date_range_record_file):
          with open(date_range_record_file, 'rb') as f:
              dates = pickle.load(f)
              if table_name in dates:
                  return dates[table_name]
              else:
                  return [None, None]
      else:
          return [None, None]
  
  from inspect import signature
  
  
  def widget(table_name, crawl_func, range_date=None):
  
  
      sig = signature(crawl_func)
  
      if len(sig.parameters) == 0:
          @out.capture()
          def onupdate(x):
              print('updating ', table_name)
              df = crawl_func()
              to_pickle(df, table_name)
              print('done')
  
          btn = widgets.Button(description='update ')
          btn.on_click(onupdate)
  
          first_date, last_date = table_date_range(table_name)
          label = widgets.Label(table_name + ' | ' + str(first_date) + ' ~ ' + str(last_date))
          items = [btn]
          display(widgets.VBox([label, widgets.HBox(items)]))
  
      else:
  
          date_picker_from = widgets.DatePicker(
              description='from',
              disabled=False,
          )
  
          first_date, last_date = table_date_range(table_name)
  
          if last_date:
              date_picker_from.value = last_date
  
          date_picker_to = widgets.DatePicker(
              description='to',
              disabled=False,
          )
  
          date_picker_to.value = datetime.datetime.now().date()
  
          btn = widgets.Button(description='update ')
  
          def onupdate(x):
              dates = range_date(date_picker_from.value, date_picker_to.value)
  
              if len(dates) == 0:
                  print('no data to parse')
  
              update_table(table_name, crawl_func, dates)
  
          btn.on_click(onupdate)
  
  
          label = widgets.Label(table_name + ' | ' + str(first_date) + ' ~ ' + str(last_date))
  
          items = [date_picker_from, date_picker_to, btn]
          display(widgets.VBox([label, widgets.HBox(items)]))
  
  import requests
  from io import StringIO
  import pandas as pd
  import numpy as np
  from tqdm import tqdm_notebook as tqdm
  import os
  import pickle
  import datetime
  import random
  
  def afterIFRS(year, season):
      season2date = [ datetime.datetime(year, 5, 15),
                      datetime.datetime(year, 8, 14),
                      datetime.datetime(year, 11, 14),
                      datetime.datetime(year+1, 3, 31)]
  
      return pd.to_datetime(season2date[season-1].date())
  
  def clean(year, season, balance_sheet):
  
      if len(balance_sheet) == 0:
          print('**WARRN: no data to parse')
          return balance_sheet
      balance_sheet = balance_sheet.transpose().reset_index().rename(columns={'index':'stock_id'})
  
  
      if '會計項目' in balance_sheet:
          s = balance_sheet['會計項目']
          balance_sheet = balance_sheet.drop('會計項目', axis=1).apply(pd.to_numeric)
          balance_sheet['會計項目'] = s.astype(str)
  
      balance_sheet['date'] = afterIFRS(year, season)
  
      balance_sheet['stock_id'] = balance_sheet['stock_id'].astype(str)
      balance = balance_sheet.set_index(['stock_id', 'date'])
      return balance
  
  def download_html(year, season, stock_ids, report_type='C'):
  
      directory = os.path.join('history', 'financial_statement', str(year) + str(season))
      if not os.path.exists(directory):
          os.makedirs(directory)
  
      headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}
      files = [os.path.join(directory, str(i) + '.html') for i in stock_ids]
      pbar = tqdm([sid for file, sid in zip(files, stock_ids) if not os.path.exists(file) or os.stat(file).st_size < 10000])
  
      for sid in pbar:
  
          pbar.set_description('downloading stock %s in report type %s' % (sid, report_type))
  
          file = os.path.join(directory, str(sid) + '.html')
  
          # start parsing
          if int(year) >= 2013:
              url = ('https://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=' + str(sid) + '&SYEAR=' + str(year) + '&SSEASON='+str(season)+'&REPORT_ID=' + str(report_type))
          else:
              url = ('https://mops.twse.com.tw/server-java/t147sb02?t203sb01Form=t203sb01Form&step=0&comp_id='+str(sid)+'&YEAR1='+str(year)+'&SEASON1='+str(season)+'&R_TYPE1=B')
  
          try:
              r = requests_get(url, headers=headers)
          except:
              print('**WARRN: requests cannot get stock:')
              print(url)
              continue
  
          r.encoding = 'big5'
  
          # write files
          f = open(file, 'w', encoding='utf-8')
  
          f.write('<meta charset="UTF-8">\n')
          f.write(r.text)
          f.close()
  
          # finish
          # print(percentage, i, 'end')
  
          # sleep a while
          time.sleep(random.uniform(0, 3))
  
  import requests
  import os
  import time
  import requests
  import datetime
  import random
  import requests
  import io
  import shutil
  import zipfile
  import sys
  import urllib.request
  def crawl_finance_statement2019(year, season):
  
      def ifrs_url(year, season):
          url = "https://mops.twse.com.tw/server-java/FileDownLoad?step=9&fileName=tifrs-"+str(year)+"Q"+str(season)\
                  +".zip&filePath=/home/html/nas/ifrs/"+str(year)+"/"
          print(url)
          return url
  
  
      headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}
  
      from tqdm import tqdm
      class DownloadProgressBar(tqdm):
          def update_to(self, b=1, bsize=1, tsize=None):
              if tsize is not None:
                  self.total = tsize
              self.update(b * bsize - self.n)
  
  
      def download_url(url, output_path):
          with DownloadProgressBar(unit='B', unit_scale=True,
                                   miniters=1, desc=url.split('/')[-1]) as t:
              urllib.request.urlretrieve(url, filename=output_path, reporthook=t.update_to)
  
      def download_file(url, filename):
          """
          Helper method handling downloading large files from `url` to `filename`. Returns a pointer to `filename`.
          """
          chunkSize = 1024
          r = requests.get(url, stream=True, verify=True)
          with open(filename, 'wb') as f:
              # pbar = tqdm( unit="B", total=int( r.headers['content-length'] ) )
              for chunk in r.iter_content(chunk_size=chunkSize): 
                  if chunk: # filter out keep-alive new chunks
                      # pbar.update (len(chunk))
                      f.write(chunk)
          return r
  
      def ifrs_url(year, season):
          url = "https://mops.twse.com.tw/server-java/FileDownLoad?step=9&fileName=tifrs-"+str(year)+"Q"+str(season)\
                  +".zip&filePath=/home/html/nas/ifrs/"+str(year)+"/"
          print(url)
          return url
  
      url = ifrs_url(year,season)
      print('start download')
      download_file(url, 'temp.zip')
      print('finished!')
      
  
      path = os.path.join('history', 'financial_statement', str(year) + str(season))
  
      if os.path.isdir(path):
          shutil.rmtree(path)
  
      print('create new dir')
  
      zipfiles = zipfile.ZipFile(open('temp.zip', 'rb'))
      zipfiles.extractall(path=path)
  
      print('extract all files')
  
      fnames = [f for f in os.listdir(path) if f[-5:] == '.html']
      fnames = sorted(fnames)
  
      newfnames = [f.split("-")[5] + '.html' for f in fnames]
  
      for fold, fnew in zip(fnames, newfnames):
          if len(fnew) != 9:
              print('remove strange code id', fnew)
              os.remove(os.path.join(path, fold))
              continue
              
          if not os.path.exists(os.path.join(path, fnew)):
              os.rename(os.path.join(path, fold), os.path.join(path, fnew))
          else:
              os.remove(os.path.join(path, fold))
  
  
  def crawl_finance_statement(year, season, stock_ids):
  
      directory = os.path.join('history', 'financial_statement', str(year) + str(season))
      if not os.path.exists(directory):
          os.makedirs(directory)
  
      if year >= 2013:
          download_html(year, season, stock_ids, 'C')
      download_html(year, season, stock_ids, 'B')
      download_html(year, season, stock_ids, 'A')
  
  def remove_english(s):
      result = re.sub(r'[a-zA-Z()]', "", s)
      return result
  
  def patch2019(df):
      df = df.copy()
      dfname = df.columns.levels[0][0]
  
      df = df.iloc[:,1:].rename(columns={'會計項目Accounting Title':'會計項目'})
  
  
      refined_name = df[(dfname,'會計項目')].str.split(" ").str[0].str.replace("　", "").apply(remove_english)
  
      subdf = df[dfname].copy()
      subdf['會計項目'] = refined_name
      df[dfname] = subdf
  
      df.columns = pd.MultiIndex(levels=[df.columns.levels[1], df.columns.levels[0]],codes=[df.columns.codes[1], df.columns.codes[0]])
  
      def neg(s):
  
          if isinstance(s, float):
              return s
  
          if str(s) == 'nan':
              return np.nan
  
          s = s.replace(",", "")
          if s[0] == '(':
              return -float(s[1:-1])
          else:
              return float(s)
  
      df.iloc[:,1:] = df.iloc[:,1:].applymap(neg)
      return df
  
  def read_html2019(file):
      dfs = pd.read_html(file)
      return [pd.DataFrame(), patch2019(dfs[0]), patch2019(dfs[1]), patch2019(dfs[2])]
  
  
  import re
  def pack_htmls(year, season, directory):
      balance_sheet = {}
      income_sheet = {}
      cash_flows = {}
      income_sheet_cumulate = {}
      pbar = tqdm(os.listdir(directory))
  
      for i in pbar:
  
          # 將檔案路徑建立好
          file = os.path.join(directory, i)
  
          # 假如檔案不是html結尾，或是太小，代表不是正常的檔案，略過
          if file[-4:] != 'html' or os.stat(file).st_size < 10000:
              continue
  
          # 顯示目前運行的狀況
          stock_id = i.split('.')[0]
          pbar.set_description('parse htmls %d season %d stock %s' % (year, season, stock_id))
  
          # 讀取html
          if year < 2019:
              dfs = pd.read_html(file)
          else:
              try:
                  dfs = read_html2019(file)
              except:
                  print("**ERROR: fail to parse ", file)
                  continue
  
          # 處理pandas0.24.1以上，會把columns parse好的問題
          for df in dfs:
              if 'levels' in dir(df.columns):
                  df.columns = list(range(df.values.shape[1]))
  
          # 假如html不完整，則略過
          if len(dfs) < 4:
              print('**WARRN html file broken', year, season, i)
              continue
                     
          if year <= 2012:
              df = dfs[1]
              category = (df[0] == '會計科目').cumsum()
              df[category == 1]
              dfs = {
                  1: df[category == 0],
                  2: df[category == 1],
                  3: df[category == 2],
              }
  
          # 取得 balance sheet
          df = dfs[1].copy().drop_duplicates(subset=0, keep='last')
          df = df.set_index(0)
          balance_sheet[stock_id] = df[1].dropna()
          #balance_sheet = combine(balance_sheet, df[1].dropna(), stock_id)
  
          # 取得 income statement
          df = dfs[2].copy().drop_duplicates(subset=0, keep='last')
          df = df.set_index(0)
  
          # 假如有4個columns，則第1與第3條column是單季跟累計的income statement
          if len(df.columns) == 4:
              income_sheet[stock_id] = df[1].dropna()
              income_sheet_cumulate[stock_id] = df[3].dropna()
          # 假如有2個columns，則代表第3條column為累計的income statement，單季的從缺
          elif len(df.columns) == 2:
              income_sheet_cumulate[stock_id] = df[1].dropna()
  
              # 假如是第一季財報 累計 跟單季 的數值是一樣的
              if season == 1:
                  income_sheet[stock_id] = df[1].dropna()
  
          # 取得 cash_flows
          df = dfs[3].copy().drop_duplicates(subset=0, keep='last')
          df = df.set_index(0)
          cash_flows[stock_id] = df[1].dropna()
  
      # 將dictionary整理成dataframe
      balance_sheet = pd.DataFrame(balance_sheet)
      income_sheet = pd.DataFrame(income_sheet)
      income_sheet_cumulate = pd.DataFrame(income_sheet_cumulate)
      cash_flows = pd.DataFrame(cash_flows)
  
      print('balance_sheet', balance_sheet.shape)
      print('income_sheet', income_sheet.shape)
      print('cumulate_income_sheet', income_sheet_cumulate.shape)
      print('cash_flows', cash_flows.shape)
  
      # 做清理
      ret = {'balance_sheet':clean(year, season, balance_sheet), 'income_sheet':clean(year, season, income_sheet),
              'income_sheet_cumulate':clean(year, season, income_sheet_cumulate), 'cash_flows':clean(year, season, cash_flows)}
  
      # 假如是第一季的話，則 單季 跟 累計 是一樣的
      if season == 1:
          ret['income_sheet'] = ret['income_sheet_cumulate'].copy()
  
      ret['income_sheet_cumulate'].columns = '累計' + ret['income_sheet_cumulate'].columns
  
      pickle.dump(ret, open(os.path.join('history', 'financial_statement', 'pack' + str(year) + str(season) + '.pickle'), 'wb'))
  
      return ret
  
  def get_all_pickles(directory):
      ret = {}
      for i in os.listdir(directory):
          if i[:4] != 'pack':
              continue
          ret[i[4:9]] = pd.read_pickle(os.path.join(directory, i))
      return ret
  
  def combine(d):
  
      tnames = ['balance_sheet',
              'cash_flows',
              'income_sheet',
              'income_sheet_cumulate']
  
      tbs = {t:pd.DataFrame() for t in tnames}
  
      for i, dfs in d.items():
          for tname in tnames:
              tbs[tname] = tbs[tname].append(dfs[tname])
      return tbs
  
  
  def fill_season4(tbs):
      # copy income sheet (will modify it later)
      income_sheet = tbs['income_sheet'].copy()
  
      # calculate the overlap columns
      c1 = set(tbs['income_sheet'].columns)
      c2 = set(tbs['income_sheet_cumulate'].columns)
  
      overlap_columns = []
      for i in c1:
          if '累計' + i in c2:
              overlap_columns.append('累計' + i)
  
      # get all years
      years = set(tbs['income_sheet_cumulate'].index.levels[1].year)
  
      for y in years:
  
          # get rows of the dataframe that is season 4
          ys = tbs['income_sheet_cumulate'].reset_index('stock_id').index.year == y
          ds4 = tbs['income_sheet_cumulate'].reset_index('stock_id').index.month == 3
          df4 = tbs['income_sheet_cumulate'][ds4 & ys].apply(lambda s: pd.to_numeric(s, errors='coerce')).reset_index('date')
  
          # get rows of the dataframe that is season 3
          yps = tbs['income_sheet_cumulate'].reset_index('stock_id').index.year == y - 1
          ds3 = tbs['income_sheet_cumulate'].reset_index('stock_id').index.month == 11
          df3 = tbs['income_sheet_cumulate'][ds3 & yps].apply(lambda s: pd.to_numeric(s, errors='coerce')).reset_index('date')
          
          if len(df3) == 0:
              print('skip ', y)
              continue
          # calculate the differences of income_sheet_cumulate to get income_sheet single season
          diff = df4 - df3
          diff = diff.drop(['date'], axis=1)[overlap_columns]
  
          # remove 累計
          diff.columns = diff.columns.str[2:]
  
          # 加上第四季的日期
          diff['date'] = pd.to_datetime(str(y) + '-03-31')
          diff = diff[list(c1) + ['date']].reset_index().set_index(['stock_id','date'])
  
          # 新增資料於income_sheet尾部
          income_sheet = income_sheet.append(diff)
  
      # 排序好並更新tbs
      income_sheet = income_sheet.reset_index().sort_values(['stock_id', 'date']).set_index(['stock_id', 'date'])
      tbs['income_sheet'] = income_sheet
  
  def to_db(tbs):
  
      for i, df in tbs.items():
          df = df.reset_index().sort_values(['stock_id', 'date']).drop_duplicates(['stock_id', 'date']).set_index(['stock_id', 'date'])
          df.to_pickle(os.path.join('history', 'tables', i + '.pkl'))
  
      if not os.path.isfile(date_range_record_file):
          pickle.dump({}, open(date_range_record_file, 'wb'))
  
      dates = pickle.load(open(date_range_record_file, 'rb'))
      dates['financial_statement'] = (df.index.levels[1][0], df.index.levels[1][-1])
      pickle.dump(dates, open(date_range_record_file, 'wb'))
  
  
  def html2db(year, season):
  
      pack_htmls(year, season, os.path.join('history', 'financial_statement', str(year) + str(season)))
      d = get_all_pickles(os.path.join('history', 'financial_statement'))
      tbs = combine(d)
      fill_season4(tbs)
      to_db(tbs)
      return {}
  
  def crawl_finance_statement_by_date(date):
      year = date.year
      if date.month == 3:
          season = 4
          year = year - 1
          month = 11
      elif date.month == 5:
          season = 1
          month = 2
      elif date.month == 8:
          season = 2
          month = 5
      elif date.month == 11:
          season = 3
          month = 8
      else:
          return None
  
      if year < 2019:
          df = crawl_monthly_report(datetime.datetime(year, month, 1))
          crawl_finance_statement(year, season, df.index.levels[0].str.split(' ').str[0])
      else:
          crawl_finance_statement2019(year, season)
  
      html2db(year, season)
      commit()
      return {}
  
  
  import os
  import gc
  import shutil
  import pandas as pd
  import numpy as np
  
  def commit(*commit_tables):
      
      ftables = os.path.join('history', 'tables')
      fitems = os.path.join('history', 'items')
  
      fnames = [os.path.join(ftables, f) for f in os.listdir(ftables)]
      tnames = [f[:-4] for f in os.listdir(ftables)]
      
      if len(commit_tables) == 0:
          commit_tables = tnames
  
      for fname, tname in zip(fnames, tnames):
          
          if tname not in commit_tables:
              continue
          
          if fname[-4:] != '.pkl':
              continue
  
          fdir = os.path.join(fitems, tname)
          
          if os.path.isdir(fdir) and os.path.getmtime(fname) < os.path.getmtime(fdir):
              print("已經成功commit過", tname, "了，跳過！")
              continue
          
          if os.path.isdir(fdir):
              shutil.rmtree(fdir)
              os.mkdir(fdir)
          else:
              os.mkdir(fdir)
          
          try:
              df = pd.read_pickle(fname)
          except:
              print("**檔案過大，無法成功commit", fname)
              continue
  
          # remove stock name
          df.reset_index(inplace=True)
          if sum(df['stock_id'].str.find(' ') >= 0) > 0:
              cond = df.stock_id.str[4] == ' '
              df = df[cond]
              gc.collect()
              new_sid = df['stock_id'].str[:4]
              df['stock_id'] = new_sid
              
          df.set_index(['stock_id', 'date'], inplace=True)
  
          # select 4 digit stock ids
          if tname == 'price':
              sids = df.index.get_level_values(0)
              df = df[sids.str.len()==4]
              gc.collect()
  
          if tname == 'monthly_report':
              check_monthly_revenue()
  
          df = df.apply(lambda s: pd.to_numeric(s, errors='coerce'))
          gc.collect()
  
          df[df == 0] = np.nan
  
  
          df = df[~df.index.duplicated(keep='first')]
          gc.collect()
          
          items = list(df.columns)
          df.reset_index(inplace=True)
          
          df = df.pivot("date", "stock_id")
          gc.collect()
  
          for name, (_, series) in zip(items, df.items()):
  
              print(tname, '--', name)
              fitem = os.path.join(fdir, name.replace('+', '_').replace('/', '_'))
              #series.reset_index()\
              #    .pivot("date", "stock_id")[name].to_pickle(fitem + '.pkl')
              df[name].to_pickle(fitem + '.pkl')
apis: pandas.read_pickle pandas.Series pandas.to_numeric pandas.DataFrame pandas.read_html pandas.to_datetime pandas.concat pandas.MultiIndex