【投資】Excelメモ①
ぼくお気に入り銘柄の、これまでのリターンを分析するためのツールを作成中。
株価は毎日動くものなので、自動で株価取得してほしい。
配当金・分配金を自動で加算してほしい。
比較グラフを自動で作りたい。
この為の方法をしらべて、メモしたい(まさに備忘録)
【必要な物】
●パソコンと、Excel2018
●自分のお気に入り銘柄
【やりかた】
<1>Excelを立ち上げる
<2>適当な株価サイトで、「表」のページにしてURLをコピーする
①適当なサイトを開く(YAHOO!financeを愛用)
②Historical Data(株価が表になるタブ)
③Time Periodを2020年1月1日から今に
④FrequencyをMonthlyに
⑤Applyする
⑥このURLをコピーする
<3>Excelシートに、株価表を取り込む
①Excelの「データ」
②「Webから」
③先ほどのURL貼付
④OKボタン
※適当な株価サイト=「YAHOO!FINANCE」を使用
【Excel表の加工する】
この例では、Vanguard S&P500 ETF(VOO)を使用。
①ヨコ一列を挿入。これはADR株でないので、E1セルに「not ADR」
②H2セルに「Div」と書く。その下H3セルの数式は「=IFERROR(IF(MID($E3,1,7)=MID($E3,1,6)&"D",MID($E3,1,6),IF(MID($E3,1,8)=MID($E3,1,7)&"D",MID($E3,1,7),"")),"")」
※左の表から、配当金(非課税)のみを引き抜く為のセル。MID($E3,1,7)でE3セルの1文字目から7文字をみる、の意味。
I2セルに「Div(Taxed)」と書く。その下I3セルの数式は「=IF($H3="","",IF(OR($E$1="ADR","日本株"),$H3*0.79265,$H3*0.79625*0.9))」
※左の表の配当金を、課税後額を調べるためのセル。「ADR」「日本株」であれば税率20.375%が課税。一方で「not ADR」は、さらにアメリカ課税10%も追加。
③下までドロップダウン
④Buy Timingは、買った日付。
Gaind Divは、買った日付から今までの課税後配当金の合計。数式は「=SUMIF(A3:A65525,">="&$L2,I3:I65525)」で、SUMIF関数に「L2の日付以降の合計」となるよう、 ”>=”&$L2 と付けている。
【この表の意味】
買ったタイミングから、今までの値動き+課税後配当金を加味 できる事。値動きだけでなく、課税後配当金をふくめてトータルで考えたい為。
また「Webから取得」としたことで、更新ボタン一つで自動でトータルリターンを計算してくれる。