Googleスプレッドシートを使った株式ポートフォリオ管理

Finance

投資関連の記事は訪れる方に結構読んでいただけるようなので、今日も投資関連の記事にしてみます。ちなみにこのブログはカーネルの四方八方に発散する興味をアウトプットして固定化する目的で始めたもので、若い賢明な社会人向けに知識や洞察を共有することを理念としています。

スポンサーリンク

Googleスプレッドシートでポートフォリオ管理

私が使っている資産管理のスプレッドシートを公開しようと思います。管理にはGoogleスプレッドシートが便利です。Google financeのデータベースから株価や様々な指標を自動で取得したりと色々なことができます。ただ、米国市場に上場しているものしか基本的に対応していないんですよね。

つまり、Coca Cola (KO)など米国株の株価を取得するのは簡単でも、最近流行り始めている楽天VT(9I311179)とかは実は取得が結構めんどくさい。

ただ、WebスクレイピングでYahoo Japanのファイナンスデータの現在価格データは取得できるので、Googleスプレッドシートで取得しています。記録用シートに売買記録やセクター、国際分散先、ポートフォリオを書いて、複数のポートフォリオに分けた管理や、セクター分析、国際分散の分析のグラフを表示して眺めています。BloombergやMorning Starなどの投資情報サイトでもポートフォリオを管理できるので便利ですが、スプレッドシートは機能の拡張もできて便利なので、もし使っていない方がいれば試していただければと思います。

スプレッドシートを公開しますが、自分のGoogle Driveにコピーして使用するようにしてください。(入力されたデータは私の資産ではありません。奥さんの許可が得られません笑)

PortfolioAnalyzer
Graph

※当初YahooファイナンスでWebスクレイピングと書きましたが、禁止されていることをご指摘頂きました。

Yahoo!ファイナンスヘルプ – Yahoo!ファイナンス掲載情報の自動取得(スクレイピング)は禁止しています

プログラミングによる株価の取得が過度になるとサーバに負担がかかります。無料で使える貴重なサービスを守るためにも禁止行為でのスクレイピングはすぐに取りやめて修正しました。一応日経新聞のwebサイトには明確な禁止規定はないようなので日経新聞のサイトから取得する設定にしています。GoogleスプレッドシートによるWebスクレイピングのやり方はこちらが参考になります。過度な読み込みはサーバーに負荷をかけるので使い方にはご注意ください。

rilakkuma.hatenadiary.jp

スプレッドシートの使い方

コピーの作り方

  1. 上のアドレスを開く
  2. ファイル → コピーを作成

f:id:drkernel:20180114125641p:plain

スプレッドシートの使い方

  1. 下のTabから「Transaction」を開く。サンプルの入力データが出てきます。
  2. 売買のデータを入れていきます。日付、ティッカー、購入合計額、購入数、取得平均単価、売却数、売却した値段、売却時の平均取得単価、売却合計額、商品タイプ、商品クラス、国際分散、上場、セクター、ポートフォリオの入力項目を作っています。 買った場合は購入合計額、購入数、取得平均単価のうち2つを入力すると自動的に残りの1項目が計算されます。売った場合は売却数、売却した値段(単価)、売却時の保有している商品の平均取得単価を入力します。売却合計額は勝手に計算されます。
  3. 商品タイプはMutual Fund, ETF, Stock, Otherが入力できるようになっているので選択。(これは後の分析に使っていません。
  4. 商品クラスはStock, Bond, Alternativeが選べるようになっています。オルタナティブは自分でやらないのですがREITETFとかは自動取得できるようにしてあります。
  5. 国際分散は、International (VTとか), JP (日本), US (米国), Developed (先進国), Emerging (後進国)を適当に選びます。
  6. 上場は日本か米国市場のどちらに上場した商品かを選びます。このデータを元にGoogle financeか日本株のサイトでデータを取得するか判断しています。
  7. セクターもS&Pの分け方に準じたものと、ごっちゃになったIndex用とどれにも当てはまらないものとしてOtherを用意しています。自動取得もある程度できるのですが、動作がかえって重くなるので自分で入力したほうが良いです。
  8. ポートフォリオをA, B, C,…と分けて管理してリバランスしたいという方はそれぞれの割当を選んでください。
  9. 全部入力するとこんな感じになります。

f:id:drkernel:20180114125645p:plain

一応配当の管理と、入出金の管理もできるようにしてあります。

配当の入力

1. 配当日とシンボル、配当の入金額を入力します。
2. ドル建ては日本円換算額が表示されます。Monthは後でグラフを作る時に使用します。
f:id:drkernel:20180114125648p:plain

入出金の管理

1. 2018年の1年用に作っています。入金額と出金額を入力します。
2. 前年終了時点で積立てた額を一番上に入力します。
3. 各月に入金または出金した額を入力します。
4. Cumulativeの中にそれまで入金した総額が表示されます。
5. 一番下の行は1年終了時点の状態を把握するためのものなので特に使いません。

f:id:drkernel:20180114125654p:plain

Summaryという所に商品の集計データのテーブルが沢山並ぶようになっています。必要であればキャッシュのポジションも入力します。
緑色のセルに証券会社毎のキャッシュを入力します。ドル建てやユーロ建ては下の方に入力します。現在残高と通貨を選びます。
全て入力すると左上のセルに資産総額や、これまで入金した金額からの収支が表示されます。
f:id:drkernel:20180114125657p:plain

文字・数字は見にくいので、Graphというところで各種グラフが表示されるようになっています。
「今日はどんな感じかなー」となんとなく見たければこちらを眺めるとポートフォリオが見れます。
ポートフォリオ毎のグラフやアセットクラス毎、国際分散、セクター毎のグラフも一覧で見れます。
今年の配当がどれくらい来たかや、商品ごとのキャピタルゲインもグラフに表示されます。

f:id:drkernel:20180114133230p:plain

他のTabの解説;MarketというタブはGoogle Financeから計算に使う金融データをリアルタイムに取得するために作りました。使わないけど、各種インデックスや、為替市場のデータが取得されます。
CalcとAggregateという所で自動的に処理が行われるようになっています。Calcで集計を行って、Aggregateのところでは集めた商品ごとに価格を取得したり損益を計算したりしています。

Try It

もしまだスプレッドシートポートフォリオを管理したことがないという方はぜひ試してみてください。
修正点や改善点があれば教えていただけると嬉しいです。

最後に、貴重なデータ資源を提供してくださっている日経新聞に敬意を込めて、皆さんぜひ購読してくださいね。

コメント

  1. マサ より:

    手探りで作っていたGooglespread sheetを作り直そうと思い調べています。
    transaction→Calc→Aggregateとデータを整理していくところがとてもとても参考になりました。
    日本株はgooglefinance関数で拾えないのが残念ですよね。。。

    • drkernel drkernel より:

      コメントどうもありがとうございます。
      自分でも試行錯誤しつつスプレッドシートを改変しながら使っています。
      日本株は未だにimportxmlとかを駆使しないと取得できないのでたしかにめんどくさいですよね。