Build a Stock Analyzer

Overview

Build a powerful stock analysis dashboard that combines real-time quotes, historical data, and fundamental metrics to evaluate any stock.

Features

Company overview and key statistics
Fundamental metrics comparison
Financial statement data
Valuation ratios
Year-over-year comparisons

Setup

Create Input Cell

Create a designated cell for the ticker symbol you want to analyze. In cell B1, label it “Ticker:” and in C1, enter a default ticker like “AAPL”.

Tip

Name the ticker cell for easier formula references: select C1, then Data > Named ranges in Sheets, or the Name Box to the left of the formula bar in Excel. Call it "TICKER".

Build Company Info Section

Create a company overview section:

// Company Name
=TICKERDATALIVE(TICKER, "Company Name")
// Sector
=TICKERDATALIVE(TICKER, "Sector")
// Industry
=TICKERDATALIVE(TICKER, "Industry")
// Exchange
=TICKERDATALIVE(TICKER, "Exchange")
// Full Time Employees
=TICKERDATALIVE(TICKER, "Full Time Employees")

// Company Name
=TICKERDATA.LIVE(TICKER, "Company Name")
// Sector
=TICKERDATA.LIVE(TICKER, "Sector")
// Industry
=TICKERDATA.LIVE(TICKER, "Industry")
// Exchange
=TICKERDATA.LIVE(TICKER, "Exchange")
// Full Time Employees
=TICKERDATA.LIVE(TICKER, "Full Time Employees")

Add Key Statistics

Create a statistics panel:

// Market Cap
=TICKERDATALIVE(TICKER, "Market Cap")
// P/E Ratio (TTM)
=TICKERDATA(TICKER, "Price To Earnings Ratio", "ttm")
// Beta
=TICKERDATALIVE(TICKER, "Beta")
// Dividend Yield
=TICKERDATALIVE(TICKER, "Dividend Yield")
// 52-Week Range
=TICKERDATALIVE(TICKER, "365 Day Range")

// Market Cap
=TICKERDATA.LIVE(TICKER, "Market Cap")
// P/E Ratio (TTM)
=TICKERDATA.DATA(TICKER, "Price To Earnings Ratio", "ttm")
// Beta
=TICKERDATA.LIVE(TICKER, "Beta")
// Dividend Yield
=TICKERDATA.LIVE(TICKER, "Dividend Yield")
// 52-Week Range
=TICKERDATA.LIVE(TICKER, "365 Day Range")

Price & Valuation Section

// Current Price
=TICKERDATALIVE(TICKER, "Price")
// Market Cap
=TICKERDATALIVE(TICKER, "Market Cap")
// 52-Week Range
=TICKERDATALIVE(TICKER, "365 Day Range")

// Current Price
=TICKERDATA.LIVE(TICKER, "Price")
// Market Cap
=TICKERDATA.LIVE(TICKER, "Market Cap")
// 52-Week Range
=TICKERDATA.LIVE(TICKER, "365 Day Range")

Fundamental Analysis Section

Income Statement Data

// Revenue (TTM)
=TICKERDATA(TICKER, "Revenue", "ttm")
// Net Income (TTM)
=TICKERDATA(TICKER, "Net Income", "ttm")
// EPS
=TICKERDATA(TICKER, "EPS", "ttm")
// Gross Profit
=TICKERDATA(TICKER, "Gross Profit", "ttm")
// Operating Income
=TICKERDATA(TICKER, "Operating Income", "ttm")
// EBITDA
=TICKERDATA(TICKER, "EBITDA", "ttm")

// Revenue (TTM)
=TICKERDATA.DATA(TICKER, "Revenue", "ttm")
// Net Income (TTM)
=TICKERDATA.DATA(TICKER, "Net Income", "ttm")
// EPS
=TICKERDATA.DATA(TICKER, "EPS", "ttm")
// Gross Profit
=TICKERDATA.DATA(TICKER, "Gross Profit", "ttm")
// Operating Income
=TICKERDATA.DATA(TICKER, "Operating Income", "ttm")
// EBITDA
=TICKERDATA.DATA(TICKER, "EBITDA", "ttm")

Profitability Margins

// Gross Margin
=TICKERDATA(TICKER, "Gross Profit Margin", "ttm")
// Operating Margin
=TICKERDATA(TICKER, "Operating Profit Margin", "ttm")
// Net Profit Margin
=TICKERDATA(TICKER, "Net Profit Margin", "ttm")

// Gross Margin
=TICKERDATA.DATA(TICKER, "Gross Profit Margin", "ttm")
// Operating Margin
=TICKERDATA.DATA(TICKER, "Operating Profit Margin", "ttm")
// Net Profit Margin
=TICKERDATA.DATA(TICKER, "Net Profit Margin", "ttm")

Balance Sheet

// Total Assets
=TICKERDATA(TICKER, "Total Assets", 2023)
// Total Debt
=TICKERDATA(TICKER, "Total Debt", 2023)
// Cash
=TICKERDATA(TICKER, "Cash And Cash Equivalents", 2023)
// Total Equity
=TICKERDATA(TICKER, "Total Equity", 2023)

// Total Assets
=TICKERDATA.DATA(TICKER, "Total Assets", 2023)
// Total Debt
=TICKERDATA.DATA(TICKER, "Total Debt", 2023)
// Cash
=TICKERDATA.DATA(TICKER, "Cash And Cash Equivalents", 2023)
// Total Equity
=TICKERDATA.DATA(TICKER, "Total Equity", 2023)

Cash Flow

// Operating Cash Flow
=TICKERDATA(TICKER, "Operating Cash Flow", "ttm")
// Free Cash Flow
=TICKERDATA(TICKER, "Free Cash Flow", "ttm")
// Capital Expenditure
=TICKERDATA(TICKER, "Capital Expenditure", "ttm")

// Operating Cash Flow
=TICKERDATA.DATA(TICKER, "Operating Cash Flow", "ttm")
// Free Cash Flow
=TICKERDATA.DATA(TICKER, "Free Cash Flow", "ttm")
// Capital Expenditure
=TICKERDATA.DATA(TICKER, "Capital Expenditure", "ttm")

Year-over-Year Comparison

Create a multi-year comparison table:

Metric	2023	2022	2021	Growth
Revenue	`=TICKERDATA(TICKER, "Revenue", 2023)=TICKERDATA.DATA(TICKER, "Revenue", 2023)`	`=TICKERDATA(TICKER, "Revenue", 2022)=TICKERDATA.DATA(TICKER, "Revenue", 2022)`	`=TICKERDATA(TICKER, "Revenue", 2021)=TICKERDATA.DATA(TICKER, "Revenue", 2021)`	`=(B-C)/C`
Net Income	`=TICKERDATA(TICKER, "Net Income", 2023)=TICKERDATA.DATA(TICKER, "Net Income", 2023)`	`=TICKERDATA(TICKER, "Net Income", 2022)=TICKERDATA.DATA(TICKER, "Net Income", 2022)`	`=TICKERDATA(TICKER, "Net Income", 2021)=TICKERDATA.DATA(TICKER, "Net Income", 2021)`	formula
EPS	`=TICKERDATA(TICKER, "EPS", 2023)=TICKERDATA.DATA(TICKER, "EPS", 2023)`	`=TICKERDATA(TICKER, "EPS", 2022)=TICKERDATA.DATA(TICKER, "EPS", 2022)`	`=TICKERDATA(TICKER, "EPS", 2021)=TICKERDATA.DATA(TICKER, "EPS", 2021)`	formula

Key Ratios

// Return on Equity
=TICKERDATA(TICKER, "Return On Equity", "ttm")
// Return on Assets
=TICKERDATA(TICKER, "Return On Assets", "ttm")
// Return on Invested Capital
=TICKERDATA(TICKER, "Return On Invested Capital", "ttm")
// Debt to Equity
=TICKERDATA(TICKER, "Debt To Equity Ratio", "ttm")
// Current Ratio
=TICKERDATA(TICKER, "Current Ratio", "ttm")

// Return on Equity
=TICKERDATA.DATA(TICKER, "Return On Equity", "ttm")
// Return on Assets
=TICKERDATA.DATA(TICKER, "Return On Assets", "ttm")
// Return on Invested Capital
=TICKERDATA.DATA(TICKER, "Return On Invested Capital", "ttm")
// Debt to Equity
=TICKERDATA.DATA(TICKER, "Debt To Equity Ratio", "ttm")
// Current Ratio
=TICKERDATA.DATA(TICKER, "Current Ratio", "ttm")

Dividend Analysis

// Dividend Per Share
=TICKERDATALIVE(TICKER, "Last Dividend")
// Dividend Yield
=TICKERDATALIVE(TICKER, "Dividend Yield")
// Dividend Payout Ratio
=TICKERDATA(TICKER, "Dividend Payout Ratio", "ttm")
// 5-Year Dividend Growth
=TICKERDATA(TICKER, "5 YR Dividend GPS", "ttm")

// Dividend Per Share
=TICKERDATA.LIVE(TICKER, "Last Dividend")
// Dividend Yield
=TICKERDATA.LIVE(TICKER, "Dividend Yield")
// Dividend Payout Ratio
=TICKERDATA.DATA(TICKER, "Dividend Payout Ratio", "ttm")
// 5-Year Dividend Growth
=TICKERDATA.DATA(TICKER, "5 YR Dividend GPS", "ttm")

Pro Tips

Use data validation: Create a dropdown list of tickers you frequently analyze
Add conditional formatting: Color-code metrics based on thresholds (e.g., green for ROE > 15%)
Create multiple sheets: Compare stocks side-by-side
Use named ranges: Makes formulas more readable

Tip

Reference the ticker from a single cell (named "TICKER") so you can quickly analyze different stocks by changing just one cell.

Next Steps

Build a Portfolio Tracker
Learn about Valuation Models
Explore Financial Modeling use cases