Google Sheets provides a powerful and flexible platform for building custom dashboards and analysis tools. By creating a Stock Market Dashboard in Google Sheets with the EODHD Financial APIs, you can make a robust dashboard that pulls in real-time historical and current market data into Google Sheets.

This tutorial will walk through importing stock data from EODHD into Google Sheets, setting up functions to fetch live quotes, and visualizing the data through charts and summaries.

You can also check our guides for How to Create Google Sheets Portfolio Tracker and EODHD Google Sheets Add-in. Here you’ll find more information on our Ready-to-Go solutions.

This is a usable Google Sheets example.

Register & Get Data

Copy Google Sheets Stock Market Dashboard

To create a new, independent version of the spreadsheet (any changes made to the copied spreadsheet will not affect the original spreadsheet), follow the steps:

  1. Open the Google Sheets spreadsheet.
  2. Click “File” in the top menu.
  3. From the dropdown menu, select “Make a copy”.
  4. In the “Copy document” dialog box, you can customize the options:
    • Enter a name for the copied spreadsheet in the “Name” field.
  5. Click the “Make a copy” button.
Copy Google Spreadsheet

Connect Your Google Sheets to EODHD API

Using the API requires registering and getting your personal API key. However, you can follow the article using the ‘demo’ key for the tickers AAPL, MSFT, TSLA, and MCD.

Register & Get Data

To start with, you need to make yourself familiar with the basic functionality that the EODHD APIs provide.

The “=IMPORTDATA(URL)” function in Google Sheets allows you to import data from an URL directly into cells. We’ll use this to fetch data from the EODHD API endpoints. The IMPORTDATA function can fetch and parse both CSV and JSON formats into the table. However, the function handles the formats slightly differently, and you need to test which output satisfies your needs.

Examples of EODHD APIs Usage in Google Spreadsheet

In a new Google Sheet, enter the following formula to pull in the list of supported exchanges:

=IMPORTDATA("https://eodhd.com/api/exchanges-list/?api_token=YOUR_API_KEY&fmt=json")

Replace YOUR_API_KEY with your actual EODHD API key. This will populate the sheet with a JSON array of the exchange data. You can change the field “&fmt=json” into “&fmt=csv” to get a CSV output from the API.

To get a stock ticker list for a particular exchange, you can use the “Get Ticker List API” with the exchange code. For the US market, the code is “US”:

=IMPORTDATA("https://eodhd.com/api/exchange-symbol-list/US?api_token=YOUR_API_KEY&fmt=json")

This will return all active stocks and ETFs on the US market in JSON format.

To import historical end-of-day data for stock, use this formula:

=IMPORTDATA("https://eodhd.com/api/eod/AAPL.US?from=2022-01-01&to=2023-01-01&api_token=YOUR_API_KEY&fmt=json")

This will pull the daily open, high, low, close, and volume for Apple (AAPL) between the date range specified. Adjust the ticker, date range, and your API key as needed.

The Live API also provides a live prices endpoint to get delayed quotes. Create a new sheet and enter:

=IMPORTDATA("https://eodhd.com/api/real-time/AAPL.US?api_token=YOUR_API_KEY&fmt=json&filter=close")

This will fetch the current trading price data for Apple Inc. “&filter=close” field helps to get only the close value from the API. To get open/close/volume values, you can delete the filter.

Live data Google Sheet

Create a Sparkline Chart in The Cell

Now that you have imported market data into Google Sheets, you can start building your trading dashboard!

To get a sparkline into cell, you need to IMPORTDATA from a historical API (either intraday or EOD) into Google’s SPARKLINE function.

The cell value will look like this:

=SPARKLINE(IMPORTDATA("https://eodhd.com/api/eod/AAPL?api_token=YOUR_API_KEY&fmt=json&filter=adjusted_close&from=2024-01-01"))

This will create a sparkline based on the end-of-day EODHD data for AAPL from the specified date.

Financial Sparkline Chart in The Cell Google Sheets Dashboard

Treemap Market Cap Chart

For a Treemap Chart of a market, you need to structure the data in 4 columns similar to the following table:

TickersExchMarket Cap($)Change(%)
US{The sum of components market cap}
MSFTUS3,094,183,936,000.00-2.0695
AAPLUS2,665,580,593,152.00-0.2197
NVDAUS2,195,912,523,776.00-0.1222
AMZNUS1,811,770,245,120.00-2.4224
Example of the table structure for a Google Sheets Treemap Chart

The Tickers column will be used to name elements of the chart, Exchange will show to which node value the name belongs, Marketcap will be used for the size of the element, and the Change(%) column for the color. Note that the first line after the header specifies the node and the total sum of the included component weights.

Treemap Market Cap Chart

To get market capitalization, you can use the Fundamentals API with the “&filter=Highlights::MarketCapitalization” field:

=IMPORTDATA("https://eodhd.com/api/fundamentals/AAPL?api_token=YOUR_API_TOKEN&fmt=json&filter=Highlights::MarketCapitalization")

The cell formula will fetch the market cap value for AAPL into the selected cell.

For daily changes in price, you can our Live(delayed) API with field “&filter=change_p”

=IMPORTDATA("https://eodhd.com/api/real-time/AAPL.US?api_token=YOUR_API_KEY&fmt=json&filter=change_p")

The formula will provide the current stock price change for the selected cell.

An example of Google Sheets Stock Market Dashboard is shown below.

Treemap Market Cap Chart Google Sheets Dashboard

Leverage EODHD APIs for your Financial results

You can use your ingenuity to create summaries of your portfolio holdings, import news/sentiment data, analyze pricing trends using charts and sparklines, and set up conditional formatting rules to highlight trading signals.

The EODHD API provides a wide range of data endpoints from pricing to fundamentals to corporate events that you can integrate to create a rich, comprehensive trading dashboard.

By leveraging the power of Google Sheets combined with the high-quality data from EODHD, you can deploy a robust, customizable trading analysis tool to aid your investment decisions. Get started today by signing up for an EODHD API key!

Register & Get Data

Feel free to contact our amazing support to ask for the currently available discounts, we would be more than happy to assist and guide you through the process.

support@eodhistoricaldata.com

Do you enjoy our articles?

We can send new ones right to your email box