茶助の備忘録

★★現行ブログはコチラ

【投資】Excelメモ①

ぼくお気に入り銘柄の、これまでのリターンを分析するためのツールを作成中。

 

株価は毎日動くものなので、自動で株価取得してほしい。

配当金・分配金を自動で加算してほしい。

比較グラフを自動で作りたい。

 

この為の方法をしらべて、メモしたい(まさに備忘録)

 

【必要な物】

●パソコンと、Excel2018

●自分のお気に入り銘柄

 

【やりかた】

<1>Excelを立ち上げる

<2>適当な株価サイトで、「表」のページにしてURLをコピーする

 ①適当なサイトを開く(YAHOO!financeを愛用)

 ②Historical Data(株価が表になるタブ)

 ③Time Periodを2020年1月1日から今に

 ④FrequencyをMonthlyに

 ⑤Applyする

 ⑥このURLをコピーする

f:id:chaske1024:20200223220822p:plain

 

<3>Excelシートに、株価表を取り込む

 ①Excelの「データ」

 ②「Webから」

 ③先ほどのURL貼付

 ④OKボタン

f:id:chaske1024:20200223222141p:plain

※適当な株価サイト=「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%も追加。

f:id:chaske1024:20200224230204p:plain

 

 ③下までドロップダウン

 

 ④Buy Timingは、買った日付。

  Gaind Divは、買った日付から今までの課税後配当金の合計。数式は「=SUMIF(A3:A65525,">="&$L2,I3:I65525)」で、SUMIF関数に「L2の日付以降の合計」となるよう、 ”>=”&$L2 と付けている。

 

【この表の意味】

買ったタイミングから、今までの値動き+課税後配当金を加味 できる事。値動きだけでなく、課税後配当金をふくめてトータルで考えたい為。



また「Webから取得」としたことで、更新ボタン一つで自動でトータルリターンを計算してくれる。