Your Simple Guide To Creating An Effective Rebalancing Tool

Rebalancing is a great method to ensure you sell high and buy low, it takes the emotion out of trying to call the bottom or the top. It really is a staple of investing, although most asset management firms rebalance for you whether its an index fund or your 401(k), for this tool you will have to do the trading yourself. Normally you see a stock portfolio rebalanced quarterly but in crypto a more aggressive rebalancing strategy may be necessary.

In light of this I'm going to share a new Google Sheet I put together that assists you in rebalancing your portfolio.

Features

Before we start here are some cool features:

• Prices pulled from Coinmarketcap
• Automatically tells you what to buy and sell in your portfolio
• Can account for adding recurring deposits to your crypto portfolio

Ok lets begin...

1 - Make a copy of this Google Sheet: Rebalance Tool
2 - Once the Google Sheet is copied open Tools> Script Editor...
3 - Remove default text when script editor loads.
4 - Copy and Paste this into the new script:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Refresh",
    functionName : "refreshLastUpdate"
  }];
  sheet.addMenu("Refresh", entries);
};

function refreshLastUpdate() {
  SpreadsheetApp.getActiveSpreadsheet().getRange('I5').setValue(new Date().toTimeString());
}

function ccprice(name, currency, datetime) 
{
var url = "https://api.coinmarketcap.com/v1/ticker/" + name + "?convert=" + currency
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);

var priceval = {  "EUR" : data[0].price_eur, "USD" : data[0].price_usd, "BTC" : data[0].price_btc,
                  "AUD" : data[0].price_aud, "BRL" : data[0].price_brl, "CAD" : data[0].price_cad, 
                  "CHF" : data[0].price_chf, "CNY" : data[0].price_cny, "RUB" : data[0].price_rub,
                  "GBP" : data[0].price_gbp, "HKD" : data[0].price_hkd, "IDR" : data[0].price_idr, 
                  "INR" : data[0].price_inr, "JPY" : data[0].price_jpy, "KRW" : data[0].price_krw, 
                  "MXN" : data[0].price_mxn, "eur" : data[0].price_eur, "usd" : data[0].price_usd, 
                  "btc" : data[0].price_btc, "aud" : data[0].price_aud, "brl" : data[0].price_brl,  
                  "chf" : data[0].price_chf, "cny" : data[0].price_cny, "rub" : data[0].price_rub,
                  "gbp" : data[0].price_gbp, "hkd" : data[0].price_hkd, "idr" : data[0].price_idr, 
                  "inr" : data[0].price_inr, "jpy" : data[0].price_jpy, "krw" : data[0].price_krw, 
                  "mxn" : data[0].price_mxn, "cad" : data[0].price_cad }

var price = priceval[currency]
                                    
return price
SpreadsheetApp.flush();
}

4 - Save Script (any name is fine)
5 - Start editing for your data. Collumns B, C, D are the only manually entered data.

• B - Name of your coin
• C - Ideal value that coin should be in as a percentage of your portfolio
• D - Quantity of coin currently held (You can put 0 if you intent to buy some)

6 - To add or change a coin or the FIAT currency just select the respective $G cell and change the name of the coin. See this screen shot.

7 - To refresh the sheets prices anytime use the menu option refresh. Note: This actively calls the function which will require permissions - google will ask if you want to run it, follow the prompts to approve it. It will only do this the first time you run the script in a new sheet.

The end!

Hope you like the tool, if you have any questions or run into issues I'll try to support you in the comment section. I wish you the best in your journey to wealth!

Tips always appreciated:

BTC: 1LdHmPUAEggc5Rk6UmczSkfT8tYL68txb6

Sources & References

Kick ass Coinmarketcap api script sampled from: rathergood Github

Refresh function to update google sheet sampled from: Stack Overflow