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 > Data > Named ranges > Name 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")

Add Key Statistics

Create a statistics panel:

// Market Cap
=TICKERDATALIVE(TICKER, "Market Cap")

// P/E Ratio
=TICKERDATALIVE(TICKER, "PE")

// Beta
=TICKERDATALIVE(TICKER, "Beta")

// Dividend Yield
=TICKERDATALIVE(TICKER, "Dividend Yield")

// 52-Week Range
=TICKERDATALIVE(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")

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

Profitability Margins

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

// Operating Margin
=TICKERDATA(TICKER, "Operating Income Ratio", "ttm")

// Net Profit Margin
=TICKERDATA(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)

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

Year-over-Year Comparison

Create a multi-year comparison table:

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

Key Ratios

// Return on Equity
=TICKERDATA(TICKER, "ROE", "ttm")

// Return on Assets
=TICKERDATA(TICKER, "Return On Assets", "ttm")

// Return on Invested Capital
=TICKERDATA(TICKER, "ROIC", "ttm")

// Debt to Equity
=TICKERDATA(TICKER, "Debt To Equity", "ttm")

// Current Ratio
=TICKERDATA(TICKER, "Current Ratio", "ttm")

Dividend Analysis

// Dividend Per Share
=TICKERDATALIVE(TICKER, "Dividend")

// Dividend Yield
=TICKERDATALIVE(TICKER, "Dividend Yield")

// Payout Ratio
=TICKERDATA(TICKER, "Payout Ratio", "ttm")

// 5-Year Dividend Growth
=TICKERDATA(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