How to add real-time crypto values in Excel

Keeping track of your earnings can be quite a chore. You can use sites like coinmarketcap, worldcoinindex or the Blockfolio app to see the up-to-date prices of your favorite coins.

But lately I have been expanding my collection of crypto coins. I was able to participate in a couple of ICOs. Some I intent to sell the first chance I get and some are for the long term HODL.
To keep things organized I decided to make an excel sheet so that I could keep a close eye on things.
However I also wanted to have real-time prices of my coins.

It took some time to figure out but I found a way to insert real-time crypto pricing (even in Euros) in my spreadsheets. The solution is the coinmarketcap ticker, this enables you to download all the real-time prices of your favorite cryptocurrency.

https://api.coinmarketcap.com/v1/ticker/name_coin/?convert=EUR

Step 1:

Open a new Excel spreadsheet.
Go to the “DATA” tab en choose the “FROM WEB” option.

A new window will pop up. Here you can insert the url. Replace the name_coin with the name of the coin you want. So in this example the url will be:
https://api.coinmarketcap.com/v1/ticker/kucoin/?convert=EUR

Step 2:

Rename the query in the Query settings then click on source so that the option “to table” becomes available

Press the “to table” option and a new sceen will pop up. Just go with the default options and press “ok”.

Rename the table with the coin you are adding (to keep things organized)
Click on the icon with the two arrows pointing away from each other. A new window will pop up.
In the new window you can select the info you want to add to your spreadsheet. In this example we want the price of kucoin in euros so I selected the euro price.

After this we are all done with adding the ticker info. Click “Close and load”.
Your data will be added to a new sheet and will look something like this:

Step 3:

Go back to the “DATA” tab, select the cell with your crypto price and click on properties. A new window will open up. Click on the menu icon behind the name of the query.

A new window will pop up. Here you can select how often and when the price will be refreshed.

After this we are all done. You can finish your crypto spreadsheet and have real-time pricing.
If you want to add the pricing in another sheet, just enter “=” in a blank cell, go to the sheet with the real-time price, select the price and press enter..and voila! the price will show up in your new sheet.

If you want to refresh the price(s) just go to data and select “refresh all”.

Another tip is (for those who are not really into Excel) is to add the amount of crypto in the first cell then add the price in the second cell and the value in the third cell.

Something like this:

Here I manually added the amount of Kucoin in cell A2. In cell B2 I entered “=” and went to my real-time kucoin price cell, selected it and pressed enter. In cell C2 I entered the simple formula “=A2*B2”. Now I can see the real-time value of my Kucoins.

I hope this will help you with your crypto trading.

These are posts by @tspink in the #xervanteschallenge. Go give my man some support in his efforts!