数日前、ついに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関数で引っ張りました。ローカルでの計算量は多いはずですが、結果的にこっちの方が安定しました。
１．別シート（例：為替リスト）を作成して”為替”に下記を記載してテーブル作成する。
１セル書けばテーブル出来上がります。
=GoogleFinance(“currency:usdjpy”,“price”,”2014/1/1″,Today(),“DAILY”)
２．終値で時間まで出るので日付のみにする。
細かいですが、これだと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と操作が異なりました。
１．ピボットの「値」から一番下の「計算フィールド」を選択
２．計算フィールドに計算式を入力
=iferror(divide(‘評価損益’,’購入コスト’))
単純に四則演算で表記すると上手く動作しなかったので、商を求めるのにわざわざDivide関数を使用してます。
‘評価損益”購入コスト’はトランザクションシートのデータの見出しラベルになります。これを計算式に入力します。この時シングルクオテーションで囲う必要がありました。
これはドキュメントに記載がなく、下記ビデオに解説がありました。
—-
まだ生煮えですが、Goolge Spreadsheetは面白いです。興味があればこちらかにアクセスして、「ファイル」→「コピーの作成」
で利用してみてください。