Microsoft Excel Stock Market Dashboard

Learn how to create a comprehensive Stock Market Dashboard in Microsoft Excel using EODHD Financial APIs.

Microsoft Excel offers a robust platform for creating custom financial dashboards and analysis tools. By leveraging the EODHD Financial APIs, you can build a comprehensive Stock Market Dashboard in Excel that seamlessly integrates real-time historical and current market data.

This tutorial will guide you through the steps of importing stock data from EODHD into Excel, setting up functions to retrieve live quotes, and visualizing the data with charts and summaries.

Additionally, you can explore our guides on creating a Google Sheets Portfolio Tracker and the EODHD Google Sheets Add-on for more ready-to-go solutions.

To follow along with the article, download our Excel Stock Market Dashboard Template:

https://eodhd.com/financial-academy/wp-content/uploads/2024/03/EODHD_Stock_Dashboard.xlsm

Connecting Excel to the EODHD API

To use the EODHD API, you’ll need to register for an API key. However, for this tutorial, you can use the ‘demo’ key, which supports the tickers AAPL, MSFT, TSLA, and MCD.

To import data from the EODHD API into Excel, we’ll utilize the `WEBSERVICE` function, which allows us to fetch data from a URL directly into cells.

In a new Excel spreadsheet, enter the following formula to retrieve the list of supported stock exchanges:

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

Replace `YOUR_API_KEY` with your EODHD API key. This will populate the sheet with a JSON array of exchange data. You can switch “&fmt=json” to “&fmt=csv” to get the data in CSV format instead.

The `WEBSERVICE` function loads all the data into one cell, which is sufficient for a single value. However, for a list of values, it’s better to transform the data using `TEXTSPLIT` and `TRANSPOSE` functions. Here is an example formula:

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

Similarly, to get the stock ticker list for a specific exchange, such as the US market (code “US”):

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

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

Importing Historical EOD Data

To import historical end-of-day (EOD) price data for a stock, use this formula:

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

Adjust the ticker symbol, date range, and API key as needed. This will fetch the daily OHLCV data for Apple within the specified dates.

Getting Live (Delayed) Stock Quotes

The EODHD API also provides a live endpoint for retrieving delayed price quotes. In a new sheet, enter:

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

This fetches the current trading price for AAPL. The “&filter=close” parameter ensures only the last price is returned. Removing the filter will retrieve the full OHLCV quote.

Creating a Treemap Chart in Excel

A treemap chart is an excellent way to visualize hierarchical data, allowing you to easily spot patterns and compare proportions. In the context of the stock market, it can be used to show the relative market capitalizations of companies within a sector or index.

To create a treemap of the largest stocks in a market:

1. Set up a table with four columns:

- Tickers: Stock symbols

- Market Cap: Market capitalization in dollars

- Change (%): Daily percent change

2. Fetch market capitalization data using the EODHD Fundamentals API with the filter set to “Highlights::MarketCapitalization”:

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

3. Get the daily percent change using the Live API with “&filter=change_p”:

=VALUE(WEBSERVICE("https://eodhd.com/api/real-time/"&B50&"?api_token="&$B$2&"&fmt=json&&filter=change_p"))

Here, the `VALUE` function converts text data received from the API into numerical values, which will be used in charts.

4. Create the Treemap:

- Select the data table.

- Go to Insert > Charts > Treemap.

5. Apply Filters and Customize:

- Apply a filter to the range: Select the filtered cells, then go to Home > Sort & Filter > Filter.

- Sort by the change column to create a gradient color for the treemap based on the current stock price change.

6. Customize the chart labels, colors, and styles as needed.

Sparklines, Charts, and Other Visualizations

To create charts from EODHD data:

1. Import the Data:

• Use the WEBSERVICE function with the appropriate API endpoint (EOD, Intraday, Fundamentals, etc.) on a separate sheet.

2. Select the Data for Charting:

• Highlight the data you want to include in your chart.

3. Insert the Chart:

• Go to Insert > Charts and choose the desired chart type, or select a sparkline.

4. Customize the Chart:

• Adjust the chart title, axis labels, series names, and formatting to suit your needs.

By following these steps, you can create a robust and dynamic stock market dashboard in Excel that visualizes key financial data effectively.

With the raw market data imported, you can create various charts and visualizations in Excel:

• Use Insert > Sparklines > Line to generate inline price charts in individual cells.
• Create a traded volume pie chart to visualize the proportion of trading volume.
• Build a histogram of the daily change in % to analyze the distribution of daily price changes.

The EODHD API provides a wealth of fundamental, event-driven, and sentiment data you can incorporate to build a comprehensive, interactive dashboard.

Conclusion

Creating a Stock Market Dashboard in Microsoft Excel using the EODHD Financial APIs provides a powerful and flexible tool for real-time financial analysis and visualization. By following the steps outlined in this tutorial, you can import historical and live stock data, generate interactive treemaps, and create various charts to analyze market trends effectively.

This approach allows you to customize your dashboard to meet specific investment and trading needs, leveraging Excel’s robust features along with the comprehensive data provided by the EODHD API. The ability to integrate and visualize fundamental, event-driven, and sentiment data offers valuable insights, enhancing your decision-making process.

We hope you found this guide useful and that it helps you build an efficient stock market dashboard tailored to your requirements. Happy analyzing!

Please note that this article is for informational purposes only and should not be taken as financial advice. We do not bear responsibility for any trading decisions made based on the content of this article. Readers are advised to conduct their own research or consult with a qualified financial professional before making any investment decisions.

For those eager to delve deeper into such insightful articles and broaden their understanding of different strategies in financial markets, we invite you to follow our account and subscribe for email notifications.

Stay tuned for more valuable articles that aim to enhance your data science skills and market analysis capabilities.