APIで金・銀の過去価格を取得する：25年分の日次データ

curl "https://metalapi.com/api/v1/2024-01-15?currencies=XAU,XAG" \
  -H "X-API-Key: $METALAPI_KEY"

{
  "success": true,
  "timestamp": 1705276800,
  "historical": true,
  "date": "2024-01-15",
  "base": "USD",
  "rates": {
    "XAU": 0.0004874244,
    "USDXAU": 2051.6,
    "XAG": 0.0431741646,
    "USDXAG": 23.162
  }
}

curl "https://metalapi.com/api/v1/timeframe?start_date=2026-10-19&end_date=2026-10-23&currencies=XAU" \
  -H "X-API-Key: $METALAPI_KEY"

{
  "success": true,
  "timeframe": true,
  "start_date": "2026-10-19",
  "end_date": "2026-10-23",
  "base": "USD",
  "rates": {
    "2026-10-19": { "XAU": 0.0002403788, "USDXAU": 4160.1 },
    "2026-10-20": { "XAU": 0.0002413943, "USDXAU": 4142.6 },
    "2026-10-21": { "XAU": 0.0002409639, "USDXAU": 4150 },
    "2026-10-22": { "XAU": 0.0002462812, "USDXAU": 4060.4 },
    "2026-10-23": { "XAU": 0.0002495072, "USDXAU": 4007.9 }
  }
}

curl "https://metalapi.com/api/v1/change?start_date=2026-01-02&end_date=2026-10-23&currencies=XAU,XAG" \
  -H "X-API-Key: $METALAPI_KEY"

{
  "success": true,
  "change": true,
  "start_date": "2026-01-02",
  "end_date": "2026-10-23",
  "base": "USD",
  "rates": {
    "XAU": { "start_rate": 0.0002309682, "end_rate": 0.0002495072, "change": 0.000018539, "change_pct": 8.02665 },
    "USDXAU": { "start_rate": 4329.6, "end_rate": 4007.9, "change": -321.7, "change_pct": -7.43025 }
  }
}

curl "https://metalapi.com/api/v1/ohlc?date=2026-10-23&currencies=XAU" \
  -H "X-API-Key: $METALAPI_KEY"

{
  "success": true,
  "ohlc": true,
  "date": "2026-10-23",
  "base": "USD",
  "rates": {
    "USDXAU": { "open": 4042.2, "high": 4050.2, "low": 3989.1, "close": 4007.9 },
    "XAU": { "open": 0.000247390, "high": 0.000250683, "low": 0.000246901, "close": 0.000249507 }
  }
}

import os
import sqlite3
import time
from datetime import datetime, timezone

import requests

API = "https://metalapi.com/api/v1"
session = requests.Session()
session.headers["X-API-Key"] = os.environ["METALAPI_KEY"]

db = sqlite3.connect("metals.db")
db.execute(
    """create table if not exists daily_close (
         day    text not null,
         symbol text not null,
         base   text not null,
         price  real not null,
         primary key (day, symbol, base)
       )"""
)


def fetch_timeframe(start, end, symbols, base="USD"):
    """startからendまで（両端を含む）の日次終値。日付をキーとする。"""
    while True:
        r = session.get(
            f"{API}/timeframe",
            params={"start_date": start, "end_date": end, "currencies": ",".join(symbols), "base": base},
            timeout=30,
        )
        data = r.json()
        if data.get("success"):
            return data["rates"]
        err = data["error"]
        if err["code"] == 104:  # 1分あたりの上限：待機してから再試行（課金されない）
            time.sleep(int(r.headers.get("Retry-After", "60")))
            continue
        if err["code"] == 300:  # この期間にはデータがない
            return {}
        raise RuntimeError(f"{err['code']} {err['type']}: {err['info']}")


def store(rates, symbols, base="USD"):
    rows = [
        (day, s, base, values[f"{base}{s}"])
        for day, values in rates.items()
        for s in symbols
        if f"{base}{s}" in values
    ]
    db.executemany("insert or replace into daily_close values (?, ?, ?, ?)", rows)
    db.commit()
    return len(rows)


def backfill(first_year, symbols=("XAU", "XAG"), base="USD"):
    today = datetime.now(timezone.utc).date()
    for year in range(first_year, today.year + 1):
        start = f"{year}-01-01"
        end = min(f"{year}-12-31", today.isoformat())
        n = store(fetch_timeframe(start, end, symbols, base), symbols, base)
        print(f"{year}: {n} rows")


if __name__ == "__main__":
    backfill(2000)

from datetime import datetime, timedelta, timezone

from backfill import fetch_timeframe, store

SYMBOLS = ("XAU", "XAG")
today = datetime.now(timezone.utc).date()
start = (today - timedelta(days=7)).isoformat()
end = (today - timedelta(days=1)).isoformat()
print(store(fetch_timeframe(start, end, SYMBOLS), SYMBOLS), "rows updated")

-- 年ごとの行数：完全な年には取引日ごとにおよそ1行がある
select substr(day, 1, 4) as year, symbol, count(*) as days from daily_close
 where base = 'USD' group by year, symbol order by year, symbol;

-- 保存済みの終値の間に4日を超える空白がある箇所
select symbol, prev_day, day from (
  select symbol, day, lag(day) over (partition by symbol order by day) as prev_day
    from daily_close where base = 'USD'
) where julianday(day) - julianday(prev_day) > 4;

import sqlite3
from datetime import date

import matplotlib.dates as mdates
import matplotlib.pyplot as plt

db = sqlite3.connect("metals.db")
rows = db.execute(
    """select g.day, g.price, s.price
         from daily_close g
         join daily_close s on s.day = g.day and s.base = g.base and s.symbol = 'XAG'
        where g.symbol = 'XAU' and g.base = 'USD'
        order by g.day"""
).fetchall()

days = [date.fromisoformat(day) for day, _, _ in rows]
gold = [g for _, g, _ in rows]
ratio = [g / s for _, g, s in rows]

fig, (top, bottom) = plt.subplots(2, 1, sharex=True, figsize=(10, 6))
top.plot(days, gold, linewidth=1)
top.set_ylabel("Gold, USD per troy oz")
bottom.plot(days, ratio, linewidth=1)
bottom.set_ylabel("Gold/silver ratio")
bottom.xaxis.set_major_locator(mdates.YearLocator(base=5))
fig.tight_layout()
fig.savefig("gold-silver.png", dpi=150)

<canvas id="gold" height="320"></canvas>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/chart.umd.js"></script>
<script>
  // 自社のエンドポイント。[{ "day": "2026-10-23", "price": 4007.9 }, ...]を返す
  fetch("/api/gold-history")
    .then((res) => res.json())
    .then((points) => {
      new Chart(document.getElementById("gold"), {
        type: "line",
        data: {
          labels: points.map((p) => p.day),
          datasets: [{ label: "Gold, USD per troy oz", data: points.map((p) => p.price), pointRadius: 0, borderWidth: 1 }],
        },
        options: {
          animation: false,
          scales: { x: { ticks: { maxTicksLimit: 10 } } },
        },
      });
    });
</script>