2017年5月5日金曜日

Google Sheets による資産管理のススメ


普段ご自身のポートフォリオをどのように管理されていますか?

私はもともとエクセルを資産管理に使っていましたが, PCの不調により数年に渡るデータを遺失した苦い経験から, 現在は資産管理に Google Sheets を活用しています.

基本的にはエクセルの関数とほぼ同じものが使えるため便利なうえ, Google Sheets 独自の関数がかなり使い勝手が良く気に入っています. 前回の記事のとおり私は保有銘柄数がそこそこ多いですが, それができるのも, この Google Sheets のおかげといえます.


Google Sheets はなかなかに優秀です. 関数で現在の株価や為替を引っ張ってこられるため, その時の資産状況がほぼリアルタイムで把握できます.


以下に関数を示します. コピペすればそのまま使えるはずですよ.


株価に関わる関数


米国株株価 (赤字を任意のティッカーへ変更する. 例はシェブロン)


=GoogleFinance("CVX","price")


ADRにも対応しています. Google Finance で検索する際のティッカーを入力することで株価を引っ張ってこれます. このティッカー部分ですが, ティッカーが書かれているセルを指定することもできます.

A 株, B 株と種類のあるものは少しコツが必要で, 下記はロイヤルダッジシェル B 株を引っ張ってくる時の関数です.


=GoogleFinance("RDS.B","price")


この様に RDS と B の間に . が入ります. これを抜いた RDSB やハイフンに変えた RDS-B ではデータを引っ張ってこれません. 下図の通り素直に Google Finance の予測検索で表示された表記を書き写すことが必要です.


香港株株価 (赤字を任意の銘柄コードへ変更する. 例は中国移動)


=GoogleFinance("0941","price")


注意点は SBI 等の銘柄コードからゼロを一つ抜くことです. SBI での中国移動の銘柄コードは00941 です. これをそのまま用いるとデータを引っ張ってこられません. 4 桁に収まるよう最初のゼロを省いてください.
それでもダメな場合は先の RDS の例の様に予測検索で表示される銘柄コードを入力してみてください.

日本株株価 (赤字を任意の銘柄コードへ変更する. 例はNTT)

=ImportXML("http://stocks.finance.yahoo.co.jp/stocks/history/?code=9432","//td[@class='stoksPrice']")

日本株の株価を引っ張ってくるのは, 残念ながら先の関数ではできません. Google Finance 以外の場所から持ってくる必要があります. 上記はYahoo finance から持ってくるときの関数です. 赤色の銘柄コードを任意の数字に変えることで任意の株価を引っ張ってこられます.

持ち株が米国株 (+ADR), 香港株, 日本株なのでそれ以外の国の株を購入した際には改めて調べます.

Sponsored link

為替に関わる関数

下記の関数で赤字の部分を任意の通貨の略称に変更することで各通貨と日本円との為替を表示させることができます.

=GoogleFinance("currency:USDJPY","average")

他の通貨をいくつか例に示します.

香港ドル HKD
人民元 CNY
英国ポンド GBP
ユーロ EUR

これらの赤字部分を関数の赤字と置き換えることで対円の為替を引っ張ってくることができます.
注意: 通貨単位の JPY とこれら通貨の表記順を逆にした場合は円任意の通貨から見た円の換算値が表示されてしまいます. 例えば JPYUSD とした場合 =0.00892 (USD/JPY) となります (17.05.02現在). これでは 1 USD を 112 JPY で割った値となってしまうため, 私たちの使い慣れた JPY/USD を表示させるためには USDJPY の並びが必須となります.

一株配当の関数

米国株(+ ADR)
=importdata(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",CVX,"&f=d"))

先ほどの日本株と同様に外部データを持ってくる関数です. 赤字に任意のティッカーを入力することで Yahoo finance から一株配当のデータを持ってこれます.

香港株
香港株の場合は銘柄コード .HK を別のセルに入力し, このセルを赤文字に入れることで同様にYahoo finance から引っ張ってこれます. 以下に例を示します (中国移動).
任意のセルに 0941.HKを入力 (私の場合は Q45 のセルに入力した).
その後下記の関数の赤字の部分に先ほどのセル番号を入力する
=importdata(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$Q45,"&f=d"))

注意: 本データは Yahoo fainance のデータを利用するため実際の配当支払い額と異なる可能性があります.

以下は私の資産管理項目です. その場で知りたいデータはおよそ把握することが可能です.
データ内容を以下に順に並べます.

銘柄名, 買付価格 (円), 買付数量 (株数), 取得単価 (現地通貨), 時価 (現地通貨), 損益(額, 現地通貨), 損益(%), 取得額 (取得単価×数量, 現地通貨), 時価評価額 (現地通貨), 評価額 (為替損益込, 円), 一株配当 (現地通貨), 見込受取配当額/年 (現地通貨), 時価配当利回り, 取得価格配当利回り, 買付平均為替, 為替考慮後損益

となっています. 以下にスクリーンショットを項目部分だけ切り取り載せましたがなんか見づらい...

入力する一時的な手間はありますがその後の管理が楽になります.
Google Sheets を利用することで, 一般口座と特定口座, 複数の証券会社にまたがって保有する銘柄の損益状況,  USドル, 香港ドルの平均取得単価など, 正確に資産状況を把握し管理することができます. 私の場合は特定口座対応前に購入した銘柄が多数ある為, こういった資産管理が必須です. 定期的にアップしている資産状況もこれらをもとに作っています.

一般口座での取引の場合, 確定申告で一般口座年間取引明細書の提出が必要ですが, こちらについても Google Sheets で作成しており, ここに入力したデータを銘柄名, 買付価格など各項目のセルにリンクさせています. 売買した際には明細書セルに書き込むことで, 自動的に資産管理画面も更新されるので便利です.一般口座取引明細書についてはまた改めてアップします.→アップしました.

少しでも資産管理の手間が省ける一助と成れば幸いです.

Let's enjoy investment!

Sponsored link

関連記事です.
2017年 5 月, 投資セクター比と配当利回り, 投資国状況

ほかにもこんな記事を書いています

我が家の節約法です. こうして投資資金を捻出しています.
踏み出すことで開ける未来があります.

0 件のコメント:

コメントを投稿