ZScoreValuation – BTC On‑Chain Z‑Score Valuation

This project builds a Bitcoin long‑term valuation dashboard from:

• The public BigQuery Bitcoin dataset (bigquery-public-data.crypto_bitcoin)
• Your own BigQuery dataset (for a daily BTC price mirror, you will set that up with the below steps)
• A local SQLite database (Database/btc_data.db)
• A final Python dashboard (sdca.py) that shows:
  • An SDCA valuation table (fundamentals + technicals, color‑coded by Z‑score)
  • A regime chart: average valuation Z‑score + BTC price + top/bottom zones

Everything is stored in SQLite and visualized via Plotly.

---

1. Repository Layout

You cloned the repo as:

ZScoreValuation/

Key files and directories:

../                                
  .env                             # Contains SDCA_PROJECT_ID (one level above repo)
  BigQuerycredentials/             # One level above the repo
    sdca-bigquery.json             # Your BigQuery service account JSON

ZScoreValuation/
  sdca.py                          # Main dashboard (builds dataset + plots)
  build_data_from_metrics_bigquery.py  # Merges all tables & computes z-scores
  zscore_utils.py                  # Z-score helpers
  Metrics_Indicators.py            # RSI, Sharpe, Sortino, Omega, etc.

  Database/
    init_db.py                     # Creates/initializes SQLite schema
    sqlite_manager.py              # Wrapper around sqlite3 + schema
    bigquery_client.py             # Thin BigQuery client (pandas DataFrame)
    upload_btc_price_bigquery.py   # Fetch BTC price (CoinMetrics) → BigQuery
    price_bigquery_to_sqlite.py    # Mirror BigQuery btc_price → SQLite
    utxo_bigquery_pipeline.py      # SOPR, realized PnL, CDD → SQLite (btc_sopr)
    realized_cap_bigquery_pipeline.py  # Realized cap, MVRV, NUPL → SQLite (btc_realized)
    cvdd_bigquery_pipeline.py      # CVDD/VDD → SQLite (btc_cvdd)
    tx_volume_bigquery_pipeline.py # Tx volume + NVTS → SQLite (btc_nvts)
    sql_loader.py                  # Helper: loads SQL & replaces {{PROJECT_ID}}

    sql/
      sopr_utxo_bigquery.sql       # SOPR + realized metrics query
      realized_cap_bigquery.sql    # Realized cap / MVRV / NUPL query
      cvdd_bigquery.sql            # CVDD query
      tx_volume_bigquery.sql       # Tx volume + NVTS base query

    btc_data.db                    # Main local SQLite DB (created/updated at runtime)

  BigQuerycredentials/
    sdca-bigquery.json             # Your BigQuery service account JSON (you add this)

The live DB is Database/btc_data.db.

All paths in the code are relative to the project root ZScoreValuation/.
Whenever you run a command, make sure your working directory is the project root:

cd /your/path/to/ZScoreValuation

---

2. Python Environment & Dependencies

You need Python 3.10+ and a few libraries.

From the project root:

pip install pandas numpy plotly google-cloud-bigquery google-api-core requests

That’s enough to run all scripts in this repo.

If you use a virtual environment (recommended), just activate it before installing.

---

3. BigQuery & Service Account Setup

The pipelines depend on two things:

1. Google’s public Bitcoin dataset: bigquery-public-data.crypto_bitcoin
2. Your own project + dataset to store a mirrored BTC price table

3.1 Create a Google Cloud project

1. Go to the Google Cloud console.

2. Create a project, e.g.:

   my-sdca-project
   

3. DO NOT add a billing account. BigQuery can run in Sandbox mode, which lets you query public datasets and run this project completely for free, including up to 1 TiB of query processing per month. Read below for an implementation idea for Free-Tier–Optimized Query Windows (with Dry Run + Byte Limits).

Free-Tier–Optimized Query Windows (with Dry Run + Byte Limits)

• To ensure all BigQuery queries stay within the 1 TiB/month free tier (Sandbox mode), each query can be given a fixed daily byte budget and automatically adjust its time window to fit that budget.
  • Choose a safe daily budget (e.g. 30 GiB/day).
  • Divide it across all queries in the pipeline:

      daily_budget_gib = 1 * 1024 / 30        # ~34 GiB/day available
       query_count = 6                         # number of queries in your pipeline
       per_query_limit_gib = daily_budget_gib / query_count
       per_query_limit_bytes = per_query_limit_gib * 1024**3

  • Use dry_run=True to estimate how many bytes the query would scan.
  • Shrink the timestamp window until the estimated bytes fit the limit.
  • The final query is executed with maximum_bytes_billed to guarantee BigQuery never scans more than the allowed limit (Sandbox remains 100% free).
  • Execute the query only when it fits safely under the free-tier budget.

How Dry Runs Help

• A BigQuery dry run does not execute the query. It only returns the estimated scan size, without consuming free-tier bytes:

from google.cloud import bigquery  

client = bigquery.Client()  

def estimate_bytes(sql: str) -> int:     
	job_config = bigquery.QueryJobConfig(         
	dry_run=True,         
	use_query_cache=False     
	)     
	job = client.query(sql, job_config=job_config)     
	return job.total_bytes_processed

Adaptive Time-Window Loop

• You control the date window; SQL syntax always uses:

  • WHERE timestamp BETWEEN @start AND @end

Python adjusts the window dynamically:

window_days = 30   # start with a wide window

while window_days > 0:
    start = end - timedelta(days=window_days)
    sql = f"""
        SELECT ...
        FROM ...
        WHERE timestamp BETWEEN '{start}' AND '{end}'
    """

    est = estimate_bytes(sql)

    if est <= per_query_limit_bytes:
        # Safe to run
        job_config = bigquery.QueryJobConfig(
            maximum_bytes_billed=per_query_limit_bytes
        )
        client.query(sql, job_config=job_config).result()
        break   # window accepted
    else:
        # Too big → shrink window
        window_days //= 2

Result

• Each query stays under its safe daily byte limit.
• The script fetches as much history as possible per day.
• Subsequent runs only fetch new missing data.
• Project never exceeds the 1 TiB/month free tier.

3.2 Enable BigQuery API

In the same project:

1. Open BigQuery in the console.
2. If prompted, enable the BigQuery API.

3.3 Create a dataset for SDCA

In BigQuery, create a dataset, for example:

Dataset ID: sdca
Location:   (any region, e.g. US)

You will then refer to tables as:

my-sdca-project.sdca.btc_price

(adjust my-sdca-project / sdca to your own IDs).

3.4 Create a service account + JSON key

1. Open IAM & Admin → Service Accounts.

2. Create a service account, for example:

   Name: sdca-bigquery
   

3. Grant it roles (a simple option):

   • BigQuery Job User
   • BigQuery Data Editor

4. Create a JSON key for this service account and download it.

One level above your local repo, create the folder:

BigQuerycredentials/

Place the JSON file inside and rename it to:

../BigQuerycredentials/sdca-bigquery.json

Or, if you prefer a different name/path, you must also update the constants described in the next section.

3.5 .env with project ID

In the same top-level SDCA folder (one level above the repo), create a file:

../.env

Put your Google Cloud project ID in it:

SDCA_PROJECT_ID=your-gcp-project-id-here

All Python scripts that talk to BigQuery will read this via python-dotenv, so you usually do not need to edit PROJECT_ID inside the code files.

---

4. Configure Project ID, Dataset, and Credentials

The SDCA pipelines do not require you to manually edit PROJECT_ID inside each script.   Instead, configuration is handled cleanly through:

• A .env file containing your Google Cloud project ID
• A BigQuerycredentials/ folder containing your service account JSON
• SQL files that use a placeholder {{PROJECT_ID}}
• A helper loader (sql_loader.py) that replaces the placeholder at runtime

This eliminates hardcoded project IDs and keeps your repo clean.

4.1 Project ID via .env

Create a .env file one level above the repo folder:

../
  .env
  BigQuerycredentials/
  ZScoreValuation/

Inside .env, put:

SDCA_PROJECT_ID=your-gcp-project-id

All BigQuery‑related Python files automatically read this via python-dotenv.

4.2 Credentials JSON (service account)

Place your credentials file here:

SDCA/BigQuerycredentials/sdca-bigquery.json

From inside the repo (ZScoreValuation/), scripts refer to it as:

../BigQuerycredentials/sdca-bigquery.json

This keeps credentials outside the repo, but still accessible.

4.3 SQL placeholder replacement ({{PROJECT_ID}})

All SQL files in:

ZScoreValuation/Database/sql/

use:

{{PROJECT_ID}}

Example:

JOIN `{{PROJECT_ID}}.sdca.btc_price` AS p

The helper:

Database/sql_loader.py

automatically loads the SQL file, replaces {{PROJECT_ID}} with the real project ID from .env, and then executes the query.

You normally do not need to modify SQL files manually.

4.4 Dataset and table names inside Python scripts

Some Python scripts still define non‑secret constants like:

DATASET_ID = "sdca"
TABLE_ID = "btc_price"
SQLITE_PATH = "Database/btc_data.db"

These are safe to leave unchanged unless you modify your dataset layout.

You do not need to modify:

• PROJECT_ID
• CREDENTIALS
• BQ_CREDENTIALS

because they now come from:

• .env
• The relative credentials path (../BigQuerycredentials/sdca-bigquery.json)

This keeps configuration centralized and secure.

---

5. What Lives in SQLite (Database/btc_data.db)

All metrics used by the dashboard are stored in Database/btc_data.db.
The schema (from sqlite_manager.py) creates these tables:

5.1 btc_price

Daily BTC price mirror:

CREATE TABLE IF NOT EXISTS btc_price (
    date TEXT PRIMARY KEY,   -- YYYY-MM-DD
    price_usd REAL NOT NULL,
    open REAL,
    high REAL,
    low REAL,
    close REAL,
    volume REAL
);

5.2 btc_sopr

UTXO‑based realized metrics:

CREATE TABLE IF NOT EXISTS btc_sopr (
    date TEXT PRIMARY KEY,            -- YYYY-MM-DD

    spent_btc REAL,                   -- Σ value_btc spent that day

    realized_value_usd REAL,          -- Σ(value_btc * spend_price_usd)
    cost_basis_usd REAL,              -- Σ(value_btc * create_price_usd)
    sopr REAL,                        -- realized_value_usd / cost_basis_usd

    realized_profit_usd REAL,         -- Σ max(PnL, 0)
    realized_loss_usd REAL,           -- Σ min(PnL, 0) (negative)
    net_realized_pnl_usd REAL,        -- profit + loss

    cdd REAL,                         -- Coin Days Destroyed
    avg_utxo_age_days REAL,           -- BTC-weighted avg age of spent UTXOs

    n_spent_outputs INTEGER           -- number of spent UTXOs that day
);

5.3 btc_realized

Realized cap / MVRV / NUPL:

CREATE TABLE IF NOT EXISTS btc_realized (
    date TEXT PRIMARY KEY,

    circulating_supply_btc REAL,
    market_cap_usd REAL,

    realized_cap_usd REAL,
    mvrv REAL,

    unrealized_profit_usd REAL,
    unrealized_loss_usd REAL,
    nupl REAL
);

5.4 btc_cvdd

CVDD metrics:

CREATE TABLE IF NOT EXISTS btc_cvdd (
    date TEXT PRIMARY KEY,
    cvdd REAL,
    vdd REAL,
    cumulative_vdd REAL
);

5.5 btc_nvts

Transaction volume + NVTS:

CREATE TABLE IF NOT EXISTS btc_nvts (
    date TEXT PRIMARY KEY,
    tx_volume_usd REAL,      -- daily tx volume in USD
    tx_volume_ma90 REAL,     -- 90-day MA of tx_volume_usd
    nvts REAL                -- market_cap_usd / tx_volume_ma90
);

5.6 metadata_pipeline

Generic key/value store for pipeline metadata (currently minimal use):

CREATE TABLE IF NOT EXISTS metadata_pipeline (
    key TEXT PRIMARY KEY,
    value TEXT
);

The DB is created/initialized by Database/init_db.py.

---

6. First‑Time End‑to‑End Setup

This section assumes:

• You already edited project IDs / dataset / credential paths as in §4.
• You have a working Python environment with dependencies installed.

All commands are run from project root:

cd /your/path/to/ZScoreValuation

Step 0 – Initialize SQLite schema

python -m Database.init_db

This will create Database/btc_data.db (if it doesn’t exist) and ensure all tables described above exist.

---

Step 1 – Upload BTC price history to BigQuery

python -m Database.upload_btc_price_bigquery

What this does:

• Calls the CoinMetrics community API for BTC daily metrics.

• Normalizes the date/column types.

• Uploads the result into BigQuery table:

  <PROJECT_ID>.<DATASET_ID>.btc_price
  

• Uses a load job that overwrites or upserts rows by date (depending on config).

This price table is the anchor for all later BigQuery SQL (SOPR, CVDD, Realized Cap, NVTS all join against it).

---

Step 2 – Mirror btc_price from BigQuery into SQLite

python -m Database.price_bigquery_to_sqlite

What this does:

• Reads the BigQuery btc_price table via BigQueryClient.
• Filters for rows not yet present in SQLite (btc_price table).
• Upserts them into Database/btc_data.db → btc_price.

On first run it will pull the full history.
On later runs it only pulls new dates.

---

Step 3 – SOPR & realized PnL (UTXO pipeline)

python -m Database.utxo_bigquery_pipeline

Core logic (incremental_update_btc_sopr):

• Loads Database/sql/sopr_utxo_bigquery.sql.

• Determines date range:

  • If from_date not provided:
    • If btc_sopr is empty → use 2011‑01‑01.
    • Else → use (last available date + 1 day).
  • If to_date not provided:
    • Uses today - 1 day.

• Queries BigQuery:

  • Public dataset: bigquery-public-data.crypto_bitcoin
  • Your price table: <PROJECT_ID>.<DATASET_ID>.btc_price

• Computes per‑day:

  • spent_btc
  • realized_value_usd
  • cost_basis_usd
  • sopr
  • realized_profit_usd
  • realized_loss_usd
  • net_realized_pnl_usd
  • cdd
  • avg_utxo_age_days
  • n_spent_outputs

• Normalizes column types and upserts into SQLite table btc_sopr``.

You can optionally call main(from_date, to_date) programmatically if you want tighter control over the date range.

---

Step 4 – Realized Cap / MVRV / NUPL

python -m Database.realized_cap_bigquery_pipeline

Core logic:

• Loads Database/sql/realized_cap_bigquery.sql.

• Queries:

  • UTXO values from bigquery-public-data.crypto_bitcoin
  • Prices from your BigQuery btc_price table

• Produces per‑day:

  • circulating_supply_btc
  • market_cap_usd
  • realized_cap_usd
  • mvrv
  • unrealized_profit_usd
  • unrealized_loss_usd
  • nupl

• Normalizes dates and upserts into btc_realized.

Like the SOPR pipeline, main(from_date=None, to_date=None) supports optional manual date overrides.

---

Step 5 – CVDD (Cumulative Value Days Destroyed)

python -m Database.cvdd_bigquery_pipeline

Core logic:

• Loads Database/sql/cvdd_bigquery.sql.

• Uses public Bitcoin UTXO data + your price table to compute:

  • Daily VDD = Σ(value_btc × age_days × price_at_creation)
  • Cumulative VDD (running sum)
  • CVDD = cumulative_vdd / 6,000,000

• Normalizes and upserts into btc_cvdd.

---

Step 6 – NVTS (Tx Volume, MA90, NVTS ratio)

python -m Database.tx_volume_bigquery_pipeline

This script has two stages:

1. incremental_update_tx_volume()

   • Loads Database/sql/tx_volume_bigquery.sql.
   • Computes daily transaction volume in USD via the public dataset and your price table.
   • Upserts date + tx_volume_usd into btc_nvts.

2. recompute_ma90_and_nvts()

   • Reads all rows in btc_nvts and btc_realized.

   • Recomputes:

     • tx_volume_ma90 (90‑day MA of tx_volume_usd)
     • nvts = market_cap_usd / tx_volume_ma90

   • Upserts the full NVTS series back into btc_nvts.

After this step:

• btc_price, btc_sopr, btc_realized, btc_cvdd, and btc_nvts in SQLite are all populated.

---

Step 7 – Build the merged dataset & Z‑scores

You can explicitly build the dataset with:

python build_data_from_metrics_bigquery.py

The core function is:

from build_data_from_metrics_bigquery import build_dataset
df = build_dataset("Database/btc_data.db")

What it does:

1. Loads each table from SQLite:

   • btc_price
   • btc_sopr
   • btc_realized
   • btc_cvdd
   • btc_nvts

2. Merges them on date.

3. Ensures a PriceUSD column (from price_usd in btc_price).

4. Computes:

   • log_return from PriceUSD
   • Canonical metrics: MVRV, NUPL, SOPR, RealizedPnL_Mom_30d (30‑day rolling sum of net_realized_pnl_usd), CVDD, NVTS, technical indicators (RSI, realized vol, Sharpe, Sortino, Omega, etc.).

5. Computes static Z‑scores (*_z) using zscore_utils.

6. Computes rolling Z‑scores:

   • Fundamentals (MVRV, NUPL, SOPR, RealizedPnL_Mom_30d, CVDD, NVTS) with 730‑day window (*_z730).
   • Technicals (RSI, RealizedVol, Sharpe, Sortino, Omega) with 1200‑day window (*_z1200).

7. Sets date as index and returns the final DataFrame.

Running the script as __main__ just builds the dataset and prints the tail, no plots.

---

Step 8 – Run the SDCA Valuation Dashboard

The main entrypoint is:

python sdca.py

This:

1. Calls build_dataset() from build_data_from_metrics_bigquery.

2. Passes the result through compute_average_z(df) in sdca.py, which:

   • Selects all configured fundamental/technical Z‑score columns (*_z730, *_z1200).

   • Normalizes them into a clipped percentile scale (e.g. −2.5 to +2.5).

   • Smooths them with an exponential moving average (*_scaled_smooth).

   • Aggregates them into:

     • Per‑metric smoothed scores, e.g.:
       • MVRV_scaled_smooth
       • NUPL_scaled_smooth
       • SOPR_scaled_smooth
       • RealizedPnL_Mom_30d_scaled_smooth
       • CVDD_scaled_smooth
       • NVTS_scaled_smooth
       • RSI_14_scaled_smooth
       • etc.
     • Global avg_zscore: the portfolio‑style average of all active metrics.

3. Classifies each day into a valuation regime via classify_regime(avg_zscore):

   • +2.0 → darkred (extreme overvaluation)

   • +1.0 to +2.0 → lightred
   • −2.0 to −1.0 → lightgreen
   • < −2.0 → darkgreen
   • otherwise → neutral

4. Calls sdca_dashboard(df), which:

   • Prints a console summary of the latest values.

   • Shows two Plotly windows:

     1. SDCA Table (plot_sdca_table):

        • Left: fundamentals + Z.
        • Right: technicals + Z.
        • Cells are color‑coded by the current scaled Z.

     2. Regime Chart (plot_regime_z_with_price):

        • Bottom panel: avg_zscore with horizontal lines (e.g. +2, −1.5, 0).
        • Background shading for top/bottom regimes.
        • Top panel: BTC price (on secondary Y‑axis) overlaid as a dashed line.

---

7. Daily / Maintenance Workflow

Once the system is set up and you have run the full pipeline once, daily or periodic updates are straightforward.

From the project root:

cd /your/path/to/ZScoreValuation

Run, in this order:

python -m Database.upload_btc_price_bigquery      # Update BTC price table in BigQuery
python -m Database.price_bigquery_to_sqlite      # Mirror new price rows into SQLite

python -m Database.utxo_bigquery_pipeline        # Update SOPR / realized PnL / CDD
python -m Database.realized_cap_bigquery_pipeline# Update realized cap / MVRV / NUPL
python -m Database.cvdd_bigquery_pipeline        # Update CVDD
python -m Database.tx_volume_bigquery_pipeline   # Update tx_volume_usd / NVTS

python sdca.py                                   # Rebuild dataset & show updated dashboard

Notes:

• All BigQuery pipelines are incremental by default:
  • They look at the last available date in SQLite.
  • They start from last_date + 1 (or a hardcoded early date on first run).
  • They default to_date to yesterday.
• You can override ranges by calling the main(from_date, to_date) functions from Python if needed.

This separation keeps a clear distinction between:

• Initial setup: fill the whole history
• Maintenance: only append new days and rebuild the dashboard

---

8. Quick Reference – Commands

From project root:

One‑time (after cloning & configuring)

python -m Database.init_db
python -m Database.upload_btc_price_bigquery
python -m Database.price_bigquery_to_sqlite
python -m Database.utxo_bigquery_pipeline
python -m Database.realized_cap_bigquery_pipeline
python -m Database.cvdd_bigquery_pipeline
python -m Database.tx_volume_bigquery_pipeline
python sdca.py

Typical refresh

python -m Database.upload_btc_price_bigquery
python -m Database.price_bigquery_to_sqlite
python -m Database.utxo_bigquery_pipeline
python -m Database.realized_cap_bigquery_pipeline
python -m Database.cvdd_bigquery_pipeline
python -m Database.tx_volume_bigquery_pipeline
python sdca.py

After that last command, you will see the updated SDCA valuation table and the regime chart based on the newest available Bitcoin data.