How to Build a Real Time Streaming Cryptocurrency Dashboard in Excel in 5 Minutes

It has never been easier to pull live streaming cryptocurrency prices into your spreadsheets. Now you can do it for free in a few seconds, here’s how.

It simply doesn’t get any easier than pasting this into your spreadsheet =CS.EXRATE(“BTC”,”USD”,1)

OVERVIEW

This article is intended for anyone including noobies to professional traders. It focuses on using basic Cryptosheets custom functions (aka formulas) to create a simple layout that will provide live streaming market prices for thousands of crypto assets. The best part is you can do it yourself right for free!

In this article we will:

• Create a symbols list
• Get historical price data
• Connect live streaming prices
• Calculate the percentage (%) change
• Rank the top winners and losers

HOW TO

This article demonstrates how to build a basic streaming dashboard that you can use in Excel 365 for desktop PC, Excel online and Excel 265 for Mac OSX. Click here to make sure you meet the minimum Excel version requirements and click here to install the Cryptosheets add-in for Microsoft Excel.

STEP 1: 👉 Create a symbols List

• For this we’re going to use the built in Cryptosheets custom function =CS.SYMBOLS() to get a list of symbols from Coinbase
• To make sure we’re getting all of them we’ll use the _limit global argument and a value of 50

Start with a basic symbols list that can be dynamically updated using custom functions

• Then using the _distinct global argument we’re going to select only the distinct symbols available

There can be many trading pairs for each symbol so using the _distinct global argument is very helpful

• Next we’re going to sort them using the _orderBy global argument to sort our list by the symbol column alphabetically

• Finally we’re going to filter our list to only return the specific columns we want using the _fields global argument in this case symbol and name

STEP 2: 👉 Get Relative historical prices

Now that we have our clean and sorted symbols list in place, we need to get some historical prices. For that we’re going to use the built in Cryptosheets custom function =CS.PRICE.

• First we’ll add a quote cell where we’ll type USDT (Tether) to maximize our crypto asset pricing data coverage
• Then we’ll add a column for the historical price and a date in cell F3 for a specific historical period (7/26/2020 in our example)

Using cells for our quote and date values allows us to rapidly change them later

Then we’ll use the =CS.PRICE custom function to get the price for each symbol from the specific date by selecting the symbol and also selecting + locking the cell reference for the quote and date cells we just created

The syntax for CS.PRICE is
CS.PRICE("base","quote","time","exchange","returnType")

Locking cell references allows us to easily apply the same formula to multiple cells

• After we’ve created the formula once then we can simply drag it down to get prices for the other symbols

STEP 3: 👉 Connect live streaming prices

IMPORTANT: If you accidentally connect your Cryptosheets custom function formulas to live streaming cells/formulas → you can easily and rapidly run up and exhaust your request quotas (especially for free forever subscriptions) so please go slow, check your formulas and check your subscription usage in real time any time in the side panel

Now using the built in Cryptosheets custom function =CS.EXRATE we’re going to pull in live streaming prices right next to our historical prices

• Create a similar formula in cell E4 using =CS.EXRATE and instead of pointing the third parameter to the date cell we created, we’re simply going to type a 1 to indicate we want a 1 second refresh interval

• Similar to the steps above for historical price, now we just need to drag the formula with the proper locked relative cells down to populate the streaming prices

It may take a few seconds for all of the prices to start refreshing and streaming live, some symbols may also not have volatile price action so be sure to increase the number of decimal places in each cell

STEP 4: 👉 Calculate the percentage (%) change

Using basic math and native Excel formulas now we can create the calculations for the percentage change using the historical prices we already pulled in earlier relative to the streaming prices we now have

percentage (%) change formula: =(newPrice-oldPrice)/oldPrice

• In column G we’re going to type the following formula =(E4-F4)/F4 then drag it down just like the others to populate all the cells with the percentage change for each symbol

• Then we’ll add some basic conditional formatting from the Excel ribbon menu to help visualize the biggest gains and losses

STEP 5: 👉 Rank the top winners and losers

Next we need filter again and grab just the top winners and losers from the main list.

• First we’ll create two new list sections for Top 5 Winners and Top 5 Losers
• Then using the native Excel formulas =LARGE() and =SMALL() we can easily grab just the ones we want for each ranking that will also update dynamically as the prices in the cells change

• Lastly we’re going to use the powerful new Excel function =XLOOKUP() to quickly grab the symbols for each corresponding symbol from our original list (if you don’t have =XLOOKUP yet you can easily use a combination of =INDEX and =MATCH)
• Then we’ll also add some additional conditional formatting for data bars to help illustrate the magnitude of the percentage change for each asset

Adding some basic conditional color formatting and data bars helps us identify the most important changes more easily

DONE!

👉 That’s it congratulations!

When you’re done you should have a dynamic, live streaming dashboard of prices and percentage gains including top 5 winners & losers!

👉 Click here to try this dashboard template right now for free

TIP: You can easily build your own customized dashboard including dynamic heat map charts or try one of our starter templates for free

IMPORTANT NOTES

Streaming data without any downloaded files or heavy local client software is a powerful and relatively recent capability in Excel. That means it may interact with legacy VBA code, legacy macros or custom scripts you have so please be conscious of these interactions to avoid runaways and quota overages or being disabled.

ADDITIONAL RESOURCES

Cryptosheets is free to sign up for and start using now….
👉 What are you waiting for?

Signing up for Cryptosheets is free → Get crypto data in 30 seconds or less

Get the Excel add-in now

Get the Googlesheets add-in now

ADDITIONAL RESOURCES

Website | Docs Portal | WebApp | Excel | Googlesheets | Help Center | Blog | Twitter

TAGS: crypto, bitcoin price, bitcoin,cryptocurrency, blockchain, excel, googlesheets, crypto api,formulas, btc, bitcoin price history, spreadsheet, streaming, live, real time, websocket, wss, refresh, automatic price refresh, formulas, custom functions