Market data of any cryptocurrency (Bitcoin, Ether, …) into Excel (Macro included)


Good day everyone! Today I would like to show you, how you can
get all types of market data of any cryptocurrency into Excel. If you only care about the current price then
I would recommend to watch another video of mine. There the macro is slightly simpler to set
up. Just click on the i-symbol on the top right
corner to go to this video. If you want additional market data like “Volume”,
“highest and lowest price in the last 24 hours”, “change in percent in the last 24 hours” or
“market capitalization”, then this video is for you! Just reproduce following steps: Open a new
Excel file, click on “Sheet 1” with your right mouse button, select “View Code”. Here you insert a new module. Now it is very important that this module
has the correct name. The name is supposed to be “JsonConverter”. I is important that it is written in exactly the same way. So capital J, capital C. Then you have to
go to a website. I am going to put the link into the description. And on this website you have to copy all the
code that you find on this site. Important is that you ignore the very first
line. So you only start on line 2 and select everything
till the very bottom. Copy the code and paste it in the module,
that we have just created. So this was step 1. Step 2 is to insert the macro, that I have
written. So you insert another module. Here the name doesn’t matter. And now you copy and paste the code, that will be in the very first comment of this video. Copy, paste. So now we are almost done. The last step, which has to be done is to
click on “Tools –>References…” and here we need to select the “Microsoft Scripting Runtime” and tick it. Okay. Then the VBA editor can be closed again. And that’s all. Now I will show you how you can get for example the latest price into Excel. You write “=CryptoInfo”, quotation mark. The first information you have to enter is
the ticker symbol of the crypto currency. Bitcoin for example is “BTC”. Semicolon. The second information is what you want to
withdraw from the server. For example the price is “PRICE” – all capital. Quotation mark. Close the bracket. Here you go. If you don’t want the price but someting else
then you just have to change the second word. So for example market capitalization is “MKTCAP”. So this is the current market capitalization
in Euro of Bitcoin. 64.3bn. What else I wanted to tell you? So instead of writing it here another option
is to link to this information. For example let me show you. Ticker Symbol. Market info. Ticker symbol of Ether, of Ripple is XRP for
example. And we would like to see the price and the
volume within the last 24 hours. “To” always means your Fiat currency. It works like this: “CryptoInfo” of Ether. Semicolon. The volume in the last 24 hours in Euros. Ooops…. Here that’s the volume in the last 24 hours
of Ether trading. If you have a different fiat currency than
Euro you have to go into the macro and change it in the 3rd line of the code. So instead of Euro you can go with US-Dollar,
with Brazilian Real, Australian Dollar and so on. The list of valid values can be found here. Still anything else I wanted to tell you? No, that’s all. Thanks very much for watching. Bye-bye.

13 thoughts on “Market data of any cryptocurrency (Bitcoin, Ether, …) into Excel (Macro included)”

  1. Function CryptoInfo(strTicker As String, requestType As String)

    Dim fiatCurrency As String
    fiatCurrency = "EUR" 'Valid values are: "AUD", "BRL", "CAD", "CHF", "CLP", "CNY", "CZK", "DKK", "EUR", "GBP", "HKD", "HUF", "IDR", "ILS", "INR", "JPY", "KRW", "MXN", "MYR", "NOK", "NZD", "PHP", "PKR", "PLN", "RUB", "SEK", "SGD", "THB", "TRY", "TWD", "ZAR"
    strURL = "https://min-api.cryptocompare.com/data/pricemultifull?fsyms=" & strTicker & "&tsyms=" & fiatCurrency

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strJSON = http.responsetext

    JsonConverter.JsonOptions.UseDoubleForLargeNumbers = True
    Set Json = JsonConverter.ParseJson(strJSON)
    CryptoInfo = Json("RAW")(strTicker)(fiatCurrency)(requestType)

    End Function

  2. If you want your spreadsheet to update the values automatically just add the code below to the macro. Add it ABOVE the CryptoInfo function. You can change the update interval (currently set to 5mins, 00:05:00). Furthermore the Excel file has to be saved, closed and reopened for the auto-update to start working.

    Dim TimeToRun

    Sub auto_open()
    Call ScheduleUpdateAll
    End Sub

    Sub ScheduleUpdateAll()
    TimeToRun = Now + TimeValue("00:05:00") 'Set the update interval
    Application.OnTime TimeToRun, "UpdateAll"
    End Sub

    Sub UpdateAll()
    Application.CalculateFull
    Call ScheduleUpdateAll
    End Sub

    Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "UpdateAll", , False
    End Sub

  3. Hello Rene,

    Can you help me with the following request. I want to get the "social stats" from cryptocompare.

    An example of such a request is: https://www.cryptocompare.com/api/data/socialstats/?id=1182
    This ID represents Bitcoin and the ID can be found in cell A1.

    Now I followed the procedure in your movie but I use the following module:

    Function CryptoCC(strID As String, requestType As String)

    strURL = "https://www.cryptocompare.com/api/data/socialstats/?id=" & strID

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strJSON = http.responsetext

    JsonConverter.JsonOptions.UseDoubleForLargeNumbers = True
    Set Json = JsonConverter.ParseJson(strJSON)
    CryptoCC = Json("DATA")("CRYPTOCOMPARE")(strID)(requestType)

    End Function

    If I want to get the number of followers on CryptoCompare for Bitcoin I use the following Function

    cell B2 = CryptoCC(A1, B1). Where cell B1 consists of "Followers". The function gets a #VALUE! as result.

    Could you tell me what I am doing wrong? Thank you in advance!!

  4. Hello René,

    This video It's a very good Job. It retrives all data required, but it's not updating. I pasted the following into the VBA macro:

    Dim TimeToRun

    Sub auto_open()
    Call ScheduleUpdateAll
    End Sub

    Sub ScheduleUpdateAll()
    TimeToRun = Now + TimeValue("00:05:00") 'Set the update interval
    Application.OnTime TimeToRun, "UpdateAll"
    End Sub

    Sub UpdateAll()
    Application.CalculateFull
    Call ScheduleUpdateAll
    End Sub

    Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "UpdateAll", , False
    End Sub

    Function CryptoInfo(strTicker As String, requestType As String)

    Dim fiatCurrency As String
    fiatCurrency = "EUR" 'Valid values are: "AUD", "BRL", "CAD", "CHF", "CLP", "CNY", "CZK", "DKK", "EUR", "GBP", "HKD", "HUF", "IDR", "ILS", "INR", "JPY", "KRW", "MXN", "MYR", "NOK", "NZD", "PHP", "PKR", "PLN", "RUB", "SEK", "SGD", "THB", "TRY", "TWD", "ZAR"
    strURL = "https://min-api.cryptocompare.com/data/pricemultifull?fsyms=" & strTicker & "&tsyms=" & fiatCurrency

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strJSON = http.responsetext

    JsonConverter.JsonOptions.UseDoubleForLargeNumbers = True
    Set Json = JsonConverter.ParseJson(strJSON)
    CryptoInfo = Json("RAW")(strTicker)(fiatCurrency)(requestType)

    End Function

  5. It 's possible to change the parameter  "CHANGE24HOUR" to "1 WEEK" or another time parameter (example 48HOUR)?

    Thanks for the video 😉

  6. Thank you for this! is it possible to only get price from 1 market?`when i write LASTMARKET I can see which market it take price from, but if i use 1 exhanger i wanna define it only to that.. is that possible ?

  7. I love your script. 🙂 Since 2 days I've the problem that some coins like rpx, mod, kcs …. would shown like 2.334 and not like iot or neo like 2,334 so that I can't calculate any prices. Do you have an idea why some coins with dot and some with comma?

  8. Hey Rene everything is working smoothly but is there a reason Iota price is not correct? You can try it yourself, not working probably also will this never work for mac ? I got windows and mac..
    And it works perfect on windows.
    Thanks again 🙂

  9. Dear Rene, your macro is great. It runs perfectly on Microsoft Excel 2016, however on Excel 2010, I receive #ARG! error. Do you have any idea what might be the cause?

  10. Do you have directions how to alternate the code in order to get an update of the last price every 1 second or any other value/timeinterval?

  11. Hi René!
    Long time we heard from you!
    I got issuc with just appered, everytime i log on the excel on the bottom right side it says "(4 topic) estimated 22% and so on) and it freeze everytime and close? any idea what the issuc might be ?

  12. Is it possible to include USD as well? I tried to add "USD" , but it came out as an error. could you help with this ?

Leave a Reply

Your email address will not be published. Required fields are marked *