BI Excel Spreadsheet

Bots Institute Excel Spreadsheet with two sheets, by Thijs, contributor.

For most users the BOTS-App will satisfy the requirement of the smartphone user-interface. It shows the current total value, and the invested value and yield per BOT. Including a yield-diagram and the basecoin versus de Euro development.

If you are an advanced user this information may not be sufficiënt. If you are one of those peoples, you want to get more insight in the trends, yield in relation to the marked and the coins of your BOTS. This spreadsheet will offer a good starting point to manually monitor your ‘annualized return’ of your entire BOTS-portfolio.

Most text instructions, from the Excel Spreadsheet, are now available on this browser page. You can use the G-translate site function at the right-top.

Sheet 1 stores data about your bots and can be used to track performance and rebalance across bots. It has a ‘Coin KPI’ (key performance indicator) to assess in what coins you are investing through your bots. This could help in balancing your portfolio.

Sheet 2 helps you calculate your ‘annualized return’ of your entire portfolio. Just enter your deposits and current value of your portfolio.

BOTS(institute version)02XLS
Excel Spreadsheet with two sheets
Instructions Sheet 1

This sheet is starting point to manually monitor your BOT portfolio. All columns concern manual input, except for columns O through AW. These contain formulas.
Column O shows a ‘Coin KPI’. The higher this KPI, the more shared coins the bot has with other bots in this overview. The KPI is calculated as follows: for each coin in the bot, add up how many times you have this coin in your bot portfolio. Then divided by # coins that bot is in.

Example: Suppose you have bot trading in ETH and BTC. And you have ETH in 3 bots and BTC in 5 bots. Column O then shows sum of 3 and 5 (=8) divided by 2 coins in the bot, coin KPI = 4.
For the Coin KPI you need to enter in column F the coins a bot is trading. In case of non-USDT basecoin, enter the basecoin also in column F. In column L, enter whether you have the bot in portfolio (Yes / No).
Next, these coins should also appear on line 11 starting in column P or beyond. If not, substitute an ’empty’ for the abbreviation of the missing coin in line 11.

Instructions Sheet 2

Enter input fields. These are fields with orange shading.

If you have more than 80 moments of insertion, add rows
and copy the formulas in columns D to BF through to those rows.
Annualized return runs from 50 to 100% by default, of course it can be that you score higher. In that case, adjust the 50% in cell H24.
The other percentages are adjusted based on cell H25.

Increase or decrease cell H25 if ‘annualized return’ is #n/a or too low.

Annualized Return (taking into account time of deposit).
Return point-in-time (profit / deposit).
Entry Current portfolio value (in EUR)

Formula compares current value portfolio with values on row 16 and takes first amount below it and shows corresponding percentage of row 19.
Return % annualized and passed on to earnings in Euro’s in maturity.