Stop Calculating Your Stock Gains and Losses Manually: How to Automate Visualization with Google Sheets + Python #21

When you invest in stocks, the history of "stocks bought and sold" is recorded for each brokerage account, but
"How much is it worth now?"
"How much is the profit or loss?"
It is surprisingly tedious to view these in a list.

So, this time, I built a system using Python to create a "Holdings Summary" from the "Trading History" sheet in Google Sheets.

1. Data Preparation

Create a sheet named "Trading History" in your spreadsheet and include columns like the following.

Load this into 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())

Mapping to "Sheet Structure Example"

2. Data Formatting

First, convert "Buy/Sell" into +1 / -1 numeric values, and
calculate the "Total Transaction Amount" and "Effective Unit Price" considering commissions and taxes.

# 「買い／売り」を +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))

Usage and Notes

3. Obtaining Current Prices

Next, use yfinance to obtain stock prices.
By backing up in the order of info -> fast_info -> minute data -> daily data,
I minimize the number of "stocks that could not be retrieved" as much as possible.

# 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. Creating a Holdings Summary

Finally, summarize everything including "Quantity Held, Average Acquisition Cost, Current Price, Valuation, and Profit/Loss".

# 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. Summary

With this, you can now automatically create a "Holdings Summary" that reflects the latest stock prices from the "Trading History" recorded in your spreadsheet.

With this as a foundation:

• Managing gains and losses for each stock

• Adding dividend data

• Integrating with trading signals

These kinds of developments are possible.
The code in this article is based on what I have actually run and verified myself. However, since behavior may vary depending on your environment, I hope you will check and adjust it for your own setup as you use it.

6. Related Articles

• How to Automatically Retrieve Dividend Data and Save It to a Spreadsheet

• Visualizing Stock Volume and Price Movements with Python