Import COINMARKETCAP data into Excel with automatically update in few steps

in cryptocurrency •  2 years ago  (edited)

Hello Stemians! :)

Today i was looking for a way to import data from coinmarketcap to excel because i need to update information about curencies, i use this for track how valuable are my savings. 

And I found resolving! 

Now you can import this data to your sheets;).

I used Excel 2013, so i think in Excel 2016 this work too. Let me know. 

---------------------------------------------------------------------------------------

1. You should download „Microsoft Power Query for Excel”. When you finished, install it. 

You can download from: https://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=bc3557e1-418d-4325-b101-ad17694d3901

2. Run your Excel – now you should have a new tab „Power query” - click it


3. Next click „FROM WEB”. Now should apper window. Copy ticker API (https://api.coinmarketcap.com/v1/ticker/) and paste in new window in Excel. Click "OK"

This api provide all data about every cryptocurrency on coinmarketcap. If you want you can use other api (https://coinmarketcap.com/api/) to select currency or select another data. On this API is all data you can get from this site. In my opinion the better way is select data inside Excel.


7. Now you see table of many record, but not data. Go click buton „To Table”. In next windows don't change anything. Click "OK"

In this window in properties section you can change table name to whatever you like.


8. Ok, now We want see any data. Click a small icon (with two opposite arrows) next Column1. There you can select columns what you interested. I recomended select all. Click "OK"


10. On This steps you can change columns names. But if you don't want do on now, you can change it later, normally in sheet. When you finish, click "Close & Load"

 

12. From this time, when you click „Refresh all” buton, the information on this sheet wil be upadted.  

------------------------------------------------------------------------------------------------------------------------------------

If you like post with tricks and hack let me know. In future i try post more information about solutions that make everyday work easier.

If you think my post is helpful, go upvote :) 

Thank you!


Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Świetna sprawa, mógłbys gdzieś wrzucić na serwer aby można ściągnąć?

I like it very much!!! Amazing post, this is something I'm looking for because I just know the crypto currency 💴 Thank you brother! I started my Steemit adventure! - Follow me @jakir as suggestions about my future posts will be amazing 👏 thank you very much

This is awesome, thanks!

Do you know how to import more than just the largest 100 coins?

Edit: I figured I would need to add the coin I'd like to import at the end of the API address (e.g, https://api.coinmarketcap.com/v1/ticker/dcorp/ or https://api.coinmarketcap.com/v1/ticker/melon/)

I've just made a similar thing in Google Sheets (Docs/Drive):
https://docs.google.com/spreadsheets/d/121y1_U2PiiSDjMGi9BM7PISsLfKkQpqPhOgthWgXcHM/edit?usp=sharing

Enjoy! :)

looked at your Google Doc.....i just wanna ask if we can see the coin prices on differnt exchanges using the API.

yeah this application could be useful for any trader

not loading more than 100 rows for me

I have Excel 2016 and by trying to install query i got the massage it is already included. Thank you.

Anyone looking for a truly robust solution for importing cryptocurrency, stocks, bonds, and essentially all other all source financial data into Excel should check out XLQ -- more at:

https://steemit.com/cryptocurrencies/@mckibbinusa/new-version-xlq-6-0-now-supports-cryptocurrencies

Thanks; this was very helpful.