Weighted Average Shares (Historical) Formula in Excel

Discover how to use the Weighted Average Shares (Historical) formula in Excel with MarketXLS to quickly retrieve a company’s weighted average number of shares outstanding for specific historical periods. This function helps investors and analysts make accurate calculations when evaluating earnings per share over time.

Understanding Weighted Average Shares (Historical)

• The Weighted Average Shares (Historical) formula returns a company’s weighted average number of shares outstanding for a given year, quarter, or a trailing twelve-month (TTM) period.
• It’s particularly useful when calculating and analyzing several EPS (Earnings Per Share) metrics accurately.
• Use this formula to:
  • Compare quarter-over-quarter or year-over-year share counts.
  • Improve earnings calculations and trend analysis.
  • Integrate with other historical fundamental functions for comprehensive financial modeling.

Syntax and Parameters

=hf_Weighted_Average_Shares(Symbol, year, [quarter], [TTM])

?? Note: This function requires a valid MarketXLS subscription with historical fundamental data access. If the symbol or data is invalid, the function returns "NA".

Return Value

The function returns a numeric value representing the weighted average number of shares outstanding for the specified period.

Error Handling

• Returns "NA" if an invalid symbol or period is specified, or if data is not available.
• Ensure your MarketXLS license is active to avoid authorization errors.

Examples and Usage

Below are practical ways to use the formula. Replace the symbol and year/quarter/TTM parameters as needed:

=hf_Weighted_Average_Shares("MSFT", 2022)
=hf_Weighted_Average_Shares("MSFT", 2022, 2)
=hf_Weighted_Average_Shares("MSFT", 2022, 3, "TTM")
=hf_Weighted_Average_Shares("MSFT", "lq")      // Last quarter
=hf_Weighted_Average_Shares("MSFT", "lq-1")    // One quarter prior to last quarter
=hf_Weighted_Average_Shares("MSFT", "ly")      // Last year
=hf_Weighted_Average_Shares("MSFT", "ly-1")    // One year prior to last year
=hf_Weighted_Average_Shares("MSFT", "lt")      // Last 12 months
=hf_Weighted_Average_Shares("MSFT", "lt-1")    // Previous last 12 months

? Pro Tip: Combine hf_Weighted_Average_Shares with other historical fundamental formulas (e.g., hf_Revenue, hf_Gross_Profit) for deeper trend analysis.

Symbol Variations

Use different symbol formats:

=hf_Weighted_Average_Shares("MSFT", 2022)
=hf_Weighted_Average_Shares("^SPX", 2022)
=hf_Weighted_Average_Shares("@MSFT 110122C00020000", 2022)
=hf_Weighted_Average_Shares("BTCUSD:DEFAULT", 2022)

While typically purposed for equities, the function supports various symbol types in MarketXLS.

Common Questions

1. Why am I getting "NA"?

   • The function returns "NA" if the symbol is invalid, the data doesn’t exist for the requested period, or the MarketXLS subscription is inactive.

2. How can I use TTM in this formula?

   • Set the TTM parameter to "TTM" and supply the year and quarter. For example:

     =hf_Weighted_Average_Shares("MSFT", 2022, 3, "TTM")

   • This calculates the rolling 12-month average shares through the end of the specified quarter.

3. Does this work for all securities?

   • Primarily, weighted average shares data is most relevant for equities. Indices, crypto, and options symbols may return "NA" or incomplete data, depending on availability.

4. Any performance considerations?

   • When using large datasets, consider minimizing repeated calls by referencing results in separate cells. MarketXLS fetches data from the cloud, and many simultaneous calls can slow performance.

5. What if I need more historical fundamental data?

   • MarketXLS offers a variety of historical fundamentals, such as hf_Revenue, hf_Cost_Of_Revenue, and more. Refer to the MarketXLS knowledge base or contact support for details.

Related Functions:

• Revenue (Historical): Returns the company's total revenue for a specified historical period.
• Cost Of Revenue (Historical): Returns the company's total cost of revenue for a specified historical period.
• Gross Profit (Historical): Returns the company's gross profit for a specified historical period.
• R & D Expenses (Historical): Provides the company's research and development expenses historically.
• Selling General and Administrative Expense (Historical): Retrieves the company's SG&A expenses over time.