Excel Power Queryとメジャー(DAX)でSBI証券の投資信託の損益推移計算が遅い
【更新情報】本記事は古いです。更新した記事のリンクは次の通りです。[2025/01/15]
（SBI証券の投資信託のCSVファイルから損益推移グラフを作成 : https://bigarrow.tea-nifty.com/blog/2025/01/post-d12b13.html）
SBI証券の投資信託では次の2つのCSVファイルをダウンロードできます。
(1)取引履歴(2年間)、(2)基準価格推移
これをExcelのワークシートに手作業で貼り付けて損益を計算し推移を見ていたのですが、面倒です。
そこで、モダンExcelで集計してみました。集計はできますが遅いです。約9銘柄2口座6年間の毎日のデータで、すべて更新をするとTradeクエリが「他のデータ モデル クエリを待機してい... 」表示になり、約11分後に「51,208行が読み込まれました」と表示されます。更にデータが多ければメモリ不足かタイムオーバー(60分)で止まります。
使用PCはi9-12900、32GB(DDR5-4800)です。CPU使用率は約10%で、ディスクアクセスは殆どありません。
バックグランドで更新する=OFF、高速なデータ読込を有効にする=ON、プライバシーレベル=なし、は速度と関係ないような気がします。
メジャーをピボットテーブルに展開しなければ、短時間で更新が終わります。なので、メジャーの式が悪いのだと思うのですが、速い式が分かりません。もし、分かる方がいればコメントいただけると嬉しいです。
■ 更新が遅いトータルリターン推移集計ファイル：ダウンロード - sbi.zip
C:\Users\Public\Documentに展開してください。
Pythonを使っている人もいるようですが、Pythonの知識がありません。(メジャー/DAXも理解できていないのですが...)
https://note.com/fuyoda/n/na2028f13274f
Pythonが分かる人は、Pythonの方が高速だと思います。
色々な側面で集計を切り替えたい場合は、モダンExcelが便利そうなのですが、速度が欠点です。
Power QueryでCSVのマージまでやってピボット展開後、DAXを使用せずワークシートで累計と損益計算する手もあるかと思います。
CSVの取り込みは面倒ですが、VSTACKとXLOOKUPとスピルなどを使用して全部レガシーExcelでやると速いと思います。
1. 概要
1) CSVのダウンロード：取引履歴と基準価格推移のCSVファイルを、日付が重ならないように手作業でダウンロードし、それぞれ「Trade」「Unit」フォルダに格納します。
毎日買いをしている銘柄の基準価格は、取引履歴の基準価格を利用するので、基準価格推移のダウンロードは不要です。
(基準価格推移は日付が重なっても大丈夫ですが、取引履歴の日付が重なると売買が重複して集計されます。)
(Pythonなどは分からないので、自動化するとすればPower Automate desktop(RPA)なのですが、UI要素の認識に失敗することが多い印象があり試していません。検索すると下記のような記事が見つかりました。https://guchoku.club/power-automate-desktop/ )
2) CSVデータの集計：集計Excelファイルの[すべて更新]をクリックすると、Power Queryで各フォルダのCSVファイルを取り込み、取引が無い日の単価情報をフィルで穴埋めして、メジャー(DAX)で累計と損益を計算して推移グラフを表示します。スライサーで銘柄単独の損益に切り替えることができます。
2. Power Queryの作成
Power Queryは殆どクリック操作でコードを作成するので、下記コードのステップ名を参考にクリック操作で作るのが良いと思います。
エラーを修正できる人は、下記コードを詳細エディタに貼り付け、フォルダや銘柄の違いによるエラーのステップを修正するか作り直してください。銘柄を追加削除するときも同様です。
1) TransFund：銘柄名の変換テーブル：接続専用
SBI証券のCSVファイルの銘柄名は、取引履歴と基準価格推移で異なるものがあります。また、途中で名前が変わることがあります。そこで、銘柄名の変換テーブルを手作りし、Power Queryに取り込みます。他のクエリからマージして銘柄名を統一します。速度を優先し集計Excelファイルに作成します。
■ 手順1)
(1) 集計ブックに上の画面のような変換テーブルを作成し、[テーブル デザイン]でテーブル名を"TransFund"に変更します。
銘柄名はダウンロードしたCSVファイルをExcelで読み込み、重複の削除かUNIQUE()関数で抽出してください。
取引履歴と基準価格推移の銘柄名に差が無い場合は、元銘柄と銘柄を同じにしてください。後のクエリのマージのステップを削除した方が高速になりますが、途中で銘柄名が変わることがあるので、残しておいた方が良いと思います。
(2) [データ]-[テーブルまたは範囲から]でクエリを作成します。
(3) [閉じて読み込む]-[閉じて次に読み込む...]-[接続の作成のみ]-[OK]。
let
ソース = Excel.CurrentWorkbook(){[Name="TransFund"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"元銘柄", type text}, {"銘柄", type text}})
in
変更された型
2) TradeUnit：取引履歴から基準価格を取得するクエリ：接続専用
取引履歴から[日][銘柄][単価]のリストを作成します。
TransFundをマージして古い柄名を新しい銘柄名に置換しています。
■ 手順2)
(1) [データ]-[データの取得]-[Power Queryエディタの起動...]-[新しいソース]-[その他のソース]-[空のクエリ]。
(2) {クエリの設定}-{プロパティ}-{名前}でクエリ名を"TradeUnit"に変更。
(3) [ホーム]-[詳細エディタ]で下記コードを貼り付け、ソースのパスを修正後、[完了]。
(4) エラーがあれば、エラーステップの前に新しいステップを作り直してエラーステップを削除するか、数式欄のM言語の数式を修正する。
(5) [閉じて読み込む]-[閉じて次に読み込む...]-[接続の作成のみ]-[OK]。
let
ソース = Folder.Files("C:\Users\Public\Documents\SBI\Trade"),
Content = Binary.Combine(ソース[Content]),
#"インポートされた CSV" = Csv.Document(Content,[Delimiter=",", Columns=14, Encoding=932, QuoteStyle=QuoteStyle.None]),
削除された他の列_残日銘単 = Table.SelectColumns(#"インポートされた CSV",{"Column1", "Column2", "Column10"}),
フィルターされた行_除空単 = Table.SelectRows(削除された他の列_残日銘単, each ([Column10] <> "" and [Column10] <> "約定単価")),
名前が変更された列_日銘1単 = Table.RenameColumns(フィルターされた行_除空単,{{"Column1", "日"}, {"Column2", "銘柄1"}, {"Column10", "単価"}}),
変更された型_日単 = Table.TransformColumnTypes(名前が変更された列_日銘1単,{{"日", type date}, {"単価", type number}}),
並べ替えられた行_日 = Table.Sort(変更された型_日単,{{"日", Order.Ascending}}),
マージされたクエリ数_TransFund = Table.NestedJoin(並べ替えられた行_日, {"銘柄1"}, TransFund, {"元銘柄"}, "TransFund", JoinKind.LeftOuter),
#"展開された TransFund" = Table.ExpandTableColumn(マージされたクエリ数_TransFund, "TransFund", {"銘柄"}, {"銘柄"}),
削除された列_銘1 = Table.RemoveColumns(#"展開された TransFund",{"銘柄1"})
in
削除された列_銘1
3) Unitフォルダのファイルを変換するヘルパークエリ：2. 4)と5)を作成するときに自動的に作成されます。
サンプルファイル:= ソース{0}[Content]
パラメータ1(サンプルファイル)
#"サンプル ファイル" meta [IsParameterQuery=true, BinaryIdentifier=#"サンプル ファイル", Type="Binary", IsParameterQueryRequired=true]
fxファイルの変換:function(パラメータ1 as any) as any
サンプルファイルの変換
let
ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=4, Encoding=932, QuoteStyle=QuoteStyle.None])
in
ソース
4) UnitFund：基準価格のCSVファイルからファイル名と銘柄名のリストを作成するクエリ：接続専用
基準価格のCSVファイルには銘柄の列が無いので、これを後のUnitクエリでマージして銘柄名を作成するためのものです。
■ 手順4)
(1) [データ]-[データの取得]-[ファイルから]-[フォルダから]で"Unit"フォルダを選択し、[開く]。
(2) [結合]-[データの結合と変換]。(ヘルパークエリも生成されます。)
(3) {クエリの設定}-{プロパティ}-{名前}でクエリ名を"UnitFund"に変更。
(4) [ホーム]-[詳細エディタ]で下記コードの"展開されたテーブル列1"の次の行以降を貼り付け、[完了]。
(5) エラーがあれば、エラーステップの前に新しいステップを作り直してエラーステップを削除するか、数式欄のM言語の数式を修正する。
(6) [閉じて読み込む]-[閉じて次に読み込む...]-[接続の作成のみ]-[OK]。
let
ソース = Folder.Files("C:\Users\Public\Documents\SBI\Unit"),
カスタム関数の呼び出し1 = Table.AddColumn(ソース, "ファイルの変換", each ファイルの変換([Content])),
削除された他の列1 = Table.SelectColumns(カスタム関数の呼び出し1, {"Name", "ファイルの変換"}),
展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
フィルターされた行_ファンド = Table.SelectRows(展開されたテーブル列1, each ([Column1] = "ファンド名")),
削除された他の列_残csv銘 = Table.SelectColumns(フィルターされた行_ファンド,{"Name", "Column2"}),
変更された型_銘柄 = Table.TransformColumnTypes(削除された他の列_残csv銘,{{"Column2", type text}}),
マージされたクエリ数_TransFund = Table.NestedJoin(変更された型_銘柄, {"Column2"}, TransFund, {"元銘柄"}, "TransFund", JoinKind.LeftOuter),
#"展開された TransFund" = Table.ExpandTableColumn(マージされたクエリ数_TransFund, "TransFund", {"銘柄"}, {"銘柄"}),
削除された列_Column2 = Table.RemoveColumns(#"展開された TransFund",{"Column2"})
in
削除された列_Column2
5) Unit：基準価格のリストを作成するクエリ：接続専用
基準価格のCSVファイルに上記2)4)のクエリの結果をマージして、基準価格のリストを作成します。
本来は[預り]は不要だと思うのですが、後の4)で完全外部(両方の行のすべて)マージすると銘柄がnullになるので、入れています。何か間違いがあると思うのですが、間違いを発見できていません。
フィルの「下方へコピー済み」の部分は、銘柄が異なるとエラーになるので、フィルをクリックして作り直してください。
「ピボット」「下方向へコピー済」「ピボット解除」で取引無の日の単価のnull値を上のレコード値で埋めています。(DAXでの穴埋め方法が分からずQueryで行いました。DAXでできればPower Pivotでリレーションできるのですが？ 分かる人がいればコメントをください。)
■ 手順5)
(1) [データ]-[データの取得]-[ファイルから]-[フォルダから]で"Unit"フォルダを選択し、[開く]。
(2) [結合]-[データの結合と変換]。(ヘルパークエリも生成されます。)
(3) {クエリの設定}-{プロパティ}-{名前}でクエリ名を"Unit"に変更。
(4) [ホーム]-[詳細エディタ]で下記コードの"展開されたテーブル列1"の次の行以降を貼り付け、[完了]。
(5) エラーがあれば、エラーステップの前に新しいステップを作り直してエラーステップを削除するか、数式欄のM言語の数式を修正する。
"下方向へコピー済み_銘_単"は作り直した方が楽です。１つ上のステップでShiftかCtrlを押しながら銘柄列を全て選択し、[変換]-[フィル]-[下へ]でステップを作成し、エラーステップを削除します。
(6) [閉じて読み込む]-[閉じて次に読み込む...]-[接続の作成のみ]-[OK]。
let
ソース = Folder.Files("C:\Users\Public\Documents\SBI\Unit"),
カスタム関数の呼び出し1 = Table.AddColumn(ソース, "ファイルの変換", each #"ファイルの変換"([Content])),
削除された他の列1 = Table.SelectColumns(カスタム関数の呼び出し1, {"Name", "ファイルの変換"}),
展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(#"ファイルの変換"(#"サンプル ファイル"))),
名前が変更された列_日単 = Table.RenameColumns(展開されたテーブル列1,{{"Column1", "日"}, {"Column2", "単価"}}),
削除された他の列_残csv銘単 = Table.SelectColumns(名前が変更された列_日単,{"Name", "日", "単価"}),
フィルターされた行_除ヘッダ = Table.SelectRows(削除された他の列_残csv銘単, each ([日] <> "" and [日] <> "ファンド名" and [日] <> "基準価額一覧" and [日] <> "年月日" and [日] <> "検索期間")),
変更された型_日単 = Table.TransformColumnTypes(フィルターされた行_除ヘッダ,{{"日", type date}, {"単価", type number}}),
マージされたクエリ数 = Table.NestedJoin(変更された型_日単, {"Name"}, UnitFund, {"Name"}, "UnitFund", JoinKind.LeftOuter),
#"展開された UnitFund" = Table.ExpandTableColumn(マージされたクエリ数, "UnitFund", {"銘柄"}, {"銘柄"}),
削除された列_csv = Table.RemoveColumns(#"展開された UnitFund",{"Name"}),
並べ替えられた列_日銘単 = Table.ReorderColumns(削除された列_csv,{"日", "銘柄", "単価"}),
追加されたクエリ_TradeUnit = Table.Combine({並べ替えられた列_日銘単, TradeUnit}),
削除された重複_日銘 = Table.Distinct(追加されたクエリ_TradeUnit, {"日", "銘柄"}),
ピボットされた列_銘_単 = Table.Pivot(削除された重複_日銘, List.Distinct(削除された重複_日銘[銘柄]), "銘柄", "単価"),
並べ替えられた行_日 = Table.Sort(ピボットされた列_銘_単,{{"日", Order.Ascending}}),
下方向へコピー済み_銘_単 = Table.FillDown(並べ替えられた行_日,{"ｅＭＡＸＩＳ ＮＡＳＤＡＱ１００インデックス", "ｉＦｒｅｅレバレッジ ＮＡＳＤＡＱ１００", "ＳＢＩ・Ｖ・Ｓ＆Ｐ５００インデックス・ファンド", "ｅＭＡＸＩＳ Ｓｌｉｍ 全世界株式（オール・カントリー）", "＜購入・換金手数料なし＞ニッセイ外国株式インデックスファンド", "ｅＭＡＸＩＳ Ｓｌｉｍ 新興国株式インデックス", "＜購入・換金手数料なし＞ニッセイ日経平均インデックスファンド", "＜購入・換金手数料なし＞ニッセイＮＡＳＤＡＱ１００インデ", "＜購入・換金手数料なし＞ニッセイＳＯＸ指数インデ米国半導体株", "ｉＦｒｅｅＮＥＸＴ ＦＡＮＧ＋インデックス", "たわらノーロード フォーカス ＡＩ", "ＳＢＩ・ｉシェアーズ・インド株式インデックス・ファンド"}),
ピボット解除された他の列_日 = Table.UnpivotOtherColumns(下方向へコピー済み_銘_単, {"日"}, "銘柄", "単価"),
追加されたカスタム_旧NISA = Table.AddColumn(ピボット解除された他の列_日, "旧NISA", each "旧NISA"),
追加されたカスタム_特定 = Table.AddColumn(追加されたカスタム_旧NISA, "特定/一般", each "特定/一般"),
追加されたカスタム_NISA成 = Table.AddColumn(追加されたカスタム_特定, "NISA(成)", each "NISA(成)"),
追加されたカスタム_NISAつ = Table.AddColumn(追加されたカスタム_NISA成, "NISA(つ)", each "NISA(つ)"),
ピボット解除された他の列_預り = Table.UnpivotOtherColumns(追加されたカスタム_NISAつ, {"日", "銘柄", "単価"}, "預り", "値"),
削除された列_値 = Table.RemoveColumns(ピボット解除された他の列_預り,{"値"}),
追加されたカスタム_日銘口 = Table.AddColumn(削除された列_値, "日銘口", each Date.ToText([日]) & "_" & [銘柄] & "_" & [預り]),
変更された型_日銘口 = Table.TransformColumnTypes(追加されたカスタム_日銘口,{{"日銘口", type text}})
in
変更された型_日銘口
6) Trade：取引履歴のCSVに5)で作成した基準価格のリストをマージするクエリ：ピボットテーブル、データモデル
この結果をピボットテーブルとデータモデルに読み込みます。データモデルにする理由は、累計と損益計算にメジャーを使用するためです。
取引履歴のCSVの預りには、頭とお尻にスペースが入っているのでText.Trimで取り除きます。
取引履歴には取引が無い日のレコードが無いので、Unitのマージでは「完全外部(両方の行全て)」か「右外部(2番目の行全て、および最初の行のうち一...」を選びます。
（Power Pivotのリレーションも試みたのですが、取引が無い日の単価や累計などの穴埋め方法が分からず、Power Queryでマージしています。）
■ 手順6)
(1) [データ]-[データの取得]-[Power Queryエディタの起動...]-[新しいソース]-[その他のソース]-[空のクエリ]。
(2) {クエリの設定}-{プロパティ}-{名前}でクエリ名を"Trade"に変更。
(3) [ホーム]-[詳細エディタ]で下記コードを貼り付け、ソースのパスを修正後、[完了]。
(4) エラーがあれば、エラーステップの前に新しいステップを作り直してエラーステップを削除するか、数式欄のM言語の数式を修正する。
(5) [閉じて読み込む]-[閉じて次に読み込む...]で{ピボットテーブル レポート}と{このデータをデータ モデルに追加する}を選び[OK]。
let
ソース = Folder.Files("C\SBI証券\Trade"),
Content = Binary.Combine(ソース[Content]),
#"インポートされた CSV" = Csv.Document(Content,[Delimiter=",", Columns=14, Encoding=932, QuoteStyle=QuoteStyle.None]),
削除された他の列_残日銘口数金 = Table.SelectColumns(#"インポートされた CSV",{"Column1", "Column2", "Column7", "Column9", "Column14"}),
#"トリム テキスト_預り" = Table.TransformColumns(削除された他の列_残日銘口数金,{{"Column7", Text.Trim, type text}}),
フィルターされた行_除空 = Table.SelectRows(#"トリム テキスト_預り", each [Column7] <> null and [Column7] <> ""),
昇格されたヘッダー数 = Table.PromoteHeaders(フィルターされた行_除空, [PromoteAllScalars=true]),
フィルターされた行_除預り = Table.SelectRows(昇格されたヘッダー数, each ([預り] <> "預り")),
フィルターされた行_預り = Table.SelectRows(フィルターされた行_除預り, each [預り] = "旧NISA" or [預り] = "特定/一般" or [預り] = "NISA(成)" or [預り] = "NISA(つ)"),
変更された型_数金 = Table.TransformColumnTypes(フィルターされた行_預り,{{"約定日", type date}, {"銘柄", type text}, {"約定数量", Int64.Type}, {"受渡金額/決済損益", type number}}),
グループ化された行_日銘柄預り = Table.Group(変更された型_数金, {"約定日", "銘柄", "預り"}, {{"約定数量", each List.Sum([約定数量]), type nullable number}, {"売買金額", each List.Sum([#"受渡金額/決済損益"]), type nullable number}}),
マージされたクエリ数_TransFund = Table.NestedJoin(グループ化された行_日銘柄預り, {"銘柄"}, TransFund, {"元銘柄"}, "TransFund", JoinKind.LeftOuter),
#"展開された TransFund" = Table.ExpandTableColumn(マージされたクエリ数_TransFund, "TransFund", {"銘柄"}, {"銘柄.1"}),
削除された列_銘柄 = Table.RemoveColumns(#"展開された TransFund",{"銘柄"}),
追加されたカスタム_日銘口 = Table.AddColumn(削除された列_銘柄, "日銘口", each Date.ToText([約定日]) & "_" & [銘柄.1] & "_" & [預り]),
変更された型_日銘口 = Table.TransformColumnTypes(追加されたカスタム_日銘口,{{"日銘口", type text}}),
マージされたクエリ数_Unit = Table.NestedJoin(変更された型_日銘口, {"日銘口"}, Unit, {"日銘口"}, "Unit", JoinKind.FullOuter),
#"展開された Unit" = Table.ExpandTableColumn(マージされたクエリ数_Unit, "Unit", {"単価", "日", "銘柄", "預り"}, {"単価", "日", "銘柄", "預り.1"}),
削除された列_約定日銘柄預り = Table.RemoveColumns(#"展開された Unit",{"約定日", "銘柄.1", "預り", "日銘口"}),
名前が変更された列_日銘柄預り = Table.RenameColumns(削除された列_約定日銘柄預り,{{"預り.1", "預り"}}),
置き換えられた値_数金null0 = Table.ReplaceValue(名前が変更された列_日銘柄預り,null,0,Replacer.ReplaceValue,{"約定数量", "売買金額"}),
並べ替えられた列 = Table.ReorderColumns(置き換えられた値_数金null0,{"日", "銘柄", "預り", "約定数量", "単価", "売買金額"})
in
並べ替えられた列
3. メジャーの作成
以下のメジャーをTradeに作成します。
メジャーを入れるとデータの更新が非常に遅くなります。特に「M評価額1」以降は数10分以上掛かり、DAX式が長いとメモリ不足で止まります。DAX式が悪いのだと思いますが、原因不明です。アドバイスいただけると嬉しいです。
（保守性は悪化しますが、データモデルに入れずにピボットテーブルから単価・約定数量・売買金額をGETPIVOTDATA()で参照し、ワークシート関数で累計と損益を計算したほうがデータ更新が速いと思われます。）
（累計から損益までPower Queryでやった方が速いかもしれません? しかし、M言語の累計がややこしそうで、銘柄が変わると変更が面倒そうです。https://sakatakablog.com/excel/1627/。）
（計算列での累計はメジャーとほぼ同じ計算式なので速くならない気がします。また、ピボットテーブルの小計、総計を正しく表示しようとすると、メジャーが必要になります。）
■ 手順3.
(1) 2.6)(5)で作成したピボットテーブルのセルを選択。
(2) [ピボットテーブルのフィールド]で[Trade]を右クリック。
(3) [メジャーの追加]で以下のメジャーを追加する。
:=の前がメジャーの名前です。
:=以降が数式です。数式を貼り付けたら[DAX式を確認]でエラーが無いことを確認してください。
カテゴリで[数値]を選択し、書式を設定し、[OK]。
M最新単価:=CALCULATE(MAX('Trade'[単価]),
VALUES('Trade'[銘柄]),VALUES('Trade'[預り]),
FILTER(ALLSELECTED('Trade'),'Trade'[日]=MAX('Trade'[日])))
（'Trade'[約定単価]は小計、総計に合計や平均などしか選べません。なので[M最新単価]で小計、総計に最新単価を表示します。）
（銘柄が異なる合計は意味が無いので、HASONEVALUE('Trade'[銘柄')で合計を計算しないようにすると僅かに速くなるかもしれません。）
M累計数:=CALCULATE(SUM('Trade'[約定数量]),
VALUES('Trade'[銘柄]),VALUES('Trade'[預り]),
FILTER(ALLSELECTED('Trade'),'Trade'[日]<=MAX('Trade'[日])))
（このDAXの累計は日毎に頭から加算するので、同じ計算を繰り返していることになります。同じ銘柄と預りの前のレコードの累計＋当日値にする方法が分かりません。https://qiita.com/akihiro_suto/items/8e840d9b65bb360bf765。https://www.linkedin.com/pulse/offset-its-usage-calculation-groups-%C5%A1t%C4%9Bp%C3%A1n-re%C5%A1l。タイムインテリジェンスを使用すると取引が無い120日/年も無駄な行が増えます。更に年毎の成績を求めたければ、年の計算列を作成して、年のフィルタを追加すれば良さそうですが、メモリ不足かタイムオーバーするかも知れません。）
（銘柄が異なる合計は意味が無いので、HASONEVALUE('Trade'[銘柄')で合計を計算しないようにすると僅かに速くなるかもしれません。）
M累計売買金:=CALCULATE(SUM('Trade'[売買金額]),
VALUES('Trade'[銘柄]),VALUES('Trade'[預り]),
FILTER(ALLSELECTED('Trade'),'Trade'[日]<=MAX('Trade'[日])))
M評価額1:=VAR Cnt = HASONEVALUE('Trade'[銘柄])
VAR Eva = [M最新単価]*[M累計数]/10000
VAR EvaSum=CALCULATE(SUMX('Trade',[M最新単価]*[M累計数]/10000),FILTER(ALLSELECTED('Trade'),'Trade'[日]=MAX('Trade'[日])))
RETURN IF(Cnt,Eva,EvaSum)
【問題】M評価額1の日毎の合計=EvaSumが間違っています。この式がピボットテーブルの合計で何を算出したのか理解できてません。正しい式を教えていただけると嬉しいです。
対処療法として下のM評価額を追加しました。
（間違いで使用できないのでEvaSumを外しても問題ないです。僅かに速くなりますが大差ないです。）
M評価額:=VAR Cnt = HASONEVALUE('Trade'[日])
VAR Eva = SUMX('Trade',[M評価額1])
VAR EvaSum=CALCULATE(SUMX('Trade',[M評価額1]),VALUES('Trade'[銘柄]),VALUES('Trade'[預り]),FILTER(ALLSELECTED('Trade'),'Trade'[日]=MAX('Trade'[日])))
RETURN IF(Cnt,Eva,EvaSum)
M損益:=[M評価額]-[M累計売買金]
M損益率:=DIVIDE([M損益],[M累計売買金])
ご参考(計算列の累計DAX)
C累計数=VAR Fund='Trade'[銘柄]
VAR Acnt='Trade'[預り]
VAR Latest='Trade'[日]
RETURN CALCULATE(SUM('Trade'[約定数量]),FILTER('Trade','Trade'[日]<=Latest && 'Trade'[銘柄]=Fund && 'Trade'[預り]=Acnt))
4. ピボットテーブルの展開：データソース=Trade
■ 手順4.
(1) 見たい情報をピボットテーブルに展開してください。
(2) 最新単価、M累計数に間違いが無いかを確認してください。
列：預り、銘柄、Σ値
行：日
Σ値：最新単価、M累計数、M累計売買金、M評価額、M損益、M損益率
※ Σ値の追加に数10分以上掛かります。
※ M累計数はSBI証券のポートフォリオの値と合わないことがあります。
(3) グラフを作成する場合は、ピボットテーブルのシートをコピーし、グラフ表示する項目にピボットテーブルのフィールドを変更します。
(4) [ピボットテーブル分析]-[ピボットグラフ]-[折れ線]または[組み合わせ]でグラフを表示します。
(5) [ピボットテーブル分析]-[スライサーの挿入]-[銘柄]でスライライサーを追加します。
5. 補足
1) Unitフォルダのファイルの結合
[データ]-[データの取得]-[ファイルから]-[フォルダから]-[結合]-[データの結合と変換]で結合します。
自動的にヘルパークエリが作られます。
(フォルダから[データの変換]で取り込み、ファイルの結合をする前にファイルのプロパティの列を消してしまうと、自動解析のサンプルに最初のファイルしか選べなくなります。最初のファイルのみ1行目がヘッダ行で、２つ目以降のファイルの２行以降にヘッダ行があると、Power Queryが2つ目以降のファイルのデータを異なる項目のデータとみなし、うまく処理できないようです？)
【注意】2年以上前の取引履歴はCSVが無いので、今まで手作業でCSVを貼り付けていたExcelから作成しました。ExcelでCSVに出力すると、UTF-8、CRLFになります。これはSBIのCSVと異なるので、テキストエディタでShift-JIS、LFに変更します。メモ帳の場合はエンコードをANSIにします。
2) Tradeフォルダのファイルの結合
下記記事に従い[データ]-[データの取得]-[ファイルから]-[フォルダから]-[データの変換]でPower Queryに取り込み、Content列を右クリック、[ドリルダウン]、「ソース[Content]」をBinary.Combine()で括ります。
https://marshal115.hatenablog.com/entry/2020/10/09/144529
これによりヘルパークエリ無しで結合ができます。（Unitは銘柄の識別にファイル名が必要なので、この方法は使えません。）
3) 基準価格のクエリのマージ
取引履歴に基準価格をマージする時、結合の種類に完全外部(両方の行のすべて)を選択しても、取引が無い日付のレコードが表示されません。照合列には「約定日」「銘柄」「預り」を選択しています。「預り」を入れる理由は、取引が無いが保有している預りにも基準価格を取り込んで、損益を計算するためです。
マージ後「単価」だけを展開すると、該当する「約定日」「銘柄」「預り」が取引履歴に無い場合、「単価」「約定日」「銘柄」「預り」がnullの行が追加されます。
これだと「約定日」「銘柄」「預り」が分かりません。
【対策】
基準価格の「約定日」「銘柄」「預り」も展開し、こちらをピボットテーブルに読み込むようにします。
6. 旧NISA終了時の自動特定口座への移行によるアンマッチ
5年経った旧NISAは特定口座に移行されますが、取引履歴に現れないようです。なので、この分は手動で取引履歴のCSVに書き込む必要があります。しかし、移行口数と金額を何処で見ればよいのかが分かりません。[口座管理]-[口座(NISA)]-[旧NISA 口座管理]-"売却損益/配当金･分配金明細(前日基準)"-"取得年"-"数量"にそれらしい数字があるのですが、「ニッセイ－＜購入・換金手数料なし＞ニッセイ日経平均インデックスファンド 」だけ口数が合いません。
移行された時は移行時の金額で新たに特定口座の口数を取得した扱いになるのかも知れません。売りが入るとポートフォリオの含み損益とトータルリターンは合わなくなるようです。本Excelで求めている損益はトータルリターンの含み損益になります。但し、合うのは4桁までで、ピッタリは合いません。口数の丸め方の違いによるものかもしれません。
旧NISAをそのままにしておくと満期で一旦売却扱いになります。この瞬間はSBI証券のデータからこの金額が消えます。直ぐに特定口座で同額の買いが入ります。すると、SBI証券の保有額に復活します。この為、トータル損益額は正しく出ます。しかし、トータル損益率は買いが増えるので、実態より大幅に小さくなります。前記メジャーでは、分母にNISAから特定に移行した分を入れていないので、実態に合ったトータル損益率を算出しています。
7. SBI証券のトータルリターンのサマリーと本Excelの照合結果
【A】評価金額：保有している口数の現在の評価額。売却した分は含まない。-->概ね本Excel集計と合う。若干合わない。
【B】累計売却金額：売却した金額+旧NISAからロールオーバーと特定口座にSBI証券が移動した金額。-->本Excel集計には無い。
【C】累計分配金額：
【D】累計買付金額：購入金額+B(売却した金額+旧NISAからロールオーバーとSBI証券が特定口座に移動した金額)。-->本Excel集計には無い。
(F)=【A】+【B】+【C】-【D】：トータルリターン。-->概ね本Excel集計の損益と合う。若干合わない。
トータルリターン率：トータルリターン÷【D】。-->本Excel集計には無い。
以下はSBI証券のページから求めた数値
(H)=【D】-【B】：累計買付金額-累計売却金額。-->本Excel集計の売買金額にピッタリ合う。
(F)÷(H)：トータルリターン÷売買金額。-->概ね本Excel集計の損益率に合う。若干合わない。
旧NISAから特定への移管以外で口数が合わないのは「ｅＭＡＸＩＳ ＮＡＳＤＡＱ１００インデックス」「ｉＦｒｅｅレバレッジ ＮＡＳＤＡＱ１００」の2つでした。口数の丸め方の違いによるものかもしれません。取引履歴の口数を全部SBIのポートフォリオの数字に合うように補正したところ、損益の誤差は5桁まで合いましたが、ピッタリは合いませんでした。謎です。
8. 金額指定で購入したときの口数の参考情報
https://teiiyone.com/blog/2014/02/post_172.html
http://valavg.com/how-to-round-and-record-for-funds/
売買履歴のCSVファイルの口数とSBI証券内部で使用している口数に丸め方法の差があるように思えるのですが、良く分かりません。
売買金額はピッタリ合うので、CSVファイルの口数を使用せずに売買金額から小数点以下の口数を求めたのですが合いません。
本Excelの口数の集計結果とSBI証券のポートフォリオの口数は合わないので、本Exceは概算になります。
最近のコメント