Modeling stock price and write to cloud database (01)

建立股票五線譜模型並寫入資料庫 (01)

Chris Lee

11 min readSep 29, 2020

五線譜投資術是一個很簡單的統計模型應用，甚至有人特別出書說明這個原理，簡單來說就是股市通常會遵循均值回歸狀態，透過這個趨勢我們可以建立一條線性回歸線，並且在這條線建立常態分佈的 4 條標準差平行線。

最簡單的看法就是先挑斜率>0的股票，然後找股價低於 2 倍標準差，如果該股票的標準差區間穩定，就會是個好買點。

整體的統計概念很簡單易懂，我們下面用 python 來實現五線譜模型，並且將股票計算結果寫入資料庫，每日更新提供我們隨時查閱。

開發環境：

macbook 本機 jupyter lab 開發程式
部署到 GCP F1-micro VM (免費額度)，並定期運行程式
GCP 的 cloud SQL，本篇使用 Mysql 儲存運算結果

抓取股票清單

因為我們想要計算股票現有的股價狀況，第一步要先建立自己的股票清單，可以自行維護一份，或是直接網路抓市場所有股票代號。

抓取清單的方法很多，可以去證交所之類的網站抓，網路上很多範例，下面簡單示範爬網過程，我們的資料來源是嗨投資的台股排行資訊，裡面有台股的清單、現價及交易狀況，我們抓前面幾個有計算過的欄位。

下面語法可以貼在 jupyter lab 或 jupyter notebook 運行，首先建立一個抓取 html 資訊的函數，記得建立 headers 避免被擋：

def get_url_data(URL):
    headers = {'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36'}
    res = requests.get(URL,headers = headers)
    print(res)
    soup = BeautifulSoup(res.text, 'html.parser')
    return soupsoup = get_url_data('https://histock.tw/stock/rank.aspx?p=all')

可以看到 html 資訊完整備抓下來，接下來可以用 chrome 的檢視原始碼，找尋 table 的所在位置，看到 id 為 CPHB1_gv 就是我們要抓的目標。

使用下面語法將資料抓取下來，我們要的元是 tr 的內容，第 [0] 元素為標題，所以實際我們要抓的資訊會從第 [1] 個元素開始：

tb = soup.select_one('#CPHB1_gv').select('tr')[1::]

接下來就是 for 迴圈塞內容囉：

df = pd.DataFrame()for row in tb:
    stock_no = row.select('td')[0].text
    stock_name = row.select('a')[0].text
    stock_price = row.select('span')[0].text
    stock_change = row.select('span')[1].text
    stock_percentage = row.select('span')[2].text
    stock_weekchange = row.select('span')[3].text
    df = df.append(pd.DataFrame({'STOCK_NO':stock_no,
                                 'STOCK_NAME':stock_name,
                                 'STOCK_PRICE':stock_price,
                                 'STOCK_CHANGE':stock_change,
                                 'STOCK_PERCENTAGE':stock_percentage,
                                 'STOCK_WEEKCHANGE':stock_weekchange
                                },index=[0]))df = df.reset_index(drop=True)

看一下成果，最重要的前兩個欄位有了就算完成囉：

整理一下我們的程式碼，包成函數如下，後面整理成 script 就可以直接調用了：

def get_stock_list(soup):
    tb = soup.select_one('#CPHB1_gv').select('tr')[1::]
    df = pd.DataFrame()
    for row in tb:
        stock_no = row.select('td')[0].text
        stock_name = row.select('a')[0].text
        stock_price = row.select('span')[0].text
        stock_change = row.select('span')[1].text
        stock_percentage = row.select('span')[2].text
        stock_weekchange = row.select('span')[3].text
        df = df.append(pd.DataFrame({'STOCK_NO':stock_no,
                                     'STOCK_NAME':stock_name,
                                     'STOCK_PRICE':stock_price,
                                     'STOCK_CHANGE':stock_change,
                                     'STOCK_PERCENTAGE':stock_percentage,
                                     'STOCK_WEEKCHANGE':stock_weekchange
                                    },index=[0]))
    df = df.reset_index(drop=True)
    return df

上傳雲端機器運行

完成後我們把程式丟到雲端 VM 運行看看，畢竟未來是要在 GCP 上自動跑，總要先跑跑看正不正常。

使用 GCP 的 Compute Engine 建立一台 f1-micro 規格的輕量機器，右邊通常會顯示前 720 小時免費的提示，後面我選擇 ubuntu 20 加上 50G 的空間，並開啟 http/https 通道、允許所有 Cloud API 的完整存取權。

進去後建立運行環境：

sudo apt update
sudo apt install python3 python3-dev python3-venv python3-pippython3 -m venv venv
source venv/bin/activate

然後建立專案資料夾：

mkdir stock
cd stock

使用 vim crawler.py 把剛剛的程式碼貼上，並整理如下：

記得相依的套件要安裝：

pip install bs4
pip install pandas
pip install requests

完成後運行腳本看看：

python crawler.py

有顯示以下結果代表在雲端機器正常運行囉：

資料寫入雲端資料庫

爬下來的資訊我們希望先留一份備著，並定期更新股票清單，所以我們要建一個資料庫來放。

資料庫建立方法很多，可以在定期跑程式 VM 建立，或是使用 Cloud Database 服務，我們選擇後者示範。主因是為避免排程機器壞掉後，影響到資料庫內容，所以請 GCP 代管是個不錯的選擇，而且有每個月 5G 的免費空間。

到 GCP 的 SQL 頁面，選擇建立 MySQL，記得建立密碼別讓隨便人登進去：

完成後可以在總覽頁面看到基本資訊，下面有說可以用 Cloud Shell 連進資料庫，這是個很快也很方便的方法。

我們前面有建一台 VM 來跑程式，因為要讓 python 直接寫入 SQL，所以要先建立雙方的連線。

將 VM 的外部 IP 複製，貼到 SQL 連線設定的公開 IP，新增一個連線授權，後續我們就可以在 VM 讀寫 SQL 了，如果有其他機器要連 SQL 也是相同做法。

如果想要從 VM terminal 直接操控 SQL 也是可以的，請回到 VM 安裝 Mysql-client，並且確認 SQL 允許不安全連線：

sudo apt install apt mysql-client

然後在 VM 打下面語法登入，SQL_IP 改成 SQL 總覽上面顯示的公開 IP 位置，輸入建立 SQL 時的設定密碼即可：

mysql -h [SQL_IP] -u root -p

一些資料庫的基本操作可以參考這裡，目前裡面應該是空空如也，我們要建立資料表來塞我們的資料，到 GCP 建立一個叫 stock 的資料庫。

接下來就可以來存資料了，資料庫的操作我們使用 python 來進行，首先規劃一下資料庫內容，一共是兩張表：

# 儲存股票清單
STOCK_LIST
├── STOCK_NO
├── STOCK_NAME
├── STOCK_PRICE
├── STOCK_CHANGE
├── STOCK_PERCENTAGE
└── STOCK_WEEKCHANGE# 記錄資料表更新時間
ETL_INFO
├── TABLE_NAME
└── ETL_DATE

python 要操控 Mysql 的話，必須先安裝下面套件：

pip install sqlalchemy
pip install pymysql
pip install configparser

然後我們在專案資料夾路徑下，建立資料庫的參數，讓我們可以用 python 直接讀取，使用 vim config.ini 儲存下面內容：

[MYSQL]
HOST=[SQL_IP]
DBNAME=stock
UNAME=root
PWD=[資料庫密碼]

這樣就能在 script 裡面讀取 Mysql 資訊，並建立連線 engine：

import configparserconfig = configparser.ConfigParser()
config.read("config.ini")
hostname = config['MYSQL']['HOST']
dbname = config['MYSQL']['DBNAME']
uname = config['MYSQL']['UNAME']
pwd = config['MYSQL']['PWD']engine = create_engine("mysql+pymysql://{user}:{pw}@{host}/{db}".format(host=hostname, db=dbname, user=uname, pw=pwd))

接下來寫一個寫入資料庫的函數，要下面幾個功能：