【株価を自動取得】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

コメント

コメントを投稿

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

このブログの人気の投稿

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

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

セレクト・セクター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
続きを読む

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

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

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

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

3種のギャンブルを組み合わせるという投資戦略

 「現代ポートフォリオ理論」には難しい数式とか出てきて私もあまり理解できていませんが、要するに分散投資するときに相関性の低い投資先を組み合わせることで、全体として低リスク・高リターンな運用を目指すということなのだと思います。 小難しい理論を振りかざす人が必ずしも相場で儲けられるとは思いませんが、とりわけこの理論を実地に活かす際にネックとなるのが ボラティリティはボラタイルである ということです。 たとえば新興国株は高リスク高リターンで、先進国株は低リスク低リターンだとされているけど、今後もこの傾向が未来永劫続くとは限らず、この傾向がいつ転換するかをリアルタイムで相場に参加しつつ見極めるのは難しいはずです。我々が目にするボラティリティは常に過去の数値であるわけで、今後もこのボラティリティが不変であるとは限らないということです。 ただし肌感覚として、「相関性の低い投資先を組み合わせることで日々のボラティリティを下げる」という戦略の有効性は実感しています。管理人的に言い換えれば、性質の異なった3種のギャンブルを同時進行させるということですね。2021年2月現在、具体的には①石油株、②ハイテクグロース株、③その他雑多なペニー株やビットコインなどです。 まず①石油株について、今でこそ機関投資家のレポートにおいて「今年はエネルギーセクターが復活する！」などと言われていますが、2020年11月以前に石油株の購入を進めていた管理人としては「いつか必ずワクチン報道が出るはず。ワクチン完成のニュースさえ報道されれば、石油株は上がるはず。ワクチン報道さえ…」という祈りにも似た思惑で、暴騰するハイテク株を尻目に見つつ、ジリ貧の石油株をナンピン買いし続けていました（正直言うと、一部ハイテク株に乗り換えましたが…）。 結果的に11月上旬のワクチン報道後、私の思惑通り石油株が上昇したわけですが、「ワクチン報道」にベットしていたという意味で、石油株は勝ち目の高いギャンブルだったと思います。コロナ収束が見えつつある今は徐々にギャンブル要素が薄まりつつあり、それに伴って今後の期待リターンも徐々に下がりつつある気がしますが、石油株価がコロナ前に戻るまでは保有継続する予定です。 私の場合、主力の石油株がポートフォリオの3～4割くらいを占めます。これと組み合わせるのは②ハイテクグロース株です。具体的にはGA
続きを読む

ペニー株は指値売りと逆指値売り、いずれが有利か？

今回はいつにもまして間口の狭い記事です。べつに読まなくていいです…。 2020年4月初現在、KOPN、MARAなどの中小ハイグロ株やペニー株は上がりきらず下がりきらず、その結果、スイングトレードに有利な相場環境になっているようです。 スイングならば値幅を自分であらかじめ予測したうえでトレードすることになるので、必然的に「指値買い→指値売り」となりそうです。 というわけで、逆指値売り戦略と指値売り戦略について、つらつらまとめてみました。 ①逆指値売り戦略 安値で仕込んで噴火を待つ場合、予測に反した下落時に逃げられるように逆指値売りは必須。株価上昇に合わせて逆指値売り価格を上げることによって、リスク管理しつつ、株価上昇をどこまでも追尾できる。 ②指値売り戦略 自分でチャートから値幅を予測して、「安値で指値買い→高値で指値売り」する場合（いわゆるスイング）、予測に反した下落時にナンピンで手当てするための現金余力が必要。そしてさらに下落が続くなら、損切りもやむを得ない。よって大量買いは禁物。 これはリスク管理の話で、逆指値売り戦略では逆指値売り自体が急激な下落に対するリスク管理となります。一方、指値売り戦略では、別の方法でリスク管理する必要があるってことですね。 また逆指値売りだと予期せぬ株価暴騰にどこまでもついていくことができますが、指値売りだと利確後の暴騰を指を加えて見る羽目になります。 こう書くと、なんだか逆指値売りはいいことずくめのようですが、実際はなかなか上手くいきません。タイトな逆指値売り設定だと（現在株価マイナス5％くらい）、上昇トレンドにおける一時的な下振れで振り落とされるし、ルーズな逆指値売り設定だと（現在株価マイナス30％くらい）、あくまで万が一のためのリスクヘッジのつもりであってまさか刺さる訳ないとタカをくくっていたら本当に刺さってしまい、結構な損失を食らったりします。とくにKOPNなどボラの激しい銘柄の場合は、数日で30％動くことは普通にありますからね。 そもそも私の愛用しているSBI証券の米国株で指値と逆指値を同時に注文できるなら、こんなことで悩む必要ないんですけどね。ではまた。 （2021/04/11 誤記修正）
続きを読む

【投資日記 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（ビットコイン）の含み益が-
続きを読む