参考になればと導入の手順例を書き記します。エクセルは完全独学ですのである程度ご容赦願います。スマホで見ると何のこっちゃかなのでPCで見てください。
エクセルで何がわかるの？
・年間配当金
・持ち株の現在の利回り
・総合利回り
・グラフで可視化。など
概略
・Googleスプレッドシートで「株価」「利回り」取得し、エクセルシート②に手動コピペ。
・エクセルシート③に銘柄情報記載。
・エクセルシート①に株数、購入単価記入。②③を読み込み管理していく。って流れです。
※スプシ上で全て管理出来ますが、銘柄が多くなると読み込みに時間が掛るのでオススメしません。
準備するもの
・PC、エクセル
・Googleアカウント
■手順1 Googleアカウント登録
■手順2 スプレッドシートログイン
■手順3 スプレッドシートで株価・利回り取得
■手順4 【データ】にコピペ
■手順5 【コード】編集①
■手順6 【コード】編集② 月別配当割合
■手順7 読み込み【コード】より
■手順8 読み込み【データ】より
■手順9 計算式入力
■手順10 配当金の計算、総利回り算出、月別配当計算
■手順11 銘柄を追加する
■手順１ Googleアカウントの登録
まずは、Googleアカウントを作成します。
①Googleアカウントの種類を選択する
②氏名とユーザー名・パスワードを登録する
③生年月日・性別を登録する
④利用規約に同意する
■手順2 スプレッドシートログイン
Google スプレッドシートは、ブラウザ上で表計算ができるソフトです。
Microsoft Excel のような図表作成・表計算を、オンラインで行うことができます。
※Google Chrome（クローム）以外でも動くようですが、クローム推奨。
■手順３ スプレッドシートで株価・利回り取得
以降、
| セル番地 | 数式 or 文字入力 |
とします。
①ログインしたスプレッドシートの1行目テキスト入力。
| A1 | コード |
| B1 | 株価 |
| C1 | 利回り |
| D1 | 配当（手入力） |
②2行目以降入力します。
A2には証券コード（ここでは例でJT）
| A2 | 2914 |
| B2 | =IMPORTXML(CONCATENATE("https://kabutan.jp/stock/?code=",A2),"//*[@id='kobetsu_left']/table[1]/tbody/tr[4]/td[1]") |
| C2 | =if(INDEX((IMPORTXML(CONCATENATE("https://kabutan.jp/stock/?code=",A2),"//*[@id='stockinfo_i3']/table/tbody/tr[1]/td[3]")),1,1)="-",D2/B2*100,INDEX((IMPORTXML(CONCATENATE("https://kabutan.jp/stock/?code=",A2),"//*[@id='stockinfo_i3']/table/tbody/tr[1]/td[3]")),1,1)) |
③「アクセスを許可する」
すると株探さんのページから、『株価』と『利回り』を取得してくれます。（※頻繁に取得すると負荷が掛かるらしいので、たまにで。）
取得したい銘柄のコードをずらーっとＡ列に入力しておきます。
エクセル同様、B2:C2を選択後、C2右下隅「+」を掴み下方へドラッグしコピーします。
※スプレッドシートの保存先確認は、「ファイル」→「詳細」から。
Googleドライブに保存すると便利。
【メモ】表示→固定→１行。先頭行固定で更に見やすく。
③稀にクボタ（6326）の様に利回りが「－」と表示され、取得できない銘柄があります。
（利回り0％も「－」と表示されます）
↓スプレッドシートで取得した利回りを見ると0になっている
その場合は、D38（右隣の手入力欄）に「1株あたりの年間配当」を手入力します。
ここでは、「48」と入力する。利回りが表示される。
～ここからはエクセルの作業です。～＜主な流れ＞エクセル内に【シート】を３つ作り、【保有株】で配当管理。取得単価、持ち株数入力【データ】で株価、利回りを格納【コード】で銘柄情報、配当月、優待情報等を管理
■手順4 エクセル【データ】にコピペ
以降、【シート名】とします。
①エクセル新規ブックを開きます。
②シート名を変更します。Sheet1上で右クリックしシート名を変更します。
Sheet1～3を【保有株】【コード】【データ】へ。
※シートが足りなければ、+で追加します。
↓
③【データ】へテキスト入力。
| A1 | コード |
| B1 | 株価 |
| C1 | 利回り |
④【データ】２行目以降へ、スプレッドシートのデータをコピー＆ペーストします。
※「読み込んでいます」のアナウンスがない事を確認する。
■手順5 【コード】編集①
【コード】を編集します。優待や配当月などの銘柄情報を格納します。
①1行目は空けます。（ハイパーリンクでジャンプ用。検索用。別記事で解説します。）
②2行目には『証券コード』『月』『業種』『銘柄』『優待』『区分』『都道府県』『1月』････『12月』『確認』等から、必要と思われる項目を。
一番左は証券コードにします。あとの並びはテキトーで可。不要なものは省略可。
それぞれ、解説していきますね～
| 証券コード | 証券コード |
| 月 | 1～6までの6グループに分けます。（決算月）例：9月決算は「3」グループ。 |
| 判定 | 銘柄判定を記入。☆、◎、◯、×など自身の評価 |
| core30 | core30企業であるか。 |
| 225 | 日経225銘柄であるか |
| 業種 | 業種名 |
| 銘柄 | 企業名 |
| 長期保有 | 優待の長期保有の判定。◎、◯、△など |
| 優待 | 優待内容 |
| 優待条件 | 1年以上保有、300株以上、等条件を記入。 |
| 区分 | Ｐ、Ｓ、Ｇ。（プライム、スタンダード、グロース） |
| 都道府県 | 本社所在地 |
| 配当情報 | 月別配当割合記入【手順6で解説】 |
③3行目以降に
持ち株 や 狙っている銘柄 の情報を記入していきます。ここがちょいと面倒な作業。
コード・銘柄名・業種・都道府県・区分は、↓からコピペして作りました。
■手順6 【コード】編集②月別配当割合
月別の配当（アバウトですが）を出したい場合は、
持ち株の＜配当月＞と金額を自身で調べ、配当情報欄に割合を計算し入力します。
例：
8058 <3月>35円<9月>50円 なので、<3月>0.41<9月>0.59
9831 <3月>13円<9月>0円 なので、<3月>1<9月>0
6392 <3月>180円<9月>100円 なので、<3月>0.64<9月>0.36
6059 <3月>5円<9月>5円 なので、<3月>0.5<9月>0.5
と手入力します。
※合計が１になるように入力。（AC列でSUM関数にて合計1を確認してます。）
※小数点以下一桁表示にしてます。
以上で、【コード】入力完了。
一旦休憩するにゃ。
■手順7 【保有株】編集①（コードから読み込み）
いよいよ最後のシート【保有株】の編集です。※体裁は一番最後に整えます。
①税率の設定
| I1 | 税率 |
| I2 | 0.79685 |
②3行目
| A3 | 月 |
| B3 | 区分 |
| C3 | 業種 |
| D3 | コード |
| E3 | 銘柄 |
| F3 | 口座 |
| G3 | 保有株数 |
| H3 | 単位 |
| I3 | 取得単価 |
| J3 | 配当利回り |
| K3 | 配当金 |
| L3 | 取得額 |
| M3 | 株価 |
| N3 | 時価評価額 |
| O3 | 利回り |
| P3 | 1月 |
| Q3 | 2月 |
| R3 | 3月 |
| S3 | 4月 |
| T3 | 5月 |
| U3 | 6月 |
| V3 | 7月 |
| W3 | 8月 |
| X3 | 9月 |
| Y3 | 10月 |
| Z3 | 11月 |
| AA3 | 12月 |
※上記以外の項目は後ほど追加で大丈夫デス
③保有している銘柄の「証券コード」「口座」「保有株数」「取得単価」を入力。
（ここでは例でJT）
| D4 | 2914 |
| F4 | 特定 |
| G4 | 100 |
| I4 | 4400 |
※一般口座は対応してません。
④E4に数式入力。
| E4 | =INDEX(コード!$B$3:$AB$3852,MATCH($D4,コード!$A$3:$A$3852,0),MATCH(E$3,コード!$B$2:$AC$2,0)) |
すると、【コード】から証券コードに合致する銘柄名が呼び出される。
⑤E4セルを選択。右クリック→コピーを左クリック
⑥A4セルを選択後、右クリック→貼り付けを左クリック。
⑦A4を選択後、A4右下隅「+」を掴み（右方向へ）C4までドラッグしコピーします。
※操作が分からない場合は「フィルハンドル」で検索
⑧配当月と割合を呼び出す。
| P4 | =IF($F4="特定",INDEX(コード!$B$3:$AB$3852,MATCH($D4,コード!$A$3:$A$3852,0),MATCH(P$3,コード!$B$2:$AB$2,0))*$I$2,INDEX(コード!$B$3:$AB$3852,MATCH($D4,コード!$A$3:$A$3852,0),MATCH(P$3,コード!$B$2:$AB$2,0))) |
⑨P4を選択後、P4右下隅「+」を掴み（右方向へ）AA4までドラッグしコピーします。
■手順8 【保有株】編集②（データから読み込み）
①株価、利回りを呼び出し。
| M4 | =INDEX(データ!$B$2:$C$70,MATCH($D4,データ!$A$2:$A$70,0),MATCH(M$3,データ!$B$1:$C$1,0)) |
| O4 | =INDEX(データ!$B$2:$C$70,MATCH($D4,データ!$A$2:$A$70,0),MATCH(O$3,データ!$B$1:$C$1,0))/100 |
②利回りを「％」表示へ
O4セルを選択→右クリック→セルの書式設定→パーセンテージを選択。（小数点桁数は"2"）
■手順9 【保有株】編集③（計算式入力）
①計算式入力。
| J4 | =M4*O4/I4 |
| K4 | =IF($F4="特定",M4*O4*G4*$I$2,M4*O4*G4) |
| L4 | =I4*G4 |
| N4 | =G4*M4 |
※J列は「％表示」に変更しましょう。
②数値は、3桁カンマ区切りすると見やすくなります。
範囲選択→右クリック→表示形式→数値→桁区切り（.）を使用する。
■手順10 配当金の計算、総利回り算出、月別配当計算
①テキスト入力、数式入力。
| J1 | 総合利回り |
| J2 | =K2/L2 |
| K1 | 年間配当金 |
| K2 | =SUM(K3:K4) |
| L1 | 総取得額 |
| L2 | =SUM(L3:L4) |
※J2は「％表示」に変更しましょう。
②月別の配当を算出
| P5 | =SUMPRODUCT($M$3:$M$4,$O$3:$O$4,$G$3:$G$4,P3:P4) |
③P5を選択後、P5右下隅「+」を掴み（右方向へ）AA4までドラッグしコピーします。
※操作が分からない場合は「フィルハンドル」で検索
④P5～AA4の表示形式を「数値」に変更。※少数点以下の桁数0
範囲選択→右クリック→表示形式→数値→桁区切り（.）を使用する。
■手順11 銘柄を追加する
①４をクリック。（４行目が全選択される）
②右クリック→挿入。
③5をクリック。（5行目が全選択させる）
④（選択部で）右クリック→コピー
⑤4をクリック。（４行目が全選択される）
⑥（選択部で）右クリック→貼付。
⑦「コード」「口座」「保有株数」「取得単価」を編集します。
銘柄を更に追加したい場合は、再度同じ作業を。
※上で紹介した他にもやり方はありますので、ご自分の好きな操作方法で作業しましょう。
※手順7で追加しなかったcore30や225などの情報を入れたい場合は、列を挿入して追加してください。
以上デス。
後は、ご自分で好きなように罫線引いたり、網掛けしたり、色つけたり、先頭行固定したり装飾してください。
グラフも追加するとこんな感じになります。
お疲れ様でした。Zzzz
続いて小技集です。
