在此系列文章，我們將討論如何在Google Colab的環境下，撈取所有台美股的股票代碼，並獲取相關細節，最後將獲取的資訊存取於SQLite資料庫中。
在此篇文章中，我們先來探討如何獲取所有的股票代碼。
步驟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()
我們獲取一萬多筆資料，在接下來的文章，我們將會對此資料進行像是PE, moving average等相關資料的撈取。
Thank you and Enjoy it! You can check this for English version!
Enjoy it! If you want to support Informula, you can buy us a coffee here :)
Thank you and more to come :)
