Pythonでメルカリ×Yahoo!オークションの価格差を自動判定する方法｜赤字候補を除外する7ステップ

許可された方法で候補を収集
  ↓
型番・状態・付属品をCSVへ記録
  ↓
Pythonで手数料・送料・梱包費を計算
  ↓
利益・ROI・成約数・状態一致で絞り込み
  ↓
候補URLを人間が確認
  ↓
予測利益と実利益を記録して条件を改善

item,description,sale_price,landed_cost,outbound_shipping,packing_cost,fee_rate,sold_count_30d,condition_match,source_url,target_url
Canon EF 50mm F1.8 STM,動作確認済み 箱なし,13200,9200,750,80,0.10,5,yes,https://example.com/source-1,https://example.com/market-1
Nintendo Switch Lite,本体のみ 動作確認済み,18800,15050,850,120,0.10,8,yes,https://example.com/source-2,https://example.com/market-2
Wireless Headphones,動作未確認,7800,6120,520,80,0.10,2,no,https://example.com/source-3,https://example.com/market-3

販売手数料 = 想定販売価格 × 手数料率

期待利益 =
想定販売価格
- 販売手数料
- 仕入れ総額
- 発送費
- 梱包費

ROI =
期待利益 ÷（仕入れ総額 + 発送費 + 梱包費）

販売手数料 = 13,200 × 0.10 = 1,320円
期待利益 = 13,200 - 1,320 - 9,200 - 750 - 80 = 1,850円
ROI = 1,850 ÷ 10,030 = 約18.4％

保守的期待利益 =
期待利益
- 想定値下げ額
- 返品引当
- 保管費
- その他変動費

import csv
import json
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP

MIN_PROFIT = Decimal("1000")
MIN_ROI = Decimal("0.12")
MIN_SOLD_COUNT = 3

NG_KEYWORDS = {
    "ジャンク",
    "動作未確認",
    "部品取り",
    "破損",
    "模造品",
    "レプリカ",
}

INPUT_COLUMNS = [
    "item",
    "description",
    "sale_price",
    "landed_cost",
    "outbound_shipping",
    "packing_cost",
    "fee_rate",
    "sold_count_30d",
    "condition_match",
    "source_url",
    "target_url",
]

OUTPUT_COLUMNS = INPUT_COLUMNS + [
    "fee",
    "expected_profit",
    "roi",
    "ng_hits",
    "passed",
]


def parse_decimal(row: dict, name: str) -> Decimal:
    raw_value = row.get(name)
    value = raw_value.strip() if isinstance(raw_value, str) else ""

    if not value:
        raise ValueError(f"{name} が空です")

    try:
        return Decimal(value)
    except InvalidOperation as exc:
        raise ValueError(f"{name} が数値ではありません: {value}") from exc


def parse_integer(row: dict, name: str) -> int:
    raw_value = row.get(name)
    value = raw_value.strip() if isinstance(raw_value, str) else ""

    if not value:
        raise ValueError(f"{name} が空です")

    try:
        return int(value)
    except ValueError as exc:
        raise ValueError(f"{name} が整数ではありません: {value}") from exc


def round_yen(value: Decimal) -> int:
    return int(value.quantize(Decimal("1"), rounding=ROUND_HALF_UP))


def calculate(row: dict) -> dict:
    sale_price = parse_decimal(row, "sale_price")
    landed_cost = parse_decimal(row, "landed_cost")
    shipping = parse_decimal(row, "outbound_shipping")
    packing = parse_decimal(row, "packing_cost")
    fee_rate = parse_decimal(row, "fee_rate")
    sold_count = parse_integer(row, "sold_count_30d")

    amounts = {
        "sale_price": sale_price,
        "landed_cost": landed_cost,
        "outbound_shipping": shipping,
        "packing_cost": packing,
    }

    negative_fields = [
        name for name, value in amounts.items() if value < 0
    ]
    if negative_fields:
        raise ValueError(f"負数は入力できません: {negative_fields}")

    if not Decimal("0") <= fee_rate < Decimal("1"):
        raise ValueError("fee_rate は0以上1未満で入力してください")

    if sold_count < 0:
        raise ValueError("sold_count_30d は0以上で入力してください")

    fee = sale_price * fee_rate
    profit = sale_price - fee - landed_cost - shipping - packing
    investment = landed_cost + shipping + packing
    roi = profit / investment if investment > 0 else Decimal("0")

    description = (row.get("description") or "").lower()
    ng_hits = sorted(
        word for word in NG_KEYWORDS
        if word.lower() in description
    )

    condition_match = (
        row.get("condition_match") or ""
    ).strip().lower()

    source_url = (row.get("source_url") or "").strip()
    target_url = (row.get("target_url") or "").strip()

    valid_urls = (
        source_url.startswith(("https://", "http://"))
        and target_url.startswith(("https://", "http://"))
    )

    passed = (
        profit >= MIN_PROFIT
        and roi >= MIN_ROI
        and sold_count >= MIN_SOLD_COUNT
        and condition_match == "yes"
        and not ng_hits
        and valid_urls
    )

    return {
        **{name: row.get(name, "") for name in INPUT_COLUMNS},
        "fee": round_yen(fee),
        "expected_profit": round_yen(profit),
        "roi": f"{roi:.1%}",
        "ng_hits": ",".join(ng_hits),
        "passed": passed,
    }


with open("items.csv", newline="", encoding="utf-8-sig") as src:
    reader = csv.DictReader(src)

    missing = set(INPUT_COLUMNS) - set(reader.fieldnames or [])
    if missing:
        raise ValueError(f"不足している列: {sorted(missing)}")

    results = []
    errors = []

    for line_number, row in enumerate(reader, start=2):
        try:
            results.append(calculate(row))
        except (ValueError, TypeError, KeyError) as exc:
            errors.append({
                "line": line_number,
                "item": row.get("item", ""),
                "error": str(exc),
            })

passed_rows = [row for row in results if row["passed"]]

with open(
    "candidates.csv",
    "w",
    newline="",
    encoding="utf-8-sig",
) as dst:
    writer = csv.DictWriter(dst, fieldnames=OUTPUT_COLUMNS)
    writer.writeheader()
    writer.writerows(passed_rows)

summary = {
    "input_count": len(results) + len(errors),
    "evaluated_count": len(results),
    "pass_count": len(passed_rows),
    "error_count": len(errors),
    "errors": errors,
}

print(json.dumps(summary, ensure_ascii=False, indent=2))

python --version
python price_research.py

{
  "input_count": 3,
  "evaluated_count": 3,
  "pass_count": 1,
  "error_count": 0,
  "errors": []
}

manual_draft: codex CLI succeeded
Saved post: ...
git commit failed:
Unable to create '.git/HEAD.lock': File exists.

データ収集：成功／失敗
CSV保存：成功／失敗
利益計算：成功／失敗
候補抽出：成功／失敗
通知送信：成功／失敗
URL再確認：未確認／確認済み
仕入れ判断：採用／保留／除外
販売結果：販売済み／在庫中／返品

採用：確認項目をすべて満たす
保留：追加質問や現物確認が必要
除外：利益、状態、規約、許可などの条件を満たさない

商品名：Canon EF 50mm F1.8 STM
想定販売価格：13,200円
仕入れ総額：9,200円
販売手数料：1,320円
発送費：750円
梱包費：80円
期待利益：1,850円
ROI：18.4％
直近30日成約数：5件
状態一致：yes
仕入れ候補URL：...
相場確認URL：...

purchased_at,sold_at,actual_sale_price,actual_shipping,actual_profit,discount_count,return_flag,failure_reason

shipping_overrun
price_drop
condition_mismatch
missing_accessory
return
long_inventory
input_error
rule_violation

実行日時
入力件数
正常評価件数
通過件数
入力エラー件数
判定条件
CSV保存結果
通知結果
例外名
エラーメッセージ