Юридическая консультация онлайн
На главнуюЛюди и блогиПохожее видеоЕще от: A Block of Crypto

Connect Excel to Coinmarketcap for live and historic data

Оценок: 454 | Просмотров: 32812
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
Текстовые комментарии (94)
Gajendra Mahadik (1 месяц назад)
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 (1 месяц назад)
Thanks, do you know how I can download the historical data of all coins automatically using Excel?
eric grissom (1 месяц назад)
thanks for your video
Shahroz Hameed (1 месяц назад)
it's highly valuable thing for me. Thanks for updating this info.
Ludovico Principe (2 месяца назад)
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 (2 месяца назад)
what is the benefit of doing this?
Chihuahua Michael Jackson (2 месяца назад)
fantastic! thank you a lot!
gene golub (2 месяца назад)
thank you, very educational.
Joe Morgan (2 месяца назад)
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 (2 месяца назад)
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 (2 месяца назад)
Excellent tutorial! Step-by-step instructions that are very clear and easy to follow.
gfgfdj;l;l gdfgdf (2 месяца назад)
power query here https://ufile.io/7035b
Amr Daoud (2 месяца назад)
Wtf is this shit why does it close
Amr Daoud (2 месяца назад)
🐛🐛🐛🐛🐞🐞🐫👹😈🙀😈😼😈👱😈👹😼👹🐨👹 im using fuckin cell phonenind🐨 i dont know excelll
Amr Daoud (2 месяца назад)
I subscribed
CryptoJerk (3 месяца назад)
Incredibly helpful! Many thanks for this video.
Inversiones ROCA (3 месяца назад)
Thank you for the videos, very helpful to calculate volatilities, rents and correlations.
Freddy Rodriguez (4 месяца назад)
awesome
andre 2112 (4 месяца назад)
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 (4 месяца назад)
Awesome video. Any reason why the link only gave me top 100 coins instead all the rows?
Mr Green (4 месяца назад)
I press the refresh button but the data is not updated. How can i fix it? Thank u so much!
The-owner (4 месяца назад)
Thank you so much !!!
Erkan Ince (4 месяца назад)
Wonderful
Me Me (4 месяца назад)
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 (5 месяцев назад)
Nice video thanks 👍
Moisés Uclés Lirola (5 месяцев назад)
awesome
david hodgins (5 месяцев назад)
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 (1 месяц назад)
Shunwai Hong u can do that in JavaScript or python
Shunwai Hong (1 месяц назад)
did you find a solution?
KC Kiprich (5 месяцев назад)
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 (5 месяцев назад)
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 (5 месяцев назад)
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 (4 месяца назад)
would need a macro to make Column = Today() - Date(... )
Mr Green (5 месяцев назад)
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 (5 месяцев назад)
So helpful. Thanks :D
Danny L (5 месяцев назад)
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 (5 месяцев назад)
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 (5 месяцев назад)
I am using Excel 2013 and tried the "Limit=0" link however, still only getting top 100. Help please :(
DrJ (5 месяцев назад)
Where is the URL for next video that show how to develop a crypto portfolio tracker? Thanks
DM (5 месяцев назад)
With Bitcoin Historical data it gives me a DataFormat Error with the Volume Colum, it can't convert it to numbers ??
Garth Woodworth (5 месяцев назад)
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 (6 месяцев назад)
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 (6 месяцев назад)
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 (6 месяцев назад)
Is it possible an automatic uptade on the live data?
Me Me (4 месяца назад)
Data > Connections > Properties > Refresh every XX minutes Excel 2010, should be available in 2007 etc
Julia Slisa (6 месяцев назад)
is there a way to add more than 100 rows?
Bartosz Janicki (6 месяцев назад)
https://api.coinmarketcap.com/v1/ticker/?limit=0
Tileoptiko Pamfago (6 месяцев назад)
extremely nice explanation, you got thumbs up and subscribed :) do you have any idea how to show the percent-changes of all coins with respect to BTC? somebody in the comments suggested using https://api.coinmarketcap.com/v1/ticker/?convert=BTC but even though it runs, the changes are somehow strange (BTC change is not 0% with respect to itself, as expected), thanks in advance!!
DutchDude1984 (5 месяцев назад)
actually you should use time series regression with lagged and lead effects , granger causality testing to determine leaders, followers, factor analysis to group cryptos; but to understand it you must get to higher education first
Tileoptiko Pamfago (6 месяцев назад)
answering my own question and for anyone interested, I think it is NOT possible to return the 1h,24h,7d-changes of any coin with respect to anything BUT USD, no matter what suffix you put in the API url. If you check the API documentation (https://pypi.python.org/pypi/coinmarketcap/), changing the suffix to a custom currency (e.g. EUR or GDP or BTC) only returns "price, 24h volume, and market cap" in terms of that currency. Unfortunately, the 1h,24h and 7d-changes will always be returned with respect to USD as of now.
Akkas Miah (6 месяцев назад)
Is this method compatible with MS Excel 2010?
Gerard Kooij (6 месяцев назад)
Thanks for the clear explanation , however I am facing the fact that 100 coins are load up, can you please help further.
pd craft (6 месяцев назад)
awesome, im going to watch the next one...
Matthew Chambers (6 месяцев назад)
thank you that was very helpful
GABRIELE LUCINI (6 месяцев назад)
tnx.. only first 100 rows? if i need more rows?
GABRIELE LUCINI (6 месяцев назад)
thanks a lot.. Jackspot
Jackspot: Le blog de la ruée vers l'or (6 месяцев назад)
Use https://api.coinmarketcap.com/v1/ticker/?limit=0 instead of the default ;)
Dr Fil (6 месяцев назад)
Great tutorial thank you so much.
Jim O'Donnell (6 месяцев назад)
This is a great demo. Thank you!
Moosy Research (6 месяцев назад)
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 (7 месяцев назад)
Is there an App for this . Don't want to buy a new software.
Chris Roh (7 месяцев назад)
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 (7 месяцев назад)
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 (7 месяцев назад)
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 (7 месяцев назад)
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 (7 месяцев назад)
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 (7 месяцев назад)
Great presentation.  Can ending date in Advanced Editor be linked to current GLOBAL date field in EXCEL 2016 ?
Jens-Ingeman Stad Fosseli (7 месяцев назад)
thx for sharing
Simon Levin (7 месяцев назад)
Great tutorial! Thank you!
ItsHajjster (7 месяцев назад)
Super helpful. Thank you very much.
Mohammad Maniat (7 месяцев назад)
thank you for great information
Sailing Epiic (7 месяцев назад)
I use a Mac. PowerQuery is not available on Mac yet. Booo!
Walter Cândido (7 месяцев назад)
Sailing Epiic try Bootcamp, than run Windows on your Mac, then Excel for Windows, and voilá.
Marc Mirallas (7 месяцев назад)
Awesome, many thanks!!
Paul Fcum (8 месяцев назад)
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 (4 месяца назад)
The accent does rock
Paul Fcum (8 месяцев назад)
that's it, thanks
Saravanan Rajaraman (8 месяцев назад)
Try this API: https://api.coinmarketcap.com/v1/ticker/?limit=0
An Truong (8 месяцев назад)
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 (8 месяцев назад)
it can read json, when you know how, excel 2016 standalone is awesome and should have all the features
Niels de Vrij (8 месяцев назад)
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 (3 месяца назад)
Nice Idea! Thanks.
An Truong (8 месяцев назад)
I'm unable to do it using Excel 2010. Please show me another way to connect. Thank you very much
A Block of Crypto (8 месяцев назад)
have you installed power query?
neumann10403 (8 месяцев назад)
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 (8 месяцев назад)
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 (7 месяцев назад)
you need to install the power query add in
A Block of Crypto (8 месяцев назад)
have you installed power query?
Lucas L (8 месяцев назад)
Thanks this was super helpful!
soyeb ravat (8 месяцев назад)
Hi thanks!!! really wanted my data in excel :-)
Nivio Fuschini (9 месяцев назад)
hi there, is it possible to add it in GBP instead of USD ?
Elco de Man (6 месяцев назад)
Go to the last column of the table. you will find them there
wobwobbird (7 месяцев назад)
This link still posts it in USD, not GBP??
caroltachibana (8 месяцев назад)
https://api.coinmarketcap.com/v1/ticker/?convert=GBP

Хотите оставить комментарий?

Присоединитесь к YouTube, или войдите, если вы уже зарегистрированы.
Задайте и вы свой вопрос, это бесплатно! Адвокат по телефону получит предварительную информацию от вас, после чего может в спокойной обстановке восполнить возможные пробелы и проанализировать правовые нормы. Он оценит все факты и сможет подготовить развернутую консультацию для вас. По этой причине адвокат является вашим сторонником в разрешении возникших проблем. Вы сбережете свои средства, обратившись за телефонной консультацией. Таким образом, обращаясь по телефону к услугам адвоката, вы получаете возможность решить возникшие проблемы с минимальными финансовыми и временными затратами. В некоторых случаях в дальнейших очных консультациях не возникает необходимости. По этой причине помощь адвоката, оказываемая по телефону, становится неоценимой.