アンクのFX日記

不労取得で悠々自適な生活を夢見る中年リーマンのブログ

不労取得をPowerBIで「見える化」してみた③

前回まででインストール編、データ準備編を投稿し今回は実際にさまざまな表・グラフの作り方をご紹介しようと思っていました。

前回までの記事はこちら↓

 

 

作成方法を説明するに際し、実際に2019年の収支データをもとに説明しようと思ったところ、今までの説明では不十分であることがわかったため、今回もデータ準備編②として投稿したいと思います。

そもそもなぜ不十分だとわかったかというと、2018年収支ではSBIFXの口座1つしかなかったため、グラフ作成元のデータも1つのみ考慮しておけばグラフは作成できてました。ですが今年からSBIFXだけでなくシストレ24とトライオートFXにも口座を開設したので、取引データとしては3つのデータを考慮する必要があるため、前回説明したデータ準備だけでは不十分であることが分かりました。

もし、当ブログをご覧の方で、口座が1つしかない方は今回の投稿は読み飛ばしてもらって結構ですが、複数口座をお持ちの方は、下記も合わせて設定しましょう。

まずは完成形をご覧ください。

f:id:ankuearn:20190119163315j:plain

これは1ページ目です。

上段左は週別損益の前年対比棒グラフです。
上段真ん中のゲージは目標達成進捗度です。最低を300万円、最高を600万円、目標を500万円で作成しています。 
上段右は損益累計の前年対比の棒グラフです。
下段左は口座別の日別損益を積み上げた棒グラフです。目安線として、最低取引額を12,500円(年間300万円)、目標取引額を21,000円(年間504万円)、上限取引額を30,000円(年間720万円)で破線を引いています。
下段右は口座別の日別損益を折れ線グラフで表しています。

f:id:ankuearn:20190120022308j:plain

2ページ目は各口座の明細データです。

SBIFX、シストレ24、トライオートFXそれぞれの口座の取引実績をマトリックスで表現しています。また3つの口座の合計値で前年対比を表現しています。

なかなか今回の出来に関しては自分では気に入っています。

3つの口座の取引データを繋げるために

完成形を見てもらえればわかるように、すべてのグラフや表は日や週ごとの取引をベースとしています。ですので、3つの口座を繋げるには約定日付をキーとして連結させる必要があります。3つの口座のうち、どれか1つでも「必ず毎日」取引しているのであれば、その口座をメインテーブルとして他の2つの 口座を繋げればいいのですが、どの口座も毎日取引が発生するとは限りません。そのため、1年365日毎日データが存在する「日付テーブル」を作成して、その日付テーブルをメインテーブルとして3つの口座を連結させることで、結果的に3つ口座を連動させます。

現状の確認

それではます現状がどうなっているかの確認をしておきます。

f:id:ankuearn:20190120175441j:plain

前回までの記事で、SBIFX、シストレ、トライオートの3つの口座が取り込まれているところから始めます。

f:id:ankuearn:20190120175651j:plain

左端の一番下のマークをクリックすると、上記のようになっていると思います。もしいずれかのテーブルが自動で連結されていた場合は、その連結を削除してください。

左端の一番上のマークをクリックして、元の画面に戻ります。

日付テーブルの作成

それでは日付テーブルを作成していきます。

f:id:ankuearn:20190120180052j:plain

「モデリング」タブの「新しいテーブル」をクリックします。すると、「テーブル=」

というふうに、出てきますので、それを消して下図のように書き直します。

f:id:ankuearn:20190120180415j:plain

日付テーブル = CALENDAR("開始日","終了日")

これで、「日付テーブル」という名前のテーブルが作成されます。また、開始日と終了日までの日付がデータとして作成されます。今回は前年対比のグラフなども作成するため、2018年1月1日から2019年12月31日までの日付データを作成します。

また、4半期ごと、月ごと、週ごとなどの分析もしたいので、前回説明した方法で「列の追加」を行います。ただ1つずつ設定するのは面倒臭いので、直接関数を書き込むことで、いっぺんに項目を追加することができます。PowerBIではDAX関数というで記述していきます。ネットで「PowrBI」「日付テーブル」で検索すれば色々出てきますが、以下に日付テーブルを作成するのに必要なDAX関数を貼り付けておきますので、コピペしてもらえれば、簡単に日付テーブルが作成できます。

↓ここから

日付テーブル =
VAR StartDate="2018/01/01"
VAR EndDate="2019/12/31"
VAR Today=UTCTODAY()+9/24
VAR BaseCalendar =CALENDAR (StartDate,EndDate)
RETURN

ADDCOLUMNS(
BaseCalendar,
"年_数値", YEAR ( [Date] ),
"年_yyyy年",FORMAT([Date],"yyyy年"),
"月_数値", MONTH ([Date]),
"月_MM月", FORMAT ([Date], "MM月" ),
"日_数値", DAY ([Date]),
"年月_数値", VALUE(FORMAT([Date], "yyyyMM" )),
"年月_yy年MM月", FORMAT ( [Date], "yy年MM月" ),
"年度四半期_数値", if(MONTH([Date])<4,1,if(MONTH([Date])<7,2,if(MONTH([Date])<10,3,4))),
"年度四半期_0Q", if(MONTH([Date])<4,"1Q",if(MONTH([Date])<7,"2Q",if(MONTH([Date])<10,"3Q","4Q"))),
"曜日番号日曜日から_数値", WEEKDAY ( [Date], 1 ),
"曜日_aaa", FORMAT([Date],"aaa"),
"日と曜日", FORMAT ( [Date], "DDaaa" ),
"月初めから第xx週",WEEKNUM ( [Date], 1 ) - weeknum ( date(year([Date]),month([date]), "01") , 1) + 1 )

↑ここまで

なお、曜日に関しては複数年のデータを扱うとき、去年と今年で同じ日でも曜日は異なり、集計時にデータがバラバラになるので、下記では曜日関係の3行ははずしています。これをコピペすると、

f:id:ankuearn:20190120181911j:plain

上記でエンターキーを押下すると日付テーブルが出来上がります。

では実際に出来上がった日付テーブルの中身を見てみましょう。

f:id:ankuearn:20190120182053j:plain

左端の真ん中のマークをクリックするとデータの中身を確認することができます。右端に日付テーブルが出来上がっていると思いますので、「日付テーブル」をクリックします。すると日付テーブルの中身が上図のように確認できると思います。

各テーブル同士の連結

日付テーブルが出来上がったので、SBIFX、シストレ、トライオートのテーブルを連結します。左端一番下のマークをクリックします。

f:id:ankuearn:20190120182625j:plain

日付テーブルの「Date」項目をドラッグしSBIFXの「日付」項目のうえにドロップします。すると日付テーブル側に「1」、SBIFX側が「*」で線で繋がります。これは「1対多」で繋がったということです。

どうように他のテーブルも連結させていきます。

f:id:ankuearn:20190120183153j:plain

これで、日付テーブルをメインテーブルとした、3口座のテーブルの連結は完了です。

日付テーブルの設定

作成した日付テーブルに対して「これは日付テーブルですよ」と明示してあげる必要があります。

f:id:ankuearn:20190120183442j:plain

「モデリング」タブの「日付テーブルとしてマークする」をクリックします。

すると下図のようにどの項目を日付として定義するかという画面が表示されますので「Date」項目を選択します。

f:id:ankuearn:20190120183617j:plain

「OK」をクリックすれば、日付テーブルの設定は完了です。

これで3つの口座のデータを利用してグラフや表を作成することができます。

必ず「日付テーブル」が必要というわけではありませんが、基本的に「タイムインテリジェンス」:時系列での集計や分析をするのに日付テーブルがあれば便利ですので、作成しておくことをオススメします。

© 2019 anku.