Kanchan(@kanchanblog)です。
分散投資をモットーにしています。
日本株、米国株、投資信託、金、銀、プラチナ、ビットコインと資産を分散させていくと、最新株価のチェックが大変になってきます。
一か所でまとめて取得、管理できないかなと気になって探していましたが、
楽天証券の提供するマーケットスピードのRSSはまだ米国株への対応がなく、
米国株はOffice 365のExcelの中にティッカーシンボルから銘柄の最新データを取得する機能が追加されていますが日本株への対応はなく、
日本株、米国株、投資信託の価格を取得してくれるツールを見つけましたがBTC、貴金属への対応はないといった具合に一発解決という訳にはいきませんでした。
それでも組み合わせれば行けそうで、初心者ながら頑張ってみたので記録しておきます。
分散投資をしている人
最新の資産価格を一括収集するツールを探している人
Office365を使用している投資家
（準備）マクロ、VBAを使えるようにする
初期設定のExcelではマクロ・VBAを作るのに必要な「開発」タブが無いかと思います。
使えるようにするには、ホーム画面の「オプション」を開きます。
Excelのバージョンによってこの画面は変わると思いますが、リボンユーザーの設定＞□開発にチェックを入れる＞OK
または、
□[開発]タブをリボンに表示する にチェックを入れるバージョンもあります。
これで開発タブが現われ、Visual Basic（VBA)、マクロを作ることが出来るようになります。
株価を取得する
株価を取得したい銘柄のティッカーシンボル・コードを1列に並べます。
米国株、日本株、投資信託まで一気に取得できます。
その隣の正式名称はあってもなくても構いません。
富士宮で貯蓄と資産運用のmochiさんの無料公開しているVBAコードを使わせて頂いています。（感謝です）
コード名から前日の株価、現在の株価を取得してくれるため、数式を手入力すれば変化率まで自動で出せます。
保有数量も入れて掛け算すれば保有額が自動で出せて、ポートフォリオが一気に見えるようになります。
まずは、開発タブからVisual Basic（VBA)を開きます。
シートの中のVBAコードを書くモジュール（画面ではThisWorkbook）をクリックすると、まっさらなシートが出てきます。
ここにコードを書いていきます。と言うか、コピペします。
エクセルで株価を自動取得するＶＢＡコードを紹介します。（日本株・米国株・投信対応版） のリンク先にコードのコピーボタンがあります。
上記のVBAコードをコピーして、貼り付けると出来あがり。
銘柄コードのマスの位置と、その銘柄の株価を取得する位置を修正して自分のExcelシートに合うようにアレンジする必要があります。
上記のサイトでDLしたファイルに入力する場合はそのままで良いんですけどね。
毎回VBAコードを表示させて、▶︎「実行」をクリックしてもマクロとして動作するのですが、手間になるのでマクロを起動するためのボタンを設置します。
開発タブ＞挿入＞フォームコントロール＞ボタンを選択します。
ドラッグしてボタンの位置を決めると、紐付けするマクロの一覧が出てくるので先ほど作ったマクロである「ThisWorkbook.ボタン1_Click」を選んでOKを押します。
これで使えるのですが、格好悪いので
右クリックでボタン上のテキストを編集して、「最新情報取得」ボタンとしました。
VBAコードの冒頭で check_row = 4 code_column = 2にしておくと4行目、2列目（＝B列）であるB4から銘柄コードを読み込んで、指定した列に前日株価、現在株価を取得していくプログラムになっています。
米国株はティッカーシンボル。
日本株は4桁のコードのあとに個別株であれば証券取引所（殆ど東京証券取引所のT）の頭文字を付けて.Tとします。
日本のETFの場合は「.E」を付けます。
投資信託の場合は投信協会コードを入力します。
そしてボタンをポチッと押すと、
最新株価の一覧が一気に完成。
プログラミングで一番快感が得られる瞬間です。
「脳汁が出る」というやつです。
変化率は =（前日株価−現在株価）×100/（前日株価）
保有額 ＝ 現在株価 × 保有数量
で求められます。
投資信託の注意点は、保有数量に保有口数を入れたら、更に0.0001を掛けた額が保有額になることです。
ここまでで日本株、米国株、投資信託の価格取得が完成しました。
為替、BTCの価格を取得する
次はドル円、金などの貴金属、BTC価格の取得をします。
Office365のサブスクに入っている場合に使える追加機能ということなので、買い切りのExcelを使っている方には対応していないと思いますので悪しからず。
内蔵された機能を使うだけなので難しさはあまりありません。
マクロも使えなくて大丈夫です。
ドル円なら「USD/JPY」
1BTCの円換算は「BTCJPY」
を入力したセルを選択した状態で、［データ］タブ＞データの種類＞通貨（英語）を選択します。
すると文字列の頭に家のようなマークが付きます。
セルの右側に出る「データを追加」マークをクリックすると、
現在値、変化率、取引所、最終取引時刻など表示させられる項目の一覧が表示されて、
選ぶとセルの右隣に追加されていきます。
ドル円を表示させられると、ドル建ての株価を日本円の価値に直して表示させられて便利になります。
米国株もこの機能でティッカーシンボルを入力したセルを選択して「株式（英語）」を押すと、同様に株価、変化率、高値、安値、終値、出来高、経費率など様々なデータを表示させることが出来ます。
最新の値を取得したい時は、「Ctrl+Alt+F5」を押すと更新されて最新になります。
貴金属の価格を取得する
最後に貴金属の価格ですが、難関でした。
貴金属の現物価格は証券会社によって異なり、簡単に取得出来るツールは見つかりませんでした。
ETF投資をすると決めて、金はGLDやGLDM、銀はSLV、プラチナはPPLTにするのであれば、米国株として価格を取得出来るので十分です。
私は一部現物で購入しているので、表示方法を探しました。
Gold、Platinum、Silverと入力して、株価（英語）で出してくれれば良いようなもんですが、
近い名前の外国株が表示されてしまいます。
ティッカーシンボルGOLDはBarrick Goldという金鉱山会社ということは知っていましたが、PPMH、GUANGDONG Silver ageは知らなかったです。。。
ドル建ての貴金属の先物価格ならExcelの機能で取得できることが分かったので、そこから円/g換算することにしました。きっと相関するはずですから。
誤った銘柄選択がされてしまったセルを右クリック＞データの種類＞変更で改めてGold, Platinum, Silverをそれぞれ検索すると、
@GC0Y future GOLD
@PL0Y future Platinum
@SI0Y future Silver
という先物価格が選択肢に現れるので選択します。
すると、それぞれの先物価格を取得することが出来るようになりました。
1トロイオンス(≒31.1g)あたりのドル建て価格のため、円/gに直すには、
USD/JPYを掛けて円にして、31.1で割ると1gあたりの円価格になります。
証券会社によって異なりますが日本の金価格に直すには±2%程度の係数を掛けてあげると売買手数料などを調整した日本円での売値に近づくかなと思います。
日本の取引所が閉じた後、米国の取引所だけが動いている時間には差が大きくなることもあるので、日米どちらも動いていて、値動きが落ち着いている時に換算値を近付ける「係数探し」をしてみると良いと思います。
これで完成しました。
注意点
mochiさんのサイトにも注意書きがありますが、Yahooファイナンスから取得するコードのためサイトの構文が変わると使用出来なくなる可能性がありますし、メンテナンス中やサーバーが落ちている時にも使えません。
頻繁にボタンを押すとサーバーに負荷がかかってしまうため自重しましょう。
スクレイピング禁止のサイト…（おっと誰か来たようだ
作ってみた感想
ボタンをクリックして、「Ctrl+Alt+F5」を押すと最新のポートフォリオが表示されるようになりました。
とても快適で、ポートフォリオ集計の時短になりました。
何より自分で作ったものには愛着が出て、使いたくなります。
色々なパラメータを表示させて分析にも使えると思います。
快適な投資生活の一助になれば幸いです。
参考
エクセルで株価を自動取得するＶＢＡコードを紹介します。（日本株・米国株・投信対応版）
