Preferred Shares (Historical) Formula in Excel
Looking to analyze historical data for a company’s outstanding preferred shares? The Preferred Shares (Historical) formula in Excel with MarketXLS returns the value of preferred shares issued by a specific company over a designated period. This helps investors and analysts gain insights into how a company’s preferred obligations have evolved over time.
Understanding Preferred Shares (Historical)
Preferred shares exhibit characteristics of both bonds and common stock, often paying fixed dividends and having priority over common stock in case of liquidation. By using the Preferred Shares (Historical) formula, you can:
- Monitor long-term trends in a company’s capital structure.
- Compare changes in preferred shares across different years and quarters.
- Evaluate the impact of preferred share issues on a company’s equity and debt profile.
? Pro Tip: Utilize historical preferred share data alongside other fundamentals (e.g., revenue, cost of revenue) for a comprehensive financial analysis.
Syntax and Parameters
Use the following syntax in Excel:
=hf_Preferred_Shares(Symbol, Year, [Quarter], [TTM])| Parameter | Description | Required | Example |
|---|---|---|---|
| Symbol | The security’s ticker symbol. Supports stocks, indices, options, and crypto. | Yes | "MSFT", "^SPX", "@MSFT 110122C00020000", "BTCUSD:DEFAULT" |
| Year | The specific year or keyword for dynamic lookup: "lq" (last quarter), "ly" (last year), or "lt" (last 12 months). | Yes | "2022", "lq", "ly-1" |
| Quarter | The calendar quarter (1-4). Defaults to "1" if omitted. | No | "2" |
| TTM | Use "TTM" to calculate trailing twelve months values. Leave empty if not required. | No | "TTM" |
?? Note:
• If an invalid symbol or license is detected, the function returns "NA".
• Passing special placeholders like "lq" automatically fetches the last quarterly value, and so on.
Return Value
Returns the numerical value of preferred shares for the specified period or "NA" if the data (or license) is invalid.
Performance Considerations
Frequent or repeated calls to external data within large spreadsheets may slow down recalculations. Consider using caching or limiting calls where possible.
Examples and Usage
Below are some practical ways to use the Preferred Shares (Historical) formula:
-
Retrieve preferred shares for a full year:
=hf_Preferred_Shares("MSFT", 2022) -
Fetch data from the last quarter automatically:
=hf_Preferred_Shares("MSFT", "lq") -
Specify a particular quarter and TTM:
=hf_Preferred_Shares("^SPX", 2022, 3, "TTM") -
Retrieve for options or crypto:
=hf_Preferred_Shares("@MSFT 110122C00020000", "ly-1") =hf_Preferred_Shares("BTCUSD:DEFAULT", "2023", "1")
? Pro Tip: Combine this formula with other MarketXLS historical functions like Revenue (Historical) or Gross Profit (Historical) to compare how preferred shares stack up against the company’s broader financial performance.
Common Questions
-
What if I get "NA"?
- "NA" appears if your MarketXLS license is invalid, the symbol is incorrect, or there is no data for the specified period.
-
How can I leverage TTM values effectively?
- Use the TTM parameter when you need rolling 12-month data rather than a specific quarter or annual snapshot.
-
Is there a way to handle dates directly?
- You can use placeholders “ly”, “lq”, or direct year inputs. While this function is primarily structured around year and quarter, you can incorporate date logic in your workbook if needed.
-
Which other MarketXLS formulas complement Preferred Shares (Historical)?
- Consider combining with functions like Revenue (Historical), Gross Profit (Historical), or R & D Expenses (Historical) to get a fuller picture of the company’s historical fundamentals.
?? Note: For large-scale or real-time data analysis, be mindful of recalculation times and server response delays. Make sure your version of MarketXLS is up to date for best results.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today