iT邦幫忙

2021 iThome 鐵人賽

DAY 29
0

本篇重點

  • 在SQLite的Table中,新增欄位
  • 透過pandas.DataFrame.rolling計算均線
  • 回寫資料至SQLite中

在SQLite的Table中,新增欄位

由於之前建立的日K線資料中,並沒有均線的相關欄位,所以必須要手動增加欄位。若要在現有的Table中增加欄位,可以使用DB Browser for SQLite。
開啟資料庫後,請先選擇所要調整的Table,再按「Modify Table」
https://ithelp.ithome.com.tw/upload/images/20211013/201408274vPkGCTZPt.png
接著按「Add」按鈕,輸入欄位名稱並選擇欄位類型
https://ithelp.ithome.com.tw/upload/images/20211013/20140827H7ViPGBoD0.png
這裡我新增「MA5」、「MA20」、「MA5_diff」、「MA20_diff」這4個欄位,並將欄位類型設為「REAL」,用來儲存下面要計算的均線結果,完成後按下「OK」
https://ithelp.ithome.com.tw/upload/images/20211013/20140827CZxzsUcVaO.png
因為這裡只是調整Table的結構,並沒實際寫入資料,所以完成後直接關閉程式即可。

透過pandas.DataFrame.rolling計算均線

接著計算均線資料,我們可以透過DataFrame.rolling
pandas.DataFrame.rolling來計算
程式範例如下:

df['MA5'] = df['Close'].rolling(window=5).mean().round(2) #依照收盤價計算均線,並取至小數點後2位
df['MA20'] = df['Close'].rolling(window=20).mean().round(2)
df['MA5_diff'] = df['MA5'].diff() #計算MA5差異
df['MA20_diff'] = df['MA20'].diff()

在第一行中,我們將Close收盤價的資料抓出來執行rolling,而rolling的window參數,就是看你要計算的均線而定;例如要計算MA5(5根K線收盤價的平均價格),window就設為5,MA5資料計算完後,再回存至df['MA5']中。
下面的df['MA5_diff'],就是依照df['MA5']中的資料去做差異計算,也就是與前一筆MA5的價格差異,之所以增加這個欄位,是為了方便之後判斷均線方向(向上或向下)使用。

計算均線資料的程式,也可以跟上一篇產生其它週期K線的程式一起執行並寫入

回寫資料至SQLite中

下面就以日K線資料為例,讀取K線資料、計算均線資料並更新至Table中。

import pandas as pd
import sqlite3
# 更新資料用的SQL語法
UPDATE_SQL = '''UPDATE stocks_1day_kbars
                SET MA5 = ?,
                    MA20 = ?,
                    MA5_diff = ?,
                    MA20_diff = ?
                WHERE Code = ? AND ts = ?'''
conn = sqlite3.connect('D:/shioaji.db') #建立資料庫連線
# conn = sqlite3.connect('C:/shioaji.db') #建立資料庫連線
cursor = conn.cursor()

# 傳入股票代碼,產生MA均線及差異
def generate_ma_of_day_kbar(stock_code):
    print(f'calc MA for stock:{stock_code}')
    # 僅取出ts跟Close欄位資料,並將ts欄位設為DataFrame的Index
    df = pd.read_sql(f'SELECT ts, Close FROM stocks_1day_kbars WHERE Code = {stock_code}', 
        conn, index_col='ts')
    #依照收盤價計算MA5,並取至小數點後2位
    df['MA5'] = df['Close'].rolling(window=5).mean().round(2)
    df['MA20'] = df['Close'].rolling(window=20).mean().round(2)
    df['MA5_diff'] = df['MA5'].diff() #計算MA5差異
    df['MA20_diff'] = df['MA20'].diff()
    # 歷遍df中的資料,並update至table中
    for index, row in df.iterrows():
        cursor.execute(UPDATE_SQL, (row['MA5'], row['MA20'], row['MA5_diff'], 
            row['MA20_diff'], stock_code, index))
        conn.commit()

codes = cursor.execute('SELECT DISTINCT code FROM stocks_1day_kbars').fetchall()
for code in codes:
    generate_ma_of_day_kbar(code[0])

conn.close() #關閉資料庫連線

因為計算均線只需要用Close的價格去做計算,所以在read_sql只有取ts跟Close這兩個欄位,並且將index_col設定為ts欄位。請注意這裡並沒有做parse_dates,第一是因為做parse_dates後在下面執行update動作時會出現type錯誤;另一個原因是rolling是以row為單位做計算,不像是resample是需要使用datetime做為計算單位。
計算完成後,再使用for loop的方式將計算的結果回寫回去資料庫。另一個要注意的地方是,在一開始抓所有股票的Code時,是先用fetchall將所有的資料先存入codes中,因為在同一個conn中只能有一個cursor,所以必須要先這麼做,後面的cursor在執行update時才不會有問題。


上一篇
Day 28 - 建立自己的K線資料庫 (下)
下一篇
Day 30 - 每日產生觀察名單
系列文
深入解析 Shioaji API30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言