Jump to content

Setting up an Excel sheet with an API


RobertStrong

Recommended Posts

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 by RobertStrong
Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...