Track Daily OHLCV Data in Google Sheets (No Code)

In this tutorial, you’ll:

  1. Get your CoinAPI key
  2. Use a single Google Sheets formula to import OHLCV data
  3. Format and visualize that data with built-in charts

Skill level: Beginner

Estimated time: 10–15 minutes

Tools needed: Google Sheets, web browser, CoinAPI account

Before starting this tutorial, make sure you have a valid API key.

👉 Getting Started: How to Create Your API Key

Once you have your API key ready, continue to Step 1 below.

  1. Go to docs.google.com.
  2. Create a new blank spreadsheet.
  3. Name it something like: “Daily OHLCV Tracker (CoinAPI)”

CoinAPI’s OHLCV endpoint returns historical candlestick data between two dates.

A typical request URL looks like this:

1<https://rest.coinapi.io/v1/ohlcv/BINANCE_SPOT_BTC_USDT/history?period_id=1DAY&time_start=2025-10-01T00:00:00&time_end=2025-10-05T00:00:00&limit=10&apikey=YOUR_API_KEY&output_format=csv>
ParameterDescription
BINANCE_SPOT_BTC_USDTExchange + symbol type + pair (you can change to BINANCE_SPOT_ETH_USDT, etc.)
period_id=1DAYCandle interval – 1 day per row
time_startStart time (UTC) of data range
time_endEnd time (UTC) of data range
limitMax number of rows to return (optional safety limit)
apikeyYour CoinAPI key
output_format=csvReturns spreadsheet-ready CSV

🧮 Step 3: Use IMPORTDATA() in Google Sheets

In cell A1, enter:

1=IMPORTDATA("<https://rest.coinapi.io/v1/ohlcv/BINANCE_SPOT_BTC_USDT/history?period_id=1DAY&time_start=2025-10-01T00:00:00&time_end=2025-10-05T00:00:00&limit=10&apikey=YOUR_API_KEY&output_format=csv>")

Replace YOUR_API_KEY with your actual key, then press Enter.

Once this is fully loaded, you will see the data reflected in your sheet:

When you run the formula, a yellow banner may appear:

Warning: Some formulas are trying to send and receive data from external parties.
[Allow access]

Why: Sheets is confirming you’re allowing external data from CoinAPI.

Fix: Click Allow access once, your data will load.

After you click Allow access, you’ll notice that all your imported data appears inside Column A only, each row contains comma-separated values. This happens because Google Sheets doesn’t automatically split external CSV data into separate columns.

  1. Select Column A (click the “A” header).
  2. Go to Data → Split text to columns.
  3. When the separator prompt appears at the bottom, choose Semicolon (;).
  4. The data will instantly expand into multiple columns, one per field.
  1. Highlight the columns for time_period_start, price_open, price_high, price_low, and price_close.
  2. Click Insert → Chart.
  3. In the Chart Editor:
    • Change Chart type → Candlestick chart.
    • Set X-axis = Date (time_period_start).
    • Adjust colors or line thickness as desired.
IssueLikely CauseFix
#REF! / #ERROR!Invalid API keyVerify your key from CoinAPI Dashboard
Blank dataWrong symbol or invalid time rangeCheck pair and ensure time_start/time_end exist on that exchange
Warning bannerSheets permissionsClick Allow access once
Rate limit errorToo many refreshesReduce update frequency or upgrade plan

You’ve Built It!

You now have a fully working, no-code Daily OHLCV Tracker pulling live CoinAPI data between your defined start and end dates!