如何撈取台美股所有的股號的資料並儲存於SQLite? Part 2
之前在如何撈取台美股所有的股號的資料並儲存於SQLite? Part 1討論到如何使用stocksymbol撈取台美股股票代碼,在這篇我們繼續介紹如何針對我們撈取的股票代碼進行資料撈取。
You can check this for English version!
步驟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 ="""
CREATETABLE"Tickers_Info"
(
[Tickers]varchar(50)NOTNULL,
[previousClose] float,
[dividendYield] float,
[trailingPE] float,
[forwardPE] float,
[priceToSalesTrailing12Months] float,
[fiftyDayAverage] float,
[twoHundredDayAverage] float,
PRIMARYKEY(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
If you want to support Informula, you can buy us a coffee here :)
Thank you and more to come :)