Coins: 14,078
Exchanges: 1,063
Market Cap: $2.479T 3.6%
24h Vol: $98.565B
Gas: 8 GWEI
Go Ad-free
API
TABLE OF CONTENTS

Free Downloadable Template: Crypto Portfolio Tracker on Google Sheets

3.4 | by Mixed Analytics | Edited by Julia Ng

With crypto prices moving 24/7, it's critical to have a reliable, accurate view of your investments. For a ready-made solution, you can use CoinGecko's built-in portfolio tracker. However, for more custom functionality, you may prefer fetching data into Google Sheets, where you can create your own custom dashboards and visualizations. 

There are a few different ways to get your data into Google Sheets – utilizing the open source importJSON App Script that hooks into the CoinGecko API is one of the ways.

This article provides an alternative API-based solution using the Google Sheets extension API Connector by Mixed Analytics. Like the importJSON method, API Connector fetches data from the CoinGecko API, but it provides a simplified user interface and built-in access to most of CoinGecko's data endpoints.

This guide will walk through:

 If you prefer to use a pre-configured template, skip ahead to the last section to download the free Google Sheets template.

Let's jump in!


Best free Google Sheets template crypto spreadsheet and price tracker - CoinGecko API

Step 1: Install API Connector

Install the API Connector add-on from the Google Marketplace. Once it's installed, it will be available in the Google Sheets extension menu.

The API Connector can be used for free but has some paid features to make workflows more efficient. While paid features (e.g. scheduling) are mentioned in this guide, they are not required to create a portfolio tracker.

Step 2: Get Your CoinGecko API Key

  1. To get started, you'll need a CoinGecko account, so, if you haven't already, create an account and log in to coingecko.com.

    CoinGecko top login bar

  2. Once you're logged in, navigate to CoinGecko API's pricing page.

  3. To subscribe to a paid API plan, click one of the Upgrade buttons in the pricing table. Alternatively, get a free API key by clicking Create Demo Account underneath the pricing table.

    CoinGecko pricing page with upgrade buttons highlighted

  4. If you select a paid plan, you'll see the Billing Info form on the left. If you are creating a free demo account, you'll see the form on the right.

    CoinGecko forms to enter billing information or create a demo account

  5. Either way, once your account is set up, navigate to the Developer Dashboard and click '+ Add New Key'.

    Add a new key from CoinGecko's developer dashboard

  6. You'll be prompted to label your key and click 'Create'.

    Create a new API key

  7. Your API key will now be listed on the page. Copy this key and keep it safe as we'll use it shortly!

    Copy API key from CoinGecko dashboard

Step 3: Pull CoinGecko API Data into Google Sheets

While you can create your own custom API requests to any of CoinGecko's API endpoints, to make things easy we'll use API Connector's built-in integration to CoinGecko.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request).

  2. If you're using a free API key through a Demo Account, select 'CoinGecko' from the drop-down list of applications. If you are using a paid CoinGecko API plan, select 'CoinGecko Pro'. The CoinGecko Pro API contains some unique endpoints that aren't accessible to free users.

    Select CoinGecko application from API Connector integrations

  3. Under Authorization, enter your API key.

    Enter CoinGecko API key into API Connector

  4. Choose an endpoint. For this example, we'll select /coins/markets, which is the endpoint for fetching the latest market data.

    Choose a CoinGecko endpoint from API Connector

  5. In the parameters section, select which vs_currency you'd like to use, which is a required query param.

  6. Optionally select other parameters. By default, each "page" contains data for 100 coins, so set the per_page parameter to 250 to get more (and see the section on pagination for getting more than 250). 

  7. Choose a destination sheet, name your request, and hit Run to see the response data in your sheet.
    Get live crypto prices in google sheets - crypto spreadsheet

For several endpoints, CoinGecko limits the number of records returned in each response. By default, only 100 records will be returned unless you set the per_page parameter to 250:

To get more than 250 records, you will need to fetch multiple pages of data. In API Connector, you can loop through multiple pages automatically with page parameter pagination handling, like this:

  • Pagination type: page parameter
  • Page parameter: page
  • Run until: choose when to stop running the request

Set up page parameter pagination in API Connector

Step 5: Configure Your Crypto Portfolio Tracker

Now that you've created the basic request, let's configure it into a portfolio tracker based on your own crypto holdings.

  1. Open your earlier request to the /coins/markets endpoint

  2. By default, this endpoint returns data for a set of the top cryptocurrencies. However, for your own portfolio tracker, you'll likely want to retrieve the set of currencies that you're holding or watching. To do that, select or enter your currencies of interest into the ids parameter (to see a full list of available ids, run a request to the /coins/list endpoint).

    Select CoinGecko coin IDs from API Connector

  3. Click Edit fields to open the field editor.

    Open API Connector's field editor

  4. You can now re-arrange the field order, rename your fields, and filter out any fields you're not interested in. Click Save fields when you're done.

    Filter and edit fields in API Connector's field editor

  5. Now that we have our report, let's add some new fields to track your own holdings. The formulas provided below are array formulas so they only need to be entered once and will apply to the entire column.

    1. Current Holdings: enter the quantities you hold of each crypto

    2. Current Holdings Value: enter =arrayformula(if(A2:A<>"",S2:S*C2:C,"")). Change S to the column holding "Current Holdings",  and C to the column holding the current price

    3. Total Invested: enter the amounts you initially spent to acquire each crypto

    4. ROI: enter =arrayformula(if(A2:A<>"",(T2:T-U2:U)/U2:U,"")). Change T to the "Current Holdings Value" column, and U to the "Total Invested" column

      Enter formulas to calculate your portolio holdings

  6. To prevent these calculated fields from being overwritten when the data refreshes, untick the 'Clear sheet data' field located under 'Output options'.

    Untick API Connector's "clear sheet" option

  7. (Optional) To add a new custom header row separating your portfolio data from market data, right-click row 1 in Sheets and click "Insert 1 row above". In API Connector, change your data destination from A1 to A2, since we'll now be starting the data pull from the second row.

  8. That's it! The tracker will be refreshed every time you hit Run. Alternatively, you can set up scheduling (paid feature) to run the report automatically in the background, or use the IMPORTAPI function to run the report with the click of a button. The final report will look like this:

Crypto portfolio tracker in Sheets

Free Downloadable Template: Crypto Portfolio Tracker on Google Sheets

This portfolio tracker is already configured so you just need to input your own Demo or Pro API key and list of coins to return a template that looks like this:

Free Gsheets Crypto Portfolio Tracker Template for Download - CoinGecko API

Grab a copy of the template (you'll be prompted to make your own copy), configuration instructions are listed in the "READ ME" tab. Happy tracking!


Looking to import crypto prices into Excel? Visit this in-depth guide that walks through how to fetch live crypto prices in Excel for trading pairs, historical prices for specific cryptocurrencies, fetch total crypto market cap data into Excel and more!

CoinGecko's Content Editorial Guidelines
CoinGecko’s content aims to demystify the crypto industry. While certain posts you see may be sponsored, we strive to uphold the highest standards of editorial quality and integrity, and do not publish any content that has not been vetted by our editors.
Learn more
Tell us how much you like this article!
Vote count: 47
Mixed Analytics
Mixed Analytics

Mixed Analytics makes API data more accessible and easier to navigate. It allows analysts and marketers to pull finance, marketing, sales and crypto price data from thousands of applications into their spreadsheets so they can analyze their data and uncover insights in one place.

Related Articles


Explore Polkadot's Ecosystem
Discover trending dApps, wallets, DeFi & more

What is Zeebu?
Learn more about the Web3 neobank


coingecko
Continue in app
Track prices in real-time
Open App
Select Currency
Suggested Currencies
USD
US Dollar
IDR
Indonesian Rupiah
TWD
New Taiwan Dollar
EUR
Euro
KRW
South Korean Won
JPY
Japanese Yen
RUB
Russian Ruble
CNY
Chinese Yuan
Fiat Currencies
AED
United Arab Emirates Dirham
ARS
Argentine Peso
AUD
Australian Dollar
BDT
Bangladeshi Taka
BHD
Bahraini Dinar
BMD
Bermudian Dollar
BRL
Brazil Real
CAD
Canadian Dollar
CHF
Swiss Franc
CLP
Chilean Peso
CZK
Czech Koruna
DKK
Danish Krone
GBP
British Pound Sterling
GEL
Georgian Lari
HKD
Hong Kong Dollar
HUF
Hungarian Forint
ILS
Israeli New Shekel
INR
Indian Rupee
KWD
Kuwaiti Dinar
LKR
Sri Lankan Rupee
MMK
Burmese Kyat
MXN
Mexican Peso
MYR
Malaysian Ringgit
NGN
Nigerian Naira
NOK
Norwegian Krone
NZD
New Zealand Dollar
PHP
Philippine Peso
PKR
Pakistani Rupee
PLN
Polish Zloty
SAR
Saudi Riyal
SEK
Swedish Krona
SGD
Singapore Dollar
THB
Thai Baht
TRY
Turkish Lira
UAH
Ukrainian hryvnia
VEF
Venezuelan bolívar fuerte
VND
Vietnamese đồng
ZAR
South African Rand
XDR
IMF Special Drawing Rights
Cryptocurrencies
BTC
Bitcoin
ETH
Ether
LTC
Litecoin
BCH
Bitcoin Cash
BNB
Binance Coin
EOS
EOS
XRP
XRP
XLM
Lumens
LINK
Chainlink
DOT
Polkadot
YFI
Yearn.finance
Bitcoin Units
BITS
Bits
SATS
Satoshi
Commodities
XAG
Silver - Troy Ounce
XAU
Gold - Troy Ounce
Select Language
Popular Languages
EN
English
RU
Русский
DE
Deutsch
PL
język polski
ES
Español
VI
Tiếng việt
FR
Français
PT
Português
All Languages
AR
العربية
BG
български
CS
čeština
DA
dansk
EL
Ελληνικά
FI
suomen kieli
HE
עִבְרִית
HI
हिंदी
HR
hrvatski
HU
Magyar nyelv
ID
Bahasa Indonesia
IT
Italiano
JA
日本語
KO
한국어
LT
lietuvių kalba
NL
Nederlands
NO
norsk
RO
Limba română
SK
slovenský jazyk
SL
slovenski jezik
SV
Svenska
TH
ภาษาไทย
TR
Türkçe
UK
украї́нська мо́ва
ZH
简体中文
ZH-TW
繁體中文
Login to track your favorite coin easily 🚀
By continuing, you agree to CoinGecko Terms of Service and acknowledge you’ve read our Privacy Policy
or
Forgot your password?
Didn't receive confirmation instructions?
Resend confirmation instructions
IT'S FREE! Track your favorite coin easily with CoinGecko 🚀
By continuing, you agree to CoinGecko Terms of Service and acknowledge you’ve read our Privacy Policy
or
Password must contain at least 8 characters including 1 uppercase letter, 1 lowercase letter, 1 number, and 1 special character
Didn't receive confirmation instructions?
Resend confirmation instructions
Forgot your password?
You will receive an email with instructions on how to reset your password in a few minutes.
Resend confirmation instructions
You will receive an email with instructions for how to confirm your email address in a few minutes.
Get the CoinGecko app.
Scan this QR code to download the app now App QR Code Or check it out in the app stores