Crypto-assets portfolio optimization and trade order creation with Google Sheets
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:
Acquisition of crypto-assets data (market capitalizations), through the CoinGecko Web API
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
Creation of trade orders for the crypto-assets included in the computed portfolio of crypto-assets, through the Binance.com cryptocurrency exchange Web API
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:
- No API key required
The API endpoint to use is:
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:
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.
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.
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). ↩
This portfolio allocation scheme is very simple, but enables for example the replication of the CRIX CRyptocurrency IndeX. ↩
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 bodyis currently incorrectly sent as
application/jsonwhile it should be
application/x-www-form-urlencoded, and fixing this issue creates other problems elsewhere in the computation of the
HMAC SHA256 signature… ↩
A call to any API endpoint will randomly result in a
418HTTP return code with the accompanying message
Way too much request weight used; IP banned until <timestamp>. ↩