The easiest way to have LIVE cryptocurrencies prices, and any other data, on excel! With a custom function! (Importing from Coinmarketcap)

I've seen some tutorials on how to import Coinmarketcap data into excel, but most of them are a little too time consuming as you need to create a new worksheet for every different coin. Here is the way I found that works the best for me. We will be creating a custom excel funcion. Here is the step by step guide:

Edit: I've just update the article to include variants of the custom function for data other than price. (Market Cap, Volume, Supply, etc).

---

1. First, create a new file (or use your own portifolio workbook) and on the "Data" tab click on "New Query" -> "From Other Sources" -> "From Web".
   

2. Past this link: "https://api.coinmarketcap.com/v1/ticker/" and click OK.

3. A new widow will pop up. Click on "To Table" at the top left corner and hit OK.

4. Click on the small grey box with two arrows pointing in opposite directions next to "Column1" and press OK. Here you can select what info do you want excel to load. I personally load all of them as you can later retreive any data you want from this source.
   

5. Now click on "Close & Load"

6. A new worksheet will be created. Rename it to "CMC".
   Note: It's important to rename it exactly to "CMC", we will use a macro that will search for a "CMC" worksheet.

7. Select the first cell of the first column and click on the "Data" tab -> Refresh All -> "Connection Properties" -> Select "Refresh every" and set the interval you want your prices updated (I set to 20) and select "Refresh data when opening the file"
   

8. Press ALT+F11 and a new widnow should open up. Now create a new module as shown below.
   

9. Past this code:

Function CMCPrice(CMCTokenSymbol As String)
Application.Volatile
CMCPrice = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 3, 0) End Function`

Now it's all set. The code above added a custom function to excel. You can now retrieve the price of any coin registered on Coinmarketcap. To retrieve a price from Bitcoin for example, type =TokenPrice("BTC") on the cell you want the price at.

Let's say you track the value of your crypto holdings in this worksheet and you have one bitcoin:

But now you just bought 2 Ethereum and want to add it tho the table. Just select and drag the cells as following:

And change the refence name to ETH:

You can add as many crypto as you want. e.g.

I've uploaded an example workbook to google drive.

I hope I've helped you! :D

Note: Remember to save your workbook as a macro enabled workbook or else the macro will not be saved.

---

Edit: I've created variants of the custom function for data other than price. (Market Cap, Volume, Supply, etc).Paste the function you want in the module window as we did with the price function.

For the coin rank on CMC:
Function CMCRank(CMCTokenSymbol As String)
Application.Volatile
CMCRank = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 2, 0)
End Function

For the coin price in BTC:
Function CMCPriceBTC(CMCTokenSymbol As String)
Application.Volatile
CMCPriceBTC = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 4, 0)
End Function

For the trading volume in the last 24h:
Function CMCVolume24h(CMCTokenSymbol As String)
Application.Volatile
CMCVolume24h = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 5, 0)
End Function

For the market cap:
Function CMCMarketCap(CMCTokenSymbol As String)
Application.Volatile
CMCMarketCap = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 6, 0)
End Function

For the available supply:
Function CMCAvailableSupply(CMCTokenSymbol As String)
Application.Volatile
CMCAvailableSupply = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 7, 0)
End Function

For the total supply:
Function CMCTotalSupply(CMCTokenSymbol As String)
Application.Volatile
CMCTotalSupply = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 8, 0)
End Function

For the percent change in the last 1h:
Function CMCPercentChange1h(CMCTokenSymbol As String)
Application.Volatile
CMCPercentChange1h = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 9, 0)
End Function

For the percent change in the last 24h:
Function CMCPercentChange24h(CMCTokenSymbol As String)
Application.Volatile
CMCPercentChange24h = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 10, 0)
End Function

For the percent change in the last 7d:
Function CMCPercentChange7d(CMCTokenSymbol As String)
Application.Volatile
CMCPercentChange7d = WorksheetFunction.VLookup(CMCTokenSymbol, Worksheets("CMC").Range("$C:$M"), 11, 0)
End Function

Now you can easily create tables like this one, in the example workbook:

:)