How to make a Stock Market dashboard in Google Data Studio

How to make a Stock Market dashboard in Google Data Studio

In this post I’ll explain the steps to creating a Stock Market dashboard in Google Data Studio. This will display the prices of shares in companies.

I’ve created one below, but you can customize the various stocks shown to what interests you.

1. Create a Google Sheet with Company share prices

The first step is to use one of the Google Finance formulas to get share prices.

For example, the formula below allows us to get share prices for today and a previous point in time.

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])  

For example, the formula below gets the price for Google for today and the previous 365 days.

If we place this in the top left cell of a spreadsheet, we get a list of all the dates and share prices.

Using the Google Finance stock price function in Sheets

We can do this for a range of different stocks, so we can have multiple different share prices.

In this example, I decided to get the share prices for the following companies and place them in a separate sheet within one Google Sheets spreadsheet.

An example of combining various share prices in Google Sheets

Now we have our different share prices, and we can start thinking about creating a Data Studio report with them.

However, we first need to place them into a separate spreadsheet in order for the data to be pulled in correctly to Data Studio from Google Sheets.

2. Create a second Google Sheet to act as a Data Source

Much like the previous post, How to create a five year Currency Exchange Rate report in Google Data Studio, we need to create a second Google Sheet with our data so that it pulls correctly for Data Studio.

To do this we need to use the IMPORTRANGE function.

=IMPORTRANGE(spreadsheet_url, range_string)  

We can then import the data from our Combined data sheet into a new spreadsheet.

An example of the IMPORTRANGE formula in Google sheets

This sheet will be the data source for Google Data Studio.

3. Create the Google Data Studio report

Add in the data from our second Google Sheet into Google Data Studio. We can change the type to Currency > USD.

Various share prices in Google data studio data source

From there, you can do what you want with the Data Studio. In this example report I’ve used scorecards, time series charts to show the data.

The chart below shows the stocks over time.

The data is set up like this;

Data set up in Data Studio

I’ve also setup various scorecards for the different stocks, and added the company logos.

Shares prices of Apple, Microsoft, Twitter, Facebook, Google and Uber displayed in Data Studio report

Because I’ve set the report up to show the close price of the previous day, it’s possible that sometimes these scorecards show “no data”.

There are a lot of different ways to use the Google Finance functions, so spend time playing around and experimenting. You can customize them for your own Stock Market dashboard if that’s something you’d want to make.

Here’s another report I made that shows a five year currency exchange rate.

If you liked this article you might like my other blog posts.

Or you can follow me on Twitter!