Stock Fundamentals in Excel

Start with a company table

Place one ticker per row and reserve columns for company identity before adding metrics. A resolved company name and exchange help catch symbols that point to a different listing than intended. Keep raw returned values separate from rankings, scores and formatting.

Use one formula pattern per column and copy it down the table. That makes the workbook easier to inspect than a collection of individually edited cells.

• Ticker and resolved company identity.
• Reporting currency or units when relevant.
• Raw fundamental fields.
• Calculated comparisons and ranks.
• Data refresh and review date.

Use verified functions for core fields

The MarketXLS function registry confirms REVENUE, PERATIO and DIVIDENDYIELD as one-symbol functions. PERATIO returns the trailing-twelve-month P/E ratio and DIVIDENDYIELD returns trailing-twelve-month dividend yield according to their registry descriptions.

Reference the ticker cell rather than typing a symbol into every formula. Preserve empty or unavailable values as missing; do not automatically convert them to zero, because zero revenue and unavailable revenue mean different things.

=REVENUE($A2)
=PERATIO($A2)
=DIVIDENDYIELD($A2)

Keep periods and definitions consistent

A comparison becomes misleading when one column mixes annual, quarterly, trailing and forward values without labels. Name the period basis in the header and document it in a notes sheet. When a metric definition matters, link to its exact MarketXLS formula documentation rather than relying on a short label.

Ratios also require interpretation. A missing or negative P/E can reflect earnings conditions rather than a broken formula. Dividend yield can change with price and should not be treated as a fixed distribution rate.

Validate before ranking companies

Scan for missing values, unit differences and extreme outliers before calculating ranks. Compare a small sample with the current source documentation and record the review date. If a figure is used in an external report, freeze the values and retain the formula workbook as the reproducible source.

Do not use formatting to hide missing data. A visible NA or blank with a note is more honest than a zero that changes an average or rank.

• Confirm every ticker and listing.
• Label TTM, annual, quarterly or forward periods.
• Keep currency and scale visible.
• Investigate outliers before winsorizing or excluding them.
• Separate factual data from screening thresholds and opinions.

Turn the table into analysis carefully

Once the raw layer is stable, calculate peer medians, growth rates or valuation ranges on another sheet. Show the selected peer group and the date of the data. A screen can narrow a research list, but it does not determine suitability or future performance.

Use conditional formatting only after the underlying missing-value rules are defined. Otherwise, visually attractive rankings can amplify a data-quality mistake.

Frequently asked questions