Creating Your Own Spreadsheets and Charts Using Coinmarketcap.com API (part 1.)

This is a quick tutorial for the noobs, totally free for the Steemit community and the world. Maybe you have some coins and want to have a clean google spreadsheet where you have the actual name of the currency, amount you have, price and the worth of your asset.

Open a new document in Google Spreadsheets

First you just have to have a Google Account(free). Go to https://docs.google.com/spreadsheets/u/0/
login and click on Blank.

Lets say, you already have a new clean google spreadsheet opened. In google sheets, you can grab xml data that updates upon refresh! Thats how we get the information from coinmarketcap.

But first, we give 4 columns a name. You can call them whatever you want. I named them Name, Amount, Actual Price, Asset Worth.

Now type in some names of your cryptocurrencies. I type in steem and steem dollars. Next type in how much you own, I typed 100 for each.

Now comes the tricky part. Paste this Code in the C2 tab for Steem:

=IMPORTxml("https://coinmarketcap.com/all/views/all#"&today(),"//tr[@id='id-steem']/td[5]")

and paste this code for steem dollars in C3:

=IMPORTxml("https://coinmarketcap.com/all/views/all#"&today(),"//tr[@id='id-steem-dollars']/td[5]")

Notice in the URL we can change the name of the Currency. We had steem and we changed to steem-dollars.

=IMPORTxml("https://coinmarketcap.com/all/views/all#"&today(),"//tr[@id='id-steem']/td[5]")

=IMPORTxml("https://coinmarketcap.com/all/views/all#"&today(),"//tr[@id='id-steem-dollars']/td[5]")

How to add other currencies

Here I have to mention, that you can click on any coin on coinmarketcap.com and copy the ending of the url. For example you want to import ethereum too. You go to coinmarketcap.com and click on ethereum. Copy the ending and paste in the original url like on the picture:

=IMPORTxml("https://coinmarketcap.com/all/views/all#"&today(),"//tr[@id='id-ethereum']/td[5]")

How to multiply values from columns

Now go to the 4. column and paste this for steem "=B2*C2" to multiply the amount with the actual price from the tab.
Value of B2 tab

value of C2 tab

=B2*C2

Next we add =B3*C3 for steem dollars and =B4*C4 for ethereum and we change the first row to Bold and give them some background color.

We change the other rows too. Give them some nice background colors.

Thats it! You have your Google Spreadsheet for your own chosen cryptocurrencies with the actual prices!

Future prediction: post took 2-3 hours to make... earning 2 dollars?

For more tutorials please feel free to follow me.

With love
Tai-Euler