不動産や太陽光について評価方法を変更してきましたが、ちょうどいい機会だと思い、資産管理のスプレッドシートも一新しました。どんな方向性で考えたか、まとめておきます。
ローデータ、集計データ、最新ピックアップデータ、グラフビューの4系統に
ぼくは資産関係のデータをスプレッドシートに入力して分析しているのですが、これを毎月行うたびに、「ここもうちょっとなんとかならないの？」と考えています。今回、太陽光や不動産などリアルアセットの評価手法を変更したので、良い機会だと思い、資産関係の集計シートも一新しました。
まずはローデータ表の設計です。ここは下記のようなカラム設計にしました。
- 銘柄名
- 取引書
- 数量
- 所得単価
- 現単価
- 通貨種別
- 記録月
- ドル円レート
- 集計額
- バケツ戦略分類
正直、微妙なところもあって、例えば数量と取得単価はあまり綺麗に活用できていません。ただこのフォーマットで2018年から記録してきているので、移行するのはかなりやっかい。なので、これを活かして行こうと思っています。またバケツ戦略分類は、別のシートを参照して銘柄名から自動入力されるようにしています。
集計データシートを一新
次に、このデータからピボットテーブルで集計します。以前はこの集計データとグラフが混ざってしまっていたのですが、グラフビューを完全に分離したのが今回のキモです。集計データは次のようなルールを使っています。
- 縦に時系列で並ぶ
- すべて昇順
これまで集計は横に時系列で行っていました。これは見やすさの面ではいいのですが、データ処理上はいくつも課題がありました。
まず、ARRAYFORMULAが使えないことです。ARRAYFORMULAは縦の各行に同じ処理をまとめて行うスプシ独自の関数で、これを使えば月次データが増えても数式をコピペする必要がありません。
また降順ではなく昇順にすることで、データの位置が固定され処理がやりやすくなります。どういう意味かというと、降順だと常に先頭に最新データが来るので、閲覧はしやすいです。でも、例えば「年初来リターン」を計算したいときなど、年初の数字が入ったセルが動くので、日付などをもとに年初データを探して割るという複雑な処理が必要になっていました。昇順なら、新データは最後の行に追加されていくので、そういったことがおきません。
ただ、昇順だと問題もあります。まず「スクロールしないと最新データが表示されない」、そして致命的なのが「最新のデータをグラフ化できない」です。今度は最新データのセル位置が変わっていくので、グラフの対象セルを固定できないのです。
これを避けるために、降順にしたピボットテーブルを作る方法もありますが、今回は関数を使い、昇順の表から見出しと最新行だけを抜き出すようにしました。
=VSTACK(
A2:E2,
CHOOSEROWS(FILTER(A3:E,A3:A<>""),-1)
)
こうやって整形したデータからグラフを作ります。
- 推移グラフ 昇順の集計データから作成
- 最新の数字のグラフ 最新行から抜き出したセルから作成
これによって、データの正規性が増し、一部残っていた手作業が完全になくなりました。実は当初は、もっとAI親和性が高いシートを目指していたのですが、今回は見送り。それでもローデータ・集計データ・ビューテーブル・グラフ、と構造が整理されたことでかなりAIが理解しやすいスプシになったと思います。
複数シートの統合
現在資産管理系のシートは複数あります。
- アセットアロケーションシート（今回一新したもの
- 入出金シート（配当額や買付額を記録したもの
- リアルアセットDCF評価シート（不動産や太陽光の資産額を評価するもの
- キャッシュフローシート（複数資産からのインカムキャッシュフローをまとめたもの
- 生活費シート（銀行口座データから支出額をまとめたもの
- 太陽光シート（太陽光の発電量と入金額をまとめたもの
- 固定資産税シート（固定資産税の額と支払い状況をまとめたもの
- 収益試算シート（個人の所得税・住民税を試算するシート
このうち、1と2は1つのシートにまとまっているのですが、ほかは別々です。ただ、それぞれ相互に参照している部分があるので、今回のようにシートを一新するとリンクがズレて数字がおかしくなる場合も多々。（4）は先日データ構造を整理してビューも切り分けてだいぶアップデートできたのですが、ほかのシートも順次チューニングしたいと思っています。
