A simple dashboard for long term price changes of Bitcoin and Ethereum (Google Sheets and Tableau)

Wai Leung

Published in

Coinmonks

7 min readFeb 27, 2022

Why?

Cryptocurrency supporters often promote major cryptocurrencies as “insert quote”

Ever since I learn about cryptocurrencies, I found myself constantly toggling between TradingView’s charts to observe the long term performance of Bitcoin and Ethereum, attempting to evaluate if this argument will stay verified through time. While I am only using the free version of TradingView, it is more than just a one-click task.

(I am very cautious about spending money on subscription services, I feel like these days every company is trying to make money from our paycheques every month.)

So I built a dashboard to track the performance of Bitcoin and Ethereum in comparison with major financial metrics, making my regular task a bit easier.

The dashboard I built

Long story short, here are some screenshots of the dashboard:

It has interactive function and mobile view too :)

Or if you want to see the original dashboard, click the following link:

https://public.tableau.com/app/profile/wai.leung/viz/BTCETHLong-termPriceTracker/Dashboard1

You are probably thinking the dashboard looks way too simple, I know, and here are a few reasons:

It is meant to be simple: I am only using this dashboard to observe the long term price change, not whether I should buy or sell particular crypto at a particular time, therefore I don’t need a lot of technical indicators in the dashboard.
At the moment I am using the GoogleFinance function of Google Sheets to generate all the financial data, some information like the trading volume of cryptos are not available with this function. Also, I have thought about putting the inflation rate and bond yield into the chart, which I may do in the future.

And here is an explanation of what are in the dashboard:

Buttons to get a better view of the selected time-frame
Small charts to see the changing price of Bitcoin and Ethereum, without any distraction (colour change to indicate if the price was above or below the beginning price point of the selected timeframe)
Major Index Tracker: tracking the price change of Bitcoin and Ethereum in percentage, compare with ETF that tracks S&P 500 Index (VOO), Nasdaq 100 (QQQ) and the price of gold (GLD)
ETH BTC Ratio: tracking the price strength of Ethereum as compared to Bitcoin, I also added 2 moving average lines to have a better understanding of the trend
Percentage differences are always calculated using the selected timeframe (i.e. if the price of a product at the first date is empty, that product’s percentage difference will be calculated using the next non-null day)

How I read the charts

Using the Year-To-Date view, we can see so far Bitcoin and Ethereum are not doing great this year:

Using the same view, we can see both Bitcoin and Ethereum are having the worse performances within selected products, and gold remains the only one that has had a positive growth rate since the year started, maybe it is too soon to say Bitcoin or any other cryptos will be digital gold:

Look at the change in the ETH BTC Ratio in the last three years, the performance of Ethereum has picked up since May 2021:

How I built the dashboard

To build this dashboard, I used Google Sheets to obtain all the data I need with the built-in GoogleFinance function and then connected the file to Tableau Software for visualisation. I also set the Tableau Software to automatically refresh every day to obtain new data from the Google Sheets file.

Scrapping Data

As mentioned, all pricing data are generated using Google Sheets’ built-in GoogleFinance function, with this basic formula

=GOOGLEFINANCE(“BTCUSD”, “price”, TODAY()-1120, TODAY(), “DAILY”)

I created individual sheets on the Google Sheets file for all Cryptos and ETFs listed. Each sheet look similar to this one:

I use the WEEKDAY column to quickly validate data return from the GoogleFinance function, and I also used the LEFT function on the Real Date column to generate cleaned date data from the result of GoogleFinance on the Date column.

As a side note on the data scraping process, as mentioned, I have thought about including volume changing trends in the dashboard, but GoogleFinance cannot return volumes of cryptos at the moment. I tried to look for other solutions and discovered this Google Sheets Extension which extracts data from CoinGecko through API interaction:

CoinGecko Prices, Volumes, Market Caps in Google Sheets and Excel

For anyone looking to extract automatically data from CoinGecko.

medium.com

However, due to restrictions from CoinGecko’s side, it does not seem reliable for the purpose of the dashboard. But in the future, I may dig into API interaction later to see if I can get a different result.

Merging Tables

I used the VLOOKUP function and some additional functions to merge the individual sheets into one table.

On the Date Cal column there are 2 different formulas, I used the following formula in cell A2 to return the date of 3 years before today:

=EDATE(TODAY()-1, -36)

Then the following cells of the column have formulas similar to this to return the date until today:

=IF($A2<TODAY(), $A2+1,””)

Because VLOOKUP could not recognise the date on the Date Cal column, I added a Date column to extract the date from the Date Cal column using the LEFT function, then from column C onwards I combined the IFERROR function with VLOOKUP, therefore, on the VOO, QQQ and GLD columns, cells related to the stock market closing dates (weekends and holidays) return NULL value instead of an error.

Visualising the data with Tableau

Interactive date view

To bring a better look at the price change under different timeframes, just like most stock dashboards provide, I created a button function by linking parameters in Tableau with an external google sheets file.

Charts

All charts in the dashboard are just normal line graphs, the trickiest part was in the Major Index Tracker. Because Cryptos prices return every day, which is different from the normal ETF quoted in the same chart, I combined the following calculated fields to always return percentage change according to the first price returned within the selected timeframe:

1. Search for non-null value in the ETF columns(insert image)
if not isnull(sum([VOO])) then index() end
2. Look for the first non-null value in the ETF tables (insert image)
window_min([VOO non empty])
3. Calculate the percentage difference according to the first non-null value
(SUM ([VOO])- LOOKUP (SUM ([VOO]),
([VOO First non empty]-index())))/
ABS (LOOKUP (SUM ( [VOO]), ([VOO First non empty]-index())))

Another tricky part was to return the latest price and price change (percentage) of ETH and BTC as text in the top two charts because there is a delay in returning the newest price in the Google Sheets file. I make sure the percentage change is always calculated according to the latest non-null value, using a concept similar to the last part:

1. Look for non-null value in the Cryptos columns
if not isnull(sum([Btcusd])) then index() end
2. Look for the first and the last non-null cell in the Cryptos columns
2a. First non-null value
WINDOW_MIN([Btc non empty])
2b. Last non-null value
WINDOW_MAX([Btc non empty])
3. Return the latest price of the Crypto
LOOKUP (SUM ( [Btcusd]), ([Btc Last non empty ]-index()))
4. Return the percentage change according to the latest non-null value
(LOOKUP (SUM ([Btcusd]),
([Btc Last non empty ]-index())) -
LOOKUP (SUM ([Btcusd]),
([Btc First non empty]-index())))/
ABS (LOOKUP (SUM ( [Btcusd]), ([Btc First non empty]-index())))

Most of the other works were just standard drag-and drop in Tableau.

Final words

I just started using Tableau in the past few months, so my solutions are probably not the smartest/cleanest way to achieve the result. If you figure out a better way to build similar functions, please feel free to leave a comment.

Thanks for reading, and special thanks to all the people who provided Tableau tutorials online, I referenced a lot of resources so I cannot list them all here, but without the online community, it would be a lot harder for me to finish this dashboard.

Cheers datafam :)

Disclaimer: This article and the dashboard are for information purpose only and are not intended for trading purpose.

Join Coinmonks Telegram Channel and Youtube Channel learn about crypto trading and investing