Building an ESG Dashboard to Monitor Portfolio Risks

Hidden risks can surface anywhere — from utilities facing carbon regulation to tech firms with social controversies. Nearly 80% of institutional investors say ESG factors lower portfolio risk, with a 2024 survey showing 78% link ESG to reduced tail risk and 61% to lower volatility. For analysts, the task is translating ESG into measurable insights alongside financial KPIs.

This guide shows how to build a live ESG dashboard in Excel, Google Sheets, or BI tools using FMP's ESG data. We'll walk through pulling scores for a stock portfolio, comparing sectors like tech vs. utilities, and visualizing sustainability risks that financial metrics alone may miss.

What You'll Build ( ESG Dashboard Overview)

A practical ESG dashboard focuses on a few portfolio-level risk metrics:

• Carbon Intensity (e.g. CO₂ emitted per $ of revenue). A rising carbon intensity signals heavier climate risk in a portfolio.
• Weighted ESG Score (portfolio average). This blends each holding's ESGScore by its portfolio weight to give a single sustainability rating.
• Governance Risk (e.g. low board quality or high controversy counts). Boards lacking independence or facing scandals often translate to higher financial risk.
• Social Risk Factors (e.g. workplace safety, customer data breaches). For example, a cluster of low socialScore companies could flag potential labor or reputation issues.
• Sector Breakdown. ESG norms vary by industry, so dashboards often compare sectors. For instance, utilities usually have high carbon exposure, whereas tech firms often score better on emissions but may lag on labor policies.

Why It Matters

ESG stands for Environmental, Social, and Governance - three pillars capturing a company's impact and practices. A high ESG score generally means a company is managing these factors well.

• Environmental: includes carbon emissions, energy use, waste and related policies.
• Social: covers labor practices, diversity, product safety, and data privacy.
• Governance: refers to board quality, shareholder rights, executive pay, and transparency.

FMP's ESG Benchmark API break ESG into granular metrics: for each company you can retrieve an ESGScore plus sub-scores like environmentalScore, socialScore, and governanceScore.

These components let you diagnose which risks matter most - for instance, the Environmental score often reflects carbon footprint and emissions management.

Getting the ESG Data You Need

To power the dashboard, first fetch ESG data for each ticker. Financial Modeling Prep (FMP) provides free APIs to retrieve ESG scores and ratings.

For example, the FMP ESG Benchmark Comparison API endpoint is:

https://financialmodelingprep.com/stable/esg-benchmark?apikey=api_key

Replace AAPL with your symbol and append &apikey=YOUR_KEY. The ESG Score API gives numeric sub-scores.

A sample response for Apple might look like:

{ "symbol": "AAPL", "environmentalScore": 68.47, "socialScore": 47.02, "governanceScore": 60.8, "ESGScore": 58.76 }

These fields mean Environmental (like carbon, energy), Social, Governance contributions, and a combined ESGScore.

Preparing and Merging the Data

In your dashboard workflow, pull this JSON via an Excel web query, Google Sheets import, or a BI data connector.Next, clean and merge the data.

Align timestamps and sectors if needed. For example, join each stock's ESG scores to its financial data (prices, weights) in a master table. You can use Excel's Power Query or Google Sheets functions to import and merge API data. Then calculate exposures: multiply each score by the security's weight. For instance, if Apple has weight 5% and ESGScore 58.8, its contribution to Weighted ESG Score is 2.94 points. Summing these gives a portfolio average.

Consider this example table of prepped data:

symbol	ESG score	portfolio weight	Contribution
AAPL	58.8	5%	2.94
MSFT	66	25%	16.5
BABA	72	15%	10.8
GOOGL	55	55%	30.25
Weighted Average Score = 60.49

Steps to Build the ESG Dashboard

Here is a streamlined workflow:

1. Connect to FMP

Use FMP's Excel or Google Sheets add-in, or Power Query, to call the ESG endpoints for each stock. Ensure you include your custom apikey.

2. Prepare Data

Filter to relevant securities (e.g. your portfolio list) and time frames.

Convert any letter ratings (A, B, etc.) into numerical risk bands if you want to chart them.

3. Compute Metrics

In your sheet, calculate portfolio aggregates (weighted averages, intensities). As shown in the above example.

4. Merge with Financials

Import price and volume data via FMP's Stock Price and Volume Data API endpoint and join it to the ESG table. This lets you chart ESG vs. financial KPIs.

5. Visualize

Set up Excel/Google sheet charts. Good options include bar charts, line graphs for trends, and gauge or meter charts for single KPIs. Use conditional formatting (e.g. green/yellow/red scales) to flag high-risk holdings.

Visualize ESG Score with Google sheet charts

More On Visualizing Portfolio Risks

An effective ESG dashboard highlights risks at a glance. Such visuals turn raw data into clear risk insights. Useful visuals include:

• Carbon Footprint Chart — bar chart by sector or stock to show which positions contribute most.
• Average ESG Score Trend — line chart tracking weighted ESG score over time.
• ESG Pillar Radar or Bars — show environmental, social, and governance contributions side by side.
• Sector Heatmap — color-coded industry rows by average ESGScore or carbon per sector.
• Rankings of Holdings — sorted bar chart of stocks by carbon intensity or controversies.

Each chart should label or bold key terms (Carbon Intensity, ESG Score, etc.) for clarity. You can even include interactive filters in Power BI to toggle sectors or time frames. Always keep the dashboard updated automatically (e.g. refresh queries daily) so analysts see live exposures.

Visual Examples

Charts or heatmaps can highlight outliers - you might see utilities lighting up in red for carbon vs technology stocks staying green on that metric. Explore how the data from the dashboard tells a story with heatmapping that identifies outliers and clear risk insights.

ESG Score Heatmap

Case Example: Tech vs. Utilities

To illustrate, consider a simplified comparison between a tech stock (Apple Inc.) and a utility company (e.g. Duke Energy).

Apple's ESG data might show a moderate ESGScore and relatively low carbon intensity, reflecting its focus on efficiency. For instance, FMP's sample shows Apple's environmentalScore (68.5) well above its socialScore (47.0).

In contrast, a utility often has very high emissions. Even if it scores well on governance (stable management), its carbon footprint per dollar could be several times higher.

On our dashboard, Apple would appear green on emissions and mid-range on ESG Score, while the utility bars glow red in the carbon chart.

Companies in heavy industries (like oil & gas or utilities) may have “excellent governance” but score poorly on environmental metrics. Similarly, tech and healthcare sectors often show lower environmental risk but may have social or governance flags worth watching.

By scanning these cross-sector contrasts, analysts quickly spot misaligned exposures - for example, too much carbon risk if the portfolio is overweight utilities.

Make it better (Tips for Effective Dashboards)

Use Interactive Filters.

Let users select time periods or specific ESG dimensions (e.g. view only carbon or governance data). This makes the dashboard more dynamic and user-friendly.

Highlight Outliers.

Apply conditional formatting (color scales or icons) to immediately flag companies or sectors that exceed risk thresholds. For instance, color companies red if their carbon intensity is above a benchmark.

Combine ESG with Finance.

Show ESG metrics next to financial KPIs. For example, plot carbon emissions vs. stock returns to see if high-polluters underperform. This contextualizes ESG in investment decisions.

Document Assumptions.

Clearly note data sources and calculation methods (e.g. how you compute carbon intensity). This builds trust and ensures reproducibility for other analysts.

By following these practices, your ESG dashboard becomes a strategic tool for risk management. It not only tracks sustainability metrics, but also ties them to investment outcomes. The visuals and summaries help equity and risk analysts make informed calls—such as rotating out of high-emission sectors or engaging with companies on weak ESG pillars.

Turn ESG into Action: Build & Download Your Dashboard

ESG monitoring is how you catch risks before they hit returns. Build your own dashboard—start with weighted ESG score, carbon intensity, and governance alerts, then set automatic refreshes. Use familiar tools (Excel/Sheets/BI) with the FMP ESG Ratings API to keep the view live. Want a head start? Check out my reference solution and download the starter code to customize for your portfolio.

FAQ

What is an ESG dashboard?

An ESG dashboard is a visual report that tracks a portfolio's environmental, social, and governance metrics alongside traditional financial KPIs. It often includes charts of carbon intensity, average ESG scores, governance ratings, etc., helping analysts spot sustainability risks at a glance.

Why include ESG in portfolio analysis?

ESG factors are linked to long-term risk. Studies show most investors view ESG as a way to reduce tail risk and volatility. Monitoring ESG helps avoid companies with hidden liabilities (like large future carbon costs or governance scandals).

How can I get ESG data for my stocks?

You can use data providers like Financial Modeling Prep. FMP offers APIs (e.g. ESG Ratings and ESG Benchmark Comparison) that return scores and risk ratings for any public stock. Simply call their endpoints (with your API key) from Excel, Sheets, or a BI tool to import the data.

Can I build an ESG dashboard without coding?

Yes. Excel or Google Sheets can connect to APIs via built-in data connectors or add-ons. Power BI, Tableau, or Looker Studio can also ingest JSON APIs. No custom code is needed - it's about configuring queries and designing charts.