（この記事は、2022/07/30に「PBIJP DAX Boot Camp #23」で使用しました。）
1.今回やること
DAXで株価の移動平均線（moving average, rolling average)の計算方法を検討します。
データは、ALPHA VANTAGEのFree Stock APIからマイクロソフト(MSFT)の株価を取得します。(https://www.alphavantage.co/)
使用するビジュアルは、カスタムビジュアルの Candlestick by OKViz を取得して使用します。
PowerQueryを使ってデータの取得
ALPHA VANTAGEからのデータの取得は、Power QueryのWeb.Contents関数を使って取得できます。ApiKeyは、パラメータMyApiKeyに登録しています。
Source = Json.Document(
Web.Contents(
"https://www.alphavantage.co",
[
RelativePath = "/query",
Query = [
function = "TIME_SERIES_DAILY",
symbol = "MSFT",
outputsize = "full",
apikey = MyApiKey
]
]
)
)
※参考 [Power BI] Power Query の Web.Contents 関数でREST APIを使う（仮）, spumoni, Qiita, 2022-05-21
Power BIの中でpandas-datareaderを使って取得する
このデータ取得については、Pythonのpandas-datareaderを使った例がよく見られます。Pythonコードを使ってデータを取得方法も試してみました。そのままPower BIに取り込もうとしたら、行インデックスにある日付が取り込めなかったので、列を追加して日付項目としています。
※参考 pandas_datareaderを使ってデータ取得をいろいろ試してみる, suto-takeshi, DevelopersIO, 2021-04-15
2.週移動平均線
以下のような株価データがあります。
ロウソク足チャートにすると、以下のようになります。
このままだと動きのトレンドがわかりにくいので、終値（close）を元に移動平均線を加えてみます。まず最初に週単位での移動平均線を作成してみます。
OneWeekMA =
VAR CurrentDate = MAX( '日付テーブル'[Date] )
VAR Duration = 7
VAR Result =
// ７日間のデータを平均する
CALCULATE(
AVERAGE( df[close] ),
'日付テーブル'[Date] > CurrentDate - Duration,
'日付テーブル'[Date] <= CurrentDate
)
VAR LastMarketDate = CALCULATE(MAX(df[date]), REMOVEFILTERS()) // データの最後の日付
RETURN
IF(CurrentDate <= LastMarketDate, Result) // データの最後の日付より大きい場合は値を返さない
※参考 Microsoft Power BI入門 BI使いになる! Excel脳からの脱却, 清水 優吾, 翔泳社, 2021-09-15
3.DATESINPERIODを使った週移動平均
DATESINPERIODを使って、以下のように移動平均を計算できます。
UseDatesinperiodMA =
VAR CurrentDate = MAX( '日付テーブル'[Date] )
VAR Duration = 7
VAR Period =
DATESINPERIOD( '日付テーブル'[Date], CurrentDate, -Duration, DAY )
VAR Result = CALCULATE( AVERAGE( df[close] ), Period )
VAR LastMarketDate = CALCULATE(MAX(df[date]), REMOVEFILTERS())
RETURN
IF(CurrentDate <= LastMarketDate, Result)
※参考 Computing rolling average in DAX, SQLBI, Youtube, 2021-04-06,
4.DATESBETWEENを使った週移動平均
UseDatesbetweenMA =
VAR CurrentDate = MAX( '日付テーブル'[Date] )
VAR Duration = 7
VAR Period =
DATESBETWEEN(
'日付テーブル'[Date],
CurrentDate - Duration + 1,
CurrentDate
)
VAR Result = CALCULATE( AVERAGE( df[close] ), Period )
VAR LastMarketDate = CALCULATE(MAX(df[date]), REMOVEFILTERS())
RETURN
IF(CurrentDate <= LastMarketDate, Result)
※参考 Power BI Desktopのクイックメジャーで生成したDAX使用移動平均集計式を紐解く, オトワ, Qiita, 2021-10-11
DATESBETWEEMを使ったものが前の結果と同じになるか確認しました。
同じ数字になるので、ビジュアルに表示させようとしましたが、表示されません。
折れ線グラフだと表示できました。
テーブルを見てわかるように、土日、祝日には値がありませんが、DAX式は値を返しています。DATESINPERIODも同様に値を返して描画されていたのですが、試しに以下のように休場日の場合は値を返さないように変更したところ、表示されるようになりました。
UseDatesbetweenMA2 =
VAR CurrentDate = LASTDATE( '日付テーブル'[Date] )
VAR Duration = 7
VAR Period =
DATESBETWEEN(
'日付テーブル'[Date],
DATEADD( CurrentDate, -Duration + 1, DAY ),
CurrentDate
)
VAR Result = CALCULATE( AVERAGE( df[close] ), Period )
VAR MarketOpenDate = MAX(df[date]) // データがあれば日付が入る
RETURN
IF(MarketOpenDate, Result) // データのない日は値を返さない
詳細な原因は不明です。
5.営業日で５日の移動平均線
先の移動平均線は、日付ベースで７日間の平均値を計算しています。しかし、土日や祝日にはデータがないため、平均を計算するときの分母は通常５ですが、祝日が入ると４や３になることもあります。データを調べてみると、土日以外の休みは以下のようになっていました。
そこで、常に分母が5になるようにDAXを書いてみます。
FiveDayMA =
VAR CurrentDate = MAX( '日付テーブル'[Date] )
VAR Duration = 5
VAR BeforeTable = FILTER( ALL( df ), df[date] <= CurrentDate )
VAR LatestFiveDays =
TOPN( Duration, BeforeTable, df[date], DESC ) // 日付で並び替えて上位を取得
VAR Result = AVERAGEX( LatestFiveDays, df[close] )
VAR MarketOpenDate = MAX(df[date]) // 開場日なら日付が入る
RETURN
IF(MarketOpenDate, Result)
週移動平均より若干の違いが出ており、カレンダーで見ると、5月30日(Memorial Day)、6月20日(Juneteenthの振替休日)、7月4日(Independence Day)が祝日になっています。
※参考 Rolling average with working days in DAX, SQLBI, Youtube, 2022-07-19
参考にしたSQLBIの動画では、RANKXを使用していますが、ここではTOPNを使用してみました。
豆知識
ちなみに、アメリカは基本的に3連休までがほとんどです。唯一、大統領就任式が1965年から4年ごとの1月20日で定められているため、28年毎に4連休が発生しています。次回の4連休は2037年の予定です。
また、アメリカの土日以外でマーケットが休む日は9日で、11月のBlack Fridayは半休です。一方、日本は19日と倍以上あります。加えて、取引時間は日本は９時から１５時までで、１１時３０分から１２時３０分まで昼休憩があるため取引時間は５時間となっている一方、アメリカは９時半から１６時の６時間３０分（サマータイムの場合は１時間ずれる）で、加えて８時から始まるプレマーケット、２０時までのアフターマーケットと、実質１２時間の取引が可能です。日本でも時間外取引（PTS取引）がありますが、あくまで私設取引です。