How to import coinmarketcap data into Excel and have it automatically update

Posted by by kul_dood

I know there have been posts on how to insert live prices into Google Sheets but I like using Excel so I set it up in there.

For those who want the quick and dirty and feel like they can put it together with a couple of hints, all you'll need are the following two links:

https://coinmarketcap.com/api/

The youtube video guy downloads a JSON file but I have my data linked directly from the website. Detailed instructions are as follows:

For the purpose of this post, let's say I'm holding 1 BTC, 10 ETH, and a few alts like 100 STEEM, 200 XLM, 300 ARK.

If you visit that API link I posted earlier, you'll see the link you need to connect to for linking live prices. For getting the entire ticker, the API link is https://api.coinmarketcap.com/v1/ticker/

Let's get this ticker imported into excel first. Keep in mind that it's not a good idea to link to values in the ticker because as the coin ranks change, so will the row number that they'll be listed on, which will mess up your links.

To import ticker into excel follow the steps as described below:

1.1. In Excel, under the "Data" tab, click on "New Query", then "From Other Sources", then "From Web."

1.2. Paste the ticker API link (https://api.coinmarketcap.com/v1/ticker/) that they provide in the link above and hit OK.

1.3. This will open up a Query Editor window. I renamed the default name for this query in the right side column from "Query1" to "Ticker." You can change it to whatever you like.

1.4. In the Query Editor, click on "To Table" and OK in the popup that asks a couple of questions. No need to change any of the default settings.

1.5. This is optional but where it says Column1 next to "ABC123" you can double click that and rename it. I renamed it to Coin.

1.6. Click on the small grey box next to "Column1" that I renamed to "Coin." It's a small box with two arrows pointing in the opposite directions. It will open up a popup that allows you to select which property of each coin to display. I left them all selected and just chose to hide the columns later I didn't like.

1.7 Click "Close & Load" in the top left corner of the Query Editor window. Now the Query has been turned into a list that will have each coin as a row and the properties for it in column. However, as it is, this list is not set up to automatically refresh at a given interval.

1.8 Set up automatic refresh by clicking on "Properties" button in the "Design" tab. This will open up the "External Data Properties" popup.

1.9 Click on the grey box next to name entry in the "External Data Properties" popup and select the boxes next to "Refresh every" and "Refresh data when opening the file" boxes. As you can see, you can set up the automatic refresh interval to any number of minutes. The shortest it allows is 1 minute, which is what I have set up. It doesn't allow fractions of a minute....I tried....it gives an error if you put 0.5 or something. Click OK then OK to close both popups.

Now you have a live ticker that will refresh every minute. However, like I mentioned before, you shouldn't use the values in this ticker to link to your own cells because the row that a give coin is listed on will change with their rank. The link will remain on that row, which will show wrong entry. I only imported the ticker to have the entire list, as well as to get the ID of the coin for getting specific API for creating cell links.

To create links for generating your own live and updating tables, we need to similarly get a query but instead of the entire ticker, we need a query for our individual coins that we're holding. In my example, it's BTC, ETH, STEEM, XLM, and ARK.

To import coin data follow the steps as described below:

2.1. Get the API link for your coin of interest from the website I mentioned at the top. It shows as an example, the bitcoin API link is https://api.coinmarketcap.com/v1/ticker/bitcoin/

2.2. Use that link and follow steps 1.1 to 1.5.

2.3. Click on the "Record" which is a link under Column1 and it will turn into a two column table. Once you see in that format, click "Into Table" in the top left corner.

2.4. Proceed similarly from step 1.7 onwards.

You will need to generate a new query for each of your coins. Simply change the bitcoin part in the API link to whichever coin you're interested in. Replace bitcoin with your preferable coin's ID as shown in the ticker. Remember, use the ID, not the coin name. They are not always the same.

For my examples, I would use the following API links:

https://api.coinmarketcap.com/v1/ticker/bitcoin/

https://api.coinmarketcap.com/v1/ticker/ethereum/

https://api.coinmarketcap.com/v1/ticker/steem/

https://api.coinmarketcap.com/v1/ticker/stellar/

https://api.coinmarketcap.com/v1/ticker/ark/

Here is an example of the type of chart I generated with the example.

Hope this was helpful. It seems sometimes when the API data refreshes the excel file switches to that sheet, which is a bit annoying. If anyone knows how to keep the sheet with your data pinned let me know. Also, I can't make sense of the values in the last updated field. If anyone knows how to interpret those let me know as well.

Enjoy!

Edit: Here is a sample portfolio file.

Edit 2: Here are examples of coinbase APIs for those who want more relevant prices. I got the API links from here:

https://api.coinbase.com/v2/prices/ETH-USD/spot

https://api.coinbase.com/v2/prices/BTC-USD/spot

Source: https://www.reddit.com/r/ethtrader/comments/6b16fk/how_to_import_coinmarketcap_data_into_excel_and/