Create a Crypto Portfolio Tracker on Google Sheets (with App Scripts) | CoinGecko API (2024)

Especially in a fast-moving space like crypto, it can be overwhelming to stay on top of your investments 24/7. In this article, we’ll be sharing how to build your own real-time portfolio tracker using Google Sheets, so you can manage and track your crypto investments easily. Creating your own custom portfolio will allow you to record and calculate your crypto holdings, analyze crypto price and volume changes, and tailor it to your trading preferences. Investors that trade stocks and other assets may even combine this with existing stocks portfolio trackers.

Regardless of whether you’re a beginner or advanced trader, this detailed guide will walk through:

• How to set your Google Sheet up for auto-refreshes
• How to import live crypto data via CoinGecko API (for both Demo& Paid API users)
• How to customize your spreadsheet to calculate crypto holdings, holdings value, and more
• The benefits of creating a portfolio tracker on Google Sheets

Let’s get started!

Create a Live Crypto Portfolio Tracker on Google Sheets in 4 Steps

First, create a new spreadsheet on Google Sheets and name it accordingly. This will be your workspace where you'll input and analyze cryptocurrency data.

Step 1: Import Live Crypto Price Data with App Scripts

Navigate to ‘Extensions’ and select ‘App Script’, where a new tab will appear.

On the left panel, select ‘< > Editor’ and add a new script using the ‘+’ button. Copy and paste the following importJSON script, and save the script as ‘ImportJSON’.This importJSON script is a versatile one that will allow you to import data in many different ways.

Create a second Apps Script by clicking on the ‘+’ button. Copy the code below and paste it into the script editor, saving it as ‘autoRefresh’ – this will allow your sheet to automatically refresh at fixed intervals.

Your Apps Script editor will now look like this:

Step 2: Automate Data Refreshes with Triggers

Now that the scripts have been created, select the clock icon on the left to navigate to ‘Triggers’.

Clicking on ‘+ Add Trigger’ will cause this pop-up to appear. Select the respective dropdowns accordingly:

• Choose which function to run: triggerAutoRefresh
• Choose which deployment should run: Head
• Select event source: Time-driven
• Select type of time based trigger: Minutes timer
• Select minute interval: Every 5 or 10 minutes (note: anything less than this may not be useful, as results are cached)

Depending on your preferred frequency, you may also toggle between Hour timer, Day timer, Week timer, and 15 or 30 minute interval triggers.

Step 3: Import Top 500 Crypto Data with CoinGecko API

CoinGecko tracks over 12,000 cryptocurrencies across 900exchanges, and is the go-to source for millions of investors globally. Coin rankings are based on market capitalization, so pulling in top 500 cryptocurrencies data would typically be more than sufficient for most investors. Do adjust parameters accordingly if you trade smaller market cap coins!

Head over to our CoinGecko API documentation, and find the endpoint /coins/markets.

If you have an API key on hand, you may leverage the readme documentation playground to generate a response. Do remember to reference the correct documentation version based on your plan and key authentication. Public APIusers with a Demo Keyshould reference v3.0.1, and Paid API (Pro Key) users should reference v3.1.1.

As each ‘Page’ tracks 250 coins, we will be importing two pages of coins data via two API calls, to derive the top 500 cryptocurrencies. Leave the ‘ids’ parameter blank, and add the following inputs:

• Per_page: 250
• Page: 1

Fill in the rest of the query params accordingly, input your API key under 'Authentication' and, click 'Try It'.

To pull data for smaller cap coins, change the page number accordingly – for instance, you will be importing coins data for coins ranked between #2500 to #3000, with the ‘Page’ parameter inputs of 11 and 12. This will consume two API calls as well.

In our example, the Request URL is:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3

Copy this and head back to your worksheet.

Label a new worksheet ‘Top 500 Coins’, as this will serve as your raw database and not your actual crypto portfolio dashboard.

In cell A1, use the following and replace the Request URL accordingly.

=IMPORTJSON(“Request URL”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate,noHeaders”,doNotDelete!$A$1)

The following script will appear:

=IMPORTJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=100&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3?x_cg_demo_api_key=YOUR_API_KEY”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate”,doNotDelete!$A$1)

Don't forget tosupply your DemoAPI Key to the root URL via the query string parameter.

Once the script loads, a list of top 250 cryptocurrencies and its respective price, market cap data will now stream into your spreadsheet up through row 251.

• /name – coin name
• /current_price – coin price
• /market_cap – all market cap details
• /price_change – 24hr price change
• /total_volume – 24hr trading volume
• /high_24h and /low24 – 24hr high and low prices

To pull the next 250 cryptocurrencies, apply the same formula on cell A252 with a few tweaks, or simply copy and paste the formulasbelow!

• Change page number to ‘2’, since we’re now moving on to Page 2 for the top 251-500 cryptocurrencies
• Add in ‘,noHeaders’ after “noTruncate” – this prevents duplicating headers (as seen in row 1) on row 252.

DemoAPI users:

=IMPORTJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=2&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3?x_cg_demo_api_key=YOUR_API_KEY”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate,noHeaders”,doNotDelete!$A$1)

Similarly, due to Google Sheets and Demo API rate limits, you may only be able to import a limited range of data.

Paid API users:

=IMPORTJSON(“https://pro-api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=2&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3?&x_cg_pro_api_key=YOUR_API_KEY”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate,noHeaders”,doNotDelete!$A$1)

This robust crypto API integration on Google Sheets allows you to easily fetch real-time prices for the top 500 cryptocurrencies on CoinGecko, and the data automatically refreshes every 10 minutes.

Obtaining Data for Specific Cryptocurrencies

In the scenario where you want to only retrieve data for a specific list of coins, you can fill in the ‘ids’ parameter with the respective coins’ API IDs – this Token API list, created by the CoinGecko team is particularly helpful. Alternatively, you may search for the specific coin on CoinGecko and copy the API id from individual coin pages. For example, XRP’s API id is ‘ripple’.

Fil in 'ripple' in the 'ids' param,and select 'Try It!'. The response will be generated accordingly.

We’ll move on to the final step where you can customize your crypto portfolio tracker and dashboard.

Step 4: Configure Your Portfolio Tracker

Now that you have an auto-updating database of the top 500 cryptocurrencies, you can customize your portfolio tracker based on your trading preferences.

Using VLOOKUP, search for the price, market cap, trading volume and % change, based on the Coin Name. In this example, we’ve done a VLOOKUP search of ‘Bitcoin’ in cell B19, cross referencing its price in the Top 500 Coins worksheet.

Since coin price data is indexed on column 2 of our Top 500 Coins database, we enter ‘2’ in the VLOOKUP formula.

This method is applied to the rest of the table, returning responding values accordingly.

Finally, create a Portfolio section at the end to track your holdings, calculate holding value and profit and loss (P&L) based on real-time cryptocurrency prices.

Create the following row headers:

• Current Holdings – How much of each coin you currently hold.
• Current Holding Value (USD) – How much value your crypto holdings is worth, in fiat currency, derived by multiplying Current Holdings by Current Price.
• Total Invested (USD) – Cost of purchase in fiat currency, for each entry.
• Unrealized P&L (USD) – The profit or loss that could be realized, if the position were closed at that time.
• Realized P&L (USD) – The actual profit or loss that has been realized, based on closing positions.
• ROI % – Return on investment, which evaluates how efficient or profitable your investment is. The higher your ROI, the more profitable your investment is.

Finally, you may want to add data visualizations to your crypto portfolio tracker. Adding a chart and a summary can help to organize and present your crypto investments in an easily digestible way, especially if you have a wide range of crypto assets in your basket.

As you continue to invest in crypto, you'll need to update your portfolio tracker with new purchase records and remove outdated ones. While calculating trading profits and losses hasn’t been fully covered in this article, we'll be creating a step-by-step guide soonon how to automate P&L – both unrealized, realized, ROI and more.

Here’s the finalCrypto Portfolio Tracker on Google Sheets,automated with App Scripts and CoinGecko API:

Benefits of Tracking Your Crypto Portfolio on Google Sheets

Tracking your crypto portfolio on Google Sheets allows you to easily analyze data with charts, pivot tables and formulas across any device, any time. Having auto-refreshing crypto price data also ensures you have an accurate view of all your investments at all times. Investors who diversify their portfolios across crypto, stocks and other assets and are working off Google Sheets, will find it extremely convenient to consolidate and customize all asset holdings in a single, master dashboard.

Why Am I Getting Rate Limited on Google Sheets?

You may be getting rate limited as Google Sheetsrelies on shared hosting, where one Google server hosts multiple sheets. Users making requests on the same server share the same API calls per minute limit. This explains why you may be hitting rate limits or running into an '#ERROR',even when you make only a few API calls or import a small range of data.

Avoid getting rate limited by subscribing to the CoinGecko API Analyst plan. If you’re an existing subscriber and have an API key, use the Pro API root URL (https://pro-api.coingecko.com/api/v3/) and include your API key at the end. This is how the URL structure will appear:

=IMPORTJSON(“https://pro-api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3?&x_cg_pro_api_key=YOUR_API_KEY”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate”,doNotDelete!$A$1)

For Advanced Traders: Useful CoinGecko API Endpoints

Here are some useful API endpoints that advanced traders might find particularly useful:

• /coins/top_gainers_losers - get the top 30 coins with the largest price gains and losses based on specific time frames
• /global/market_cap_chart - get historical global market cap and volume data, by no. of days away from now
• /nfts/markets – track NFT floor prices, market cap and volume

If you're an advanced trader and want access to more comprehensive data, historical prices and bypass rate limits, you may want to consider subscribing to an Analyst API plan.

Credits & Acknowledgements

• importJSON script by Brad Jasper and Trevor (Github)
• triggerAutoRefresh script by Andrea Borruso (Github)