Weighted Average Shares Diluted (Historical) Formula in Excel
Weighted Average Shares Diluted (Historical) is a powerful Excel formula offered by MarketXLS that retrieves the weighted average number of a company’s dilutive shares outstanding for a specified historical period. This formula is particularly useful when evaluating a company’s historical performance and trends in share count.
Understanding Weighted Average Shares Diluted (Historical)
Weighted Average Shares Diluted (Historical) helps investors and analysts understand how a company’s outstanding shares have changed over time when accounting for potential dilution. This is a crucial metric for:
- Evaluating EPS (Earnings Per Share) accuracy.
- Comparing share-based metrics across different time periods.
- Getting a historical view of how stock-options and convertible securities might affect share counts.
? Pro Tip: Use this formula alongside historical revenue and expense formulas to get a more holistic picture of a company’s fundamentals over multiple periods.
Syntax and Parameters
Below is the standard syntax to use the Weighted Average Shares Diluted (Historical) formula in Excel with MarketXLS:
=hf_Weighted_Average_Shares_Diluted(Symbol, Year, [Quarter], [TTM])| Parameter | Description | Required | Example |
|---|---|---|---|
| Symbol | The ticker symbol or identifier of the security. Can be regular symbols, indices, options, or crypto pairs. | Yes | "MSFT" or "^SPX" |
| Year | The specific year (e.g., 2022) or special references like "lq", "ly", "lt", plus offsets (e.g., "lq-1"). | Yes | 2022 |
| Quarter | The calendar quarter as a number (1–4). If left blank, defaults to Yearly data. | No | 2 |
| TTM | Set this to "TTM" to retrieve trailing twelve months data, or leave blank for standard data. Also supports "lt". | No | "TTM" (or "lt") |
?? Note: If the symbol is invalid or data is unavailable, the formula returns "NA".
?? Note: This formula requires a valid historical fundamental data subscription in MarketXLS.
Examples and Usage
Below are several examples demonstrating both basic and more advanced usages. Replace "MSFT" with your desired symbol (e.g., "^SPX", "@MSFT 110122C00020000", "BTCUSD:DEFAULT"):
- Retrieve last year’s default data:
=hf_Weighted_Average_Shares_Diluted("MSFT","ly") - Retrieve data for a specific year:
=hf_Weighted_Average_Shares_Diluted("MSFT",2022) - Retrieve data for a specific year and quarter:
=hf_Weighted_Average_Shares_Diluted("MSFT",2022,2) - Retrieve trailing twelve months data for a given year and quarter:
=hf_Weighted_Average_Shares_Diluted("MSFT",2022,3,"TTM") - Retrieve last quarter’s data and the quarter before last:
=hf_Weighted_Average_Shares_Diluted("MSFT","lq") =hf_Weighted_Average_Shares_Diluted("MSFT","lq-1") - Retrieve the last 12 months data and the previous 12 months data:
=hf_Weighted_Average_Shares_Diluted("MSFT","lt") =hf_Weighted_Average_Shares_Diluted("MSFT","lt-1")
? Pro Tip: Combine this formula with Excel’s built-in referencing. For instance, if cell A1 has your ticker symbol, you can use:
=hf_Weighted_Average_Shares_Diluted(A1, "ly")to dynamically change the retrieved data.
Common Questions
-
What does the formula return?
The formula returns a numeric value representing the weighted average of a company’s diluted shares outstanding over the specified time period. -
Why do I get “NA”?
- The symbol might be invalid.
- The data might not be available for your subscription plan.
- There could be a temporary data retrieval issue.
-
How can I improve performance when pulling multiple symbols?
- Use fewer repeated calls by storing results in intermediate cells and referencing them.
- MarketXLS employs caching to reduce repeated external data calls.
-
Can I use this formula for indices or options?
Yes. Just replace Symbol with the relevant index (e.g., "^SPX"), option contract (e.g., "@MSFT 110122C00020000"), or crypto pair (e.g., "BTCUSD:DEFAULT"). -
Do I need to specify both Quarter and TTM every time?
No. Those parameters are optional. If you do not specify them, the function defaults to yearly data (Year-only) or standard quarterly data (Year + Quarter).
?? Note: Always ensure your MarketXLS add-in is updated to the latest version to avoid potential compatibility issues.
- For related historical fundamentals:
- Revenue (Historical)
- Cost Of Revenue (Historical)
- Gross Profit (Historical)
- R & D Expenses (Historical)
- Selling General and Administrative Expense (Historical)
By understanding and leveraging the Weighted Average Shares Diluted (Historical) formula in Excel with MarketXLS, you can assess the impact of dilution on share counts over time and gain deeper insights into a company’s historical performance.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today