Fundamental Analysis of Stocks: Complete Guide With Excel Functions

Fundamental analysis of stocks is the cornerstone method for evaluating a company's intrinsic value by examining its financial statements, business model, competitive position, and economic environment. Unlike technical analysis, which focuses on price patterns and trading volume, fundamental analysis seeks to determine whether a stock is trading above or below its true worth. In this comprehensive guide, we cover every aspect of fundamental analysis — from financial statements and valuation ratios to growth metrics and quality indicators — and show you how to perform the entire analysis in Excel using MarketXLS formulas.

What Is Fundamental Analysis?

Fundamental analysis is the process of evaluating a security's intrinsic value by examining related economic, financial, and qualitative factors. The goal is to determine whether a stock's current market price accurately reflects the company's underlying value.

Two Approaches to Fundamental Analysis

Both approaches ultimately converge on the same goal: identifying stocks whose market price diverges from intrinsic value.

The Three Financial Statements

Every fundamental analysis begins with the three core financial statements filed with the SEC (for US companies):

1. Income Statement (Profit & Loss)

The income statement shows a company's revenues, expenses, and profits over a specific period.

Key line items you can pull with MarketXLS:

=Revenue("AAPL")
=hf_revenue("AAPL", "2024", "2")
=hf_revenue("AAPL", "ly")
=hf_operatingIncome("AAPL", "ly")
=hf_netIncome("AAPL", "ly")
=hf_eps("AAPL", "lq")
=hf_ebitda("AAPL", "ly")

2. Balance Sheet

The balance sheet provides a snapshot of a company's assets, liabilities, and shareholders' equity at a specific point in time.

Key MarketXLS functions:

=hf_totalAssets("MSFT", "ly")
=hf_totalLiabilities("MSFT", "ly")
=hf_totalDebt("MSFT", "ly")
=hf_currentAssets("MSFT", "lq")
=hf_currentLiabilities("MSFT", "lq")

3. Cash Flow Statement

The cash flow statement tracks the actual cash entering and leaving the business.

=hf_freeCashFlow("GOOGL", "ly")
=hf_freeCashFlow("GOOGL", "lt")

Essential Valuation Ratios

Valuation ratios help you determine whether a stock is overvalued, undervalued, or fairly valued relative to its earnings, assets, sales, and cash flow.

Price-to-Earnings Ratio (P/E)

The most widely used valuation metric:

P/E = Stock Price / Earnings Per Share

=PERatio("AAPL")
=Last("AAPL")
=EarningsPerShare("AAPL")

“

Important: P/E ratios must be compared within the same industry. A P/E of 30 might be low for a high-growth tech company but high for a utility.

Price-to-Book Ratio (P/B)

P/B = Stock Price / Book Value Per Share

=PriceToBook("AAPL")
=BookValuePerShare("AAPL")

Useful for asset-heavy businesses (banks, real estate, manufacturing). Less meaningful for asset-light companies (software, services).

Price-to-Sales Ratio (P/S)

P/S = Market Cap / Revenue

=PriceToSales("MSFT")
=MarketCapitalization("MSFT")
=Revenue("MSFT")

Useful for companies that are not yet profitable, where P/E cannot be calculated.

Enterprise Value / EBITDA (EV/EBITDA)

=EnterpriseValue("AAPL")
=EBITDA("AAPL")

EV/EBITDA is preferred by many analysts because it accounts for debt levels and is not affected by depreciation policies.

Dividend Yield

=DividendYield("JNJ")
=DividendPerShare("JNJ")

Profitability Metrics

Profitability metrics reveal how efficiently a company converts revenue into profit.

Margins

=GrossMargin("AAPL")
=OperatingMargin("AAPL")
=ProfitMargin("AAPL")

Return Metrics

=ReturnOnEquity("AAPL")
=ReturnOnAssets("AAPL")

Growth Metrics

Growth drives future earnings and valuation expansion.

=RevenueGrowth("NVDA")
=hf_revenue("NVDA", "ly")
=hf_revenue("NVDA", "2023")

To calculate year-over-year growth manually:

// Revenue growth = (Current Year Revenue - Prior Year Revenue) / Prior Year Revenue
=(hf_revenue("NVDA", "2024") - hf_revenue("NVDA", "2023")) / hf_revenue("NVDA", "2023")

Key Growth Metrics

Financial Health Indicators

Liquidity Ratios

=hf_currentRatio("AAPL", "lq")
=QuickRatio("AAPL")

Leverage Ratios

=hf_debtToEquity("AAPL", "ly")
=hf_totalDebt("AAPL", "ly")

Payout Ratio

=PayoutRatio("JNJ")

A payout ratio above 80% may indicate the dividend is unsustainable unless the company has very stable cash flows.

Building a Complete Fundamental Analysis Dashboard in Excel

Here is how to set up a comprehensive stock analysis template using MarketXLS:

Company Overview Section

Valuation Section

Profitability Section

Growth Section

Financial Health Section

Historical Comparison Section

One of MarketXLS's most powerful features is the hf_ (historical fundamentals) function family with relative time references:

=hf_revenue("AAPL", "ly")    // Last reported year
=hf_revenue("AAPL", "lq")    // Last reported quarter
=hf_revenue("AAPL", "lt")    // Last 12 months (trailing)
=hf_revenue("AAPL", "2024", "2")  // Q2 2024 specifically

Build a multi-year comparison:

The "ly", "lq", and "lt" parameters automatically update when the company files new financial statements, so your spreadsheet stays current without manual adjustments.

Comparison: Fundamental Analysis Methods

Sector-Specific Fundamental Analysis

Different sectors require emphasis on different metrics:

Common Mistakes in Fundamental Analysis

1. Relying on a single metric: No single ratio tells the whole story. Always use multiple metrics across valuation, profitability, growth, and financial health.
2. Ignoring industry context: A P/E of 25 is expensive for a utility but cheap for a high-growth tech company. Always compare within the same industry.
3. Confusing earnings with cash flow: A company can report positive earnings while burning cash. Always check free cash flow alongside earnings.
4. Ignoring the balance sheet: Revenue growth means nothing if funded by unsustainable debt. Check leverage ratios.
5. Anchoring to historical averages: A stock trading below its 5-year average P/E may deserve a lower multiple if fundamentals have deteriorated.
6. Neglecting qualitative factors: Management quality, competitive moat, regulatory environment, and industry trends all matter.

Step-by-Step Fundamental Analysis Process

Step 1: Understand the Business

Before looking at any numbers, understand what the company does, how it makes money, and what its competitive advantages are.

Step 2: Analyze the Income Statement

=hf_revenue("TICKER", "ly")
=hf_operatingIncome("TICKER", "ly")
=hf_netIncome("TICKER", "ly")
=GrossMargin("TICKER")
=OperatingMargin("TICKER")
=ProfitMargin("TICKER")

Look for consistent revenue growth, expanding margins, and growing earnings.

Step 3: Examine the Balance Sheet

=hf_totalAssets("TICKER", "ly")
=hf_totalLiabilities("TICKER", "ly")
=hf_totalDebt("TICKER", "ly")
=hf_currentRatio("TICKER", "lq")
=hf_debtToEquity("TICKER", "ly")

Ensure the company has adequate liquidity, manageable debt, and a solid asset base.

Step 4: Review Cash Flows

=hf_freeCashFlow("TICKER", "ly")
=hf_freeCashFlow("TICKER", "lt")

Free cash flow is often considered the most reliable indicator of financial health because it's harder to manipulate than earnings.

Step 5: Calculate Valuation Ratios

=PERatio("TICKER")
=PriceToBook("TICKER")
=PriceToSales("TICKER")
=DividendYield("TICKER")
=EnterpriseValue("TICKER") / EBITDA("TICKER")

Compare these ratios to industry peers and historical averages.

Step 6: Assess Growth

=RevenueGrowth("TICKER")
=hf_eps("TICKER", "ly")
=hf_eps("TICKER", "2023")

Calculate multi-year growth trends to distinguish between one-time gains and sustainable growth.

Step 7: Compare to Peers

Build a comparison table with 3–5 competitors:

Simply change the ticker symbol in each cell to populate the comparison automatically.

Historical Fundamentals With Relative Time References

One of MarketXLS's most valuable features for fundamental analysis is the ability to use relative time references in hf_ functions:

Important: All quarterly values refer to calendar quarters (Q1 = January–March), not fiscal quarters. This standardization makes cross-company comparisons straightforward.

Spreadsheets using relative references ("ly", "lq", "lt") automatically update when new financial statements are filed, keeping your analysis current without manual intervention.

Fundamental Analysis Checklist

Before making any investment decision based on fundamental analysis, run through this checklist using MarketXLS:

Valuation Check

• P/E ratio compared to industry average: =PERatio("TICKER")
• Price-to-Book for asset-heavy businesses: =PriceToBook("TICKER")
• Price-to-Sales for pre-profit companies: =PriceToSales("TICKER")
• EV/EBITDA compared to peers: =EnterpriseValue("TICKER") / =EBITDA("TICKER")

Profitability Check

• Gross margin trend stable or expanding: =GrossMargin("TICKER")
• Operating margin above industry average: =OperatingMargin("TICKER")
• ROE above 15%: =ReturnOnEquity("TICKER")
• Consistent earnings growth: =hf_eps("TICKER", "ly") vs prior years

Financial Health Check

• Current ratio above 1.5: =hf_currentRatio("TICKER", "lq")
• Debt-to-equity manageable: =hf_debtToEquity("TICKER", "ly")
• Positive free cash flow: =hf_freeCashFlow("TICKER", "ly")
• Dividend sustainable (if applicable): =PayoutRatio("TICKER")

Growth Check

• Revenue growing: =RevenueGrowth("TICKER")
• Earnings keeping pace with revenue: =hf_eps("TICKER", "ly")
• Free cash flow growing: compare =hf_freeCashFlow() across years

This systematic approach ensures you never overlook a critical metric when evaluating stocks.

Fundamental Analysis for Different Investment Styles

For Dividend Investors

Focus on income stability and sustainability:

=DividendYield("KO")
=DividendPerShare("KO")
=PayoutRatio("KO")
=DividendFrequency("KO")
=hf_freeCashFlow("KO", "ly")

A sustainable dividend stock typically has a payout ratio below 70%, consistent free cash flow generation, and a history of maintaining or increasing dividends. Use =DividendFrequency() to verify payment schedules.

For Growth Investors

Prioritize revenue acceleration and margin expansion:

=RevenueGrowth("NVDA")
=GrossMargin("NVDA")
=OperatingMargin("NVDA")
=hf_revenue("NVDA", "lt")
=hf_revenue("NVDA", "2023")

Compare trailing 12-month revenue against prior years to identify acceleration or deceleration patterns. Growing revenue combined with expanding margins is the hallmark of a high-quality growth company.

For Value Investors

Screen for undervaluation relative to fundamentals:

=PERatio("INTC")
=PriceToBook("INTC")
=PriceToSales("INTC")
=BookValuePerShare("INTC")
=hf_freeCashFlow("INTC", "ly")

Value investors look for stocks trading below intrinsic value — low P/E, low P/B relative to peers, and positive free cash flow. The margin of safety concept requires buying at a significant discount to estimated fair value.

Frequently Asked Questions

What is fundamental analysis of stocks?

Fundamental analysis of stocks is the method of evaluating a company's intrinsic value by examining its financial statements, valuation ratios, profitability, growth trends, and financial health. The goal is to determine whether a stock is overvalued, undervalued, or fairly priced relative to its true worth.

What are the most important fundamental analysis metrics?

The most important metrics include P/E Ratio (=PERatio()), Return on Equity (=ReturnOnEquity()), revenue and earnings growth (=RevenueGrowth(), =hf_eps()), profit margins (=GrossMargin(), =ProfitMargin()), and debt levels (=hf_debtToEquity()). No single metric is sufficient — use a combination across valuation, profitability, growth, and health categories.

How do I perform fundamental analysis in Excel?

With MarketXLS, you can perform complete fundamental analysis in Excel using built-in formulas. Use =PERatio(), =MarketCapitalization(), =DividendYield(), and =Revenue() for current data, and =hf_revenue(), =hf_netIncome(), =hf_eps() for historical data. Build dashboards that update automatically when companies file new financial statements.

What is the difference between fundamental and technical analysis?

Fundamental analysis evaluates a company's intrinsic value using financial data (earnings, revenue, assets). Technical analysis studies price patterns, volume, and momentum indicators to predict future price movements. Many investors use both approaches — fundamental analysis for stock selection and technical analysis for timing.

How often should I update my fundamental analysis?

Review fundamental analysis quarterly when companies release earnings reports. The hf_ functions in MarketXLS with relative references ("ly", "lq", "lt") update automatically when new filings are available, reducing manual work significantly.

Can MarketXLS pull historical fundamental data?

Yes. MarketXLS provides the hf_ (historical fundamentals) family of functions that can pull quarterly, yearly, and trailing 12-month data from company financial statements. Functions like =hf_revenue(), =hf_netIncome(), =hf_eps(), and =hf_freeCashFlow() accept year, quarter, and relative time parameters.

Start Your Fundamental Analysis in Excel

Fundamental analysis of stocks becomes significantly more efficient when you can pull all the data you need directly into Excel. MarketXLS provides hundreds of fundamental data functions — from real-time price and valuation ratios to historical quarterly financials — all accessible through simple Excel formulas. Build custom analysis templates, compare multiple companies side by side, and track your portfolio's fundamental health over time.

Ready to power your fundamental analysis with Excel? Explore MarketXLS pricing and plans to get started with comprehensive stock data and analysis functions.