Google Financeのポートフォリオの機能が使えないので、Google Spreadsheetで代替え

数日前、ついにGoogle FinanceのPortfolio機能が正式に使えなりアクセスできなくなりました。

日々の海外株のポートフォリオのチェックに使っていたので残念です。一覧性が高く、一見チェックできるので便利だと思っていました。

 

Google Portfolio機能の代替をSpreadsheetでつくってみました

こちらです。

 

トランザクションの入力方法

二重線で囲まれた購入リストにティッカーと、購入日付・購入価格・枚数を入力します。

各購入毎の最新株価と損益が算出されます。

また、購入日のドル円の為替の終値から、日本円での評価損益についても算出欄も追加しています。

下記をクリックすると該当シートが見れます。



 

 

作成した際のTIPSを記載します。

Googlefinance関数の利用解説

下記利用したものです。Execlが使えれば全くストレスなく使えてしまいます。マニュアルはこちらにあります。

 

銘柄名、株価、前日価格の取得

iferror()はエラーが出る際に空白を表示して見映えするためです。

=iferror(GOOGLEFINANCE(A2,“name”))   :A2のTickerから銘柄名を表示する。

=iferror(GOOGLEFINANCE(A2, “price”))   :A2の銘柄の最新株価を取得する。

=iferror((GOOGLEFINANCE(A2, “closeyest”)) :A2の銘柄の前日価格を取得する。

現在の為替の取得

下記で取得できます。他にもユーロや香港ドルでも動作します。

=googlefinance(“usdjpy”)

 

特定日付指定の為替の取得

下記の様な形で可能です。

=INDEX(GoogleFinance(“usdjpy”,“price”,“2016/2/10”,2),2,2)

 

しかし、インデックス関数を使う場合は、もとの指定先テーブルデータのスペースに別データの書き込みが発生しているとエラーが出るケースも多く安定しませんでした。対応として「為替」シートを作ってvlookup関数で引っ張りました。ローカルでの計算量は多いはずですが、結果的にこっちの方が安定しました。

 

1.別シート(例:為替リスト)を作成して”為替”に下記を記載してテーブル作成する。

1セル書けばテーブル出来上がります。

=GoogleFinance(“currency:usdjpy”,“price”,”2014/1/1″,Today(),“DAILY”)

 

2.終値で時間まで出るので日付のみにする。

細かいですが、これだとDateが終値で「2014/01/22 23:58:00」と出て来て余計なものが出ています。日付に変更する必要があります。

回避のためにDatevalue関数で端数を切りますが、Googlespreadsheetの便利機能「arrayformula」を使用して、赤いセルに下記の様に打つこむと、コピペせずとも1セルの入力で変換してくれます。合計2セル入力が必要ですね。

=ARRAYFORMULA(DATEVALUE(A2:A999))

Date Close <=US$
2010/01/22 23:58:00 89.855 2010/01/22
2010/01/24 23:58:00 90.05 2010/01/24
2010/01/25 23:58:00 90.255 2010/01/25
2010/01/26 23:58:00 89.625 2010/01/26

 

3.元シートでvlookupで指定日付の為替リストを引っ張る。(例)

=vlookup(“2018/1/1”,‘為替リスト’!$A:$C,2)

 

条件付き書式について

Excelと同等の機能なのですが、Spreadsheetの場合には、”条件付き書式ルール”を作成してから、適応範囲のセルを足せるというのが楽でした。

 

 

 

ポートフォリオの集計

とりあえず、以前GoogleのPortfolioと同じ項目のみ集計できる形としました。下記表もクリックするとページに飛びます。

オマケでグラフも付けました。



TIPSです。

PIVOTテーブルの利用

Excelとほぼ同等機能ですがインタフェースが異なります。

フィルターの設定や、順番の入れ替えも右側に表示される各ピボットエディターテーブルで一律行われます。

慣れるとこちらの方がシンプルでわかりやすいですですが、細かい調整はできない印象です。

 

銘柄の自動更新をする条件

ティッカーに空白行が入っていますが、ここが余計なので一度フィルターで「空白セル」を表示しない様にしました。

しかし、フィルターをかけておくと、銘柄を追加した場合にフィルターで非選択された状態となり、自動更新できなませんでした。

トランザクションを追加した際に、自動更新するために空白行も入れてある形にする必要があります。

 

損益率の換算

損益率(%)は個別の合計になりませんので、PIVOTテーブル上に表示される銘柄毎の「合計購入金額」と「合計評価金額」から求めるのでPIVOTテーブル上で計算式が必要です。

この処理方法がExcelと操作が異なりました。

1.ピボットの「値」から一番下の「計算フィールド」を選択

 

 

2.計算フィールドに計算式を入力

=iferror(divide(‘評価損益’,’購入コスト’))

単純に四則演算で表記すると上手く動作しなかったので、商を求めるのにわざわざDivide関数を使用してます。

‘評価損益”購入コスト’トランザクションシートのデータの見出しラベルになります。これを計算式に入力します。この時シングルクオテーションで囲う必要がありました。

これはドキュメントに記載がなく、下記ビデオに解説がありました。

 

 

—-

まだ生煮えですが、Goolge Spreadsheetは面白いです。興味があればこちらかにアクセスして、「ファイル」→「コピーの作成」

で利用してみてください。

 

 

 



コメントを残す