If you own some cryptocurrencies across multiple exchanges or platforms, you might want a report that shows various cryptocurrency prices in one report. In this article I’ll show you how to build a simple report using Google Sheets and Google Data Studio that allows you to see the latest prices and value of the various Cryptocurrencies you own.

First, the (hopefully) working Cryptocurrency Price dashboard showing Crypto prices

Step 1: Create a Google Sheet that will pull in Cryptocurrency prices

In a new Google Sheet we will need to create the following column names;

Name
Code
URL
Current Price CAD
Amount owned
Current Value CAD
Paid

Once you’ve created these columns you will need to use formulas to pull the data. Below is an example of the formulas used.

Name	Code	URL	Current Price CAD	Amount owned	Current Value CAD	Paid
Bitcoin	BTC	=”https://cryptoprices.cc/”&B2	=IMPORTDATA(C2)*GOOGLEFINANCE(“CURRENCY:USDCAD”)	0.05	=E2*D2	$2,257

The price data comes from cryptoprices.cc. To get data about a currency simply use this formula and replace BTC with whatever code is appropriate for the crypto price you want to check.

Below are some examples.

=IMPORTDATA("https://cryptoprices.cc/BTC/")

=IMPORTDATA("https://cryptoprices.cc/ETH/")

=IMPORTDATA("https://cryptoprices.cc/DOGE/")

A few notes on the formulas. The “Current Price CAD” column pulls in data in USD and then converts it to Canadian dollars. If you want it to convert to a different currency, simply replace CAD with GBP, ZAR, AUD or any other currency. Or remove the *GOOGLEFINANCE(“CURRENCY:USDCAD) section to keep it as USD.

The Name, Code, Amount owned and Paid columns will need to be filled in manually by you. These numbers are randomly generated and not actual amounts owned.

Once you have filled in your spreadsheet, it should look something like what is shown below.

Once your sheet is complete with the cryptocurrencies added it’s time to create the Google Data Studio report.

Step 2: Add your Google Sheet as a data source in Google Data Studio

We begin by creating a new Google Data Studio report. Then we add in our newly created Google Sheet as the Data Source.

Choose the Google Sheets connector to get started with this.

Choose the appropriate Spreadsheet and Worksheet that has your Cryptocurrency data.

Step 3: Change some of your Dimensions to Currency data type

Once you’ve added the data source, it should look something like what is shown below.

For the dimensions, you should change Current Price CAD, Current Value CAD and Paid to a Currency data type depending on which currency you have chosen to display the value in.

To do this click on the dimension and then choose whichever currency you want from the list of available currencies.

Once you’ve made these changes your dimensions should look like this.

Step 4: Create a second sheet in case there are issues with the original sheet

So after connecting my Google Sheet to the Data Studio report it worked fine. When I checked the next day most of the data was not displaying correctly, as shown below.

I haven’t found a solution for what causes this, but suspect it has to do with the formulas in the cells.

If the spreadsheet is giving you hassles, I’d suggest pulling the data into a second sheet.

You can do this by typing the following formula into the A1 cell of a new Google Sheet.

=IMPORTRANGE(“[SPREADSHEET URL}”,”Crypto Price sheet!A1:G100″)

This will pull your data into a new sheet.

Step 5: Create two calculated fields for ROI and ROI%

The next step is to create two calculated fields. One will display ROI (Return on Investment) and the other will display ROI as a percentage.

In the top right hand corner of the data source view click to ‘Add a Field’.

For ROI the formula is:
SUM(Current Value CAD)-SUM(Paid)

For ROI % the formula is
(SUM(Current Value CAD)-SUM(Paid))/SUM(Paid)

Below is an example of how ROI% should look.

Once you’ve added in these extra metrics your data source should look like this.

Once this is complete we can add in the charts in our data studio dashboard.

Step 6: Building your Google Data Studio dashboard

I won’t go into too much detail around how to create the various charts in the Cryptocurrency Price dashboard as they are fairly simple.

Below is the main bar / line chart that shows the current value of each crypto, the value paid for it and the ROI %.

It is a Line chart and not a time series (which shows data over time).

For the main cryptocurrency price table there are various conditional formatting options available.

In order to see which cryptocurrency has increased or decreased in value compared to what you paid for it, I’ve created single colour conditional formatting rules which highlight a cell in green or red depending on whether it is a profit or a loss.

Below is how to create them.

The other components are fairly simple. The five boxes are scorecards, which display a value. I’ve added conditional formatting to both the ROI and ROI % scorecards. The formulas are the same as for the other chart explained above.

I hope you found this helpful if you were interested in building a Cryptocurrency Price report in Data Studio.

My Other Blog Posts

I hope you found this post useful. You might enjoy these other blog posts.