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.
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
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
_limitglobal argument and a value of
- Then using the
_distinctglobal argument we’re going to select only the distinct symbols available
- Next we’re going to sort them using the
_orderByglobal argument to sort our list by the
- Finally we’re going to filter our list to only return the specific columns we want using the
_fieldsglobal argument in this case
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
quotecell 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
F3for a specific historical period (7/26/2020 in our example)
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
- 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
- Create a similar formula in cell
E4using =CS.EXRATE and instead of pointing the third parameter to the
datecell we created, we’re simply going to type a
1to indicate we want a
1second 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
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
Gwe’re going to type the following formula
=(E4-F4)/F4then 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
=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
👉 That’s it congratulations!
TIP: You can easily build your own customized dashboard including dynamic heat map charts or try one of our starter templates for free
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.
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
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