My Crypto Dashboard Updates Itself 96 Times a Day. Here's How I Built It.

My crypto dashboard updates itself every 15 minutes. That's 96 times a day, and I touch nothing.

That was the whole goal of this project. I didn't want a dashboard that goes stale the day after I build it. I wanted one that stays live without me.

In this post, I'll walk through how it works, step by step: where the data comes from, how it gets collected automatically, how SQL shapes it, and how two different dashboards show it. I'll also share the three bugs that taught me the most.

The problem with most dashboards

Most portfolio dashboards are built on a CSV file that someone downloaded once. They look great on day one. A week later, every number on them is old.

Real dashboards at work don't stay frozen. Data keeps arriving, and the charts keep up. I wanted to build that part too, not just the charts.

The big picture

Here is the whole pipeline in one line:

CoinGecko API → Python → Supabase (PostgreSQL) → SQL views → Power BI + Data Studio

Let's go through each step.

Step 1: The data source (CoinGecko)

What is CoinGecko? It's one of the biggest independent crypto data websites. It tracks the prices of thousands of coins across many exchanges, and gives each coin one clean, combined price.

What is an API? It's a way for a program to ask a website for data directly, without opening the website. You send a request, and you get back clean data instead of a web page.

CoinGecko has a free public API, which made it perfect for this project. My script asks for one thing: the top 50 coins by market cap, priced in US dollars. In one request, it gets back, for each coin:

• the current price
• the market cap (the total value of all coins in circulation)
• the 24-hour trading volume
• the 24-hour price change in %

Why CoinGecko helped so much: I didn't have to collect prices from different exchanges and combine them myself. CoinGecko had already done that. One request gave me everything I needed for all 50 coins.

One thing to plan for: free APIs have limits. If you ask too often, CoinGecko replies with "too many requests" (error code 429). My script handles this. It waits a little, then tries again, up to four times, waiting longer each time.

Step 2: The Python collector

A Python script (fetch_crypto.py) does the collecting. Each time it runs, it:

1. Calls the CoinGecko API and gets the top 50 coins
2. Saves one new row per coin in a price_snapshots table, with the price, market cap, volume, 24-hour change and the time it was captured
3. Saves each coin's name and symbol in a separate coins table, so names aren't repeated in every row

Every row in one run gets the same timestamp. That makes it easy later to say "show me the market at 10:15".

Keeping passwords safe: the database login details are never written in the code. They are stored as GitHub Secrets and passed to the script only when it runs.

Step 3: Running without my laptop

A script that only runs when my laptop is on isn't really automatic. So I moved it to the cloud.

GitHub Actions is GitHub's free tool for running code on its own servers. My workflow file tells it: set up Python, install the packages, run the script once, then shut down. Each run takes about 20 seconds.

cron-job.org is a free scheduler. Every 15 minutes, it sends a signal that starts the GitHub Actions workflow.

Why two schedulers? GitHub Actions has its own built-in schedule, and I started with only that. But GitHub's scheduled runs can be delayed when its servers are busy, and some of my 15-minute slots ran late or got skipped. So now cron-job.org is the main trigger, and GitHub's own schedule stays on as a backup.

Step 4: A cloud database (Supabase PostgreSQL)

I first built this with SQLite, a small database that lives in one file on my laptop. That worked, until the collector moved to the cloud. A cloud job can't write to a file on my laptop.

So I moved the data to Supabase, which gives you a free PostgreSQL database in the cloud. Now the GitHub Actions job writes to it, and both dashboards read from it, from anywhere. I wrote a small one-time script to move my older local data over, so nothing was lost.

The table grows by about 50 rows every 15 minutes. It's now past 2,400 rows and still growing.

Step 5: Letting SQL do the heavy work

Instead of building all the logic inside the dashboard, I wrote three SQL views. A view is a saved query that acts like a table. The dashboard just reads from it.

1. Latest price for each coin (using DISTINCT ON)

CREATE OR REPLACE VIEW latest_prices AS
SELECT DISTINCT ON (coin_id) *
FROM price_snapshots
ORDER BY coin_id, captured_at DESC;

The table has many rows per coin. This keeps only the newest one for each coin.

2. Top movers in 24 hours (using RANK())

CREATE OR REPLACE VIEW top_movers AS
SELECT c.name, lp.price_usd, lp.price_change_24h,
  RANK() OVER (ORDER BY lp.price_change_24h DESC) AS gain_rank
FROM latest_prices lp
JOIN coins c ON c.coin_id = lp.coin_id;

This ranks coins from the biggest gain to the biggest drop.

3. Price change since the last snapshot (using LAG())

CREATE OR REPLACE VIEW price_momentum AS
SELECT coin_id, captured_at, price_usd,
  LAG(price_usd) OVER (PARTITION BY coin_id ORDER BY captured_at) AS previous_price,
  price_usd - LAG(price_usd) OVER (PARTITION BY coin_id ORDER BY captured_at) AS price_delta
FROM price_snapshots;

LAG() looks at the row just before. So for each coin, this shows how much the price moved in the last 15 minutes. That's something CoinGecko doesn't give directly, since it only gives the 24-hour change.

Why views? Both dashboards use the same logic, so I write it once. And the dashboards stay simple and fast, because the database does the hard part.

Step 6: Two dashboards, one database

Power BI (2 pages)

Power BI connects to the Supabase database and uses DAX measures (small formulas) for the numbers:

• Avg Price and 24h Change %
• Price Volatility: how much a coin's price swings (standard deviation)
• Volatility Rank: ranks coins from the most to the least volatile
• Session High / Low and Price Range
• Distinct Coins: how many different coins have been tracked

The Overview page has KPI cards, a market cap table, a price trend line and a coin filter.

The Movers and Volatility page shows which coins gained the most, and which ones swing the most.

Data Studio (public and live)

I also wanted a version that anyone could open with just a link, with no sign-in. So I built a Data Studio dashboard on the same database. It refreshes every 15 minutes.

It has two pages: a price trend (pick a coin from a drop-down) and a top movers bar chart.

Three bugs that taught me the most

1. GitHub's schedule ran late.
Some 15-minute runs started late or didn't happen. The fix was adding cron-job.org as the main trigger and keeping GitHub's schedule as a backup.

2. My line chart dropped to zero.
Whenever a time slot had no data, the line crashed down to zero, which looked like the coin had lost all its value. The fix was one setting: linear interpolation, which connects the points on either side of the gap instead.

3. "Last 7 days, excluding today" showed an empty chart.
The chart came up empty, even though the data was there. A custom date range fixed it. A small setting can hide a lot of good data.

What this project taught me

• Fresh data is harder than a nice chart. Keeping the data flowing every 15 minutes was the real challenge. The pipeline matters as much as the visuals.
• Put logic in the database. SQL views kept both dashboards simple, fast and consistent with each other.
• Two tools, two strengths. Power BI was better for detailed measures and analysis. Data Studio was better for sharing a live dashboard with anyone.
• Small details change the story. I collect the top 50 coins, but the dashboard shows 54 distinct coins. That's because the top 50 isn't fixed: when a coin's market cap rises or falls, it moves in or out of the list.

What I'd build next

• An alert (email or message) when a coin moves more than a set percentage
• A drill-through page in Power BI with the full history of one coin
• A data quality check that warns me if a run saves fewer rows than expected
• Packaging the collector in Docker, so it runs the same way anywhere

Try it yourself

🔴 Live dashboard: open it here
💻 Code + README: github.com/HarshithaVajja/crypto-analytics-dashboard

A question for the analysts here: what is the first check you run when a dashboard number looks wrong? Tell me in the comments. 👇

---

Originally published on Compiled Thoughts. Subscribe there to get new posts every week.