SPECIFICATION: Bitcoin Volatility Tracker & Google Sheet Dashboard

follow-up.md

Scenario: Executive Slides: BTC Volatility Google Sheet

Given that we succesfully build a codebase with a BTC Volatility Google Sheet tracker. When fetched the latest BTC trends Then the agent must create an executive Google Slide: https://docs.google.com/presentation/d/1QuBYRKGVvvFvSoyzy0pWH-Jbt3tGJ6XgG22fI6rIVw8/edit?slide=id.p#slide=id.p
And include, in infographic style the following:

• Slide 1: Title = Date today. Content = Executive Summary + Timeseries Graph
• Slide 2: Data table

spec.md

1. BEHAVIOR CONTRACT

Scenario: Track Bitcoin Volatility and Update Google Sheet & Google Slides Demo

Given the CoinGecko public market API is reachable When the agent executes the market tracking routine Then the agent must inspect the Google Sheet tracker:

• If the sheet contains 1 or fewer rows (empty or header only), fetch the last 24 hours of hourly historical Bitcoin data (24 data points for 1 BTC in USD) and append them as a baseline time-series
• If the sheet contains more than 1 row (existing historical data), fetch only the latest spot price for 1 BTC in USD, 24h change %, and 24h volume, and append 1 incremental hourly row And the agent must calculate the physical quantity of Bitcoins traded: 24h Volume (USD) / Spot Price And classify market volatility (Low, Moderate, High) and generate a 2-sentence executive summary And update the Executive Summary text block directly inside the Google Sheet (cells H23:H25) And create or refresh the dynamic zoomed-in interactive line chart embedded at cell H2

2. TECHNICAL CONFIGURATION

metadata:
  name: "Bitcoin Volatility Google Sheet & Slides"
  version: "1.0"
  target_model: "gemini-3.7-flash"
  runtime: "Python 3.12"
  google_sheet_id: "1Ss0BRjfhLnR38hwY7-YZe_NDMYUnQ63bqBeLMgP4uAM"
  google_slides_id: "1QuBYRKGVvvFvSoyzy0pWH-Jbt3tGJ6XgG22fI6rIVw8"
gcloud_prerequisites:
  enable_api_command: "gcloud services enable sheets.googleapis.com slides.googleapis.com"
  auth_login_command: "gcloud auth application-default login --scopes=\"openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets,https://www.googleapis.com/auth/presentations\""
authentication:
  provider: "google_application_default_credentials"
  required_scopes:
    - "openid"
    - "https://www.googleapis.com/auth/userinfo.email"
    - "https://www.googleapis.com/auth/cloud-platform"
    - "https://www.googleapis.com/auth/spreadsheets"
    - "https://www.googleapis.com/auth/presentations"
  login_command: "gcloud auth application-default login --scopes=\"openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets,https://www.googleapis.com/auth/presentations\""
dependencies:
  requests: "2.32.3"
  pandas: "2.2.3"
  pyarrow: "18.1.0"
  matplotlib: "3.10.0"
  google-api-python-client: "2.200.0"
  google-auth: "2.57.0"
api_endpoints:
  coingecko_btc_data: "https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd&include_24hr_change=true&include_24hr_vol=true"
  coingecko_btc_market_chart: "https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=1"
volatility_rules:
  low: "Absolute 24h change < 2.0%"
  moderate: "Absolute 24h change between 2.0% and 5.0%"
  high: "Absolute 24h change > 5.0%"

3. ARCHITECT-FIRST EXECUTION POLICY ("No YOLO Mode")

• Before executing any Python code or tool calls, output an Architectural Plan containing:
  1. The target file structure and workflow pipeline.
  2. The exact API request payload.
  3. A preview of the Google Sheet row, embedded chart layout, and the Executive Summary text block.
• GCP Setup Commands:
  1. Enable Google Sheets and Slides APIs: gcloud services enable sheets.googleapis.com slides.googleapis.com
  2. Authenticate ADC: gcloud auth application-default login --scopes="openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets,https://www.googleapis.com/auth/presentations"
• All price metrics strictly denote 1 BTC to USD ($).
• Target Google Sheet: https://docs.google.com/spreadsheets/d/1Ss0BRjfhLnR38hwY7-YZe_NDMYUnQ63bqBeLMgP4uAM/edit
• Target Google Slides: https://docs.google.com/presentation/d/1QuBYRKGVvvFvSoyzy0pWH-Jbt3tGJ6XgG22fI6rIVw8/edit
• Mock data is strictly restricted to unit tests with isolated ephemeral storage; runtime and CLI executions must use real CoinGecko API data.
• Run the gcloud commands, in order to have a working code base.