Fundamental Analysis of Stocks: Complete Guide with Excel Functions

Fundamental analysis of stocks estimates what a company is worth from its financial statements, valuation ratios, profitability, growth, and balance sheet health, then compares that to the share price. Unlike technical analysis, which studies price and volume patterns, it asks whether the stock trades above or below its intrinsic value. In Excel, MarketXLS formulas pull each input: =PERatio("AAPL") for valuation, =ReturnOnEquity("AAPL") for profitability, and =hf_revenue("AAPL", "ly") for historical financials. This guide covers the three financial statements, the key ratios, and a step-by-step dashboard you can build.

See it in actionMarketXLS video

The full MarketXLS demo

Watch full demoBook a demo

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

Email address

Download free workbook

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos +

IntroductionHistorical DataOptions DataUse-cases

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")

Formula documentation: Revenue, hf_revenue, hf_eps, hf_ebitda

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")

Formula documentation: PERatio, Last, EarningsPerShare

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")

Formula documentation: PriceToBook, BookValuePerShare

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")

Formula documentation: PriceToSales, MarketCapitalization, Revenue

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

Enterprise Value / EBITDA (EV/EBITDA)

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

Formula documentation: EnterpriseValue, EBITDA

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")

Formula documentation: DividendYield, DividendPerShare

Profitability Metrics

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

Margins

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

Formula documentation: GrossMargin, OperatingMargin, ProfitMargin

Return Metrics

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

Formula documentation: ReturnOnEquity, ReturnOnAssets

Growth Metrics

Growth drives future earnings and valuation expansion.

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

Formula documentation: RevenueGrowth, hf_revenue

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")

Formula documentation: hf_revenue

Key Growth Metrics

Financial Health Indicators

Liquidity Ratios

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

Leverage Ratios

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

Formula documentation: hf_debtToEquity

Payout Ratio

=PayoutRatio("JNJ")

Formula documentation: PayoutRatio

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

The MarketXLS hf_ (historical fundamentals) function family accepts 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

Formula documentation: hf_revenue

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")

Formula documentation: hf_revenue, GrossMargin, OperatingMargin, ProfitMargin

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")

Formula documentation: hf_debtToEquity

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")

Formula documentation: PERatio, PriceToBook, PriceToSales, DividendYield, EnterpriseValue, EBITDA

Compare these ratios to industry peers and historical averages.

Step 6: Assess Growth

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

Formula documentation: RevenueGrowth, hf_eps

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

MarketXLS hf_ functions accept relative time references, so a sheet keeps pointing at the latest filing:

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")

Formula documentation: DividendYield, DividendPerShare, PayoutRatio, DividendFrequency

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")

Formula documentation: RevenueGrowth, GrossMargin, OperatingMargin, hf_revenue

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")

Formula documentation: PERatio, PriceToBook, PriceToSales, BookValuePerShare

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 is faster when the data lands directly in Excel. MarketXLS provides fundamental data functions for prices (15-minute delayed on the Standard plan, real-time on Advanced and Business), valuation ratios, and historical quarterly financials, all as Excel formulas. Build custom analysis templates, compare multiple companies side by side, and track your portfolio's fundamental health over time.

See MarketXLS pricing and plans to compare plans.