How to build a simple Bitcoin and Ethereum dashboard in Google Data Studio
In this post I’ll show you how to make a simple dashboard that lets you monitor the price of two of the most popular Cryptocurrencies; Bitcoin and Ethereum.
All you need is;
Below is the actual dashboard.
Step 1: Building a Google Sheet for your data
So for our Google Sheet we’re going to keep it simple with three columns;
- Column A: Date
- Column B: Bitcoin (BTC) price
- Column C: Ethereum (ETH) price
For column one type into cell A1 “Date”. This will be the name of the column.
Next type in the formula =TODAY() into cell A2. Below in cell A3 type the formula =A2-1. Drag this formula downwards until you get to cell A1000.
For the BTC column use the formula =index(GoogleFinance( “Currency:BTCUSD” , “close” , $A2),2,2). This will give you the price of BTC in USD for the date specified in cell A2. You can also drag this formula down so it gives you the price for each of the previous days in column A.
This will ideally ensure that we have the current date and the thousand days prior.
For your ETH column simply use the same formula and replace BTC with ETH so your formula is =index(GoogleFinance( “Currency:ETHUSD” , “close” , $A2),2,2).
Now we have our three finished columns as shown below. We’re ready to build out the report in Google Data Studio.
Step 2: Building the Google Data Studio dashboard
The first step is to add your data to the report. Choose the Google Sheets data connector and then the appropriate spreadsheet with your Bitcoin and Ethereum data.
Change the data Type to Currency USD or the currency you’ve chosen to display the price in.
The main component of the dashboard is a time series chart that shows BTC and ETH price over time.
Choose date as your dimension. Enable drill down to be able to drill up and done into smaller or larger unites of measuring time. Choose BTC and ETH as your metrics. Enable Optional metrics in order to be able to view each independently if you so wish.
At the top of the dashboard we’d like to have the latest prices for Bitcoin and Ethereum. So we need to set the Default date range from Auto to Custom and select Today as the value as shown below. We can also add in a comparison date range for the previous period which will give us the previous day’s price.
For our final graph we want to take a longer look back at the historical price of BTC and ETH so we create another time series chart with a custom date range looking at data 1000 days prior.
My Other Blog Posts
Thank you for reading this blog post. You might enjoy the previous ones I’ve written.
I followed your steps and used the exact formula
=index(GoogleFinance( “Currency:BTCUSD” , “close” , $A2),2,2)
But on Google sheets, I see an #ERROR! (Formula Parse Error). Please help me resolve this?
Hi Saurabh,
Try re-typing in the Quotation marks in the formula. If you copy and pasted that could have caused an error.
Let me know if that does not work.
hello!
the command is not working
after I copied and pasted it gave me the error as well, but I deleted the “”s and then put them back and it worked for me. Hope that helps
Thanks it’s really helpful for crypto investors. We can also create stocks trackers with the same formulas.
Thanks again for inspiring us.
Regards,
Yogesh Shinde
Hi Michael,
Amazing looking tool, super helpful instructions, I’m hoping I can get it up and running for myself however Im also having issues with the formula coming up: ”
When evaluating GOOGLEFINANCE, the query for the symbol: ‘CURRENCY:BTCUSD’ returned no data.”
Can you advise here?
There’s often a delay or error posted by the formula.