保有株の損益、もう手計算しない｜Googleスプレッドシート＋Pythonで自動可視化する方法 #21
株式投資をしていると、証券口座ごとに「買った株・売った株」の履歴は残りますが、
「今いくら評価されているのか？」
「損益はどれくらいか？」
を一覧で見るのは意外と面倒ですよね。
そこで今回は、Googleスプレッドシートの「売買履歴」シートから、“保有株サマリー”を作成する仕組みをPythonで組んでみました。
1.データの準備
スプレッドシートに「売買履歴」というシートを作り、次のようなカラムのシートを持たせます。
これをPythonで読み込みます。
# 売買履歴の読み込み
sheet_ticker_list = spreadsheet.worksheet("売買履歴")
ticker_data = sheet_ticker_list.get_all_values()
df_ticker_list = pd.DataFrame(ticker_data[1:], columns=ticker_data[0])
display(df_ticker_list.head())# 売買履歴シートを読み込むユーティリティ（Google Colab 想定）
# ==== ライブラリ ====
from google.colab import auth # Colab のユーザー認証
import gspread
from google.auth import default
import pandas as pd
import numpy as np
from gspread_dataframe import get_as_dataframe
import unicodedata
# ==== 設定（どれか1つを使う）====
SPREADSHEET_URL = "" # 例: "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit"
SPREADSHEET_TITLE = "" # 例: "保有株ポートフォリオ"
SHEET_NAME = "売買履歴"
# ==== 認証 ====
try:
auth.authenticate_user()
except Exception:
# Colab 以外の環境ならスキップしてOK
pass
creds, _ = default()
gc = gspread.authorize(creds)
# ==== スプレッドシートを開く（既存の `spreadsheet` があればそれを使う）====
def _open_spreadsheet():
# 既に上位セルで作られているものを利用
try:
return spreadsheet # noqa: F821
except NameError:
pass
if SPREADSHEET_URL:
return gc.open_by_url(SPREADSHEET_URL)
if SPREADSHEET_TITLE:
return gc.open(SPREADSHEET_TITLE)
raise ValueError("`spreadsheet` 変数が未定義です。SPREADSHEET_URL か SPREADSHEET_TITLE を設定してください。")
sh = _open_spreadsheet()
ws = sh.worksheet(SHEET_NAME)
# ==== 文字・列名の掃除 ====
def _strip_and_normalize(s):
"""前後空白除去 + 全角→半角の正規化"""
if isinstance(s, str):
return unicodedata.normalize("NFKC", s.strip())
return s
def _clean_df(df: pd.DataFrame) -> pd.DataFrame:
if df is None or df.empty:
return pd.DataFrame()
out = df.copy()
# 列名クリーニング
out.columns = [_strip_and_normalize(c) for c in out.columns]
# 全セルの左右空白を削除（文字列のみ）
for c in out.columns:
if pd.api.types.is_object_dtype(out[c].dtype):
out[c] = out[c].map(_strip_and_normalize)
# 完全空行・空列の除去
out = out.dropna(how="all").dropna(how="all", axis=1)
return out
# ==== 列名の標準化（別名の吸収）====
ALIASES = {
"約定日": ["約定日", "日付", "取引日"],
"コード": ["コード", "銘柄コード"],
"売買": ["売買", "サイド"],
"数量": ["数量", "株数", "口数"],
"単価": ["単価", "約定単価", "価格"],
"手数料": ["手数料"],
"税金": ["税金"],
"証券口座": ["証券口座", "口座"],
"口座区分": ["口座区分", "課税区分"],
"備考": ["備考", "メモ"],
"ティッカー": ["ティッカー", "ticker", "ティッカーコード"],
"銘柄名": ["銘柄名", "名称", "銘柄"],
}
def _standardize_columns(df: pd.DataFrame) -> pd.DataFrame:
col_map = {}
actual_cols = set(df.columns)
for std, cands in ALIASES.items():
for c in cands:
if c in actual_cols:
col_map[c] = std
break
out = df.rename(columns=col_map)
return out
# ==== 数値/日付の型変換 ====
NUMERIC_COLS = ["数量", "単価", "手数料", "税金"]
def _to_numeric_safe(s):
if isinstance(s, str):
s = s.replace(",", "").replace("¥", "").replace("%", "")
return pd.to_numeric(s, errors="coerce")
def _coerce_dtypes(df: pd.DataFrame) -> pd.DataFrame:
out = df.copy()
# 日付
if "約定日" in out.columns:
out["約定日"] = pd.to_datetime(out["約定日"], errors="coerce")
# 後工程の互換用（あなたの他コードで "日付" を参照する場面があるため）
out["日付"] = out["約定日"]
# 数値
for c in NUMERIC_COLS:
if c in out.columns:
out[c] = out[c].map(_to_numeric_safe)
return out
# ==== 読み込み（数式は評価済みで取得）====
df_raw = get_as_dataframe(ws, header=0, evaluate_formulas=True)
df_raw = _clean_df(df_raw)
df_ticker_list = _standardize_columns(df_raw)
df_ticker_list = _coerce_dtypes(df_ticker_list)
# ==== 必須列チェック（最低限）====
required = ["約定日", "コード", "売買", "数量", "単価"]
missing = [c for c in required if c not in df_ticker_list.columns]
if missing:
raise ValueError(f"必須列が足りません: {missing}\n実際の列: {list(df_ticker_list.columns)}")
# ==== （任意）ティッカー補完：コードがあるがティッカーが空なら .T を付ける ====
if "ティッカー" not in df_ticker_list.columns and "コード" in df_ticker_list.columns:
df_ticker_list["ティッカー"] = df_ticker_list["コード"].astype(str).str.replace(r"\.T$", "", regex=True) + ".T"
# ==== 確認表示 ====
print("✅ 売買履歴の読み込み完了")
print("行数:", len(df_ticker_list))
print("列:", list(df_ticker_list.columns))
display(df_ticker_list.head())
「シート構成例」との対応
約定日 / コード / 売買 / 数量 / 単価 / 手数料 / 税金 / 証券口座 / 口座区分 / 備考 / ティッカー / 銘柄名
列名ゆれは ALIASES で吸収（例：「日付」→「約定日」、「株数」→「数量」など）。
カンマ入り数値・通貨記号・% も自動で数値化。
コード しかない場合は ティッカー を自動補完（末尾に .T 付与）。
2.データの整形
まずは「買い／売り」を +1 / -1 の数値に変換して、
手数料や税金を考慮した「取引総額」「実効単価」を計算します。
# 「買い／売り」を +1 / -1 に正規化し、手数料・税金込みの取引総額と実効単価を計算する
# 事前に df は「売買履歴」標準カラムに整形済み（読み込み編のコード参照）
import pandas as pd
import numpy as np
# 1) 売買ラベルのゆれを吸収して +1 / -1 に正規化
BUY_ALIASES = {"買付","買い","買","buy","購入","long","buying","buy-in"}
SELL_ALIASES = {"売付","売り","売","sell","売却","short","selling","sell-out"}
def side_to_sign(x: object, strict: bool = True) -> int:
"""
売買ラベルを +1(買い) / -1(売り) に変換。
strict=True の場合、未知ラベルを検知したら例外を投げて早期に気付ける。
"""
s = str(x).strip().lower()
if s in {a.lower() for a in BUY_ALIASES}:
return 1
if s in {a.lower() for a in SELL_ALIASES}:
return -1
if strict:
raise ValueError(f"未知の売買ラベルです: {x} ｜想定={sorted(BUY_ALIASES | SELL_ALIASES)}")
# strict=False の場合は NaN を返し、後続で欠損として扱う
return np.nan
# 列の存在チェック（最低限）
_required_cols = ["売買","数量","単価"]
_missing = [c for c in _required_cols if c not in df.columns]
if _missing:
raise ValueError(f"計算に必要な列が不足しています: {_missing} ｜現在の列={list(df.columns)}")
# 2) 数値列の型を保証（文字カンマや記号が混ざっていても安全に数値化）
def _to_numeric(x):
if isinstance(x, str):
x = x.replace(",", "").replace("¥", "").replace("%", "")
return pd.to_numeric(x, errors="coerce")
for c in ["数量","単価","手数料","税金"]:
if c not in df.columns: # 無ければ0で作る（副作用なし）
df[c] = 0.0
df[c] = df[c].map(_to_numeric).fillna(0.0)
# 3) 売買サイド（+1 / -1）
df["side"] = df["売買"].apply(side_to_sign)
# 4) 取引総額（手数料・税金込み）
# 買い: 単価*数量 + 手数料 + 税金
# 売り: 単価*数量 - 手数料 - 税金
fees = df["手数料"] + df["税金"]
gross = df["単価"] * df["数量"]
df["取引総額"] = np.where(df["side"] == 1, gross + fees, gross - fees)
# 5) 実効単価（数量が0なら NaN）
# 売りのときは「売却単価（費用控除後）」という解釈になる点に注意
df["実効単価"] = df["取引総額"] / df["数量"].replace(0, np.nan)
# 6) 表示確認（記事では head だけでOK）
cols_show = ["約定日","ティッカー","銘柄名","売買","数量","単価","手数料","税金","side","取引総額","実効単価"]
print("✅ データ整形（売買→±1、総額・実効単価）完了")
display(df[[c for c in cols_show if c in df.columns]].head(10))
使い方と補足
ラベルのゆれ取り：BUY_ALIASES / SELL_ALIASES に思いつく表記を足しておくと運用が安定します。
未知ラベル検知：記事サンプルでは厳格モード（未知なら即エラー）。運用中は strict=False にしてログに流す選択もあり。
実効単価の解釈：
買い…「手数料・税金込みの実際に払った単価」
売り…「手数料・税金控除後に実際に受け取った単価」
0割り防止：数量=0 は NaN にして、後段で無視できるようにしています。
3.現在値の取得
次に、yfinance を使って株価を取得します。
info → fast_info → 分足 → 日足 の順にバックアップを取ることで、
「取得できなかった銘柄」を極力減らしています。
# yfinance から現在値と前日終値を安全に取得し、df_summary に反映する
import time
import pandas as pd
import numpy as np
import yfinance as yf
from datetime import datetime
import pytz
# ▼ 任意：ティッカー整形（".T" 付与や置換が必要な場合に使用）
TICKER_OVERRIDE = {
# 例: "2914.T": "2914.T", # 別シンボルに差し替える場合だけ記述
}
def to_yf_symbol(tkr: str) -> str:
t = str(tkr).strip()
t = t if t.endswith(".T") else f"{t}.T"
return TICKER_OVERRIDE.get(t, t)
# ▼ 安全な現在値取得（優先度: info → fast_info → 1分足 → 日足）
def safe_current_price(sym: str) -> float:
tk = yf.Ticker(sym)
# 1) info（比較的正確だが遅いことも）
try:
info = tk.info # dict想定
p = info.get("regularMarketPrice") or info.get("currentPrice")
if p is not None:
return float(p)
except Exception:
pass
# 2) fast_info（高速・欠損のことあり）
try:
fi = tk.fast_info # SimpleNamespace風かdict風
lp = getattr(fi, "last_price", None) if not isinstance(fi, dict) else fi.get("last_price")
if lp is not None:
return float(lp)
except Exception:
pass
# 3) 1分足（場中の直近値を拾う）
try:
m1 = tk.history(period="1d", interval="1m", auto_adjust=False, raise_errors=False)
if m1 is not None and not m1.empty:
last = m1["Close"].dropna()
if not last.empty:
return float(last.iloc[-1])
except Exception:
pass
# 4) 直近日足（実質、前日終値相当）
try:
d1 = tk.history(period="5d", interval="1d", auto_adjust=False, raise_errors=False)
if d1 is not None and not d1.empty:
close = d1["Close"].dropna()
if not close.empty:
return float(close.iloc[-1])
except Exception:
pass
return float("nan")
# ▼ 前日終値（優先度: fast_info → 日足N-1）
def previous_close(sym: str) -> float:
tk = yf.Ticker(sym)
# 1) fast_info
try:
fi = tk.fast_info
pc = getattr(fi, "previous_close", None) if not isinstance(fi, dict) else fi.get("previous_close")
if pc is not None:
return float(pc)
except Exception:
pass
# 2) 日足（N-1）
try:
d1 = tk.history(period="10d", interval="1d", auto_adjust=False, raise_errors=False)
if d1 is not None and not d1.empty:
close = d1["Close"].dropna()
if len(close) >= 2:
return float(close.iloc[-2])
elif len(close) == 1:
return float(close.iloc[-1])
except Exception:
pass
return float("nan")
# ▼ 一括取得（レート制限に配慮しつつ個別リクエスト）
def get_prices(symbols, sleep_sec: float = 0.05):
cur_map, prev_map, missing = {}, {}, []
for sym in symbols:
if not sym:
continue
time.sleep(sleep_sec) # 過剰アクセス回避
cur = safe_current_price(sym)
prev = previous_close(sym)
cur_map[sym] = cur
prev_map[sym] = prev
if (pd.isna(cur) or cur == 0) and (pd.isna(prev) or prev == 0):
missing.append(sym)
return cur_map, prev_map, missing
# ===== ここから df_summary への適用 =====
# 前段で df_summary が作成済みで、"ティッカー" 列がある想定
# 1) yfinance 用シンボル列
if "yf_symbol" not in df_summary.columns:
df_summary["yf_symbol"] = df_summary["ティッカー"].map(to_yf_symbol)
symbols = [s for s in df_summary["yf_symbol"].dropna().unique().tolist() if s]
# 2) 価格取得
cur_map, prev_map, missing_syms = get_prices(symbols, sleep_sec=0.05)
# 3) 反映
for c in ["保有数","簿価","実現損益","平均取得額"]:
if c in df_summary.columns:
df_summary[c] = pd.to_numeric(df_summary[c], errors="coerce")
df_summary["現在値"] = df_summary["yf_symbol"].map(cur_map)
df_summary["前日終値"] = df_summary["yf_symbol"].map(prev_map)
# 欠損・ゼロの現在値は前日終値で穴埋め（見栄え改善）
df_summary["現在値"] = df_summary["現在値"].where(
df_summary["現在値"].notna() & (df_summary["現在値"] > 0),
df_summary["前日終値"]
)
# 4) 評価系の再計算
df_summary["評価額"] = (df_summary.get("保有数", 0) * df_summary.get("現在値", 0)).round(2)
df_summary["含み損益"] = (df_summary.get("評価額", 0) - df_summary.get("簿価", 0)).round(2)
df_summary["トータル損益"] = (df_summary.get("実現損益", 0) + df_summary.get("含み損益", 0)).round(2)
# 5) タイムスタンプ（JST）
jst = pytz.timezone("Asia/Tokyo")
df_summary["データ更新時刻(JST)"] = datetime.now(tz=jst).strftime("%Y-%m-%d %H:%M:%S")
# 6) 結果確認
print("✅ 現在値・前日終値の付与と評価系の更新が完了")
if missing_syms:
print("⚠ 価格を取得できなかったシンボル:", missing_syms)
cols_show = ["ティッカー","銘柄名","保有数","平均取得額","現在値","前日終値","簿価","評価額","含み損益","実現損益","トータル損益","データ更新時刻(JST)"]
display(df_summary[[c for c in cols_show if c in df_summary.columns]].head(20))
4.保有株サマリーの作成
最後に、「保有数・平均取得額・現在値・評価額・損益」などをまとめてサマリー化します。
# df_summary に「保有数・平均取得額・現在値・評価額・損益」などを集約する
import pandas as pd
import numpy as np
from datetime import datetime
import pytz
# 0) 前提: df_summary が存在し、少なくとも以下の列を想定
# ["ティッカー","銘柄名","保有数","平均取得額","現在値","簿価","実現損益"]
# ※ 読み込み編/整形編/現在値編のコードを実行済みの想定
# 1) 数値列の型を保証（文字カンマ/記号が混ざっても安全に数値化）
def _to_numeric_safe(x):
if isinstance(x, str):
x = x.replace(",", "").replace("¥", "").replace("%", "")
return pd.to_numeric(x, errors="coerce")
for c in ["保有数", "平均取得額", "現在値", "簿価", "実現損益"]:
if c not in df_summary.columns:
df_summary[c] = 0.0
df_summary[c] = df_summary[c].map(_to_numeric_safe).fillna(0.0)
# 2) 評価系の計算
df_summary["評価額"] = (df_summary["保有数"] * df_summary["現在値"]).round(2)
df_summary["含み損益"] = (df_summary["評価額"] - df_summary["簿価"]).round(2)
df_summary["トータル損益"] = (df_summary["実現損益"] + df_summary["含み損益"]).round(2)
# 3) タイムスタンプ（JST）
jst = pytz.timezone("Asia/Tokyo")
df_summary["データ更新時刻(JST)"] = datetime.now(tz=jst).strftime("%Y-%m-%d %H:%M:%S")
# 4) 列順の整え（存在する列だけ並べる）
preferred_order = [
"ティッカー", "銘柄名",
"証券口座", "口座区分", # あれば表示
"保有数", "平均取得額",
"現在値", "前日終値",
"簿価", "評価額",
"含み損益", "実現損益", "トータル損益",
"最終取引日", # あれば表示（整形編で付与していれば）
"データ更新時刻(JST)"
]
cols = [c for c in preferred_order if c in df_summary.columns]
df_summary = df_summary.reindex(columns=cols)
# 5) 表示（上位行）
print("✅ 保有株サマリーの作成が完了（評価額・損益を付与）")
display(df_summary.head(20))
5.まとめ
ここまでで、スプレッドシートに記録した「売買履歴」から、
最新株価を反映した「保有株サマリー」が自動で作成できるようになりました。
これを基盤にすれば：
各銘柄の損益管理
配当データの追加
売買シグナルとの連携
といった発展も可能です。
本記事のコードは、私自身が実際に動かして検証したものをベースにまとめています。とはいえ環境によって挙動が異なる場合もあるため、ぜひご自身の環境で確認・調整しながら活用していただければ幸いです。
6.関連記事
いいなと思ったら応援しよう！
よろしければ応援お願いします！ いただいたチップはクリエイターとしての活動費に使わせていただきます！この記事は noteマネー にピックアップされました
