如何撈取台美股所有的股號的資料並儲存於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 :)