[HOW TO] Live tracking in excel

[Str1x]

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

Set up excel
When you finished setting up power query in excel you should see this icon:


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


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:


After pressing ok, you'll get the following screen:


Now that the API has loaded in press the button "to table"


now press "ok" and than press the left filter button as shown here:



After you press ok you can change colum name like you prefer, or even delete colums you don't need


After you done renaming colums press "Close & Load' and you are good to go:


TIP
If you want your data to refresh every X minute set the mouse on a cel -> go to Design and press connection properties


Now you can change how fast/slow you want the coinmarketcap data to be refresh as seen below:


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

[1715537917]

1715537917

[1715537917]

1715537917

[1715537917]

1715537917

[Profilerx]

Thank you, I was looking for something like that. Can you also do this for the top 100 coins only?

[Str1x]

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

[DarnellL]

any chance to do the same in Google Sheets?
Heard, they have request limitations - is there any way to do that?

[Str1x]

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.

[clip123]

Thanks, been looking for something like this!

[Spratan]

My tables has been limited to 100 lines since yesterday. Yesterday it was more than 1200. Did Coinmarketcap changed its API ?

[Str1x]

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

You are good to go

[Spratan]

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

You are good to go

Thanks, it worked !

[cryptodyn]

Excel-ent!
Many thanks for this great tutorial. I've tried so many times in the past and couldn't make it work.