Crypto-assets portfolio optimization and trade order creation with Google Sheets

2 minute read

Crypto-assets (Bitcoin, Ethereum…) have recently been attracting the attention of more and more investors, with for example JPMorgan Chase & Co. suggesting that it might make sense to integrate Bitcoin into a multi-assets portfolio.

Going with the flow, I will illustrate in this post a nearly1 complete optimization process for a portfolio of crypto-assets:

  1. Acquisition of crypto-assets data (market capitalizations), through the CoinGecko Web API

  2. Computation of an optimized portfolio of crypto-assets based on the acquired crypto-assets data (market-capitalization weighted portfolio2), through the Portfolio Optimizer Web API

  3. Creation of trade orders for the crypto-assets included in the computed portfolio of crypto-assets, through the Binance.com cryptocurrency exchange Web API

For ease of reproduction, I will be using Google Sheets and Google Sheets JavaScript-like scripting language Google Apps Script.

Note: A fully functional spreadsheet corresponding to this post is available here. I strongly encourage you to make a copy (File -> Make a copy) so that you can have a look at the code.

Acquisition of crypto-assets market capitalization data

Multiple APIs are providing crypto-assets market capitalization data:

I decided to use the CoinGecko API for this step, because it is similar in spirit to the Portfolio Optimizer API:

  1. Free
  2. No API key required

The API endpoint to use is: /coins/markets.

You can find my integration of the CoinGecko API in the CoinGecko API.js script file of the example spreadsheet.

Note: Guys at CoinGecko wrote a post about how to import their data into Google Sheets, but I find that their implementation is making too many useless API calls (one per coin and per piece of data to import!).

Computation of a market-capitalization weighted portfolio of crypto-assets

Integrating the Portfolio Optimizer API in Google Sheets is already described in another post, so, I will not insist on this step.

The API endpoint to use is: /v1/portfolio/optimization/market-capitalization-weighted.

You can find my integration of the Portfolio Optimizer API in the Portfolio Optimizer API.js script file of the example spreadsheet.

Creation of trade orders for the crypto-assets

For this step, I chose to adapt3 Diego Manuel’s Google Spreadsheets Add-On, but there are other similar resources, like Cointrexer.

The API endpoint to use are:

  • /api/v3/account, to retrieve spot account information (e.g., quantity of USD, USDT, EUR, etc. available for trading)
  • /api/v3/order, to send a trade order

You can find my integration of the Binance.com API in the Binance API.gs script file of the example spreadsheet.

Note: Because many people are using Google Sheets to interact with Binance.com, and because Binance.com is banning IP addresses responsible of too frequent API calls, API calls using Google Sheets will usually fail at random4. I implemented a workaround in the example spreadsheet, but if you are serious about using Google Sheets, I would recommend to put in place a proxy server with a dedicated IP address and have Google Sheets target it instead of Binance.com.

Going beyond

There are several additional functionalities that you might want to implement beyond what I illustrated in this post.

For example, the possibility to receive trade reports by email, the possibility to rebalance your portfolio, etc.

Happy coding!

–

  1. One important missing step is the computation of an investable portfolio from the computed optimized portfolio. Indeed, Binance.com defines a certain number of trading rules on its listed crypto-assets, and not taking them into account before creating a trade order will result in the trade order being rejected (typically, MIN_NOTIONAL, LOT_SIZE and MARKET_LOT_SIZE filters). ↩

  2. This portfolio allocation scheme is very simple, but enables for example the replication of the CRIX CRyptocurrency IndeX. ↩

  3. At this date, Diego Manuel’s Google Spreadsheets Add-On only supports GET endpoints, while POST endpoints are required to create trade orders. Also, the request body is currently incorrectly sent as application/json while it should be application/x-www-form-urlencoded, and fixing this issue creates other problems elsewhere in the computation of the HMAC SHA256 signature… ↩

  4. A call to any API endpoint will randomly result in a 418 HTTP return code with the accompanying message Way too much request weight used; IP banned until <timestamp>. ↩