Python 解鎖 - 股市股號資訊撈取

在上一篇使用Colab Python + SQLite在Google Drive裡建立簡易的資料庫，我們討論到如何在Google Drive裡建立簡易的SQLite資料庫，並在Colab環境中將資料存取於此資料庫中。

在此篇中，我們來拿實務的例子作實戰練習，我們會討論如何在Google Colab的環境下，撈取所有台美股的股票代碼，並獲取相關細節，最後將獲取的資訊存取於SQLite資料庫中。

stocksymbol

步驟1 - 於stocksymbol註冊帳號並獲取API金鑰。於Colab筆記本中安裝stocksymbol。

!pip install stocksymbol

步驟2 - 在存取於Google Drive中的SQLite資料庫Stock.db中建立資料表Tickers。

import sqlite3

con = sqlite3.connect('/content/drive/MyDrive/data/Stock.db')
cursor = con.cursor()
query = """
CREATE TABLE "Tickers"
( 
	[symbol] varchar(50) NOT NULL, 
	[longName] varchar(255) , 
	[exchange] varchar(255) , 
	[market] varchar(255), 
	PRIMARY KEY(symbol)
)
"""
cursor.execute(query)
con.commit()
cursor.close()

步驟3 - 開始對於所有股票代碼進行撈取並轉換成DataFrame。

from stocksymbol import StockSymbol

api_key = 'Your API Key'
ss = StockSymbol(api_key)

# get symbol list based on market

symbol_list_us = ss.get_symbol_list(market="US")
symbol_list_tw = ss.get_symbol_list(market="TW")

import pandas as pd

df_us = pd.DataFrame(symbol_list_us)
df_tw = pd.DataFrame(symbol_list_tw)

步驟4 - 將獲取的訊息存入Stock.db中的資料表Tickers。

con = sqlite3.connect('/content/drive/MyDrive/data/Stock.db')

for row in df_us.to_records(index=False):
	con.execute(" INSERT INTO Tickers (symbol, longName, exchange, market) VALUES (?, ?, ? ,?) ", (str(row[0]),str(row[2]), str(row[3]) , str(row[4])) )
con.commit()
for row in df_tw.to_records(index=False):
	con.execute(" INSERT INTO Tickers (symbol, longName, exchange, market) VALUES (?, ?, ? ,?) ", (str(row[0]),str(row[2]), str(row[3]) , str(row[4])) )
con.commit()

yahooquery

我們獲取一萬多筆資料，接下來我們將會對此資料進行像是PE, moving average等相關資料的撈取。

步驟1: 於Colab筆記本中安裝yahooquery。

!pip install yahooquery

步驟2: 首先，先拿高通(QCOM)來了解回傳資料的結構。我們發現我們要撈取的收盤價、本益比、均值落在list(details.summary_detail.values())[0]。

from yahooquery import Ticker

symbols = ['QCOM']
list(details.summary_detail.values())[0]

步驟3: 定義函式get_info，使用try-except避免回傳空值中斷流程。

from yahooquery import Ticker

def get_info(tickers):
	try:
		details = Ticker(tickers)
		tickers = list(details.summary_detail)[0]
		previousClose = list(details.summary_detail.values())[0].get('previousClose')
		dividendYield = list(details.summary_detail.values())[0].get('dividendYield')
		trailingPE = list(details.summary_detail.values())[0].get('trailingPE')
		forwardPE = list(details.summary_detail.values())[0].get('forwardPE')
		priceToSalesTrailing12Months = list(details.summary_detail.values())[0].get('priceToSalesTrailing12Months')
		fiftyDayAverage = list(details.summary_detail.values())[0].get('fiftyDayAverage')
		twoHundredDayAverage = list(details.summary_detail.values())[0].get('twoHundredDayAverage')
		cursor = con.cursor()
		con.execute(" INSERT INTO Tickers_Info (tickers , previousClose , dividendYield, trailingPE , forwardPE, priceToSalesTrailing12Months, fiftyDayAverage, twoHundredDayAverage) VALUES (?, ? , ?, ?, ?, ?, ?, ?) ",
		(str(tickers),str(previousClose), str(dividendYield) , str(trailingPE) , str(forwardPE ), str(priceToSalesTrailing12Months), str(fiftyDayAverage) , str(twoHundredDayAverage)))
	except:
		print(tickers)
		pass
return

步驟4 - 建立SQLite的資料表以儲存剛剛存取的資料。

import sqlite3

con = sqlite3.connect('/content/drive/MyDrive/data/Stock.db')
cursor = con.cursor()
query = """ 
CREATE TABLE "Tickers_Info"
( 
	[Tickers] varchar(50) NOT NULL, 
	[previousClose] float, 
	[dividendYield] float, 
	[trailingPE] float, 
	[forwardPE] float, 
	[priceToSalesTrailing12Months] float, 
	[fiftyDayAverage] float, 
	[twoHundredDayAverage] float, 
	PRIMARY KEY(Tickers)
)
"""
cursor.execute(query)
con.commit()
cursor.close()

步驟5 - 執行

• 於Part 1建立的股票代碼資料表撈取存成df_Tickers。
• 於每列的股號(row[0])輸入get_info。

import pandas as pd

con = sqlite3.connect('/content/drive/MyDrive/data/Stock.db')
cursor = con.cursor()
con.execute('''DELETE FROM Tickers_Info''')
con.commit()
df_Tickers = pd.read_sql('select distinct symbol from Tickers', con)
for row in df_Tickers.to_records(index=False):
tickers_list = []
tickers_list.append(str(row[0]))
get_info(tickers_list)
con.commit()
con.close()

步驟6: 執行後，我們可以進行簡單的分析，比如找尋PE<10和收盤價低於200天均線的股票。

import sqlite3
import pandas as pd

con = sqlite3.connect('/content/drive/MyDrive/data/Stock.db')
df_data = pd.read_sql('select * from Tickers_Info where forwardPE < 10 and twoHundredDayAverage - previousClose >=0', con)
con.close()
df_data

FinanceDatabase

我們同場加映介紹另一種方法撈取清單。如果僅是要獲取美股清單，FinanceDatabase會是另一種方法，儘管無法獲取台股清單，但financedatabase裡有包含ETF的資訊，這些資訊是stocksymbol裡沒有的。

步驟1 - 安裝financedatabase。

!pip install financedatabase 

步驟2 - 獲取股票清單，並將Inde轉為欄位。

import financedatabase as fd
import pandas as pd

equities = fd.Equities()
df = equities.select()
df['symbol'] = df.index

步驟3 - 獲取ETF清單，並將Inde轉為欄位。

etfs = fd.ETFs()
df_etf = etfs.select()
df_etf ['symbol'] = df_etf.index

步驟4 - 將此兩個DataFrame輸入於SQLite中。

from google.colab import drive

drive.mount('/content/drive')

df = df.reset_index(drop=True)
df_etf = df_etf.reset_index(drop=True)

import sqlite3

con = sqlite3.connect('/content/drive/MyDrive/data/Stock.db')
df.to_sql('financedatabase_eq', con, if_exists='replace')
df_etf.to_sql('financedatabase_etf', con, if_exists='replace')

con.close()

我們會持續推出更多以實用情境為主的教學，讓你知道為什麼要學這些技巧，這些技巧能在職場和工作幫上您什麼忙。謝謝您花時間將此篇文章讀完，若覺得對您有幫助可以幫忙按個讚、分享來或是珍藏喔! 也歡迎Follow我們的Threads，持續都會有這類商業分析問題討論和生產力提升的點子喔!