LoginSignup
0

More than 1 year has passed since last update.

[Power BI] DAXで株価のロウソク足チャートに移動平均線を描く

Posted at

(この記事は、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 を取得して使用します。
image.png

PowerQueryを使ってデータの取得

 ALPHA VANTAGEからのデータの取得は、Power QueryのWeb.Contents関数を使って取得できます。ApiKeyは、パラメータMyApiKeyに登録しています。

PowerQuery
    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に取り込もうとしたら、行インデックスにある日付が取り込めなかったので、列を追加して日付項目としています。
image.png

※参考 pandas_datareaderを使ってデータ取得をいろいろ試してみる, suto-takeshi, DevelopersIO, 2021-04-15

2.週移動平均線

 以下のような株価データがあります。

image.png

 ロウソク足チャートにすると、以下のようになります。

image.png

 このままだと動きのトレンドがわかりにくいので、終値(close)を元に移動平均線を加えてみます。まず最初に週単位での移動平均線を作成してみます。

週移動平均線
OneWeekMA = 
VAR CurrentDate = MAX( '日付テーブル'[Date] )
VAR Duration = 7
VAR Result =
    // 7日間のデータを平均する
    CALCULATE(
        AVERAGE( df[close] ),
        '日付テーブル'[Date] > CurrentDate - Duration,
        '日付テーブル'[Date] <= CurrentDate
    )
VAR LastMarketDate = CALCULATE(MAX(df[date]), REMOVEFILTERS()) // データの最後の日付
RETURN
    IF(CurrentDate <= LastMarketDate, Result)  // データの最後の日付より大きい場合は値を返さない

image.png

※参考 Microsoft Power BI入門 BI使いになる! Excel脳からの脱却, 清水 優吾, 翔泳社, 2021-09-15

3.DATESINPERIODを使った週移動平均

 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を使った週移動平均

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を使ったものが前の結果と同じになるか確認しました。
image.png

 同じ数字になるので、ビジュアルに表示させようとしましたが、表示されません。

image.png

 折れ線グラフだと表示できました。

image.png

 テーブルを見てわかるように、土日、祝日には値がありませんが、DAX式は値を返しています。DATESINPERIODも同様に値を返して描画されていたのですが、試しに以下のように休場日の場合は値を返さないように変更したところ、表示されるようになりました。

DATESBETWEENを使った週移動平均2
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日の移動平均線

 先の移動平均線は、日付ベースで7日間の平均値を計算しています。しかし、土日や祝日にはデータがないため、平均を計算するときの分母は通常5ですが、祝日が入ると4や3になることもあります。データを調べてみると、土日以外の休みは以下のようになっていました。
image.png
 そこで、常に分母が5になるようにDAXを書いてみます。

5営業日の移動平均
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) 

image.png

 週移動平均より若干の違いが出ており、カレンダーで見ると、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日と倍以上あります。加えて、取引時間は日本は9時から15時までで、11時30分から12時30分まで昼休憩があるため取引時間は5時間となっている一方、アメリカは9時半から16時の6時間30分(サマータイムの場合は1時間ずれる)で、加えて8時から始まるプレマーケット、20時までのアフターマーケットと、実質12時間の取引が可能です。日本でも時間外取引(PTS取引)がありますが、あくまで私設取引です。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0