Table of Contents
This post is primarily for Indian users who are into crypto markets. While most of us use WazirX, it’s a pain to quickly find the P&L of all your coins. WazirX doesn’t seem to be interested in adding this feature either, which is why many users have opted to track their crypto investments in other applications. Some simply take note of their purchases on a book or on a digital note taking application.
When it comes to money tracking, I always prefer regular spreadsheets due to the flexibility it provides. It’s plain, simple and totally free! Google Sheets offer
GOOGLEFINANCE formula to get the price of stocks but it doesn’t work with crypto markets. That’s where the WazirX public API comes in.
- Color highlights to indicate profit/loss
- View total profit/loss in INR or percentage
- View the total number of holding days
How to use the portfolio tracker sheet?
Adding your purchases is self explanatory. Simply add the number of coins, your buy price (in Rs), purchase date, etc and all the other columns will be automatically calculated. When you add a new row, copy the formulas from the row above.
Once you entered all of it, go to the Portfolio Tracker item in the menu and choose Refresh. When you run it the first time, Google will ask you to give it the relevant permissions. That is it!
Every time you open the sheet, the prices will be automatically refreshed but if you want to refresh it manually, again go to Portfolio Tracker –> Refresh
When adding a new coin, make sure that the coin name matches up with the name on the Symbols sheet
Automatically update the price every x minutes/ hours/ ..?
While this feature was there initially, I chose to remove it because most people doesn’t need this. For most of us, this is just a place to come and visit our total profit and loss and it doesn’t have to be updated all the time. It also helps to avoid the unwanted usage of the freely provided WazirX APIs.
But Shan, I need the prices to refresh automatically every 10 minutes. Here’s how you can do that.
- Open the sheet and go to Tools –> Script Editor
- Switch to the Triggers tab from the left navigation.
- Click on Add trigger
- In the modal, select
refreshPriceas the function, update the other fields to your liking and hit Save.
If you want to edit the refresh interval later, go back to the triggers page and edit the existing trigger instead of creating a new one. Or delete the old one and create a new one. Don’t add multiple triggers, let’s use the API responsibly!
You can extend the sheet functionality to automatically send Gmail messages every day or when your P&L hits a trigger. Take a look at my Mail Merge with Gmail article as a starting point.
Yes and No. By default, it doesn’t automatically update the price because I believe that it is not needed for most people and it also ensures that the WazirX APIs are used responsibly. However, if you do need to update the price automatically every day/ hour/ minutes, there is an option.
You can track all coins available in the WazirX exchange. However, all purchase prices have to be logged in INR.
This is not a full-fledged cryptocurrency portfolio tracker but rather a very simple one and was made only to demonstrate the usage of the WazirX API and Google AppScript. If you like to extend the functionality of the sheet, please feel free and share it with us in the comments. And if you truly find this to be useful, buy me a cup of coffee.