Setting up an Excel sheet with an API

[RobertStrong]

Hi, just want to give a quick tutorial on how to set up coinmarketcap API data (automatic feed for beginners) in Excel on Windows

If you have any version of excel before 2016, download power query from the Microsoft website: https://www.microsoft.com/en-us/download/details.aspx?id=39379
When you download the power query add-on (either 32-bit or 64-bit depending on your version of excel), it will add itself to your excel toolbar/ribbon, open it up and select the first option (From Web)

Enter in this address: https://api.coinmarketcap.com/v1/ticker/

Then click "Okay", then click the "To Table" (top left) and "Okay"

Next, you should  see 1 column with "Record" listed all the way down.  To the right of Column 1 (header), there is a button with two arrows pointing opposite directions, click this button and uncheck the box: "Use original column name as prefix"

Finally, click load and close, and save... your sheet now has refreshable prices aggregated from coinmarketcap

If you want the data to refresh automatically, then navigate to the data tab and click "Connections" select the query from the list and click properties.  Check the second box (under connection properties > usage) and choose how often you would like it to refresh.

It is also possible to get historical crypto data, for which i can do an additional tutorial another day.

https://www.microsoft.com/en-us/download/details.aspx?id=39379

https://coinmarketcap.com/api/

feedback, alternatives, improvements, alternate data sources are welcome

many thanks,

RS (not my real name)

 

(will test on Mac this weekend)

Edited December 14, 2017 by RobertStrong

[Sharkey]

52 minutes ago, RobertStrong said:

 

(will test on Mac this weekend)

Thanks, I'd love to use this on my MacBook!

[Guest1]

1 hour ago, RobertStrong said:

Hi, just want to give a quick tutorial on how to set up coinmarketcap API data (automatic feed for beginners) in Excel on Windows

If you have any version of excel before 2016, download power query from the Microsoft website: https://www.microsoft.com/en-us/download/details.aspx?id=39379
When you download the power query add-on (either 32-bit or 64-bit depending on your version of excel), it will add itself to your excel toolbar/ribbon, open it up and select the first option (From Web)

Enter in this address: https://api.coinmarketcap.com/v1/ticker/

Then click "Okay", then click the "To Table" (top left) and "Okay"

Next, you should  see 1 column with "Record" listed all the way down.  To the right of Column 1 (header), there is a button with two arrows pointing opposite directions, click this button and uncheck the box: "Use original column name as prefix"

Finally, click load and close, and save... your sheet now has refreshable prices aggregated from coinmarketcap

If you want the data to refresh automatically, then navigate to the data tab and click "Connections" select the query from the list and click properties.  Check the second box (under connection properties > usage) and choose how often you would like it to refresh.

It is also possible to get historical crypto data, for which i can do an additional tutorial another day.

https://www.microsoft.com/en-us/download/details.aspx?id=39379

https://coinmarketcap.com/api/

feedback, alternatives, improvements, alternate data sources are welcome

many thanks,

RS (not my real name)

 

(will test on Mac this weekend)

Thanks for this info. I used to do this + API from Bittrex as well so that I didn't have to go on their websites all the time but just press F9 to refresh from Excel