Analyzing Financial Statements Using Excel

Using power of Excel and EODHD APIs for seamless data retrieval and insights.

Evaluating a company’s financial statements is crucial for investors and finance professionals to gauge corporate performance and stability. While this can be a complex task, utilizing Excel for Financial Statement Analysis — enhanced by EODHD’s dynamic APIs — makes the process significantly more efficient.

This guide will walk through creating an Excel template that seamlessly retrieves key financial statement data from EODHD APIs, enabling analysis of any publicly traded company. With coverage of over 150,000 tickers across 70+ global exchanges, EODHD provides structured JSON APIs for fundamentals, stock prices, and more.

For additional insights, explore our guides on Excel Stock Portfolio Tracker and Market Dashboard, offering ready-to-use solutions.

Download our Financial Analysis Excel Template to follow along:
EODHD_Financials_Spreadsheet

Create an EODHD Account and Obtain Your API Key

Sign up for a free EODHD account at https://eodhd.com/register to get your API key. The free plan provides 20 API requests per day and access to one year of historical data. For expanded access, paid plans start at $19.99/month, offering more API calls and up to 30+ years of market data.

To explore the features and test API functionality, you can use the “demo” key, which grants access to Fundamentals and Historical Data for selected tickers: AAPL, TSLA, MSFT, and MCD.

Set Up Your Financial Analysis Spreadsheet

To simplify the process, you can download our template spreadsheet, which follows the steps outlined below. Alternatively, you can create your own by following these instructions:

1. Add Key Financial Line Items

• Include rows for essential data points such as Revenue, Net Income, Total Assets, Liabilities, and Shareholder Equity.

2. Use Formulas for Financial Metrics

• Implement Excel formulas to calculate Revenue Growth %, Profit Margin, Return on Equity (ROE), and other key ratios.

3. Structure the Three Financial Statements

• Organize sections for the Income Statement, Balance Sheet, and Cash Flow Statement to facilitate comprehensive analysis.

4. Enable Dropdown Navigation for Statements

• Use Excel’s dropdown list feature for easy navigation:
• Go to “Data” → “Data Tools” → “Data Validation”
• Select “Allow: List” and enter your predefined statement names (Income Statement, Balance Sheet, Cash Flow).

This setup will provide a structured financial analysis tool that dynamically updates data from EODHD APIs.

Retrieve Data from EODHD API in Excel

To pull financial statement data directly into Excel, use the =WEBSERVICE() function, which allows API calls within spreadsheet cells. This method works with EODHD’s Fundamental and Historical APIs. Thus, It is important to check the official documentation from Microsoft.

To fetch financial statement data for a specific stock, use the Fundamentals API with the following formula:

=LET(raw_data, WEBSERVICE("https://eodhd.com/api/fundamentals/"&$B$5&"?api_token="&$B$2&"&filter=Financials::"&$C$9&"::yearly::"&TEXT(D9, "YYYY-MM-DD")),
filtered_data, FILTERXML("<t><s>"&SUBSTITUTE(MID(raw_data,2,LEN(raw_data)-2),",","</s><s>")&"</s></t>","//s"),
VALUE(SUBSTITUTE(SUBSTITUTE(filtered_data, LEFT(filtered_data, FIND(":", filtered_data)), ""), """", "")))

Explanation of the Formula:

1. Fetch Data from the API

• The WEBSERVICE() function retrieves the financial statement data from EODHD.
• The ticker ($B$5) and API key ($B$2) are dynamically inserted into the API request.

2. Parse the API Response

• The raw_data variable stores the JSON response.
• The FILTERXML() function converts the JSON response into a structured XML format.
• SUBSTITUTE() and MID() functions clean the extracted data by removing unnecessary characters.

3. Convert Data to Numeric Format

• The VALUE() function ensures that the retrieved data is returned as a numeric value, making it usable for calculations.

By leveraging this formula, you can seamlessly integrate financial statement data into Excel, enabling quick and automated financial analysis.

Adding Ratios

There are multiple methods to calculate key financial ratios, allowing for a clearer view of trends over time. Below are two essential metrics that help assess a company’s financial performance:

1. Year-over-Year (YoY) Growth Rate

YoY growth measures the percentage change in earnings from one year to the next. For example, comparing 2023 vs. 2022 earnings:

2023 vs 2022: (27195000000 - 24512000000) / 24512000000 = 10.95%

To apply this across multiple periods, copy the formula across the desired range. The template includes four years, but you can expand it based on your analysis needs.

2. Compound Annual Growth Rate (CAGR)

CAGR represents the average annual growth rate over a defined period. For example, calculating CAGR over 3 years:

CAGR = (Ending Value / Beginning Value)^(1 / Number of Years) - 1 CAGR = (27195000000 / 19269000000)^(1 / 3) - 1 = 12.19%

While YoY Growth highlights yearly fluctuations, CAGR provides a long-term perspective on financial growth.

Retrieving Ratios from EODHD API

To dynamically pull updated ratios into your Excel sheet using the EODHD API, use the following formula:

=LET(raw_data, WEBSERVICE("https://eodhd.com/api/fundamentals/"&$B$5&"?api_token="&$B$2&"&filter=Highlights"),
filtered_data, FILTERXML("<t><s>"&SUBSTITUTE(MID(raw_data,2,LEN(raw_data)-2),",","</s><s>")&"</s></t>","//s"),
SUBSTITUTE(LEFT(filtered_data,FIND(":",filtered_data)-1),"""",""))

How This Formula Works:

1. Fetches Financial Data

• The WEBSERVICE() function pulls data from the Fundamental API, using the ticker from cell $B$5 and API token from $B$2.

2. Extracts Key Ratios

• The FILTERXML() function converts the JSON response into an XML structure.
• The SUBSTITUTE() function removes unnecessary characters to return clean numeric values.

By integrating this formula, you can automate ratio calculations, ensuring your financial statement analysis remains up-to-date.

Apply Conditional Formatting and Charts

Utilize conditional formatting to emphasize trends, such as declining revenue, and generate charts to display the growth rates of key financial metrics over time.

To illustrate financial changes, apply conditional formatting to calculated ratios. This will help visualize variations over a given period for a specific metric. Select the relevant range of values and navigate to Home → Conditional Formatting → Data Bars.

Conclusion

This template, powered by EODHD’s APIs, enables quick access to financial data and in-depth analysis for any stock, delivering key insights efficiently.

Key Benefits of EODHD:

• Global Coverage: 150,000+ stocks, 20,000+ ETFs, and 1,000+ indices across 70+ exchanges.
• Extensive History: 30+ years of fundamental and End-of-Day pricing data.
• Excel Integration: Seamless compatibility with WEBSERVICE for streamlined data retrieval.
• Cost-Effective: Subscription plans starting at $19.99/month.

By combining EODHD’s financial data APIs with Excel’s analytical tools, this structured approach simplifies data retrieval, allowing users to focus on deriving meaningful investment insights.

For any assistance or current discount offers, contact our support team at support@eodhistoricaldata.com. We’re here to help you optimize your investment analysis with EODHD’s comprehensive data solutions.

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.