ExcelでOFFSET関数とROW関数を組み合わせて、ネオモバのポートフォリオ表を作ってみた

 

SBIネオモバイル証券(以下、ネオモバ)には以下の特徴があります。

・通常100株単位でしか購入できない国内の株を1株単位で購入できる

・通常取引ごとにかかる手数料が月額50万円までなら月額220円の定額制

・Tポイントが月200円付与され、少額投資なら実質的にほぼ無料で利用可能

国内株式を対象にして少額分散投資を始めてみたい方向けの証券会社です。

自分も投資経験を積むための一環として去年からネオモバを使い始めました。

 

証券会社を通じて購入した株式や債券などの保有状況は、証券会社のサイト上でポートフォリオ(所有資産の一覧表)を確認できます。

確認だけでなくCSV形式でのダウンロードができれば、他の証券会社の株式等や銀行の預貯金残高などのデータをひとつにまとめることも可能です。

一般的な証券会社であればCSV形式でのダウンロードはできるのですが、ネオモバはどうもできないようです。

元々、

スマホで気軽に株式投資したい！

という方を顧客対象にしている感があり、PCで開くサイトもどう見てもスマホ向けです。

スマホでしか画面を見なければ、CSVファイルをダウンロードしたいなんて需要もないだろう、という判断なのかもしれません。

 

CSV形式のダウンロードができないからといって、所有銘柄をひとつずつExcelに入力するようなことはめんどくさいからしたくない。

なんとかラクにできる方法はないか…

ネットで検索してみても、プログラミング言語のPythonを使ってスクレイピングする方法くらいしか見つからず。

今の自分にはちょっと敷居が高く、これは断念。

ExcelのOFFSET関数とROW関数を組み合わせてみたらうまくいったので、今回はその方法を紹介します。

 

OFFSET関数とROW関数とは

まず、OFFSET関数の説明から。

 

下の画像ではF2セルにOFFSET関数を入れています。

OFFSET関数を入れた結果、F2セルにはB4セルの「73」が表示されます。

 

 

=OFFSET(基準となるセル,移動する行,移動する列)

OFFSET関数には3つの引数を入れます(4つ目、5つ目の引数もありますが今回は省略できます)。

＝OFFSET(A2,2,1)

という式で、

「A2セルから、2行移動、1列移動した先のセルを表示して！」

という意味になります。

 

お次はROW関数。

下の画像ではF2セルにROW関数を入れています。

 

ROW関数に引数を入れない場合、関数を入れたセルの行番号を返します。

F2セルに関数を入れたので、行番号の2が表示されていますね。

　

どちらの関数も、これだけ見ると

「え、それだけ？」

って思いませんか？

自分は学習した当時思いました^^

これができたからってなんの役に立つんだろー、って。

実際単独で使うことは少ない関数だと思います。

他の関数と組み合わせることで効果を発揮する関数なんです。

 

ネオモバのポートフォリオをExcelに貼り付ける

まずは、ネオモバのポートフォリオを表示します。

 

次にCtrl+Aで全体を選択。

Ctrl+Cでコピーします。

 

Excelに「形式を選択して貼り付け」ます。

ホーム→貼り付け→形式を選択して貼り付け(またはAlt→E→S)

から、「Unicode テキスト」または「テキスト」を選択して「OK」をクリック。

 

A列にネオモバのポートフォリオの情報が貼り付けられました。

※数値はすべて仮のものです。

すべての情報が下に向かって表示されているだけで、これだけでは情報として使い物になりません。

 

まずは、数値に「円」がついているとExcelが「数値」でなく「文字列」と認識して集計などができずに不便なため、「円」マークを削除します。

置換機能を使います。

A列全体を選択したまま

ホーム→検索と選択→置換(Ctrl+Hも可)

でダイアログボックスが表示されます。

「検索する文字列」に「円」

「置換後の文字列」は空白のまま「すべて置換」をクリック。

これで「円」のみを一括削除できました。

 

OFFSET関数とROW関数でポートフォリオ表を作る

ネオモバのデータを貼り付けたシートには「ネオモバ」と名前をつけ、Excelで表示するポートフォリオ用に別のシートを作成します。

今回はシート名を「集計表」としました。

1行目には見出し名(「銘柄」、「評価額」、「評価損益」)をつけ、2行目から集計用に使います。

 

「ネオモバ」シートに貼り付けたデータから、A列に「銘柄」、B列に「評価額」、C列に「評価損益」を引っ張ってくるのが目標です。

さっそく、A列の「銘柄」を見ていきましょう。

 

 

OFFSET関数の中にROW関数を組み合わせることで「ネオモバ」シートから銘柄名や数字を引っ張ることができます。

関数の中身がわかりづらくなっていますが、日本語に直すとこんな感じです。

「$」の意味記事が長くなるのでここでは省略しますが、これをつけないとうまくいきません。

特に2つ目の引数の意味がわかりづらいと思います。

「ROW()-2」とすることで、2行目A2セルではROW()が「2」となり「2-2=0」となります。

結果的に基準となるネオモバシートのA25からまったく移動せずに「ネオモバ」シートのA25セルの「日本ケアサプライ」がそのまま入ります。

続いて、A3セルにはA2セルをコピーします。

 

A3セルでも式はA2セルとまったく同じですが、表示されている銘柄がA2セルとは異なります。

これは「ROW()」の返す数字が、A2セルとA3セルで異なるからです。

A3セルでは「ROW()」は行番号の「3」となり、「ROW()-2」は「3-2=1」となります。

「7*(ROW()-2)」は「7×1=1」となり、「「ネオモバ」シートのA25セルから7行下に移動して！」ということになり、結果的に「日本たばこ産業」が表示されます。

最後の引数は「0」なので、常に列は移動しません。

さらに「日本たばこ産業」の下のA4セルを見てみます。

「ROW()」が行番号の「4」となるので、「7*(ROW()-2)」は「7×(4-2)=14」となり、「「ネオモバ」シートのA25セルから14行下に移動して！」ということで「ブリヂストン」が表示されます。

「ネオモバ」シートを見ると、7行ごとに銘柄が表示されているのが確認できます。

 

「評価額」も「評価損益」も「銘柄」と考え方は同じです。

「評価額」列の2行目、B2セルに

=OFFSET(ネオモバ!$A$29,7*(ROW()-2),0)

「評価損益」列の2行目、C2セルに

=OFFSET(ネオモバ!$A$31,7*(ROW()-2),0)

と入れて(A2セルをコピーして下線部分だけ修正すればOK)、あとは銘柄数の分だけ下方向にコピーすればポートフォリオ表の完成です！

 

ここまでできれば、後はこの表を

コピー→値のみ貼り付け

で、他の資料などにいくらでも活用できますね。

 

「ラクをしたい！」という想いが効率化につながる

「銘柄を全部手入力するなんて絶対やだ！！」

「プログラムを組まなくてもなんとかならないかなー」

と悶々とラクをしたい方法を考えてたら、ふと今回記事にした方法が思い浮かびました。

この方法なら銘柄が100あろうが1000あろうが時間はたいしてかかりません。

一番最初の式をコピーするだけですからね。

 

このアイデアが浮かんでから身につけるまでそれなりに時間はかかりましたが、効率化のための必要経費と考えれば惜しくはありません。

「ラクをしたい！！」

という強い想いは効率化のきっかけになるとあらためて実感できました。

今後も「これはもっとラクに処理できるのでは？」という自分なりの直感を、効率化につなげていきたいと思います。