# 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.*

**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

global argument and a value of**_limit****50**

- Then using the

global argument we’re going to select only the**_distinct****distinct**symbols available

- Next we’re going to
**sort**them using the

global argument to sort our list by the**_orderBy****symbol**

- Finally we’re going to
**filter**our list to only return the specific columns we want using the

global argument in this case**_fields****symbol****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)

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

ThesyntaxforCS.PRICEis

CS.PRICE("base","quote","time","exchange","returnType")

- 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 andcheck 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**

to indicate we want a**1**

second refresh interval**1**

- 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**

we’re going to type the following formula**G**

then drag it down just like the others to populate all the cells with the percentage change for each symbol**=(E4-F4)/F4**

- 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()**

we can easily**=SMALL()****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

## DONE!

👉 That’s it congratulations!

## 👉 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*

## 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