てくです。
SBI証券でアメリカ株投資をしているとSBI証券が提供している情報で扱いにくいなぁと思う点があります。
どこの情報が扱いにくいと感じているかというと外国株の「保有証券・資産」のページです。
きっと、こう感じるのは、わたしのように好きな切り口で色々とデータを見たいためにGoogleスプレッドシート等でデータを管理している人に限られますが。
そこで、SBI証券の「保有証券・資産」のページを簡単にGoogleスプレッドシートで扱う方法を説明したいと思います。
SBI証券の「保有証券・資産」ページが扱いにくい理由
実際の説明に入る前になぜSBI証券の「保有証券・資産」ページがGoogleスプレッドシートで扱いにくいのか考えてみると、以下の点に集約されます。
「「保有証券・資産」ページは表形式で表現されているが1つのセルの中に複数の情報が含まれている」
ということは1つのセルに入っている複数のデータを単一列として分離できれば、Googleスプレッドシートで扱いやすくなります。
実際のスプレッドシート
以下のGoogleスプレッドシートの「SBI証券WEBのコピー」シートと「銘柄一覧」シートになります。
これから説明する方法を実現した実際のGoogleスプレッドシート
各シートの説明
「SBI証券WEBのコピー」シート
「SBI証券WEBのコピー」シートはSBI証券の外国株の「保有証券・資産」のページをコピーして貼り付けるためのシートです。
以下のようなイメージになります。
A列はポートフォリオの種別を表しています。わたしの場合、「てく」、「てく-NISA」、「嫁」、「嫁-NISA」の4種類です。ここは1行ごとに手動入力する箇所です。特にポートフォリオを分ける必要がない場合は空でもOKです。
B列以降は貼り付ける箇所になります。
「銘柄一覧」シート
「銘柄一覧」シートは「SBI証券WEBのコピー」シートから、Googleスプレッドシートで扱いやすいように単一セル単一データの形に自動で変換してくれるシートです。
以下のようなイメージになります。
実際に作業をするのは「SBI証券WEBのコピー」シートのみです。「銘柄一覧」シートは自動で変換してくれるので、何も手作業をする必要はありません。
手順
- Google ChromeでSBI証券の外国株の「保有証券・資産」のページにアクセスします。
Google Chromeでしか試していません。他のブラウザで動作するかは・・・知りません。
- 以下のように保有銘柄を選択し、コピーします。
- Googleスプレッドシートの「SBI証券WEBのコピー」シートのB2に貼り付けをします（ポートフォリオの列(A列)は手動で入力してます）。
- 貼り付けるが完了すると「銘柄一覧」シートは以下のように表示されます。
こうすることで、簡単に保有数量、購入単価、購入金額のドルと円双方のデータを扱いやすくなります。
コメント
大変便利なスプレッドシートを惜しげもなく公開していただいてありがとうございます。
関数の使い方なども知らないことばかりで勉強になります!
はじめまして、Mr.Sundayさん。
スプレッドシート参考になれば幸いです。
もし分からないこと等あれば、聞いていただければと思います。
今後ともよろしくお願いします。
はじめまして。
スプレッドシートで米国株の管理が出来ないか調べていたところ、こちらの記事に行き着きました。
とても素晴らしいスプレッドシートを公開してくださりありがとうございます。
早速使わせていただいたのですが、私自身スプレッドシートをあまり使ったことがなく、
1点よくわからない減少が起きてしまっています。
以下私が行ったことと問題点を記載いたします。
■行ったこと
1．スプレッドシートの所有していない株情報を削除
2．SBI証券WEBのコピーに現在保有中の米国株を１つ貼り付け
3. 「銘柄サマリ」を確認すると「保有株数」「＄購入金額」「￥購入金額」の３つの項目が一つ下のセルに入ってしまいます。
「銘柄サマリ」
■正
E2「保有株数」
K2「＄購入金額」
AA2「￥購入金額」
■現状
E3「保有株数」
K3「＄購入金額」
AA3「￥購入金額」
スプレッドシートをお借りしておいてなんですが、
お手数ではありますが、対処方法をお教え頂けますでしょうか？
よろしくお願いいたします。
スプレッドシートはこちらになります。
https://docs.google.com/spreadsheets/d/14prK-rEUcL7JI1fxT8n-9wxNBnj1lUEDjrNqH2nQMhA/edit?usp=sharing
はじめまして、hanayaさん。
返事が遅くなってしまい申し訳ないです。
提示いただいたシートを確認しました。
E2、K2、AA2に項目が入っていますが、すでに解決したということでしょうか？
お返事ありがとうございます。
お返事確認出来ておらず申し訳ありません。
再度スプレッドシートをコピーさせていただき、
・「SBI証券WEBのコピー」のデータだけ削除
・保有している米国株を入力
してみました。
もう一度貼らせていただきますので、お手数ですが再度見て頂けますでしょうか？よろしくお願いいたします。
https://docs.google.com/spreadsheets/d/15ZTC-zWWeM_SPUU3JDbYDUJw-GrZDhhGFuWWYJKRiJY/edit?usp=sharing
ずれているのは
E2、K2、AA2 になります。
画像：https://gyazo.com/e1fb45adfc56b0eaa1c914eb2508d112
あけましておめでとうございます。
hanayaさんのシートで起きる理由が分かりました。
「SBI証券WEBのコピー」シートにデータのない行を想定していなかったためです。
公開しているSpreadSheetに修正を加えました。
今度は大丈夫なはずです。
確認いただけると嬉しいです。
修正内容は、E2、K2、AA2の数式の「WHERE B IS NOT NULL」の後に「 and B != ‘#VALUE!’」を追加しています。
あけましておめでとうございます。
スプレッドシート修正していただきありがとうございます。
先程確認してみましたところ、無事枠内に収まっておりました。
お手数をおかけしてしまい申し訳ありません。
ありがとうございました。
お手間を掛けて修正もしていただきましたので、
バリバリ使わせていただきたいと思います！
こちらとても素晴らしいシートを公開頂きありがとうございます。
有料のシートを購入するも使いこなせず、改めて探していたところこちらに行き着きました。
サマリーのみやすさ、必要情報の網羅性が素晴らしく、早速試しに使わせて頂いているのですが、残念ながら楽天証券しか口座をもっておらず、現在は手打ちで試しながら使用しようと試みております。
お聞きしたいことは３点ございます。
１）
楽天証券でも同様のことをできるように改修したいのですが、その場合不要となる欄などはございますでしょうか？（私も現在調べております）
２）
関数を組んでティッカーを入力するだけで、もろもろ情報取得できる形に修正しようと思っているのですが、そちらをやらずに銘柄管理を手入力にしている理由ございますでしょうか？重くなる、関数の組み方上、等あるかと思いますが、後者の場合不具合が発生しそうな箇所ございましたらご教示いただけますでしょうか？
無料公開頂いておきながらのご相談で恐縮ですが、 楽天証券版で運用できましたら逆に公開させていただきたく思いますので、どうぞ宜しくお願い致します。
はじめまして、mahonmaさん
返事が遅くなってしまって申し訳ないです。
1)について
基本的に証券会社から取得したデータは Ticker、保有株数、購入金額（ドルと円）を利用しているだけです（評価損益は計算式で計算しています）。
2)について
そっ、そうですね。確かに言われてみれば、いらなさそうですね。後から「SBI証券WEBのコピー」を付けたので、名残ですね。
気を付ける点としては、証券会社からのデータを使う場合、売却した銘柄が一覧に出てこないので、売却した銘柄も一覧で表示したい場合は別途株売買シートから取得する必要がありますね（あまりないとは思いますが）。
事前の連絡ありがとうございます。公開することに関しては問題ありません。
以前から大変便利に利用させていただいております。
このような有用なシートを公開いただき感謝申し上げます。
過去のシートのトラブルについてはこちらのコメントを確認したり自分で調べて
たいがい解決出来ているのですが、どうしても解決出来ないことがありますので
相談させてください。
「PFサマリシート」の一番下の受け入れ配当の表が2020年１月までしか表示されない状況です。参照先の「受取配当シート」を確認したところ44行まで行目まで入力していますが、22行目（2020年１月の配当）までしか反映されていません。
増加額も反映されない状況です。
反映されていない表の上の二つの表「受取配当額推移(ドル)」と「配当額(月単位)と3か月平均」は問題無く入力した2020年１月より後の全てのデータが反映されています。
データ範囲指定の問題と思い当該表の参照データ範囲を確認しましたが
「’受取配当_pivot’!A1:O10」となっておりよくわからない状況です。
お忙しいところ恐縮ですが、お時間のあるときに解決方法を御教授いただけますと幸いです。
どうぞ宜しくお願い申し上げます。
はじめまして、toricoさん
参照データ範囲を「受取配当_pivot!A1:O11」にしてもらうとうまくいくと思います。
一度試してもらえますでしょうか？
てくさま
早速のご返信ありがとうございました。
「受取配当_pivot!A1:O11」にしても反映されませんでした(T-T)
一度シートを閉じて再度開いても同じ状況です。
別に試したこととして、過去の配当に2月と7月の配当した銘柄がありませんでしたので
当然表には反映されていませんでしたが、受入配当シートの22行目までの配当日をわざと2月と7月があるようズラして全ての月に配当があるようにしましたら、一番右の増加額は反映されるようになりました。
受入配当シートの23行目以降が反映されない状況は変わりません。
表作成から同じ条件で新しい表を作成しても23行目以降が表示されません。
お手数をお掛けし恐縮です。
遅くなってすみません。
確認させてください。
「受取配当_pivot」シートの内容は正しく表示されてますでしょうか？
一度確認お願いします。
「受取配当_pivot」シートは普段は非表示になっているシートなので、左下の5本線のアイコンをクリックしてから「受取配当_pivot」をクリックすると表示されます。
てくさま
ご返信ありがとうございます。
非表示の「受取配当_pivot」シートが存在していたのですね。
てっきり「受取配当」シートをpivotしたものが「受取配当_pivot」シートに
なっていると思い込んでしまいました。
ご教示通り左下5本線アイコンから「受取配当_pivot」シートを表示させ
内容を確認したところ、「受入配当シート」の22行目までしか反映されて
いませんでした。
そのため「受取配当_pivot」シートのピボットテーブルエディタを確認した
ところ指定範囲が ’受取配当’!A1:AN22 となっており、こちらの指定範囲が
適切でなかったです。
指定範囲を ’受取配当’!A1:AN100 にしたところ「受取配当_pivot」シートも
「PFサマリ」シートも正常に反映されました！
今後、受取配当が100行以上になった場合には適宜調整いたします。
スプレッドシートは不慣れなため隠れたシートがあるとは思いもしませんでした。
これで全て正常なスプレッドシートになりました。
お忙しいところご教示いただき誠にありがとうございました。
本当に感謝申し上げます。
てく様
初めまして、ぽんたと申します。
自前ツールでの運用をしたことがなく、色々細かく管理したいと思っている中で、てく様のHPにたどり着き、非常に有益なツールを無償公開いただいていることに大変な感謝致します。
初心者のため、1から勉強中なのですが、1点教えてください。
SBIの外貨建評価損益の数字ですが、過去は正数での表示だったようですが、現在は「＋」が付くようになっており、その場合においてうまく銘柄一覧の評価損益に取り込みができない状況です。
どのようにすると良いかをご教授いただけないでしょうか。素人質問で大変恐縮です。。。
はじめまして、ぽんたさん
教えてくれてありがとうございます。
+がつくようになってうまく動かなくなっていたんですね。
動くように修正しました。
修正箇所は以下となります。
・「SBI証券WEBのコピー」シート
– C列「時価計算」の削除（不要のため）
– I列の書式を「書式なしテキスト」に変更
・「銘柄一覧」シート
– K2、O2のセルの式を修正
他にお気づきの点がありましたら教えていただけると助かります。