程式小專題 – 計算ATR #3 操控DataFrame

上一篇我建立了一個df_se的dataframe，長相大致如下。

	      日期    開盤   最高    最低    收盤	  漲跌    漲跌%
0	2019/11/21 37.270 37.460 36.6438 37.230	-0.030	-0.0805

裡面行的名稱有中文，感覺之後會有點麻煩，所以我想要原地把它們改成英文

df_se.rename(columns={'日期':'Date','開盤':'Open','最高':'High','最低':'Low','收盤':'Close','漲跌':'Change','漲跌%':'ChangePct'},inplace=True)

df_se.columns #確認它們已經被修改了
df_se.Close[:5] #用.Close屬性存取資料，來看看前三筆資料

Index(['Date', 'Open', 'High', 'Low', 'Close', 'Change', 'ChangePct'], dtype='object')

0    37.23
1    37.27
2    37.37
3    37.76
4    37.78
Name: Close, dtype: float64

1. 接著為了要計算 True Range (TR)，我需要創造一個新的 column 存放前一天的收盤價，把它叫做 Previous Close。 接著就可以計算每一日的 TR 了。

2. TR = max(Previous Close, Today’s High) – min(Previous Close, Today’s Low)

3. 然後利用滾動視窗離計算不含今天的21日平均 TR，把它叫做 Previous Average True Range (PreAvgTR)

4. 最後計算 ATR，用的是加權平均，PreAvgTR 占 20/21，今天的 True Range 占 1/20。

要注意的是這個計算方式是有問題的，因為在第一個ATR計算處來後，它就應該要取代PreAvgTR，來計算下一個ATR。我目前只要最新的ATR，所以就先不管它啦。

df_se['PreviousClose'] = df_se.Close.shift(1) #1

df_se['TrueRange']=df_se[['PreviousClose','High']].max(axis=1)-df_se[['PreviousClose','Low']].min(axis=1) #2

df_se['PreAvgTR'] = df_se['TrueRange'].rolling(window=21).mean().shift() #3

df_se['ATR'] = (df_se['PreAvgTR']*(21-1) + df_se['TrueRange'])/21 #4

df_se #看看長得咩樣

df_se[-1:]['ATR'] #我要最新的ATR

好啦，現在是moment of truth，我要把這些片段組起來，真的跑跑看。

          Date    Open    High  ...  TrueRange  PreAvgTR       ATR
0   2019/11/21  37.270  37.460  ...     0.8162       NaN       NaN
1   2019/11/22  37.020  37.570  ...     0.9900       NaN       NaN
2   2019/11/25  37.460  38.050  ...     0.9300       NaN       NaN
3   2019/11/26  37.380  38.090  ...     0.8700       NaN       NaN
4   2019/11/27  37.880  37.980  ...     0.6600       NaN       NaN
5   2019/11/29  37.720  37.720  ...     0.9650       NaN       NaN
6    2019/12/2  37.070  37.070  ...     1.2000       NaN       NaN
7    2019/12/3  35.620  37.020  ...     1.7200       NaN       NaN
8    2019/12/4  37.250  37.430  ...     0.6800       NaN       NaN
9    2019/12/5  37.540  38.300  ...     1.2200       NaN       NaN
10   2019/12/6  38.360  38.970  ...     1.4100       NaN       NaN
11   2019/12/9  37.885  37.995  ...     1.2650       NaN       NaN
12  2019/12/10  36.890  37.100  ...     0.6900       NaN       NaN
13  2019/12/11  36.650  36.800  ...     0.7900       NaN       NaN
14  2019/12/12  36.685  37.480  ...     1.2100       NaN       NaN
15  2019/12/13  37.290  37.675  ...     0.9950       NaN       NaN
16  2019/12/16  37.000  38.129  ...     1.3290       NaN       NaN
17  2019/12/17  38.430  38.480  ...     0.7700       NaN       NaN
18  2019/12/18  38.020  38.020  ...     1.2200       NaN       NaN
19  2019/12/19  37.700  39.200  ...     1.5950       NaN       NaN
20  2019/12/20  38.920  39.340  ...     1.2850       NaN       NaN
21  2019/12/23  38.700  39.360  ...     0.8400  1.076676  1.065406
22  2019/12/24  38.960  39.450  ...     0.6900  1.077810  1.059342

22    1.059342
Name: ATR, dtype: float64

東拼西湊的科學怪人真的可以動耶！話雖如此，其實程式又碰到了一些問題，出現了下面的警告：

SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame

Try using .loc[row_indexer,col_indexer] = value instead

看起來很是礙眼，不過我目前選擇先忽略它。

下面是完整的程式碼

import numpy as np
import pandas as pd 
import os

os.chdir('D:\\Project\\ATR')
 
df = pd.read_csv("SE 日線.csv", usecols = list(range(7)))

df_se = df.tail(23) 
df_se.index = range(23) 

df_se.rename(columns={'日期':'Date','開盤':'Open','最高':'High','最低':'Low','收盤':'Close','漲跌':'Change','漲跌%':'ChangePct'},inplace=True)

df_se['PreviousClose'] = df_se.Close.shift(1) 

df_se['TrueRange']=df_se[['PreviousClose','High']].max(axis=1)-df_se[['PreviousClose','Low']].min(axis=1) 

df_se['PreAvgTR'] = df_se['TrueRange'].rolling(window=21).mean().shift() 

df_se['ATR'] = (df_se['PreAvgTR']*(21-1) + df_se['TrueRange'])/21 

df_se[-1:]['ATR'] #查我們需要的ATR

接著我想要先解決要手動下載 csv 檔這件苦差事，所以我打算嘗試使用 TD Ameritrade 的 API 取得歷史報價。