|
Title: [HOW TO] Live tracking in excel Post by: Str1x on November 05, 2017, 03:43:19 PM Hi all,
Searched the forum but couldn't find a tutorial how to add coinmarketcap in excel, so here i go. What you need MS Excel 2007/2013 MS Power Query https://www.microsoft.com/en-us/download/details.aspx?id=39379 (https://www.microsoft.com/en-us/download/details.aspx?id=39379) Set up excel When you finished setting up power query in excel you should see this icon: https://image.ibb.co/nOX9qw/Untitled.jpg If the new tab is in excel we can start with setting up the coinmarket API in excel: Start with going to the new power query tab and click on from web https://preview.ibb.co/foJFVw/Untitled.jpg In the next screen copy and paste this link: Code: https://api.coinmarketcap.com/v1/ticker/?limit=0 you'll get something like this and press ok: https://preview.ibb.co/hnf7cb/Untitled.jpg After pressing ok, you'll get the following screen: https://preview.ibb.co/fSCZqw/Untitled.jpg Now that the API has loaded in press the button "to table" https://preview.ibb.co/jxwuqw/Untitled.jpg now press "ok" and than press the left filter button as shown here: https://image.ibb.co/iHjKOG/Untitled.jpg After you press ok you can change colum name like you prefer, or even delete colums you don't need https://preview.ibb.co/kxknAw/Untitled.jpg After you done renaming colums press "Close & Load' and you are good to go: https://preview.ibb.co/dmeVVw/Untitled.jpg TIP If you want your data to refresh every X minute set the mouse on a cel -> go to Design and press connection properties https://preview.ibb.co/n76cAw/Untitled.jpg Now you can change how fast/slow you want the coinmarketcap data to be refresh as seen below: https://image.ibb.co/mibAVw/Untitled.jpg Help Feel free to ask in this topic if you need help in setting up this live coinmarketcap API in excel. If you find this topic to be helpful you can always buy me a beer: BTC: 13mVkBy6HVwKuNovaWzYdF27FB6pLqoJtr Title: Re: [HOW TO] Live tracking in excel Post by: Profilerx on November 05, 2017, 08:48:18 PM Thank you, I was looking for something like that. Can you also do this for the top 100 coins only?
Title: Re: [HOW TO] Live tracking in excel Post by: Str1x on November 06, 2017, 08:18:46 AM Thank you, I was looking for something like that. Can you also do this for the top 100 coins only? Thanks for the reply, please change the api URL in: Code: https://api.coinmarketcap.com/v1/ticker/?limit=100 You can change the ticker limit how you want it, for example top 10 coins = Code: https://api.coinmarketcap.com/v1/ticker/?limit=10 Title: Re: [HOW TO] Live tracking in excel Post by: DarnellL on November 06, 2017, 05:46:40 PM any chance to do the same in Google Sheets?
Heard, they have request limitations - is there any way to do that? Title: Re: [HOW TO] Live tracking in excel Post by: Str1x on November 06, 2017, 06:36:23 PM any chance to do the same in Google Sheets? Heard, they have request limitations - is there any way to do that? The API has a limit of 10 times per minute, don't think you are going to refresh the sheet more than 10 times or are you. In regards to google spreadsheet i will look onto it and will update OP if i find it. Title: Re: [HOW TO] Live tracking in excel Post by: clip123 on November 06, 2017, 11:52:03 PM Thanks, been looking for something like this!
Title: Re: [HOW TO] Live tracking in excel Post by: Spratan on November 08, 2017, 08:54:16 AM My tables has been limited to 100 lines since yesterday. Yesterday it was more than 1200. Did Coinmarketcap changed its API ?
Title: Re: [HOW TO] Live tracking in excel Post by: Str1x on November 08, 2017, 05:18:14 PM My tables has been limited to 100 lines since yesterday. Yesterday it was more than 1200. Did Coinmarketcap changed its API ? You are right, i had that this morning. Coinmarketcap sees a lot of traffic nowadays so they made it at default 100 coins. But if you change the api to this: Code: https://api.coinmarketcap.com/v1/ticker/?limit=0 Title: Re: [HOW TO] Live tracking in excel Post by: Spratan on November 08, 2017, 11:08:34 PM My tables has been limited to 100 lines since yesterday. Yesterday it was more than 1200. Did Coinmarketcap changed its API ? You are right, i had that this morning. Coinmarketcap sees a lot of traffic nowadays so they made it at default 100 coins. But if you change the api to this: Code: https://api.coinmarketcap.com/v1/ticker/?limit=0 Thanks, it worked ! Title: Re: [HOW TO] Live tracking in excel Post by: cryptodyn on November 08, 2017, 11:37:26 PM Excel-ent!
Many thanks for this great tutorial. I've tried so many times in the past and couldn't make it work. |
