如何撈取台美股所有的股號的資料並儲存於SQLite? Part 1
IPFS
步驟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等相關資料的撈取。
Enjoy it! If you want to support Informula, you can buy us a coffee here :)
Thank you and more to come :)
喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!