Track your cryptocurrency assets near real time using Google Sheets!

Published in

Coinmonks

9 min readMar 20, 2021

Everyone is crazy about cryptocurrencies nowadays and it is getting hotter and hotter every single day.

Yes, personally I also believe that blockchain technology is a revolution and will shape the future of us less than a decade or so.

Most of the people is trying to get rich quickly by buying and selling the right coins that have potential to grow exponentially in a short period of time and people should have a good/rock solid strategy on investing cryptocurrencies in order to achieve that. Because it is very very risky as well, you may be rich in one day but you may also lose most of your money in hours, even minutes! So you have to be very very careful about your investments and you should have a good strategy.

Having said that, in order to maintain a good strategy you must track your investments very closely so that you can take necessary actions as soon as possible to maximize your profits or minimize your loses.

In this story, I will share a very simple solution for tracking your crypto currency assets near real time! You don’t have to subscribe to a paid service for that, you only need Google Sheets! and basic knowledge of Excel! That’s it…

After completing all the steps which I will describe in detail, you will have something looks like below which will fetch the asset prices periodically and will update the document automatically based on the prices.

UPDATE: Please join our Telegram Group for more information and help. I am sharing important updates/information via telegram group constantly!!!

https://t.me/cryptoassettracker

For better quality of the above screenshot please follow this link https://i.imgur.com/rDJb1N3.png

Definition of the columns

Symbol: The symbol of the crypto currency
Name: Name of the currency
Holdings: Your holdings for the crypto
Total Price: Shows how many coins you are holding
URL: URL of the crypto (will be used for fetching current price). Should be either investing.com or coinmarketcap.com
Current Price: Current price of the coin
Share (%): Initial investment of the coin as percentage
Total Price: Initial investment of the coin (how much you invested in this coin)
Total Amount: How many of coins you would be able to buy with your initial investment (not total investment, initial money invested on the coin)
Buy Price: At what price you did got the coin
Change: Profit/loss percentage of the coin
Platform: On which trading platform you have this coin (binance, gate.io, kucoin etc)

Ok let’s start…

STEP 1. Follow the link below to make a copy of the document I have shared.

https://docs.google.com/spreadsheets/d/1t3tX22qEIK0HmgXgwiI0kxCLkBWFSSfusGGvxULWO1I

STEP 2. Make a copy of it by opening “File” menu and store the document on your own Google Drive.

STEP 3. Rename the document as you wish and open it by simply double clicking it.

After making a copy you will have access to edit it.

STEP 4. Get the document ID from the URL as shown below. This is very important, you should get the exact ID shown in the URL (we will use it later)

Getting the Google Sheet document ID from the URL.

STEP 5. Open script editor using the “Tools” menu.

STEP 6. You will see a script in Code.gs file which will refresh the crypto asset prices periodically. We will need to replace some information in this document.

First replace the document ID which you get in STEP 4 as shown below.

Replace this one with the document ID you got in STEP 4. — Replace this one with the document ID you got in **STEP 4.**

Scroll down a little bit and you will find a part that you should replace your email address to get buy/sell alerts regarding your buy/sell margins in the document! Replace this part with your email address.

Place your email address here to get buy/sell alerts!

STEP 6. After all the changes on STEP 5, save the script and then run it.

Save the script after applying changes on STEP 5. — Save the script after applying changes on **STEP 5**.

After hitting the “”Run” you will get an “Authorization required” warning. Don’t worry, this is just for giving access to make changes on the document and for sending emails! Simply review permissions and accept them.

You should give access the script to make changes on the documents and to send emails!

You may also get a warning from Google, don’t worry just click on “show advanced” and click “Go to Crypto Tracker (unsafe)” link (please see the important note below about this screen)

IMPORTANT!

You may have trouble seeing the note below if you enabled “ad blocker” on the page, if so please disable it! (https://medium.engineering/the-unluckiest-paragraphs-751dd36d2d30)

I see some of you have concerns about this screen and I just wanted to make it clear for everybody. As soon as you create a copy of the original document in STEP 2 you became the OWNER of the document and the SCRIPT. Even if the script is owned by you, Google will show up this notification since the script is not verified by Google (doesn’t matter if you own it or not, please see https://stackoverflow.com/questions/48482260/getting-this-app-isnt-verified-for-google-sheets-script-that-only-touches-my and watch this https://www.youtube.com/watch?v=Sxu-4VULQ10). So there is nothing to concern about this since you are the owner of the document/script and you are actually giving YOUR SCRIPT to do changes/send emails to YOUR DOCUMENT! (After making of the copy of the original document you can change the script as you wish and see the script content).

You should see something similar, simply click “Allow” button.

Allow the script to make changes on your document and send emails.

If you have completed all the steps above (from 1 to 6) then just simply click the “run” icon on Script Editor to test it. You should be seeing that script is being executed and your document is being updated automatically.

After running the script, sheet should be automatically updated

And congratulations you are done with the initial setup of your crypto assets tracker. Let’s jump into some details…

In order fetch the asset prices periodically we need to do a final step!

STEP 7. Add a trigger for your document which will automatically updates the prices thus making the document to refresh itself and do the magic! (re-calculate everything)

Click “Add Trigger” button (located bottom right). This will pop-up a dialog for creating a new trigger.

Simply select “RefreshImports” as the function (you can rename it as you wish since you are the owner of the script and document) and select “Time-driven” as event source, select “Minutes timer” as type of the trigger and finally select interval as “Every 5 minutes” and click save. Please keep in mind that, after clicking “”Save” it may ask for some permissions like in STEP 6, don’t worry and do the same and give the necessary permissions.

Of course, you can change the “interval” as you wish but from my experiences 5 minutes is just perfect.

And you are done! After completing all the steps above you should have a sheet that is updating itself (updating prices, making re-calculations based on the prices etc.) every 5 minutes. But how about adding your own assets to the document? Let’s dive into details…

How to remove an existing crypto asset?

It is simple, just delete the row :) You don’t have to worry about anything, references will be updated automatically and script will handle the rest…

How to add new crypto asset?

Simply select the last coin in the list (not BTC) and right click and choose “Insert 1 above” option.

Let’s choose NANO as an example. The most important part here is the URL column. You should either use investing.com or coinmarketcap.com for the coin URL. In this example, NANO is listed on both sites; we will use investing.com

https://www.investing.com/crypto/nano

If we were going to use coinmarketcap.com then the URL would be

https://coinmarketcap.com/currencies/nano

We are using an IMPORTREGEX function (which we already added in previous steps as script) in order to fetch the prices. Be careful, pattern of the function argument differs for investing.com and coinmarketcap.com (maybe you can add other sites as well, who knows :D)

Function for investing.com (https://regex101.com/r/qSfrEZ/1)

=IMPORTREGEX(CELL_REFERENCE, “<span(?=[^>]*id=””last_last””)[^>]*>(.+?)<\/span>”)

Function for coinmarketcap.com (https://regex101.com/r/BWCTYD/5)

=IMPORTREGEX(CELL_REFERENCE,”<div(?=[^>]*class=”priceValue.*”)[^>]*>[^>]*<span(?=[^>]*)[^>]*>(.+?)<\/span>”)

You can copy the function from an existing cell and paste it to the new cell. Do not forget to update the cell reference as well!

And you can also use coingecko.com for fetching prices as well, here is the function for it (https://regex101.com/r/C6IEv0/1)

=IMPORTREGEX(CELL_REFERENCE, “<span(?=[^>]*class=””.*tw-text-3xl.*””)[^>]*>[^>]*<span(?=[^>]*data-target=””price\.price””)[^>]*>(.+?)<\/span>”)

And do the same thing for the other cells of the new row (be careful you have to fill in the some cells manually and some cells just contain formulas that needs to be filled for the new record with the correct cell references)

Binance Smart Chain tokens are now supported as well!

To add a BSC token to track, what you need to do is just write the token contract address instead of the URL and then call the below function within the price column and that’s all!

=IMPORTJSON(CELL_REFERENCE)

After adding or removing a record do not forget to update the totals of your initial investment amounts as shown in the picture above!

BONUS!

You will see a “Dashboard” sheet when you get the document which contains daily basis graphical data of your portfolio as well. Enjoy it!

Graphical data of the portfolio which is created and updated automatically as well.

And that’s all. I hope you will find this document useful and eventually you can make some money :D

Please also consider making a donation as well if you find this document useful/helpful for you.

BTC (BTC): 125BUtzgtBkti95s1biVk6tNPRkUv9ef6y

ETH (ERC20): 0x724a4e570a1740b2a4e0d8d84402284b0771f13f

USDT (TRC20): TM23N6eDE2tR7P9rPm5p5jQBFqqG3BoZ4i

If you are new to the crypto currencies and thinking about buying/selling them, please use the following links below to register the markets via my referral. Thanks!

BINANCE
https://www.binance.com/en/register?ref=12811135

GATE.IO
https://www.gate.io/signup/3325129

HUOBI GLOBAL
https://www.huobi.com/en-us/topic/invited/?invite_code=qysn9

KUCOIN
https://www.kucoin.com/ucenter/signup?rcode=rJ85HV3

COINLIST
https://coinlist.co/clt?referral_code=6DKN4T

MXC
https://www.mxcio.co/auth/signup?inviteCode=16nUt