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

in #cryptocurrency4 years ago (edited)

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:

:)

Sort:  

I saw your post on Reddit and came here to vote it up.
https://www.reddit.com/r/CryptoCurrency/comments/74zfor/i_just_wrote_an_article_explaining_the_way_i_came/

... And this, people, is how you help Steem go up in value!

Same here. I saw this post on Reddit.

I tried posting some of my Steem posts on r/cryptocurrency but got shadow ban each time.

Loading...

I love you for this!

Can't get it to work.

Try understanding how it works in the example workbook!

Thank you! Very helpful and simple to use. Updating my spreadsheet now.

Congratulations @aacoimbra! You have received a personal award!

1 Year on Steemit
Click on the badge to view your Board of Honor.

Do not miss the last post from @steemitboard:

Introducing SteemitBoard Ranking

Support SteemitBoard's project! Vote for its witness and get one more award!

Congratulations @aacoimbra! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 2 years!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Vote for @Steemitboard as a witness to get one more award and increased upvotes!

Coin Marketplace

STEEM 0.28
TRX 0.05
JST 0.038
BTC 36137.14
ETH 2403.64
USDT 1.00
SBD 3.80