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.

=GOOGLEFINANCE(“GOOG”,”price”,TODAY()-365,TODAY())

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

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.

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.

The reason for this was that I encountered issues pulling data from the original Google Sheet to Google Data Studio. I’m not entirely sure what the problem was / is but I found creating a second sheet solved it. You might not have this issue.

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.

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.

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;

To get around the issue that some days (for example saturdays and sundays) may have no values you need to change one of the time series settings to ‘Linear Interpolation’. You can find this setting in the Style tab of the line chart.

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

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.

My Other Blog Posts

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

Or you can follow me on Twitter!