【株価を自動取得】googleスプレッドシートでポートフォリオを管理する方法

  • B!
  • COPY
    LINK

今回は、googleスプレッドシートでポートフォリオを管理する方法を紹介します。

googleスプレッドシートでは、関数を入力すると、自動で株価を取得できます。さらにこんな円グラフがつくれちゃいます。



目的

この記事では、googleスプレッドシートの関数をまとめてみました。
たとえば、

・excelやgoogleスプレッドシートは、なんとなく使ったことあるけど、詳しくは知らない。
・米国株と、日本の投資信託をまとめて管理したい。
・米国株の評価額を日本円換算したい。

…という方のお役に立てれば幸いです。


ポートフォリオ作成例（コピペ利用可能）



参考までに、作成例を配布します。
閲覧のみ。編集はできません。
コピペ利用は可能です。各自アレンジしてご利用ください。

こちらをクリックすると、ポートフォリオ作成例（コピペ利用可能）が開きます。

https://docs.google.com/spreadsheets/d/12oNgZi3SSSXvt_UJTgB1aDsQ2SDNhHmnc84qmUdKMDg/edit?usp=sharing

リンク先の作成例シートからコピーする際、右クリックは反応しませんが、ショートカットキー「ctrl」+「c」でコピーできます。

作成例の解説――各セルの背景色について
・赤：手動で入力する部分です。株の売買ごとに、その都度、修正が必要です。
・白：関数を設定しておけば、自動で情報が取得されます。
・青、灰色：各項目の名称など。

※取得金額 (円)は、関数ではなく、株を取得するごとに手動で入力しています。
最新のドル円レートではなく、取得時のドル円レートで計算されるためです。

関数――四則演算など

googleスプレッドシートの関数は、基本的にexcelと同じです。


　関数　備考
 足し算  =●+○ 
 ●と○にそれぞれ、セル名や数値を入力します。

 たとえば、「B2」と「B3」を足し算する場合、「=B2+B3」と入力します。
 また、「B2」の値を100で割り算する場合、「=B2/100」と入力します。
 引き算  =●-○ 
 掛け算  =●*○ 
 割り算  =●/○ 

　　




　関数　備考
 （ ）を含む四則演算  =(○-●)/☆  ○●☆にそれぞれ、セル名や数値を入力します。
 たとえば「=(B2-B3)/C4」や、
「=(B2-B3)/100」といった具合です。
 　　　合計  =SUM(○:●)  A2からA4までの値を合計する場合、
「=SUM(A2:A4)」と入力します。
 　　　等しい  　　= 「A1」セルに「C3」セルの値を反映させる場合、
「A1」セルに「=C3」と入力します。

　　



関数――「Google Finance」から、為替や株価を自動取得する


　　　為替　関数
 ドル円レート =GOOGLEFINANCE("currency:USDJPY", "average")
 リンギット円レート =GOOGLEFINANCE("currency:MYRJPY", "average")

現在の為替を取得するための関数について、「USDJPY」は、ほかの通貨に置き換え可能です。
たとえば「USDJPY」を「MYRJPY」に置き換えると、マレーシアリンギット円レートを取得できます。



　銘柄　ティッカー　関数
　デルタ航空 dal =GOOGLEFINANCE("dal","price")
　サウスウエスト航空 luv =GOOGLEFINANCE("luv","price")

現在の株価を取得するための関数について、デルタ航空を指定する「dal」は、ほかの銘柄に置き換え可能です。
「dal」を「luv」に置き換えると、サウスウエスト航空の株価を取得できます。
「price」を「pe」に置き換えると、PER（株価収益率）を取得できます。

ほかにも、「Google Finance」からさまざまなデータを自動取得できるようですね。
興味のある方はこちらをどうぞ。


関数――日本の投資信託webサイトから、株価を自動取得する


　関数
 eMAXIS Slim 米国株式（S&P500） =importhtml("https://emaxis.jp/fund/253266.html","table","1") 
 eMAXIS Slim 新興国株式インデックス =importhtml("https://emaxis.jp/fund/252878.html","table","1") 

「eMAXIS Slim」であれば、公式webサイトから、現在の情報を取得できます。
上記の関数を入力すると、基準日、基準価額、前日比、解約価額、純資産総額がまとめて取得できます。

他のwebサイトからも同様のことはできますが、これはスクレイピング行為に該当します。
スクレイピングを禁止しているwebサイトも多いようです。
「eMAXIS Slim」webサイト（三菱ufj国際投信）が、スクレイピングを禁止しているかどうかは不明です。
このあたりは自己責任でお願いします。


※「円」の一文字を削除する方法

　関数　入力例
　一文字減らす =left(●, LEN(●)-○) =left(C19,len(C19)-1)

●に該当するセル名を入力し、○に削除する文字数を入力します。
入力例では、任意のセルに「C19」と同じ値を反映したうえで、右から一文字を削除しています。
たとえば「C19」で取得した基準価額から、「円」の一文字を削除するときに使用します。



関数――自動取得した情報を結びつける

自動取得した情報を結びつけるための関数です。
人によって、必要な項目が色々あると思います。必要に応じてアレンジしてください。

　米国株
　計算法
 関数（入力例）
　評価額（ドル）
　現在の株価 (ドル)×保有数
 =D4*F4
　評価額 (円)
　評価額 (ドル)×ドル円レート
 =C4*D2
　取得金額 (ドル)
　取得時の平均株価 (ドル)×保有数
 =E4*F4
　評価損益
　パターンA
（100％基準）
　評価額 (ドル)÷取得金額 (ドル)
 =C4/G4
　評価損益
　パターンB
（0％基準）
　（評価額－取得金額）÷取得金額
 =(B4-H4)/H4

パターンA（100％基準）は、ドルでの評価損益です。
「評価額 (円)÷取得金額 (円)」であれば、円での評価損益を表示できますね。

また証券会社の評価損益の画面では、パターンB（0％基準）が多いかもしれませんね。
たとえば100円が60円になると、パターンAでは「60％」と表示されますが、パターンBでは「－40％」と表示されます。どちらも同じことですが、お好みの方を使ってみてください。
それはさておき、「－40％」って、めっちゃ含み損ですね、トホホ…。


・日本の投資信託の場合
　日本の投資信託　計算法　関数（入力例）
　評価額（円） 保有口数×現在の基準価額÷10000 =F9*D9/10000

投資信託の「基準価額」とは、個別株でいう「株価 (1万口当たり)」に相当します。そのため、必ず「10000」で割り算します。
べつに手動入力でも構いませんが、ここまでくると、意地でも自動入力したいっていうね（笑）


・ポートフォリオの内訳に現金を加える場合

　計算法　関数（入力例）
　現金（買付余力）　投資資金－取得金額（円）の合計 =1000000-(SUM(H4:H7)+H9+H12)

入力例では、投資資金100万円で計算しています。



その他、表示形式について、小数点の切り捨てとか、％表示とか、色々微調整すれば完成です。
こんな円グラフも作れちゃいますね。



注意点

・ブログでgoogleスプレッドシートを公開する場合、くれぐれも個人情報の取り扱いには注意してくださいね。

・Yahoo!ファイナンスなど、スクレイピングを禁止しているwebサイトも多いようです。相手方のサーバーに負荷をかけたり、著作権侵害と見なされることもあるようです。下手すりゃハッキング行為と見なされちゃいます。気を付けてくださいね。
株価取得のために行う程度であれば、さほど気にする必要ないと私は個人的に思っていますが…。株価と法律は移ろいゆくもので、なんとも言えませんね…。



参考webサイト
こちらのブログでは、日本株の情報を自動取得する方法についても紹介されています。スゴイですね！


  • B!
  • COPY
    LINK

コメント

コメントを投稿

にほんブログ村 株ブログ 株 中長期投資へ

このブログの人気の投稿

セレクト・セクターSPDRファンドーーS&P500 部門別に投資するETF

イメージ
今回は、セレクト・セクターSPDRファンドを紹介します。 セレクト・セクターSPDRファンドとは ・「SPDR（スパイダー）」はブランド名です。ステート・ストリート社が運用しています。 ・アメリカ株の産業別に投資するETF商品です。 ・S&P500を構成する500銘柄を、11部門に分けたものです。11部門のETFを時価総額に応じて合計すると、S&P500に等しくなります。 ・不動産、コミュニケーション以外の9部門は1998年12月に運用開始。 ・不動産部門は2015年10月開始。コミュニケーション部門は2018年6月開始。いずれもS&P500の部門分けが再編されたことに伴い、新設されたらしいですね。 過去5年の基準価額推移ランキングです。 情報は2020年5月現在。基準価額推移はTradingViewより。 経費率はいずれも0.13％。 セレクト・セクターSPDRファンド ティッカー 純資産総額 (100万ドル) 配当利回り (過去1年) 基準価額推移 (過去5年) 主な企業 テクノロジー Technology XLK 27,977 1.28% 120% マイクロソフト、 アップル、Visa 一般消費財 Consumer Cyclical XLY 11,637 1.44% 53% Amazon、ホームデポ、 マクドナルド ヘルスケア Healthcare XLV 24,940 1.60% 35% Johnson&Johnson 公益事業 Utilities XLU 11,582 3.55% 28% ネクステラ・エナジー 生活必需品 Consumer Defensive XLP 14,561 2.83% 18% P&G、ウォルマート、 コカ・コーラ 資本財 Industrials XLI 7,244 2.52% 12% ユニオン・パシフィック鉄道 金融 Financial XLF 16,748 2.75% 11% バークシャー・ハサウェイ、 JPモルガン 不動産 Real Estate XLRE 4,482 3.61% 11% アメリカン・タワー コミュニケーション・サービス Communication Services XL
続きを読む

【EDV】米国債ETFの買い時を考える【2021年3月版】

イメージ
去年に仕込んでおいた石油株の売却目途が立ちそうなので、次の長期枠として米国債ETFが候補にあがってきました。 米国債ETFを購入するに際して、とりあえず次の2点を前提しています。 ①長期スパン（5年以上）では、債券利回りは低下し続け、やがて0％になる。 ②米10年債利回りは、一時的に2～3％に上振れる。 過去50年の長期チャートを見ると、米10年債利回りは0％に向かって低下し続けてきました。言い換えると、米10年債の取引価格は上がり続けてきました。 「米10年債利回りが一時的に2～3％に上振れることはあっても、長期目線では0％に近づくはず」という予測に基づき、米国債ETFはかなり勝率が高い長期ギャンブルだと思っています。金利が一時的に上振れたとしても、どうせ数年以内にFRBが株価下落を防ぐために金利を引き下げると思われるので…。 2021年3月現在、米10年債利回りは1.6％に急上昇しています。今のところ、この利回り上昇トレンドはしばらく続くとの見方が多いですね。 したがって米国債ETFの買い時としては、10年債利回りが2％を越え、利回り上昇（10年債価格下落）が一服したときですね。10年債利回りが3％を越えると、ある程度自信をもって購入を進めることができそうです。今後、明確な買い時が訪れるかどうか知りませんが、買い時がなければご縁がなかったということです。 さて、米国債ETFには色々な商品がありますが、管理人的にはEDV(超長期米国債ETF)一択です。 ちなみに米国債の利回り（インカムゲイン）は重視してないです。あくまで売却益（キャピタルゲイン）狙いです。 詳細はこちらの記事でまとめてみました。1年前の記事ですが、長期的なトレンドは変わらないはずです。参考までに。 ●今後、EDVを分割購入するとして、EDVがPF（ポートフォリオ）に占める比率をどのように調整すべきか。自分なりにプランを立ててみました。 10年債利回り EDV価格 EDVのPF比率 0.5％ 170ドル 0％ 1％ 160ドル 0％ 2％ 130ドル 0～10％ 3％ 100ドル 30％ ●備考： ・買い時としては、10年債利回りが2％を越え、利回り上昇（国債価格下落）が一服したとき。 ・EDVのPF比率はあくまで目安。必要に応じて、他の安全資産（ゴールド、ビッ
続きを読む

外国株式インデックス　長期チャートを見る方法

イメージ
外国株式インデックス・ファンドの購入を検討するとき、過去の価格チャートは要チェックですよね。 でも、証券会社のwebサイトでは、長期チャートを見れません。 たとえば「eMAXIS Slim 米国株式(S&P500)」は、2018年スタートの投資信託で歴史が浅いため、過去の株価を確認できません。 そんなわけで今回は、外国株式インデックスについて、過去のチャートを見る方法を紹介します。 この記事では、三菱UFJ国際投信が販売する「eMAXIS Slim」シリーズを例に挙げますが、楽天やＳＢＩ証券など競合他社が販売する商品の場合も、考え方は同じです。
続きを読む

長短金利差（イールドカーブ）と米株価の相関性

イメージ
最初に断っておくと、難しいことは私には分かりません。長短金利差（イールドカーブ）が株価に与える影響をどれほど信頼していいのかも分かりません。 ただ、投資をするうえで予測は必須で、しかし今後1年の相場があまりにも予測不能なので、藁にもすがる思いで過去のチャートを私は眺めているだけです。 というわけで、今回は長短金利差と株価の相関性を調べてみました。
続きを読む

【投資日記 2022/12】今年1年を振り返る、現在のポートフォリオ、来年は円安だけは来ないよね？

イメージ
年末なので、今年1年の投資記録をまとめておきます。 ● 2022年末現在、私の運用資金は276万円です。 入金額は230万円。 通算リターンは46万円。プラス20％のリターンですね。 投資資金とは別に、銀行預金が120万円くらいです。いわゆる生活防衛資金というヤツですね。 私の全財産を合計すると約400万円です。 今年1年、結構労働したのに、お金があまり増えていません。投資成績が芳しくなかったためです（泣） 前年末は、投資による通算リターンが84万円（リターン40％）でした。 よって、今年は40万円ほど投資で損した計算になります。 すべての原因はビットコインが下がり続けているせいです。トホホ…。 ちなみに私の投資歴は3年です。 この3年をざっくり振り返ると、石油株で儲けて、ビットコインで損しました（以上です）。 ● こちらは現在のポートフォリオです。 こちらは運用成績です。 合計 評価額 (円) 合計損益 (現金を含む) 現金 (円換算) 投入金額 (円) 評価損益 2418410 20％ 342100 3001572 -19% 外国株 銘柄 評価額 (円) 現在の株価 (ドル、 1570・2842は円) 取得時の平均株価 (ドル、 1570・2842は円) 保有数 取得金額 (ドル) 評価損益 DIS ディズニー 73600 90.1 150.75 6 905 -40% 1570 日経レバ2倍 583170 13885.0 15392 42 -10% 2842(NAS100インバ) 195240 32540.0 30901 6 5% BTC ビットコイン 271000 16770.9 35500 0.11871 4214 -46% MARA(BTC)マラソン③ 145200 3.9 15.92 275 4378 -76% IAU ゴールドETF 647900 34.0 34.07 140 4770 0% EDV 米長期債 502900 92.4 93.77 40 3751 -2% 現金(買付余力) 342100 ※ ・評価損益は、現在保有中の株式の含み損益のこと。投入金額（株式購入金額）に対するリターンを表す。 ・合計損益は、売却済みの株式（現金化したもの）を計算に入れている。投資資金230万円に対するリターンを表す。 ● さて、BTC（ビットコイン）の含み益が-
続きを読む

【相場雑感 2020/05】米株式市場は曇り空。一方その頃、新興国株は…

イメージ
5月。ゆるめの相場雑感です。 ・S&P500（米株価）は、急落と半値戻しまでは早かったですが、以降、米株価は足踏みを続けています。上がりもせず、下がりもせず。 ・一方、EEM（新興国株価）の戻りは鈍い。犯人はたぶんブラジルです。 ブラジルではコロナ感染拡大が止まらず、医療崩壊間近だそうです。 ブラジル株価指数は回復の兆しなし。そしてブラジル通貨レアルは対ドルで下落を続けています。 その結果、EWZ（ブラジル株式ETF、ドルベース）は残念なことになっていますね。 ・そういえば、モデルナ社のワクチン開発が話題となっていますね。世の中にワクチンが出回るのは、早くても来年以降でしょうか…。アメリカとブラジル、両国の株価の開きが今後どうなるか、要注目ですね。 ・個人的には、「コロナがいつ収束するか」は予測不能なので、単純に割安な方に投資すればよいと考えています。したがって、ブラジルを含む新興国への投資を続ける予定です。今月の投資割合としては、アメリカ対新興国＝1：1です。 ・気が早い人は、コロナ以降の市場を動かす材料として、米中摩擦から米大統領選への流れに注目する人もいますね。
続きを読む