如何撈取台美股所有的股號的資料並儲存於SQLite? Part 2

更新於 2023/08/16發佈於 2023/08/16閱讀時間約 7 分鐘

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