Claude Codeに頼んだら為替レートが毎朝Excelに自動入力されるようになった話

# Claude Codeが生成したコード: 為替レート自動取得 → Excel書き込み
import os
import requests
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment
from datetime import datetime

# ── 設定 ──────────────────────────────────────────────
API_KEY   = os.environ["EXCHANGE_RATE_API_KEY"]   # 環境変数から取得
BASE_URL  = f"https://v6.exchangerate-api.com/v6/{API_KEY}/latest/USD"
TARGET    = ["JPY", "EUR", "GBP", "CNY"]
XLSX_PATH = "forex_rates.xlsx"

def fetch_rates() -> dict:
    """ExchangeRate-API から最新レートを取得（USD基準）"""
    res = requests.get(BASE_URL, timeout=10)
    res.raise_for_status()
    all_rates = res.json()["conversion_rates"]
    # USD/JPY は直接取得、EUR/JPY は JPY÷EUR で計算
    return {
        "USD/JPY": round(all_rates["JPY"], 2),
        "EUR/JPY": round(all_rates["JPY"] / all_rates["EUR"], 2),
        "GBP/JPY": round(all_rates["JPY"] / all_rates["GBP"], 2),
        "CNY/JPY": round(all_rates["JPY"] / all_rates["CNY"], 2),
    }

def write_to_excel(rates: dict) -> None:
    """取得したレートをExcelに追記する"""
    try:
        wb = openpyxl.load_workbook(XLSX_PATH)
        ws = wb["為替レート"]
    except FileNotFoundError:
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "為替レート"
        # ヘッダー行を作成（初回のみ）
        headers = ["更新日時", "USD/JPY", "EUR/JPY", "GBP/JPY", "CNY/JPY"]
        ws.append(headers)
        green_fill = PatternFill("solid", fgColor="107C41")
        for cell in ws[1]:
            cell.fill = green_fill
            cell.font = Font(color="FFFFFF", bold=True)
            cell.alignment = Alignment(horizontal="center")

    # データ行を追記
    now = datetime.now().strftime("%Y-%m-%d %H:%M")
    row = [now, rates["USD/JPY"], rates["EUR/JPY"], rates["GBP/JPY"], rates["CNY/JPY"]]
    ws.append(row)

    # 最新行を薄緑でハイライト
    last_row = ws.max_row
    light_green = PatternFill("solid", fgColor="D1FAE5")
    for col in range(1, 6):
        ws.cell(last_row, col).fill = light_green

    wb.save(XLSX_PATH)
    print(f"[{now}] 為替レートをExcelに書き込みました")
    for pair, rate in rates.items():
        print(f"  {pair}: {rate}")

if __name__ == "__main__":
    rates = fetch_rates()
    write_to_excel(rates)

import logging
from pathlib import Path

# Claude Codeが追加したログ設定
LOG_PATH = Path("logs/forex_update.log")
LOG_PATH.parent.mkdir(exist_ok=True)

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
    handlers=[
        logging.FileHandler(LOG_PATH, encoding="utf-8"),
        logging.StreamHandler()   # コンソールにも出力
    ]
)
log = logging.getLogger(__name__)

def main():
    log.info("為替レート取得開始")
    try:
        rates = fetch_rates()
        write_to_excel(rates)
        log.info(f"完了: {rates}")
    except Exception as e:
        log.error(f"エラー発生: {e}")
        raise

# Claude Codeが生成した株価データ取得スクリプト
import yfinance as yf
import pandas as pd
import openpyxl
from openpyxl.chart import LineChart, Reference

TICKERS   = ["7203.T", "9984.T", "AAPL", "MSFT"]
PERIOD    = "3mo"
XLSX_PATH = "株価レポート_自動生成.xlsx"

def fetch_stock_data() -> dict:
    """複数銘柄の株価データを取得し移動平均を計算"""
    results = {}
    for ticker in TICKERS:
        df = yf.Ticker(ticker).history(period=PERIOD)
        df.index = df.index.strftime("%Y-%m-%d")
        df["MA5"]  = df["Close"].rolling(5).mean().round(2)
        df["MA25"] = df["Close"].rolling(25).mean().round(2)
        results[ticker] = df[["Close", "Volume", "MA5", "MA25"]]
    return results

def export_to_excel(data: dict) -> None:
    wb = openpyxl.Workbook()
    wb.remove(wb.active)

    for ticker, df in data.items():
        ws = wb.create_sheet(title=ticker.replace(".", "_"))
        ws.append(["日付", "終値", "出来高", "5日MA", "25日MA"])
        for idx, row in df.iterrows():
            ws.append([idx] + row.tolist())

        # 折れ線グラフを自動追加
        chart = LineChart()
        chart.title = f"{ticker} 株価推移（3ヶ月）"
        chart.style = 10
        data_ref = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row)
        chart.add_data(data_ref, titles_from_data=True)
        chart.width = 20
        chart.height = 12
        ws.add_chart(chart, "F2")

    wb.save(XLSX_PATH)
    print(f"株価レポートを出力: {XLSX_PATH}")

data = fetch_stock_data()
export_to_excel(data)

# Claude Codeが生成したGA4レポート自動化スクリプト
import os
import pandas as pd
from google.analytics.data_v1beta import BetaAnalyticsDataClient
from google.analytics.data_v1beta.types import RunReportRequest, DateRange, Dimension, Metric

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "./service_account.json"
PROPERTY_ID = os.environ["GA4_PROPERTY_ID"]
XLSX_PATH   = "GA4レポート_自動生成.xlsx"

def fetch_ga4_data(start: str = "30daysAgo", end: str = "today") -> pd.DataFrame:
    client = BetaAnalyticsDataClient()
    request = RunReportRequest(
        property=f"properties/{PROPERTY_ID}",
        dimensions=[Dimension(name="date"), Dimension(name="sessionDefaultChannelGroup")],
        metrics=[
            Metric(name="sessions"),
            Metric(name="screenPageViews"),
            Metric(name="conversions"),
            Metric(name="bounceRate"),
        ],
        date_ranges=[DateRange(start_date=start, end_date=end)],
    )
    response = client.run_report(request)
    rows = [{
        "日付": row.dimension_values[0].value,
        "チャネル": row.dimension_values[1].value,
        "セッション": int(row.metric_values[0].value),
        "PV": int(row.metric_values[1].value),
        "CV": int(row.metric_values[2].value),
        "直帰率": round(float(row.metric_values[3].value) * 100, 2),
    } for row in response.rows]
    return pd.DataFrame(rows)

def export_to_excel(df: pd.DataFrame) -> None:
    with pd.ExcelWriter(XLSX_PATH, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="日別データ", index=False)
        pivot = df.groupby("チャネル")[["セッション", "PV", "CV"]].sum()
        pivot.to_excel(writer, sheet_name="チャネル別集計")
    print(f"GA4レポート出力完了: {XLSX_PATH}")

df = fetch_ga4_data()
export_to_excel(df)

# Claude Codeが生成したマルチAPI統合ダッシュボード
import os, time, logging, requests, yfinance as yf
import pandas as pd, openpyxl
from datetime import datetime

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(message)s")
log = logging.getLogger(__name__)

DASHBOARD = "経営ダッシュボード.xlsx"

def safe_call(func, label: str):
    """APIが失敗してもNoneを返すだけで処理を続ける"""
    try:
        result = func()
        log.info(f"[OK] {label}")
        return result
    except Exception as e:
        log.warning(f"[SKIP] {label}: {e}")
        return None

def fetch_forex():
    key = os.environ["EXCHANGE_RATE_API_KEY"]
    r = requests.get(f"https://v6.exchangerate-api.com/v6/{key}/latest/USD", timeout=10)
    rates = r.json()["conversion_rates"]
    return {"USD/JPY": rates["JPY"], "EUR/JPY": rates["JPY"] / rates["EUR"]}

def fetch_stocks():
    return {n: round(yf.Ticker(s).fast_info["last_price"], 2)
            for n, s in {"日経225": "^N225", "S&P500": "^GSPC"}.items()}

def write_dashboard(forex, stocks) -> None:
    try:
        wb = openpyxl.load_workbook(DASHBOARD)
    except FileNotFoundError:
        wb = openpyxl.Workbook()
    ws = wb["サマリー"] if "サマリー" in wb.sheetnames else wb.active
    ws.title = "サマリー"
    ws["B1"] = f"最終更新: {datetime.now().strftime('%Y/%m/%d %H:%M')}"
    if forex:
        ws["B3"] = forex.get("USD/JPY")
        ws["B4"] = forex.get("EUR/JPY")
    if stocks:
        ws["B6"] = stocks.get("日経225")
        ws["B7"] = stocks.get("S&P500")
    wb.save(DASHBOARD)
    log.info(f"ダッシュボード更新完了 → {DASHBOARD}")

if __name__ == "__main__":
    forex  = safe_call(fetch_forex, "為替API")
    stocks = safe_call(fetch_stocks, "株価API")
    write_dashboard(forex, stocks)