２つの証券会社と１つの外貨普通預金口座に分かれたリスク資産のまとめをエクセルで作ろうとしてパワークエリやVBAを知りました。
今まで表示されていなかった開発タブをエクセルで表示するようにしましたが、VBAを使うのは一番最後でいつになるか分かりません。
初めてパワークエリを使って分からない事だらけでしたが、本やネットで調べながら何とか形になってきたので記録しておきます。
間違えてやり直す事が多かったですが、使いながらもっと効率のいい方法が見つけられそうです。
完成したらまとめるつもりでしたが、何か月か修正しながら使ってみないと完成しそうにありません。
初心者は同じようなところでつまずくと思うので試作段階ですがまとめます。
スクリーンショットも沢山あって長くなりそうなので今日は大まかな流れだけ記録しておきます。
CSVファイルを比べて項目を決める
２つの証券会社から保有資産のCSVファイルをダウンロードして比べました。
楽天証券のCSVファイルです。
取得金額はありません。
項目を絞るため保有数量×平均取得額ではなく時価評価額-評価損益で出します。
SBI証券のCSVファイルです。
円建保有資産のみで外貨建保有資産は調べた限りではCSVファイルをダウンロードできませんでした。
株式と投資信託では取得金額や評価額などの列がずれています。
この２つを見比べて項目は銘柄名と取得金額、評価額、評価損益に絞り後から日付も追加しました。
CSVファイルの形式が違うのでそれぞれにクエリを作成してから結合します。
４つのクエリを作成してから結合
SBI証券のCSVファイルでは株と投資信託が一緒のままでは列がずれて難しかったので分けてクエリを作成する事にしました。
入力用
楽天証券とSBI証券のCSVファイルには入っていないSBI証券の外貨建資産とiDeCoの楽天・オールカントリー、米ドル普通預金はエクセルに入力してクエリを作成しました。
日付と10カ所の金額部分はコピペか手入力でいれます。
初めはコピーして日付を入れましたが、クエリ作成後にフィルを使ってコピーするので消しました。
楽天証券CSVファイルからクエリを作成
最初は上下の行を削除してからフィルターで選びましたが、保有銘柄は変わらなくてもＣＳＶの形式がずれるかもしれないと行の削除は止めてフィルターで選択しました。
今日の日付で追加した列も止めて空白列を追加して、結合後に入力した日付をコピーする事にしました。
日本株（マクドナルド）のみでクエリを作成
投資信託とは分けた株のみのクエリです。
後で結合するので列名を合わせておきます。
SBI証券のCSVファイルから投資信託を抜き出したクエリ
楽天証券と同じく、上下の行を削除して行を絞っていくのは止めて銘柄名のフィルターで絞りました。
列名を同じにした４つのクエリを縦に結合
資産手入力用のクエリに他の３つのクエリを結合しました。
連結したテーブルの金額がずれているのはSBI証券のCSVファイルを取り込んだ後にデータ型の指定し忘れていたのが原因でした。
「SBI投資信託」と「SBI株」のクエリで取得金額、評価額、評価損益のデータ型を整数に指定したら揃いました。
楽天証券とSBI証券でオルカンの表記が違います。
ハイフンで区切って分けて全角を半角に置き換えようかと思いましたが置き換えテーブルを使う事にしました。
2つの置き換えテーブルを結合
eMAXIS Slimの表記が違ってもオルカンに統一します。
長い名前は分かりやすい名前に置き換えました。
銘柄置換後のクエリです。
ファンド名の間にあった「・」と「iFreeNEXTインド株」の株は後で削除しました。
円建と外貨建資産の区別が楽なように記号を追加しました。
あまり項目が多くならないようにまとめましたが、後で分けた方が良いかもしれないので間にハイフンを入れておきました。
通し番号（主キー）も追加
結合した４つのクエリに（銘柄）置き換えリストと記号リストをマージしました。
４つの口座に分かれているオルカンはまとめて総額を把握できればいいのですが、クエリのデータとしては１つ１つ独立した番号があった方がいいようなので主キーを追加しました。
順番を並び変えてから通し番号を追加して主キーとしました。
最後にカテゴリーテーブルを追加してマージ
ここでカテゴリーテーブルもあった方が良さそうと気付いて追加しました。
マージ1の列は多過ぎるのでクエリを複製後に銘柄名、銘柄、評価額の３つの列に絞り、これにカテゴリーテーブルを横に結合（マージ）しました。
（追記）
クエリをマージする時は全ての行数の多いテーブルを選んでおいてから少ない方を選ぶとうまくいきました。
楽天・オールカントリーの「・」を無しにしたり修正して16行中16行が一致してマージできました。
マージ後のクエリです。
このクエリを閉じて読込んでピボットテーブルを作成しました。
左のピボットテーブルから円グラフを作成しました。
iDeCoやオルカンの積立経過を把握するクエリには日付を使用して列と行を入れ替えて表示してみるつもりです。
デスクトップには３つのフォルダー（SBI証券と楽天証券、入力用）を作り、毎月25日頃証券会社からCSVファイルをこのフォルダーに日付のファイル名でダウンロードする予定です。
日付の列はファイル名を日付にするやり方ができればと思ってやってみたもののできませんでした。
エクセルの上段のみに入力した日付だけで毎月うまくいくのか分かりません。
余裕があったら1月～4月までのエクセルファイルをCSVで保存して試してみるつもりです。
