getDailyDataFinal1.ipynb (link)

#請先mount 你的Google Drive

import pandas as pd

import sqlite3

import time

dataBase='/content/drive/My Drive/pyStock/stockData.db'

def getDailyQuotation(date):

import pandas as pd

url='http://www.twse.com.tw/exchangeReport/MI_INDEX?response=html&date='+date+'&type=ALLBUT0999'

try:

df = pd.read_html(url,match='每日收盤行情')[0]

except:

return None

if df is not None:

nl=df.columns.nlevels

df.columns = df.columns.get_level_values(nl-1)

df.drop(['證券名稱', '漲跌(+/-)'], inplace=True, axis=1)

df['日期'] = pd.to_datetime(date)

df = df.set_index(['證券代號', '日期'])

df = df.apply(pd.to_numeric, errors='coerce')

df.drop(df[df['收盤價'].isnull()].index, inplace=True)

return df

def saveDailyQuotation(newDF):

connection = sqlite3.connect(dataBase)

try:

dbDF = pd.read_sql('select * from dailyQuotation', connection, parse_dates=['日期'], index_col=['證券代號', '日期'])

except:

dbDF = pd.DataFrame()

appendDF = dbDF.append(newDF, sort=False)

finalDF = appendDF.reset_index().drop_duplicates(subset=['證券代號', '日期'], keep='last').set_index(['證券代號', '日期']).sort_index()

finalDF.to_sql('dailyQuotation', connection, if_exists='replace')

connection.close()

def updateDailyQuotation(start_date, end_date):

newDF = pd.DataFrame()

for date in pd.date_range(start_date, end_date):

df = getDailyQuotation(date.strftime('%Y%m%d'))

if df is not None:

newDF = newDF.append(df)

print('{} complete'.format(date.strftime('%Y%m%d')))

else:

print('{} not found'.format(date.strftime('%Y%m%d')))

time.sleep(15)

saveDailyQuotation(newDF)

return newDF

updateDailyQuotation('2021/2/23', '2021/2/26')

dataBase='/content/drive/My Drive/pyStock/stockData.db'

connection = sqlite3.connect(dataBase)

try:

df_0050 = pd.read_sql('select * from dailyQuotation where [證券代號]="0050"', connection, parse_dates=['日期'], index_col=['證券代號', '日期'])

except:

df_0050 = pd.DataFrame()

connection.close()

df_0050

DB.Browser.for.SQLite-3.12.1-win64

官網(link)

從Yahoo取得股票行情

YahooStockData.ipynb(share)