Now, this is something I learned recently so I thought of making it into a post for those that don't know how to do it and interested in making an excel for their crypto portfolio. I've been wanting to make a portfolio for my crypto on an excel sheet and I finally got to it with some help because my coding kind of sucks. I luckily have a friend on discord that taught me how to set this up. Now I'm going to make it easy for you as I give you a step by step process on how to do this.
Step 1 - Have a Google Sheet Ready
You can set up the Hive and HBD there and any other crypto you want to add.
Step 2 - Create Demo Account & Get An API Key
You have to create a demo account and get an API Key. You can go to "https://www.coingecko.com/en/api/pricing" and this will take you to the page for the photo above. Then you can get your API key from "https://www.coingecko.com/en/developers/dashboard" after you created an account you can add a new key I believe or there will be one there already.
Step 3 - Go Back To Excel and Go to Apps Script
Step 4 - Add Script
Now all you have to do is copy and paste the following code into the script (starting from function to the } and edit accordingly to your api key, name of the sheet and the cell you want for the price to show up. Make sure to remove the brackets after editing. Below I have given the script code for Hive and HBD.
If you want to do different crypto, you have to edit the apiURL and the part after the setCryptocurrencyPriceInSheet(hive) and getCoinGeckoTicker(hive) and return data["hive"]. You will need to change the hive to whatever crypto name it is. If the crypto has more than one word in the name make sure to add the hyphen or underscore. You can see HBD has hive_dollar. The list of the apiurl for the tokens and for the names to change can be taken from here https://api.coingecko.com/api/v3/coins/list
Hive
function getCoinGeckoTickerhive() {
var apiKey = "[YOUR API KEY HERE]";
var apiUrl = "https://api.coingecko.com/api/v3/simple/price?ids=hive&vs_currencies=usd";
var headers = {
"x-cg-demo-api-key": apiKey
};
var response = UrlFetchApp.fetch(apiUrl, {headers: headers});
var data = JSON.parse(response.getContentText());
return data["hive"].usd;
}
function setCryptocurrencyPriceInSheethive() {
// Replace '0.05' with the result from your existing function.
var cryptocurrencyPrice = getCoinGeckoTickerhive();
// Access the "Sales summary" sheet and set the price in cell G1.
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("[Sheet Name]");
sheet.getRange("D6").setValue(cryptocurrencyPrice);
}
HBD
function getCoinGeckoTickerhivedollar() {
var apiKey = "[YOUR API KEY HERE]";
var apiUrl = "https://api.coingecko.com/api/v3/simple/price?ids=hive_dollar&vs_currencies=usd";
var headers = {
"x-cg-demo-api-key": apiKey
};
var response = UrlFetchApp.fetch(apiUrl, {headers: headers});
var data = JSON.parse(response.getContentText());
return data["hive_dollar"].usd;
}
function setCryptocurrencyPriceInSheethivedollar() {
// Replace '0.05' with the result from your existing function.
var cryptocurrencyPrice = getCoinGeckoTickerhivedollar();
// Access the "Sales summary" sheet and set the price in cell G1.
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("[Sheet Name]");
sheet.getRange("D7").setValue(cryptocurrencyPrice);
}
Step 5 - Run The Script
Run the script and make sure you have setCryptocurrency and not get coingeckoticker if not you might get an error.
If everything is done right, it should show up below in the cell designated.
Bonus Step - Trigger Update Price
Now this is not necessary but is nice to have. If you don't want to keep running the script, to get an updated price there's a way to trigger it.
When you add a trigger, make sure it's time-driven and the correction function. After that you can choose how often you want it to run. Since you are using a demo account, it's not ideal to have it update by minute you would hit the limit for the free plan. I suggest every 12 hours. Personally I set it to by day but that's up to you.
I hope this helps. If you have any issues, find me on discord or drop some comments. I'll do my best to help!