之前在如何撈取台美股所有的股號的資料並儲存於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 = """
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
If you want to support Informula, you can buy us a coffee here :)
Thank you and more to come :)
