HomeЛюди и блогиRelated VideosMore From: A Block of Crypto

Connect Excel to Coinmarketcap for live and historic data

481 ratings | 34570 views
WILDSPARK.ME link : https://l.wildspark.me:8000/H12OrNzhW In this video you will learn how to connect Excel to Coinmarketcap data. First we will look at using the API to connect to live ticker data. Then we look connect to a historic table on the coinmarketcap website to gather historic cryptocoin pricing and volume data The API used is https://api.coinmarketcap.com/v1/ticker/ and for the historic prices we will look at the coins STEEM and BITCOIN This can be used to create a crypto currency excel tracker that will auto update. Follow me to see how you can use this data to create your own cryptocurrency holding dashboard in Excel





Html code for embedding videos on your blog
Text Comments (95)
Ahsen Shah (2 days ago)
Thanks Dear. This was just what I was looking for. God Bless.
Richard Harrington (1 month ago)
Awesome video. Thank you very much. Have you made the follow up tutorial on tracking your holdings using these external data sources?
Sifiso Malinga (1 month ago)
Great video. Please help. How do I make the dates variable in later Excel version - e.g. make last day Today(). Very much appreciated!
Mark (1 month ago)
Looks like CMC is forcing API users to update to V2. My current spreadsheet uses this method with V1 and it works. No luck with V2 though, have you tried and any thoughts? Thanks!
Gajendra Mahadik (3 months ago)
Hi, it nice video. i like & useful too. you can transfer this excel sheet to AMIBROKER charting software. 100% perfect work. Thanks you sir
Andres Lozano (3 months ago)
Thanks, do you know how I can download the historical data of all coins automatically using Excel?
eric grissom (3 months ago)
thanks for your video
Shahroz Hameed (4 months ago)
it's highly valuable thing for me. Thanks for updating this info.
Ludovico Principe (4 months ago)
Thanks for your interesting howto. I have recently started using https://bitpanels.com/balance which allows me to monitor thousands of coins in real time in many exchanges and from any device. I also use it to export data and even charts in the most famous formats. I heard that it will soon be possible to insert the exchange APIs to synchronize your asset and your trades. Amazing
Sam Sam (4 months ago)
what is the benefit of doing this?
fantastic! thank you a lot!
gene golub (4 months ago)
thank you, very educational.
Joe Morgan (4 months ago)
Thank you. Well described tutorial. Coin market cap version 2 of the API is up and running and V1 will be closing Nov 2018, so will there be an updated video. Many thanks for the effort.
Beren Gamble (4 months ago)
If you want to get all the historical data for a coin without having to mess around with changing the end date in the URL, use this in your Power Query Source = Web.Page(Web.Contents("https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end="& DateTime.ToText((DateTime.LocalNow()), "yyyyMMdd"))),
Sharon Artis (4 months ago)
Excellent tutorial! Step-by-step instructions that are very clear and easy to follow.
gfgfdj;l;l gdfgdf (5 months ago)
power query here https://ufile.io/7035b
Amr Daoud (5 months ago)
Wtf is this shit why does it close
Amr Daoud (5 months ago)
🐛🐛🐛🐛🐞🐞🐫👹😈🙀😈😼😈👱😈👹😼👹🐨👹 im using fuckin cell phonenind🐨 i dont know excelll
Amr Daoud (5 months ago)
I subscribed
CryptoJerk (5 months ago)
Incredibly helpful! Many thanks for this video.
Inversiones ROCA (5 months ago)
Thank you for the videos, very helpful to calculate volatilities, rents and correlations.
Freddy Rodriguez (6 months ago)
awesome
andre 2112 (6 months ago)
I have a question: how you prevent the reordering of the rows everytime the query updates? I would like to extract the data from the table but every reference I do to a cell it changes after the update.
Alec Wilson (6 months ago)
Awesome video. Any reason why the link only gave me top 100 coins instead all the rows?
Mr Green (6 months ago)
I press the refresh button but the data is not updated. How can i fix it? Thank u so much!
The-owner (6 months ago)
Thank you so much !!!
Erkan Ince (6 months ago)
Wonderful
Me Me (7 months ago)
This video was so professional, informative and helpful. Just way ahead of so many videos I've seen on youtube, as good as TED Talks. Thank you Wildspark :-)
Marqus Zar (7 months ago)
Nice video thanks 👍
Moisés Uclés Lirola (7 months ago)
awesome
david hodgins (7 months ago)
Thanks for the video, got it working first try. I'm wondering if it is possible to download hourly, historical data for any given coin - to a new spreadsheet each day, at a given time, and save automatically. Cheers
John Brown (3 months ago)
Shunwai Hong u can do that in JavaScript or python
Shunwai Hong (3 months ago)
did you find a solution?
Jan-Kees (7 months ago)
Thank you for this video. Can someone tell me how the code looks when i want to import the value of a coin in another sheet. This ticker wil update automaticlyb so the coins can change from the row the are in because of marketcap value. I know it’s something with =index
Mistar92 (7 months ago)
Thanks for this awesome video. Can you tell me why I can't retreive data from the "markets" tab when i have chosen a coin on coinmarketcap? E.g. I want to compare the different prices of Bitcoin between the markets. Is it possible to do it?
Sietse (7 months ago)
I have a question. I got the same table that you have at 2:11, but then in such a way that the only two columns are the name and the price. My price automatically updates, and basically overwrites the existing value in the cell. Now what I want is that every minute (that is my update interval) it automatically creates a new column with the new price, while the old column(s) remain their value. So each column will basically then represent a certain point in time (with the exception of the first column which is only a name column). Do you know if it's possible to do this in excel?
Me Me (7 months ago)
would need a macro to make Column = Today() - Date(... )
Mr Green (7 months ago)
I wanna thank u for your guidance. But when i put on "Workbook Queries" and try to Refresh one of them, it show me that "You can not refresh a connection-only query". And now my file can not be updated anymore! How sad! Did i do something wrong? And How can i fix it? Thanks u very much! --- One more quesion: Can I create a formula in the sheet containing the coin that was derived from Query?
Doo-san Bahk (7 months ago)
So helpful. Thanks :D
Danny L (7 months ago)
hi great video, is there a way to import each individual exchange price from coinmarketcap? for example the recent bitcoin price on binance, bittrex, poloniex. thanks!
A1 Media Producciones (7 months ago)
Thanks for this video, there is a way to do the same but with binance, that is, to be able to see the price of bitcoin but in binance?
Michael Burnett (7 months ago)
I am using Excel 2013 and tried the "Limit=0" link however, still only getting top 100. Help please :(
DrJ (7 months ago)
Where is the URL for next video that show how to develop a crypto portfolio tracker? Thanks
DM (8 months ago)
With Bitcoin Historical data it gives me a DataFormat Error with the Volume Colum, it can't convert it to numbers ??
Garth Woodworth (8 months ago)
Super well done video tutorial. You don't miss a step and keep it easy to follow, thanks so much. I wonder if you could help with MS Access? I'm imagining a system where I can use it so easily add purchases and keep a historic record of them. I think it could be done using crypto pairs, to say buy ETH with BTC, or Steem with ETH, and so on. Thanks again for your great work!
viajar la vida (8 months ago)
thank you so much. Very easy to follow, and learn! ;-) Do you happen to know if is there any way to colour the cells: I mean, if the number of % change is negative, to colour its cell red. (I was not able to do it with conditional formula). ;-)
GianluGian (8 months ago)
In the video you say that it’s not possible at the moment to find an API for all the cryptos. Therefore we have to download the historic data for every single coin manually. In the meantime, have you found a way to automatically do it? I have to download 500 of them =(
Manoel Neto (8 months ago)
Is it possible an automatic uptade on the live data?
Me Me (7 months ago)
Data > Connections > Properties > Refresh every XX minutes Excel 2010, should be available in 2007 etc
Julia Slisa (8 months ago)
is there a way to add more than 100 rows?
Bartosz Janicki (8 months ago)
https://api.coinmarketcap.com/v1/ticker/?limit=0
Akkas Miah (8 months ago)
Is this method compatible with MS Excel 2010?
Gerard Kooij (8 months ago)
Thanks for the clear explanation , however I am facing the fact that 100 coins are load up, can you please help further.
pd craft (8 months ago)
awesome, im going to watch the next one...
Matthew Chambers (8 months ago)
thank you that was very helpful
GABRIELE LUCINI (8 months ago)
tnx.. only first 100 rows? if i need more rows?
GABRIELE LUCINI (8 months ago)
thanks a lot.. Jackspot
Use https://api.coinmarketcap.com/v1/ticker/?limit=0 instead of the default ;)
Dr Fil (8 months ago)
Great tutorial thank you so much.
Jim O'Donnell (8 months ago)
This is a great demo. Thank you!
Moosy Research (8 months ago)
Here is another cool Coinmarketcap spreadsheet. It's updated every 5 min. https://docs.google.com/spreadsheets/d/1_FchZPoFmP_0igDxxZPwPAhiXjWxKN1Fh-5M25k2x9E/edit?hl=en&rm=minimal#gid=224399891
Capa Crypto (9 months ago)
Is there an App for this . Don't want to buy a new software.
Chris Roh (9 months ago)
Question: When i do it like this it does not get the decimal points so i just get values like 94894 for 0.94894 in the columns. Does anyone else have that problem?
Rakz86 (9 months ago)
Hi, thanks for this video, really helped step by step. Apart from the coinmarket api, is there anywhere else that can provide historic data through the api?
Rakz86 (9 months ago)
Yeah, im sturggling to find other decent apis. However, last question, when importanting from the web, I notice the prices are in USD, if i click to change it to btc on the site, it doesnt update the address. Is there anyway around this to pull BTC prices?
A Block of Crypto (9 months ago)
This video focuses on the coinmarketcap api. If there is somewhere you want to get data from then you need to check with them if they have an api
Chuks Obiri (9 months ago)
Amazing Video! Thanks a bunch. I would like to add an additional column, Is it possible for me to get Coin ranking in terms of 24h Volume Change instead of just price change alone???
MatrixUSA (9 months ago)
Great presentation.  Can ending date in Advanced Editor be linked to current GLOBAL date field in EXCEL 2016 ?
thx for sharing
Simon Levin (9 months ago)
Great tutorial! Thank you!
ItsHajjster (9 months ago)
Super helpful. Thank you very much.
Mohammad Maniat (9 months ago)
thank you for great information
Eco Charged (9 months ago)
I use a Mac. PowerQuery is not available on Mac yet. Booo!
Walter Cândido (9 months ago)
Sailing Epiic try Bootcamp, than run Windows on your Mac, then Excel for Windows, and voilá.
Marc Mirallas (9 months ago)
Awesome, many thanks!!
Paul Fcum (10 months ago)
Great video and I love your accent. Is it Dublin ? My question is, how can I get data for all of the coins listed on CMC ? currently Im only seeing the top 100.
Me Me (7 months ago)
The accent does rock
Paul Fcum (10 months ago)
that's it, thanks
Saravanan Rajaraman (10 months ago)
Try this API: https://api.coinmarketcap.com/v1/ticker/?limit=0
An Truong (10 months ago)
I installed the power quiery already but it still not work. It's unable to read something like .json. And I just purchased the 2016 version and it works now
A Block of Crypto (10 months ago)
it can read json, when you know how, excel 2016 standalone is awesome and should have all the features
Niels de Vrij (10 months ago)
Thanks for your video! Putting a date far into the future as end date in the query would also work instead of changing the end date in the editor each time.
Inversiones ROCA (5 months ago)
Nice Idea! Thanks.
An Truong (10 months ago)
I'm unable to do it using Excel 2010. Please show me another way to connect. Thank you very much
A Block of Crypto (10 months ago)
have you installed power query?
neumann10403 (10 months ago)
im using excel 2016 but the API dosn't work. Stays loading for a long time and then stop. Can you help me, please?
Ufuk Kuvan (10 months ago)
i tryed but this not wworking with office 2010 so how can i do this for my office 2010? please can you check it when you have free time. thanks alot
A Block of Crypto (9 months ago)
you need to install the power query add in
A Block of Crypto (10 months ago)
have you installed power query?
Lucas L (10 months ago)
Thanks this was super helpful!
soyeb ravat (10 months ago)
Hi thanks!!! really wanted my data in excel :-)
Nivio Fuschini (11 months ago)
hi there, is it possible to add it in GBP instead of USD ?
Elco de Man (8 months ago)
Go to the last column of the table. you will find them there
wobwobbird (9 months ago)
This link still posts it in USD, not GBP??
caroltachibana (10 months ago)
https://api.coinmarketcap.com/v1/ticker/?convert=GBP

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.