Crypto Tax Calculator in Google Sheets (Free Template)

Calculating crypto taxes manually is error-prone, especially when you’re transacting between centralized exchanges, DeFi pools, and multiple wallets. A crypto tax calculator helps to calculate capital gains and losses accurately, saving you time while lowering the risk of penalties. This free Crypto Tax Calculator Google Sheets template automatically fetches historical crypto prices using the CoinGecko API, calculates your capital gains and losses with the FIFO method, and separates them into Short-Term and Long-Term categories. The template provides a structured way to determine your tax liability from selling, swapping, or spending crypto, all based on accurate, verifiable historical data from the industry's most trusted source.

To get started and follow along with the setup steps, you can make a copy of the free Google Sheets template using the download link at the end of this article.

What is Crypto Tax?

Cryptocurrency is generally treated as property by tax authorities like the IRS, not as currency, which determines how various transactions are taxed.

Tax liability for crypto falls into two main categories:

1. Ordinary Income: If you are paid in cryptocurrency (e.g., salary, staking, mining rewards, or airdrops), the Fair Market Value (FMV) in USD at the time of receipt is taxed as ordinary income. These profits are taxed at your regular income tax rate, which can range from 10% to 37% in the United States of America.

2. Capital Gains/Losses: This tax applies when you dispose of crypto holdings, which includes selling for fiat, swapping crypto-to-crypto, or using it to buy goods or services. The taxable event is calculated by subtracting your Adjusted Cost Basis from the sale proceeds. For example, if you bought 1 ETH for $1,500 and later used that 1 ETH to buy a laptop when its value was $2,000, you've made a $500 capital gain that you need to report for tax purposes.

How Capital Gains and Losses Are Calculated

To calculate your Capital Gain/Loss, you must first determine the Cost Basis of the specific coins disposed of. Your cost basis is simply the original price you paid for an asset, and it is crucial for calculating the profit from any taxable transaction, such as selling or swapping crypto. Your final cost basis includes the original price plus any related transaction fees.

For example:

• If you bought ETH for $2,000 and paid a $5 gas fee, your cost basis is $2,005.

• If you later sell this ETH for $3,000, paying a $15 fee, your capital gain is calculated as:
  $3,000 - $2,020 = $980 (where $2,020 is the total adjusted cost basis).

There are a number of different cost basis methods used in accounting. Depending on your tax jurisdiction and country, you may be entitled to select your preferred method. In the United States, for instance, the IRS allows taxpayers to choose methods like HIFO, FIFO, or LIFO, provided they can specifically identify the assets being sold. Whatever method you choose, you must use it consistently when calculating your gains or losses. If you are uncertain as to which cost basis method you can use, contact the IRS to confirm.

Note: This template is structured to comply with US tax laws and uses the FIFO (First-In, First-Out) method, the most common accounting approach, which assumes the first assets you acquired are the first ones you sell. This can be advantageous, as selling your longest-held assets may qualify them for lower long-term capital gains tax rates.

What Is the Difference Between Short-Term and Long-Term Crypto Taxes?

The primary difference is the tax rate you'll pay. The rate applied to your capital gain depends on how long you held the asset:

• Short-Term Gains:
  For crypto held for one year or less (Less than 365 days), the profit is taxed at the higher ordinary income tax rate (10% to 37% in the US).

• Long-Term Gains:
  For crypto held for more than one year (More than 365 days), the profit is subject to preferential, lower tax rates (0% to 20% in the US).

Prerequisites

Before you start, ensure the following requirements are met to use the template effectively:

• A Google Account:
  You’ll need a Google account to open, copy, and use the template in Google Sheets. This allows you to save your own version and enable Apps Script for live data connections.

• A CoinGecko API Key:
  Required to fetch live crypto prices. The free Demo API key is sufficient to get started, while a Pro plan offers higher rate limits for more frequent data refreshes. You can follow our guide to get your free Demo API key.

How to Use the Crypto Tax Calculator Google Sheet Template

This section provides a clear, step-by-step guide on how to integrate and use the Crypto Tax Calculator template effectively. Follow these instructions to set up your CoinGecko API key, input your transaction history, and view your automatically calculated tax summary.

Setting up the CoinGecko API Key in Google Sheets

First, connect the template to your CoinGecko API key. To do this, navigate to the “Read Me” sheet, this is your configuration page for API settings.

1. Open the “Read Me” sheet.

2. In cell A20, specify your subscription level as Demo or Pro.

3. In cell B20, paste your CoinGecko API key.

Once entered, the key is automatically recognized by all live queries across your workbook. You don’t need to modify any formulas manually.

Setting up Automatic Data Refreshes

Now that the API credentials are added to the spreadsheet, you can configure it to refresh the data automatically. Go to Extensions > Apps Script, then click the Triggers icon (shaped like a clock) on the left sidebar.

Clicking “+ Add Trigger” will open up a modal for you to configure the following settings:

• 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, or Week timer.

After saving, the script will run automatically at your specified interval, making sure all data is pulled correctly. Keep in mind that more frequent refreshes will consume your API call credits faster. If you run into rate limits, consider upgrading to a paid API plan for higher call credits and rate limits.

How to Add Specific Coins

To calculate historical values, the template needs the unique API ID for each asset, which you can find on any individual coin page on the CoinGecko website.

Once you have the API ID, simply paste it into cell B4 on the Tax Calculator Sheet. The template will then automatically fetch the correct historical data for your calculations.

Logging your Cryptocurrency’s Transactions in the Tax Calculator Sheet

Once the API key and coin ID are set up, you can begin logging your buy and sell transactions in the “Tax Transaction Log” table. This log is the foundation for calculating your cost basis and capital gains or losses using the FIFO method.
To log your transactions efficiently, you should import the data from your exchange history:

1. Export History from Your Source: Most crypto exchanges and centralized wallets allow you to download your complete transaction history as a CSV file. For on-chain data (e.g., from a MetaMask wallet), you can use a block explorer like Etherscan to view your address and download its transaction history.

2. Filter and Prepare Data: Open your exported CSV file and prepare the essential details required by the template's columns:

   • The Transaction Date and Time.

   • The Asset Name (e.g., "Bitcoin") to help you look up the correct CoinGecko API ID.

   • The Quantity purchased or sold.

   • The Transaction Fee, if applicable.

   • Whether the transaction was a Buy or a Sell.

Analyzing Your Crypto Tax Summary 

Upon logging your transaction history appropriately, the in-built functions will start running immediately. The CoinGecko API will fetch the precise historical price for each transaction date, and the template will begin calculating your Capital Gains/Losses, separating them into Short-Term and Long-Term categories based on the FIFO method. 

This system automatically transforms complex transaction data into auditable, tax-ready figures. You can use the Net FIFO Short-Term Gain and Net FIFO Long-Term Gain figures directly to complete your final tax forms, such as IRS Form 8949 and Schedule D.

To calculate your final tax liability, you must apply the appropriate tax rates to your Net FIFO Short-Term Gain and Net FIFO Long-Term Gain figures. Tax systems, like the one in the US, are often progressive, meaning different portions of your income are taxed at different rates (or 'brackets'). Your final tax payable is the sum of the taxes calculated for each bracket your gains fall into.

While the tax table in this template reflects US rates, the calculator's gain/loss figures can be used in any jurisdiction. Since FIFO is a widely accepted method, you can apply your country's specific tax rates to the net gains calculated by the sheet to determine your liability. To see an estimate of your U.S. federal income tax bracket, navigate to the Read me Sheet. Simply input your annual taxable income into Cell C20 and select your filing status from the dropdown menu in Cell D20.

The tax rate that automatically appears in Cell E20 is the Progressive Tax Rate Ceiling. This represents the highest marginal tax bracket your income falls into. Please note that this is not your effective tax rate and should not be used to calculate your final tax bill. It is an indicator of the highest tax rate applied to only the top portion of your income.

How to Create a Crypto Tax Calculator in Google Sheets

Creating a crypto tax calculator in Google Sheets involves three main components: a source for accurate historical price data, a structured log for transactions, and formulas to execute tax logic.

The template integrates all three seamlessly into one connected system:

• Historical Crypto Price Data
  Accurate historical prices are automatically fetched using a custom Google Apps Script function that communicates directly with the CoinGecko API. The template uses the dedicated /coins/{id}/history endpoint to retrieve the Fair Market Value (FMV) of any asset on its specific transaction date, which is essential for establishing a reliable cost basis.

• Structured Transaction Logging
  A detailed log sheet to record transaction input, such as quantities bought or sold, dates, and fees.

• Google Sheets Formulas and Tables
  Advanced Sheets formulas are used to calculate and classify every taxable event instantly. These nested calculations determine the specific cost basis consumed by a sale, calculate the resulting Capital gain or loss, and classify it as Short-Term or Long-Term.

This connected system ensures that every metric, from your original cost basis to your final net gain, is calculated dynamically and accurately.

How to Get Historical Crypto Data in Google Sheets

You can get historical crypto data in Google Sheets by creating custom functions with Google Apps Script that fetches data directly from CoinGecko API’s /coins/{id}/history endpoint.

Step 1: Create an ImportJSON Script

First, create the main function that will fetch and parse data from CoinGecko API.

Open your Google Sheet and navigate to ‘Extensions’ and select ‘Apps Script’ - 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.

/*====================================================================================================================================*   ImportJSON by Brad Jasper and Trevor Lohrbeer   ====================================================================================================================================   Version:      1.5.0   Project Page: https://github.com/bradjasper/ImportJSON   Copyright:    (c) 2017-2019 by Brad Jasper                 (c) 2012-2017 by Trevor Lohrbeer   License:      GNU General Public License, version 3 (GPL-3.0)                  http://www.opensource.org/licenses/gpl-3.0.html   ------------------------------------------------------------------------------------------------------------------------------------   A library for importing JSON feeds into Google spreadsheets. Functions include:      ImportJSON            For use by end users to import a JSON feed from a URL       ImportJSONFromSheet   For use by end users to import JSON from one of the Sheets      ImportJSONViaPost     For use by end users to import a JSON feed from a URL using POST parameters      ImportJSONAdvanced    For use by script developers to easily extend the functionality of this library      ImportJSONBasicAuth   For use by end users to import a JSON feed from a URL with HTTP Basic Auth (added by Karsten Lettow)   For future enhancements see https://github.com/bradjasper/ImportJSON/issues?q=is%3Aissue+is%3Aopen+label%3Aenhancement      For bug reports see https://github.com/bradjasper/ImportJSON/issues   ------------------------------------------------------------------------------------------------------------------------------------   Changelog:      1.6.0 (June 2, 2019) Fixed null values (thanks @gdesmedt1)   1.5.0  (January 11, 2019) Adds ability to include all headers in a fixed order even when no data is present for a given header in some or all rows.   1.4.0  (July 23, 2017) Transfer project to Brad Jasper. Fixed off-by-one array bug. Fixed previous value bug. Added custom annotations. Added ImportJSONFromSheet and ImportJSONBasicAuth.   1.3.0  Adds ability to import the text from a set of rows containing the text to parse. All cells are concatenated   1.2.1  Fixed a bug with how nested arrays are handled. The rowIndex counter wasn't incrementing properly when parsing.   1.2.0  Added ImportJSONViaPost and support for fetchOptions to ImportJSONAdvanced   1.1.1  Added a version number using Google Scripts Versioning so other developers can use the library   1.1.0  Added support for the noHeaders option   1.0.0  Initial release  *====================================================================================================================================*/ /**  * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create   * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in   * the JSON feed. The remaining rows contain the data.   * * By default, data gets transformed so it looks more like a normal data import. Specifically:  *  * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values   * of the rows representing their parent elements.  * - Values longer than 256 characters get truncated.  * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.   *  * To change this behavior, pass in one of these values in the options parameter:  *  * noInherit:     Don't inherit values from parent elements  * noTruncate:    Don't truncate values  * rawHeaders:    Don't prettify headers  * noHeaders:     Don't include headers, only the data  * allHeaders:    Include all headers from the query parameter in the order they are listed  * debugLocation: Prepend each value with the row & column it belongs in  *  * For example:  *  * =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content",  * "noInherit,noTruncate,rawHeaders")  * * @param {url}          the URL to a public JSON feed  * @param {query}        a comma-separated list of paths to import. Any path starting with one of these paths gets imported.  * @param {parseOptions} a comma-separated list of options that alter processing of the data  * @customfunction  *  * @return a two-dimensional array containing the data, with the first row containing headers  **/ function ImportJSON(url, query, parseOptions) {   return ImportJSONAdvanced(url, null, query, parseOptions, includeXPath_, defaultTransform_); } /**  * Imports a JSON feed via a POST request and returns the results to be inserted into a Google Spreadsheet. The JSON feed is   * flattened to create a two-dimensional array. The first row contains the headers, with each column header indicating the path to   * that data in the JSON feed. The remaining rows contain the data.  *  * To retrieve the JSON, a POST request is sent to the URL and the payload is passed as the content of the request using the content   * type "application/x-www-form-urlencoded". If the fetchOptions define a value for "method", "payload" or "contentType", these   * values will take precedent. For example, advanced users can use this to make this function pass XML as the payload using a GET   * request and a content type of "application/xml; charset=utf-8". For more information on the available fetch options, see  * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app . At this time the "headers" option is not supported.  * * By default, the returned data gets transformed so it looks more like a normal data import. Specifically:  *  * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values   * of the rows representing their parent elements.  * - Values longer than 256 characters get truncated.  * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.   *  * To change this behavior, pass in one of these values in the options parameter:  *  * noInherit:     Don't inherit values from parent elements  * noTruncate:    Don't truncate values  * rawHeaders:    Don't prettify headers  * noHeaders:     Don't include headers, only the data  * allHeaders:    Include all headers from the query parameter in the order they are listed  * debugLocation: Prepend each value with the row & column it belongs in  *  * For example:  *  * =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "user=bob&apikey=xxxx",   * "validateHttpsCertificates=false", "/feed/entry/title,/feed/entry/content", "noInherit,noTruncate,rawHeaders")  * @param {url}          the URL to a public JSON feed  * @param {payload}      the content to pass with the POST request; usually a URL encoded list of parameters separated by ampersands  * @param {fetchOptions} a comma-separated list of options used to retrieve the JSON feed from the URL  * @param {query}        a comma-separated list of paths to import. Any path starting with one of these paths gets imported.  * @param {parseOptions} a comma-separated list of options that alter processing of the data  * @customfunction  *  * @return a two-dimensional array containing the data, with the first row containing headers  **/ function ImportJSONViaPost(url, payload, fetchOptions, query, parseOptions) {   var postOptions = parseToObject_(fetchOptions);      if (postOptions["method"] == null) {     postOptions["method"] = "POST";   }   if (postOptions["payload"] == null) {     postOptions["payload"] = payload;   }   if (postOptions["contentType"] == null) {     postOptions["contentType"] = "application/x-www-form-urlencoded";   }   convertToBool_(postOptions, "validateHttpsCertificates");   convertToBool_(postOptions, "useIntranet");   convertToBool_(postOptions, "followRedirects");   convertToBool_(postOptions, "muteHttpExceptions");      return ImportJSONAdvanced(url, postOptions, query, parseOptions, includeXPath_, defaultTransform_); } /**  * Imports a JSON text from a named Sheet and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create   * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in   * the JSON feed. The remaining rows contain the data.   * * By default, data gets transformed so it looks more like a normal data import. Specifically:  *  * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values   * of the rows representing their parent elements.  * - Values longer than 256 characters get truncated.  * - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.   *  * To change this behavior, pass in one of these values in the options parameter:  *  * noInherit:     Don't inherit values from parent elements  * noTruncate:    Don't truncate values  * rawHeaders:    Don't prettify headers  * noHeaders:     Don't include headers, only the data  * allHeaders:    Include all headers from the query parameter in the order they are listed  * debugLocation: Prepend each value with the row & column it belongs in  *  * For example:  *  * =ImportJSONFromSheet("Source", "/feed/entry/title,/feed/entry/content",  * "noInherit,noTruncate,rawHeaders")  * @param {sheetName} the name of the sheet containg the text for the JSON  * @param {query} a comma-separated lists of paths to import. Any path starting with one of these paths gets imported.  * @param {options} a comma-separated list of options that alter processing of the data  *  * @return a two-dimensional array containing the data, with the first row containing headers  * @customfunction  **/ function ImportJSONFromSheet(sheetName, query, options) {   var object = getDataFromNamedSheet_(sheetName);      return parseJSONObject_(object, query, options, includeXPath_, defaultTransform_); } /**  * An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a   * spreadsheet.  * * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create   * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in   * the JSON feed. The remaining rows contain the data.   *  * The fetchOptions can be used to change how the JSON feed is retrieved. For instance, the "method" and "payload" options can be   * set to pass a POST request with post parameters. For more information on the available parameters, see   * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app .  *  * Use the include and transformation functions to determine what to include in the import and how to transform the data after it is  * imported.   *  * For example:  *  * ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json",   * new Object() { "method" : "post", "payload" : "user=bob&apikey=xxxx" },  * "/feed/entry",  * "",  * function (query, path) { return path.indexOf(query) == 0; },  * function (data, row, column) { data[row][column] = data[row][column].toString().substr(0, 100); } )  *  * In this example, the import function checks to see if the path to the data being imported starts with the query. The transform   * function takes the data and truncates it. For more robust versions of these functions, see the internal code of this library.  *  * @param {url}           the URL to a public JSON feed  * @param {fetchOptions}  an object whose properties are options used to retrieve the JSON feed from the URL  * @param {query}         the query passed to the include function  * @param {parseOptions}  a comma-separated list of options that may alter processing of the data  * @param {includeFunc}   a function with the signature func(query, path, options) that returns true if the data element at the given path  * should be included or false otherwise.   * @param {transformFunc} a function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data   * and row & column are the current row and column being processed. Any return value is ignored. Note that row 0   * contains the headers for the data, so test for row==0 to process headers only.  *  * @return a two-dimensional array containing the data, with the first row containing headers  * @customfunction  **/ function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) {   var jsondata = UrlFetchApp.fetch(url, fetchOptions);   var object   = JSON.parse(jsondata.getContentText());      return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc); } /**  * Helper function to authenticate with basic auth informations using ImportJSONAdvanced  *  * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create  * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in  * the JSON feed. The remaining rows contain the data.  *  * The fetchOptions can be used to change how the JSON feed is retrieved. For instance, the "method" and "payload" options can be  * set to pass a POST request with post parameters. For more information on the available parameters, see  * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app .  *  * Use the include and transformation functions to determine what to include in the import and how to transform the data after it is  * imported.  *  * @param {url}           the URL to a http basic auth protected JSON feed  * @param {username}      the Username for authentication  * @param {password}      the Password for authentication  * @param {query}         the query passed to the include function (optional)  * @param {parseOptions}  a comma-separated list of options that may alter processing of the data (optional)  *  * @return a two-dimensional array containing the data, with the first row containing headers  * @customfunction  **/ function ImportJSONBasicAuth(url, username, password, query, parseOptions) {   var encodedAuthInformation = Utilities.base64Encode(username + ":" + password);   var header = {headers: {Authorization: "Basic " + encodedAuthInformation}};   return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_); } /** * Encodes the given value to use within a URL.  *  * @param {value} the value to be encoded  * * @return the value encoded using URL percent-encoding  */ function URLEncode(value) {   return encodeURIComponent(value.toString());   } /**  * Adds an oAuth service using the given name and the list of properties.  *  * @note This method is an experiment in trying to figure out how to add an oAuth service without having to specify it on each   * ImportJSON call. The idea was to call this method in the first cell of a spreadsheet, and then use ImportJSON in other  * cells. This didn't work, but leaving this in here for further experimentation later.   *  * The test I did was to add the following into the A1:  * * =AddOAuthService("twitter", "https://api.twitter.com/oauth/access_token",   * "https://api.twitter.com/oauth/request_token", "https://api.twitter.com/oauth/authorize",   * "<my consumer key>", "<my consumer secret>", "", "")  *  * Information on obtaining a consumer key & secret for Twitter can be found at https://dev.twitter.com/docs/auth/using-oauth  *  * Then I added the following into A2:  *  * =ImportJSONViaPost("https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=fastfedora&count=2", "",  * "oAuthServiceName=twitter,oAuthUseToken=always", "/", "")  *  * I received an error that the "oAuthServiceName" was not a valid value. [twl 18.Apr.13]  */ function AddOAuthService__(name, accessTokenUrl, requestTokenUrl, authorizationUrl, consumerKey, consumerSecret, method, paramLocation) {   var oAuthConfig = UrlFetchApp.addOAuthService(name);   if (accessTokenUrl != null && accessTokenUrl.length > 0) {     oAuthConfig.setAccessTokenUrl(accessTokenUrl);   }      if (requestTokenUrl != null && requestTokenUrl.length > 0) {     oAuthConfig.setRequestTokenUrl(requestTokenUrl);   }      if (authorizationUrl != null && authorizationUrl.length > 0) {     oAuthConfig.setAuthorizationUrl(authorizationUrl);   }      if (consumerKey != null && consumerKey.length > 0) {     oAuthConfig.setConsumerKey(consumerKey);   }      if (consumerSecret != null && consumerSecret.length > 0) {     oAuthConfig.setConsumerSecret(consumerSecret);   }      if (method != null && method.length > 0) {     oAuthConfig.setMethod(method);   }      if (paramLocation != null && paramLocation.length > 0) {     oAuthConfig.setParamLocation(paramLocation);   } } /** * Parses a JSON object and returns a two-dimensional array containing the data of that object.  */ function parseJSONObject_(object, query, options, includeFunc, transformFunc) {   var headers = new Array();   var data    = new Array();      if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {     query = query.toString().split(",");   }   // Prepopulate the headers to lock in their order   if (hasOption_(options, "allHeaders") && Array.isArray(query))   {     for (var i = 0; i < query.length; i++)     {       headers[query[i]] = Object.keys(headers).length;     }   }      if (options) {     options = options.toString().split(",");   }        parseData_(headers, data, "", {rowIndex: 1}, object, query, options, includeFunc);   parseHeaders_(headers, data);   transformData_(data, options, transformFunc);      return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data; } /** * Parses the data contained within the given value and inserts it into the data two-dimensional array starting at the rowIndex.   * If the data is to be inserted into a new column, a new header is added to the headers array. The value can be an object,   * array or scalar value.  *  * If the value is an object, it's properties are iterated through and passed back into this function with the name of each   * property extending the path. For instance, if the object contains the property "entry" and the path passed in was "/feed",  * this function is called with the value of the entry property and the path "/feed/entry".  *  * If the value is an array containing other arrays or objects, each element in the array is passed into this function with   * the rowIndex incremeneted for each element.  *  * If the value is an array containing only scalar values, those values are joined together and inserted into the data array as   * a single value.  *  * If the value is a scalar, the value is inserted directly into the data array.  */ function parseData_(headers, data, path, state, value, query, options, includeFunc) {   var dataInserted = false;   if (Array.isArray(value) && isObjectArray_(value)) {     for (var i = 0; i < value.length; i++) {       if (parseData_(headers, data, path, state, value[i], query, options, includeFunc)) {         dataInserted = true;         if (data[state.rowIndex]) {           state.rowIndex++;         }       }     }   } else if (isObject_(value)) {     for (key in value) {       if (parseData_(headers, data, path + "/" + key, state, value[key], query, options, includeFunc)) {         dataInserted = true;        }     }   } else if (!includeFunc || includeFunc(query, path, options)) {     // Handle arrays containing only scalar values     if (Array.isArray(value)) {       value = value.join();      }          // Insert new row if one doesn't already exist     if (!data[state.rowIndex]) {       data[state.rowIndex] = new Array();     }          // Add a new header if one doesn't exist     if (!headers[path] && headers[path] != 0) {       headers[path] = Object.keys(headers).length;     }          // Insert the data     data[state.rowIndex][headers[path]] = value;     dataInserted = true;   }      return dataInserted; } /** * Parses the headers array and inserts it into the first row of the data array.  */ function parseHeaders_(headers, data) {   data[0] = new Array();   for (key in headers) {     data[0][headers[key]] = key;   } } /** * Applies the transform function for each element in the data array, going through each column of each row.  */ function transformData_(data, options, transformFunc) {   for (var i = 0; i < data.length; i++) {     for (var j = 0; j < data[0].length; j++) {       transformFunc(data, i, j, options);     }   } } /** * Returns true if the given test value is an object; false otherwise.  */ function isObject_(test) {   return Object.prototype.toString.call(test) === '[object Object]'; } /** * Returns true if the given test value is an array containing at least one object; false otherwise.  */ function isObjectArray_(test) {   for (var i = 0; i < test.length; i++) {     if (isObject_(test[i])) {       return true;      }   }     return false; } /** * Returns true if the given query applies to the given path.   */ function includeXPath_(query, path, options) {   if (!query) {     return true;    } else if (Array.isArray(query)) {     for (var i = 0; i < query.length; i++) {       if (applyXPathRule_(query[i], path, options)) {         return true;        }     }     } else {     return applyXPathRule_(query, path, options);   }      return false;  }; /** * Returns true if the rule applies to the given path.   */ function applyXPathRule_(rule, path, options) {   return path.indexOf(rule) == 0;  } /** * By default, this function transforms the value at the given row & column so it looks more like a normal data import. Specifically:  *  * - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values   * of the rows representing their parent elements.  * - Values longer than 256 characters get truncated.  * - Values in row 0 (headers) have slashes converted to spaces, common prefixes removed and the resulting text converted to title  * case.   *  * To change this behavior, pass in one of these values in the options parameter:  *  * noInherit:     Don't inherit values from parent elements  * noTruncate:    Don't truncate values  * rawHeaders:    Don't prettify headers  * debugLocation: Prepend each value with the row & column it belongs in  */ function defaultTransform_(data, row, column, options) {   if (data[row][column] == null) {     if (row < 2 || hasOption_(options, "noInherit")) {       data[row][column] = "";     } else {       data[row][column] = data[row-1][column];     }   }    if (!hasOption_(options, "rawHeaders") && row == 0) {     if (column == 0 && data[row].length > 1) {       removeCommonPrefixes_(data, row);       }          data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));   }      if (!hasOption_(options, "noTruncate") && data[row][column]) {     data[row][column] = data[row][column].toString().substr(0, 256);   }   if (hasOption_(options, "debugLocation")) {     data[row][column] = "[" + row + "," + column + "]" + data[row][column];   } } /** * If all the values in the given row share the same prefix, remove that prefix.  */ function removeCommonPrefixes_(data, row) {   var matchIndex = data[row][0].length;   for (var i = 1; i < data[row].length; i++) {     matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);     if (matchIndex == 0) {       return;     }   }      for (var i = 0; i < data[row].length; i++) {     data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);   } } /** * Locates the index where the two strings values stop being equal, stopping automatically at the stopAt index.  */ function findEqualityEndpoint_(string1, string2, stopAt) {   if (!string1 || !string2) {     return -1;    }      var maxEndpoint = Math.min(stopAt, string1.length, string2.length);      for (var i = 0; i < maxEndpoint; i++) {     if (string1.charAt(i) != string2.charAt(i)) {       return i;     }   }      return maxEndpoint; }    /** * Converts the text to title case.  */ function toTitleCase_(text) {   if (text == null) {     return null;   }      return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); }); } /** * Returns true if the given set of options contains the given option.  */ function hasOption_(options, option) {   return options && options.indexOf(option) >= 0; } /** * Parses the given string into an object, trimming any leading or trailing spaces from the keys.  */ function parseToObject_(text) {   var map     = new Object();   var entries = (text != null && text.trim().length > 0) ? text.toString().split(",") : new Array();      for (var i = 0; i < entries.length; i++) {     addToMap_(map, entries[i]);     }      return map; } /** * Parses the given entry and adds it to the given map, trimming any leading or trailing spaces from the key.  */ function addToMap_(map, entry) {   var equalsIndex = entry.indexOf("=");     var key         = (equalsIndex != -1) ? entry.substring(0, equalsIndex) : entry;   var value       = (key.length + 1 < entry.length) ? entry.substring(key.length + 1) : "";      map[key.trim()] = value; } /** * Returns the given value as a boolean.  */ function toBool_(value) {   return value == null ? false : (value.toString().toLowerCase() == "true" ? true : false); } /**  * Converts the value for the given key in the given map to a bool.  */ function convertToBool_(map, key) {   if (map[key] != null) {     map[key] = toBool_(map[key]);   }   } function getDataFromNamedSheet_(sheetName) {   var ss = SpreadsheetApp.getActiveSpreadsheet();   var source = ss.getSheetByName(sheetName);      var jsonRange = source.getRange(1,1,source.getLastRow());   var jsonValues = jsonRange.getValues();      var jsonText = "";   for (var row in jsonValues) {     for (var col in jsonValues[row]) {       jsonText +=jsonValues[row][col];     }   }   Logger.log(jsonText);   return JSON.parse(jsonText); } Copy

Step 2: Create the autoRefresh Script

Next, create a helper script that will allow the data to refresh automatically.

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.

/**  * Time-driven trigger: runs every X minutes and bumps Read Me!A25,  * which you can reference in ImportJSON formulas as a recalc trigger.  */ function triggerAutoRefresh() {   SpreadsheetApp.getActive()     .getSheetByName('Read Me')     .getRange('A25')     .setValue(getRandomInt(1, 1e6)); } function getRandomInt(min, max) {   min = Math.ceil(min);   max = Math.floor(max);   return Math.floor(Math.random() * (max - min + 1)) + min; } Copy

Step 3: Call the Function in Your Sheet

With the scripts saved, you can now use the =IMPORTJSON() function in any cell.

1. Construct your CoinGecko API request URL.

2. Enter the formula into your desired cell, replacing the placeholder URL with your own.

For example, to get the price of Bitcoin on 30-12-2023, your formula would look like this:

This method is modular and powerful, as the =IMPORTJSON() formula can be easily replicated across any cell with different API endpoints. For a more detailed step-by-step guide, read our article on importing crypto prices into Google Sheets.

The Calculation Logic

Once your transaction data is logged, the spreadsheet automatically calculates your capital gains or losses using the core formula:

Proceeds - Adjusted Cost Basis = Capital Gain/Loss

Here’s a step-by-step breakdown of how the template applies this logic using the FIFO method:

1. Trigger Criteria:
   When you log a "SELL" transaction, the formulas begin searching for the corresponding "BUY" transaction to determine the cost basis.

2. Applying the FIFO Rule:
   Following the First-In, First-Out rule, the template locates the earliest "BUY" transaction in your log that still has an unspent quantity. This is the specific "lot" being sold.

3. Determine the Adjusted Cost Basis:
   For that specific lot, the Adjusted Cost Basis is calculated by adding the original purchase price to any associated transaction fees
   Purchase Price + Fees

4. Calculate Gains or Loss:
   The template then subtracts this Adjusted Cost Basis from your Sale Proceeds (the sale price minus any sale fees). The result is your realized capital gain or loss for that trade.

5. Holding Period Categorization:
   Finally, the template calculates the time between the purchase date and the sale date of that specific lot. If the period is more than 365 days, the gain or loss is categorized as Long-Term; if it is 365 days or less, it is categorized as Short-Term.

Common Issues & Fixes

If you encounter errors or missing data while using the tax calculator, here are a few common issues and how to resolve them quickly.

1. API Rate Limit (Error 429)
   This happens when the free CoinGecko Demo API hits its request limit. Wait a few minutes before refreshing again, or switch to a Pro API key for uninterrupted updates.
   If you receive other error codes, refer to the full list of CoinGecko API status codes to troubleshoot them.

2. API Key or Endpoint Issues
   If your API key isn’t connecting or you’re seeing Error 401 or Error 403:

   • Confirm your API key and subscription level (demo or pro) are entered correctly in the Read Me sheet.

   • Ensure there are no extra spaces or formatting errors in the key.

3. Script Refresh Delays
   If the data doesn’t refresh automatically, make sure your Apps Script trigger is active:

   • Go to “Extensions” > “Apps Script” > “Triggers”.

   • Confirm that the “triggerAutoRefresh” function is scheduled to run every hour (or your preferred interval).

   • You can also manually click on “Run”, to force an immediate data refresh.

4. Inaccurate Gain/Loss Calculations
   This happens when the transactions were not entered in chronological order, which breaks the sequential logic of the FIFO formulas. Sort your entire transaction log by date to ensure the FIFO engine works correctly:

   • Click on the Downward arrow on the “Transactions Date & Time” Cell.

   • Navigate to “Sort Column” > “Sort A to Z”.

Further Enhancements

You can further enhance this Crypto Tax Calculator spreadsheet to better suit your needs. Here are a few ideas that leverage other CoinGecko API endpoints:

• Fetch Historical Price Data
  Pull Historical Prices for specific coins for more in-depth performance analysis. You can follow our guide on how to pull crypto historical data in Google Sheets and visualize long-term trends.

• Add OHLC (Open, High, Low, Close) Data
  If you want more granular price insights, consider fetching OHLC data for each coin. Check out our step-by-step guide on how to pull crypto OHLC data into Google Sheets.

• Add a Crypto Portfolio Tracker
  Extend the spreadsheet into a complete Crypto Portfolio Tracker to monitor unrealized P&L (the current profit/loss of assets you still hold) alongside your realized tax gains. For a full guide, see our article on how to create a crypto portfolio tracker in Google Sheets.

• Create a Crypto Exit Strategy Planner
  To complement your tax planning, you can build a Crypto Exit Strategy Planner. Planning your exits strategically may help optimize your returns, which in turn impacts your final tax calculations. Check out our step-by-step guide on how to build a crypto exit strategy planner in Google Sheets.

Conclusion

A clear and auditable tax report is crucial for any crypto investor. This free Crypto Tax Calculator Google Sheets template, powered by the CoinGecko API offers a streamlined solution to determine your capital gains.

Reporting taxes with data from a reputable, industry-trusted source is essential to avoid potential discrepancies that invite scrutiny by tax authorities. Our template helps simplify the complex process of tracing asset cost basis across trades using reliable, aggregated historical data.

If you need more historical data beyond the Demo API's limits, higher rate limits and call credits, or access to exclusive endpoints, consider subscribing to a paid API plan.

Reminder: Consult a Tax Professional

Cryptocurrency tax regulations, particularly those involving DeFi and cross-border activity, are complex and constantly evolving. Users must be aware that crypto tax jurisdictions and methodologies vary by country or region. Confirm and apply the correct rules for your locale, and always consult a qualified tax professional to ensure accurate filing and to identify strategic tax optimization opportunities.

Download Your Free Template ⬇️