Kanchan(@kanchanblog)です。
資産を日本株、米国株、投資信託、仮想通貨、コモディティと分散させて運用していますが、価格をチェックするのが大変になってきました。
そこで、以前資産ポートフォリオの現在価格を自動的に取得する方法について調べてこちらの記事に書きました。
しかし、ある日突然一部が機能しなくなりました。
VBAでの参照元がアクセスを拒否するようになったのです。
外部ページを参照している以上、HTMLの構造が変わったり、表示形式が変わったり、プログラムでのアクセス対策がなされて使えなくなることは付き物です。
スクレイピングはサーバーへの負担がかかる行為で、禁止しているサイトもあります。
Yahoo!ファイナンスも禁止サイトの一つになっています。
繰り返していると出禁になってしまう可能性もあるので、配慮するようにしましょう。
VBAなしで株価自動取得ファイルを作る方法
さて、本題。
株価を自動で取得する方法について３つ紹介します。
資産をまとめるのがかなり楽になります。
日本株（ETF含む）、米国株（ETF含む）、投資信託、暗号資産、先物、為替までは自動取得が可能でした。
今回は題材として私のポートフォリオから、自動取得したい銘柄たちをリストアップして、順番に進めてみることにしました。
Excel株価取得機能
Excel2016以降で使える機能になりますが、米国市場で取引されている銘柄や先物、為替レートについては自動取得が可能です。
Microsoft365のサブスクに入っているなら使えます。
セルにティッカーシンボルを入力して、そのセルを選択した上で、データタブ＞株式(英語)を押します。
すると、家の様なマークが付いて、銘柄の正式名称が表示されます。
隣に出るノートの＋マークをクリックすると、リアルタイム表示できる項目の一覧が出てきます。ETFと個別銘柄で少し異なります。
現在株価と変化率(前日終値から)を表示させてみます。
【銘柄を表示しているセル】.Priceが株価を表示する数式のようになりました。
変化率は.[Change(%)]となっています。
このセルをコピーするだけで米国株の株価は埋まってしまいます。
Excelの機能の一つなので、とても楽です。
主要な外国市場で取引されている先物や為替レートや暗号通貨についても対応されています。
通貨、暗号通貨の略語を入力して、株式(英語)の隣の通貨(英語)をクリックします。
すると同じように.Priceで現在価格、.[Change(%)]で変化率というように表示出来るようになります。
暗号通貨についてはビットコイン(BTC)、イーサリアム(ETH)、リップル(XRP)は、円価格、ドル価格、ユーロ価格を表示することが出来ましたが、その他のコインたちは2022年1月現在対応していないようでした。
また、ティッカーシンボルが似ている銘柄や意図しない通貨建て価格に紐付けされることもあります。
その場合は右クリック＞データの種類＞変更で、データ選択ウィザードを開き、選び直せます。
ここまでで通貨、米国株、暗号通貨の自動取得が可能となりました。
Ctrl＋Alt＋F5、データタブ＞すべて更新 でいつでも最新株価にすることが出来ます。
Market SPEEDⅡ RSSを使用する
Excelの機能では日本株は対応していませんでした。
日本株については楽天証券が出しているMarket SPEEDⅡというツールに付属するRSS(以下MS-RSS)か、次に説明するWebクエリが良いと思います。
Webクエリはウェブサイトから情報を取得することになるため、参照元のサイトが構造を変えたりスクレイピング対策を打たれたりすると、使えなくなる場合がよくあるため、運営元が明確で安定して使えるMS-RSSの方をお勧めしたいと思います。
日本株と日本市場で取引されている先物、オプションに対応しており、日本株対応外のExcel株価取得機能と補完関係になっていて、組み合わせて使いたくなります。
昔のMarket SPEEDは楽天証券で手数料の発生する取引が一定期間内にあることが使用条件でしたが無料化されて利用する敷居が下がりました。
MS-RSSのセットアップのやり方はこちらが参考になります。
セットアップが完了すると、ExcelにマーケットスピードⅡタブが登場します。
Market SPEEDⅡでログインして、未接続⇒接続中に出来たら使えます。
投資情報をクリックして、銘柄コードと表示したい項目を入力すれば一発で表示されます。
株価がリアルタイムで表示され、セル内は＝RssMarket(“銘柄コード”,”表示したい項目”)の数式となっています。
コピーして、銘柄コードを変えれば、変えたコードの株価になって便利です。
株価が一通り表示出来たら、セルをコピーした後で、表示したい項目を一括で置換してしまえば表が完成します。
現在値 を 前日比率に変えると表示したい項目に出来ました。
先物についても同じように出来ます。
投資情報で出るウィンドウで、一番上のタブを市況情報(先物OP)に変更、詳細ボタンを押します。
商品種類を商品先物に絞って検索をかけるとコモディティ先物の一覧が出てきます。
沢山ありますが適当に22-12月限の金価格と白金(プラチナ)価格を表示することにしました。
投資信託以外の項目が埋まりました。
Webクエリを使用する
最後に投資信託の基準価額を自動取得していきます。
ここまでのツールで対応していない部分です。
WebクエリというExcelの機能を使います。
外部のホームページに自動アクセス(スクレイピング)する方法となってくるので、そのページがスクレイピングを禁止していないかはチェックしておきたいところです。
チェックする簡単な方法は、ホームページで/robots.txtを付けてアクセスしてみることと、規約を確認することです。
株価情報を更新してくれているサイトは幾つかありますが、スクレイピング禁止となっていたり、対策されていてWebクエリでは見えなくなっているところも多いです。
有名なところでは、
みんかぶ、Yahoo!ファイナンスではスクレイピング禁止となっていました。
SBI証券、JPXは禁止ではありませんでしたが、Webクエリで表示させてくれない構造となっていました。
かぶたんは投資信託の対応がなく、
楽天証券のHPで投資信託の基準価額を参照するのが現状では最もやりやすかったです（いつ対策されて使えなくなるかは冷や冷やしますが）
まず参照したい投資信託のページを開き、URLをコピーします。
次に、データタブからデータ取得、Webから、とクリックしてWebクエリを起動します。
コピーしたアドレスを貼り付けて、OK
ウェブページが要素ごとに表示出来るようになりました。
Tableを探していくと、Table5に基準価額の情報が入っていることを確認。
Table 5を選択して読み込みを押します。
すると、Table 5というタブが作られ、テーブルがExcel上に記録されます。
同じ要領でeMAXIS Slim全世界株式、楽天バンガード全米株式インデックスファンドについても抽出すると、作成した順にTable 5が量産されてTable 5 (2), Table 5 (3)となります。
最後にExcelの小技で、この表記から基準価額の数字だけを抜き取ります。
LEFT関数で左から数えて任意の文字数だけ取り出し、そのままでは文字としての数字になってしまうのでVALUE関数で数値に変換してやります。
前日比率についても同様に、FIND関数を使って(と％の間にある文字列を抽出して、数値に変換する数式を書けばOKです。
最後に
少し長くなりましたが完成です。
あとは保有数量や為替レートをかけて円建ての総額を出したり、分類してポートフォリオのバランスを確認していけば使い勝手の良い自分用のポートフォリオ管理ツールとして機能してくれます。
複数の証券口座で持っているものを一元的に見たい場合には役に立ちます。
ただし、Webクエリでのスクレイピングはサイト側に負荷がかかる可能性があるので、時間を空けたり、頻回に更新しすぎないと言ったマナーは守って自己責任で行って頂くようお願いします。
皆さんの快適な投資のお役に立てれば幸いです。
