2021年の前半にPower BIで投資に関連した分析をしていく旨の記事を書きました。しかし、それきりPower BIに関する記事は殆ど書いていません。
最近耳にするようになった「リスキリング」も含め、Power BIを本格的に覚えると何かと便利なのではないかと思い、改めてPower BIを用いて毎月記事にしている米国配当株の集計をしようとツールを作成しました。
ツールで期間と銘柄を選択すると、
- 保有株数
- 現在の評価損益の割合
- 月間での変化割合
- 年間での変化割合
- 投資額
- 現在の評価額
をダッシュボードのように表示。同時に選択された期間での変化や推移をグラフでビジュアル化しています。
さて、これくらいのツールを作成するのは簡単だろうと思ったものの、かなり苦戦しました。
欲しい情報もすんなり見つけることが出来なかったこともあり、将来同様の内容を実現させようと思った時に再現出来るのか自信がありません。
そこで、この記事では引っ掛かった箇所とその解決策を備忘録的に記載していきます。備忘録なので、Power BIで使われる独自の単語の説明は省略しています。
スライサーで選択した銘柄をテキストボックスで表示
銘柄選択は、スライサーで行います。スライサーのチェックボックスで選択すると、個別銘柄の集計をしてくれます。
何を選択したかは、スライサーを見ればわかるのですが、できれば画面に大きく表示されると分かり易い。ということでこの機能を実現させます。
1. メジャーを作成する
テキストボックスに、表示させたいスライサーの名前を入れるという考えなのですが、ひと手間必要です。
まずは、メジャーを作成します。今回は「Ticker_Name」という名前のメジャーで下記の通り作成しています。
Ticker_Name = SELECTEDVALUE(Compiled_Table[Ticker],”All”)
2. テキストボックスで作成したメジャーを呼び出す設定をする
テキストボックスに文字を打ち込む代わりに「＋値」をクリックして、下にプロンプトを表示させます。
そして、「この値の計算方法」に上記のメジャー名称を入力します。下の「値に名前を付ける」には、名前を付けることが可能です。
前月との比較を計算したい
保有している株式の評価額が前月からどれくらい変化しているのかを割合で表示。具体的には、当月が90ドルと、前月の100ドルから下落した場合は「-10%」と表示します。
エクセルであれば簡単に計算ができますが、Power BIでは複数の手順を踏みます。
1. 前月の評価額を計算するメジャーを作成
まず、前月の評価額を算出するためのメジャーを作成します。
「Compiled_Table」と呼ばれるデータテーブルには、データ取得時点の株数と株価が月ごとに格納されていて、これらを基に算出した評価額を「Current_Amount」というカラムに格納しています。
この「Current_Amount」を使い、前月の評価額を計算して「PrevM_Amount」とします。
日付は「YYYY-MM」というカラムに格納しているので、この日付を用いて PREVIOUSMONTH 関数で前月分を探し出します。そのDAX関数の式は以下の通り。
PrevM_Amount = CALCULATE(SUM(Compiled_Table[Current_Amount]),PREVIOUSMONTH(Compiled_Table[YYYY-MM]))
2. 前月と当月の比較計算をする
PrevM_Amountが出来たら、変化の割合を計算するメジャーを作成。これは小学校で習う式をDAX関数としてメジャーに書きます。
Change_MoM = [Current_Amount_Value] / [PrevM_Amount] – 1
3. カードに表示させる
いよいよカードに変化割合を表示させます。カードのフィールドに作成した「Change_MoM」を入れます。
しかし、これでは前月の計算が行われません。いつPower BIを開いても直近の月とその前月との比較を行いたいので、日付を格納している「YYYY-MM」にフィルターを掛けます。
そこで下記の様にフィルターの種類で「相対日付」を選択し、最近一ヶ月のデータを表示する様に設定します。
これで、Current_Amountで直近一ヶ月、PrevM_Amountで前月を計算し、比較が行えるようになります。
忘れがちなのが、設定したら右下の「フィルターを適用」をクリックする事です。
この作業で実現していること
上述の作業で実現したことは、前月との比較です。
例えば、今月ある銘柄を10株保有。その株価は100ドル。前月は5株保有で、その時点の株価は90ドルでした。評価額の比較なので 1000ドル / 450ドルとなり、122%の増加といった結果を表示させます。
前年同月との比較を計算したい
前月との対比が出来るのであれば、前年同月との比較もしたいと欲張るのは人情。
前月と同様に PREVIOUS・・・ という関数で解決するかと思いきや、かなり手こずりました。
1. 前年同月を正しく計算するために日付を直した列を追加
手こずった要因の一つを解決させるために、テーブルに一列カラムを追加します。
日付を格納している「YYYY-MM」カラムは、月の最終営業日の日付が入力されています。そのため、前年当月の比較結果が正しく計算されず、日付を暦の末日に統一します。
この作業は左のナビゲーションウィンドウで、データビューを選択してビューを変更した上で行い、下記の様にDAX関数を作成します。そして末日日付に統一した結果は、新たに追加した「EndMonth」というカラムに格納されます。
ここで使っているのは、EOMONTH関数と言い、月末の日付を取得するものです。今回は当月の月末を取得するので「0」としていますが、翌月の月末なら「1」を記載します。
EndMonth = EOMONTH(Compiled_Table[YYYY-MM],0)
2. 前年同月の評価額を計算するメジャーを作成
日付を新たに作成したら、「EndMonth」のデータを使って前年同月の評価額を計算します。
メジャーを作成する際に用いるDAX関数は前月と似ているのですが、PREVIOUSYEARの代わりにDATEADD関数で行っています。「-1,YEAR」で一年前を表しています。
PrevY_Amount = CALCULATE(SUM(Compiled_Table[Current_Amount]),DATEADD(Compiled_Table[EndMonth],-1,YEAR))
DATEADDではなく、SAMEPERIODLASTYEARという関数を使うと、
SAMEPERIODLASTYEAR(Compiled_Table[EndMonth])
という記述で済むと思ったのですが、何故か上手く動かず、タイムインテリジェンス関数として有名なDATEADDで解決させました。
余談ですが、PREVIOUSYEARでも、前年同月の計算は出来ませんでした。マイクロソフトの説明では、
現在のコンテキストで、日付列内の最後の日付に基づいて、前年のすべての日付の列を含むテーブルを返します。
と書かれていて、前年の年間で計算をしてしまう様です。
3. 前年と当年の比較計算をする
「PrevY_Amount」が出来たら前月の比較と同様に、変化割合のためのメジャーを作成します。
Change_YoY = [Current_Amount_Value] / [PrevY_Amount] – 1
4. カードに表示させる
最後の作業として前年同月としたいため、カードに表示させる際、相対日付のフィルターを設定します。ただし設定するのは新たに作成した「EndMonth」です。
スライサーと同期させない
特定の銘柄を選択して、その銘柄に関するデータを集計、表示が出来ました。
最後にもうひとつ引っ掛かったのは、画面に下部にある表の制御です。保有銘柄の一覧で銘柄を絞る必要はなく、常に全銘柄表示させたい。
やり方は複数あります。採択した解決策は「会社で複数人で使う場合には推奨はされない」という記述も見かけましたが、このツールを使うのは私だけなので問題なし。しかも比較的簡単。
相互作用を編集
スライサーが影響を与えているビジュアルから例外となるものを選択します。
今回は表に対して例外、つまりスライサーの影響を外したいので「相互作用を編集」機能を使って表を影響下から外します。
Power BIは一昔前のExcelか
Power BIを使うと、Excelで行っていた集計や作表が簡単に行えます。また、複数のデータソースもPower Queryで一つにまとめることが出来るため、VLOOKUP関数を駆使することからも解放されます。
とは言っても、データベースの考え方、独特の使用感や癖に加え、DAX関数という新たな関数を覚える必要があるため、とっつきにくい事は否定できません。
Power BIを使っていて思ったのは、一昔前にExcelを覚えた過程を思い出させること。Windows 95が世に出てから、Excelが一般に普及することになりますが、当時はExcelが使えるというだけで尊敬のまなざしを受けていました。
Power BIはデータ分析ツールとして今は新奇性があるものの、当時のExcelの様に時代と共に一般化していくのではないかと思います。
大量のデータだけではなく、定期的にデータを蓄積して集計や分析することがあるのであれば、一度の設定であとは自動的に行うことが出来るPower BIを覚えて損はないと言えそうです。