If you’ve been in #crypto for a while, or even if you just started. There is a moment when your funds are spread in different exchanges, wallets, etc. This can get messy, and makes it hard to track your whole portfolio value. I’ve built a simple file to simplify this.
First, I want to say that there are apps that allow similar functionalities. This is for the people like me, that like to control their data and calculate additional things.
The important part in this, is how to get live coin prices from APIs and use them for whatever you want.
I have been using this file for a while now with a simple purpose:
1. Keep track of all your assets
2. Automatically update the price of the assets
3. Keep track of the progress of your portfolio
I have used this file and improved it since the last #bitcoin bull cycle.
I use a Google Sheets, that I recommend you go and get a copy of. Here is the link: https://docs.google.com/spreadsheets/d/1uZY_1PyxECG4lRFw59HxgBytseqCi_8_DDmNuy2_1qU/edit?usp=sharing.
- Open the link
- Click on File > Make a Copy — Copy it locally into your drive
This file has 2 sheets, let me cover them:
Sheet 1: Positions
This sheet is is the one that you will be using to monitor your assets. It has 3 tables.
The first table is the table with your actual values. There, you need to replace the values with your own. In the example there is #BTC, #ETH, #UNI and #USD
Replace columns B and C with the currencies and coins that you actually own and want to track. Columns E and F are simple formulas to get the USD value of the asset and its share of your portfolio.
The 2nd table is there for you to keep track of your history. It has some automation: the script editor of the file has a function called “save_values”. It automatically runs every 15 min. It checks the current value of the portfolio and will log the day’s high in a new row
The 3rd table is the “Moon table”. Input your prediction price for the asset and dream about how much money you #crypto may be worth in the future. Don’t dream too much though. This is to track your progress towards your target.
Then, we have the second sheet: Coin Prices
Very simple but crucial for this file to make sense. It simply displays the price in various currencies for the #crypto you want to follow. How does it work?
If you go to Tools > Script Editor, the code for the file will open. There are 2 functions:
save_value: mentioned above
coin_price: uses the @coingecko api to get #crypto prices. It is free and the most reliable api I used
The code uses the coins/markets api on the @coingecko site https://www.coingecko.com/en/api
Their api is so easy to use that you just need to input the right fields, click on Execute and you get a url. Just replace the url in the coin_price function with the one from @coingecko
Make sure you test it by using the Play button at the top of the screen.
If you don’t need #EUR or #PLN prices, delete them from the code, or replace them with whatever currency you would prefer to have.
Once this is done, you can then add triggers so the code can run automatically.
To do so, go to the navbar on the right and select triggers. Click on Add Trigger. Select the 1st function, change event source to time driven and then select the run frequency. Don’t do it too often, APIs have limits. Every 15 min maximum.
Repeat for the 2nd function.
And that’s it, you’ve automated the tracking of your portfolio. I’ve been in this since 2017 and this is the most convenient method I have found. I use this file for more than just this and have added multiple sheets to track my income allocation, credit, etc.