こんにちはー！ちくわです♪
みなさん、自分の資産、どのように管理していますか？
複数の証券口座に株を持っていたり、現金やゴールド、暗号資産等いろいろ持っていると、一元管理はなかなかやりにくいですよね。
・リアルタイムの自分の資産額を把握したい方
・自分のポートフォリオのバランスを可視化したい方
・自分の資産のツリーマップを見てニヤニヤしたい方
各証券会社やアプリもありますが、特定の証券会社内の資産しか見れなかったり、アプリ内で課金が必要だったりと。私はこれだと言えるサービスを見つけられませんでしたので、それであれば自分で作ってしまおうという結論に至りました。
作ると言っても、プログラミングの知識ゼロなので、グーグルスプレッドシートで作成しました。
スプレッドシートの特徴についてはこちらの記事で紹介↓↓
スプレッドシートを使う理由
家計簿を自分で作る場合、エクセルを利用する人は多いのではないでしょうか。
しかし、エクセルの場合、日々動く株価や為替を追うことは簡単ではありませんよね。
グーグルスプレッドシートはパソコンで作っても、スマホでも見ることができ、特定のHPからデータを読み取る機能も備わっていることから、自動で株価や為替を取得できるため、日々の値動きにもタイムリーに反映でき、とても面白いです。
また、スプレッドシートではツリーマップも使えるので、せっかくであれば、資産額だけでなく、ツリーマップも作ってみてはいかがでしょうか。
ツリーマップを作ってみた
日経平均株価のツリーマップを作ってみました↓
各企業の株価を数式で取得（スクレイピング）し、セクター毎に分類しています。
一番大きいセクター（企業）が左上に位置し、色で値上がりor値下がりを表しています。
直感的に全体をとらえることができます。
「全体的に下がってるなぁ」
「緑色の企業もそこそこあるけど、サイズが小さいから影響は少なそうだなぁ」
といった感じです。
上のツリーマップは日経が定めている36業種分類に振り分けていますが、国際的なセクター分類にしたらどうだろう？ということで、セクターの分け方を変えて作ってみました↓
このように、自分の好みでグループ分けを変更したり、正方形にしたり、自由自在に表すことができます。
ちなみに、私のアセットアロケーション（ポートフォリオ）はこんな感じになっています。
需要はないだろうけど、ポートフォリオ公開～↓
これは11月8日時点のもので、ビットコインが最高値圏にあり、資産額がピークでした。
ここからビットコインが下がり、岸田ショックで日本株が大きく下がり、ヘッジファンドunearthの運用も酷く、米国株を整理し、今はこんな感じです↓
現金比率がかなり上がってしまっています💦
そして、日本株がみるも無残な状況。
そんなこと、どうでもいいから、ツリーマップの作り方を教えろ～
は、はい！
ではでは、前振りが長くなりましたが、作り方を説明していきます！
ツリーマップの作り方
では、スプレッドシートを使ったことがないという方でもわかるように、説明していきます。
まずは検索↓
すると～↓
そして↓
で、グーグルアカウントでログインして
新規作成でシートを作ります。
ツリーマップのデータの構成はこんな感じです↓
この枠内が全て埋まっていれば、ツリーマップを作ることができます。自身の好みでグループ分けをどうするか決められます。
このように枠の部分を選択して↓
「挿入」→「グラフ」をクリック↓
すると、こんなグラフが出てきますが、右側の「グラフの種類」をクリック↓
一番下の方に「ツリーマップ」があります↓
これだけで、簡単なツリーマップができます。
この場合、「私のツリーマップ」や「セクター1～3」は文字が入っていますので、完全に同じ文字を入れないと認識されません。
1.「各名称」と「セクター」の列には文字が入ります。
「私のツリーマップ」と入力している4つのセルには、必ず同じ文字が入るようにしてください。
「=B3」や「=B4」で入力すると良いかと。
2.「割合」の列には数値が入ります。(セクターの3行は空白でも可)
画像では「％」で表していますが、実際の資産額等でも大丈夫です。
この「割合」の列がツリーマップの長方形のサイズになります。
3.「前日比」の列には数値が入ります。
この数値で長方形の色が決まります。全体に対してプラスであれば緑色に、マイナスであればピンク色になります。（色は変更可）
では、次に、実際に株価や為替を取得する方法を説明します。
株価・為替を自動取得する方法
それでは、株価などの自動取得方法を説明します！
関数で簡単に ～米国株・為替・暗号資産～
まず、米国株、為替、暗号資産は数式だけで簡単に取得できます。
↓↓以下参照↓↓
★米国株（ETFも可）
現在の株価 「=GOOGLEFINANCE(ティッカー,”price”)」
昨日の株価 「 =GOOGLEFINANCE(ティッカー,”closeyest”) 」
ティッカーのところにセルを入力すると、まとめて取得できます
セルA7にティッカーを入れた場合
「=GOOGLEFINANCE(A7,”price”)」
★為替
現在のドル円 「=GOOGLEFINANCE(“CURRENCY:USDJPY”)」
★暗号資産
現在のビットコイン価格 「=GOOGLEFINANCE(“CURRENCY:BTCJPY”)」
現在のイーサリアム価格 「=GOOGLEFINANCE(“CURRENCY:ETHJPY”)」
★過去の為替と暗号資産価格について
上記の関数の後ろ側に、こんな感じで期間を入力すると、5日前から今日までの価格が一気に入ります。
=GOOGLEFINANCE(“CURRENCY:USDJPY”,”price”,TODAY()-5,TODAY())
スクレイピング ～日本株・投資信託・ゴールド等～
では、次に、日本株の取得方法です。
（白からちょっとオフホワイトに寄ってきます♪）
特定のウェブページから、特定の位置にあるデータを拾ってくるスクレイピングです。
ウェブページによってスクレイピングが禁止している場合もあるので要注意です。
（ヤフーファイナンスはNGですが、今回紹介するカブタンは大丈夫かと思います💦）
スクレイピングのやり方を説明しますが、とりあえずコピペでよければ↓
★日本株
セルA7に4桁のコードを入れた場合
現在の株価 「=IMPORTXML(“https://kabutan.jp/stock/?code=”&A7,”//*[@id=’kobetsu_left’]/table[1]/tbody/tr[4]/td[1]”)」
前日比「=IMPORTXML(“https://kabutan.jp/stock/?code=”&A7,”//*[@id=’stockinfo_i1′]/div[2]/dl/dd[2]/span”)」
★ゴールド（USD/oz）
現在の価格「=IMPORTXML(“https://goldprice.org/ja/gold-price-today”,”//*[@id=’block-system-main’]/div/div[2]/div[1]/div/div/div/div/div/div/div/table/tbody/tr[1]/td[2]”)」
日本円で1gあたりの価格に換算する場合は
（スクレイピングした価格）÷ 28.3495 × USDJPY(今は113円/ドルくらい)
で計算できます。
スクレイピングの方法
関数「IMPORTXML」を使います。
構成はこんな感じです。
IMPORTXML(URL, XPathクエリ, locale)
- URLは取得したいウェブページのURLです。（今回は例としてファストリでやってみます「https://kabutan.jp/stock/?code=9983」）
- XPathクエリはウェブページ内の位置です。
XPathクエリ取得方法
①取得したいデータのあるウェブページをGoogle Chromeで開き、「F12」を押すと右側にこんな英語のページ？が出てきます↓
で、赤丸のところをクリックしてください↑
そして、取得したいデータにカーソルを合わせて（今回は前日比を取得します）、「左クリック」↓
すると、右側に難しそうな文字がずらっと出てくるので↓
↑この画像のとおりにクリック
「Copy XPath」を左クリックすると、XPathが取得完了
で、 IMPORTXML(URL, XPathクエリ, locale) に入力すると
=IMPORTXML(“https://kabutan.jp/stock/?code=9983″,”//*[@id=”stockinfo_i1″]/div[2]/dl/dd[2]/span”)
こうなります。
[@id=“stockinfo_i1“] → [@id=‘stockinfo_i1‘] ダブルからシングルに変更します
すると、最終的にはこうなります↓
=IMPORTXML(“https://kabutan.jp/stock/?code=9983“,”//*[@id=’stockinfo_i1′]/div[2]/dl/dd[2]/span”)
この9983のところにA7セルを数値を入力したい場合は
=IMPORTXML(“https://kabutan.jp/stock/?code=”&A7,”//*[@id=’stockinfo_i1′]/div[2]/dl/dd[2]/span”)
となります。
ツリーマップ出来上がり
では、これら式を最初に紹介したツリーマップの表に入れていくと、こうなります↓
（数式の頭の「=」を消しています）
↑このように入力すると
実際にはこう表示され、ツリーマップも出来上がります♪↓↓
円グラフも良いですが、種類が多くなってくるとツリーマップもなかなかいい味が出てきます。
| おすすめカスタマイズ （Finviz.com風） | 色番号(16進数) |
| 見出し | #262931 |
| データなし | ＃000000 |
| 最小 | #f63538 |
| 中央 | #414554 |
| 最大 | #30cc5a |
まとめ
いかがでしたでしょうか
銀行や証券口座の残高は取得できませんが、頻繁に売買しないのであれば
日々の株価の変動に合わせて自分の資産額がどのようになっているのか、グーグルスプレッドシートで簡単に把握することができます。
さらに、ツリーマップを作ると、サイズと色で直感的に把握でき、資産管理が楽しくなります。
是非自身の資産状況でツリーマップを作ってみてはいかがでしょう。
多少なりとも誰かのお役に立てれば幸いです。
最後までお読みいただきありがとうございました。
コメント