Common Par (Historical) Formula in Excel

Common Par (Historical) is a powerful Excel formula provided by MarketXLS that allows you to retrieve the per-share amount (par value) displayed on stock certificates or in a company’s articles of incorporation for a specific historical period. This formula helps streamline fundamental analysis by bringing critical data directly into your Excel worksheets.

Understanding Common Par (Historical)

Use this formula when you need:

• The official “par value” for a company's common share as recorded in historical financial statements
• To compare par values across different companies or multiple time periods
• A flexible approach to retrieving either annual, quarterly, or trailing twelve months (TTM) values

Key benefits:

• Automates data retrieval directly in Excel without manual lookup
• Integrates with MarketXLS to ensure accurate and up-to-date company information
• Helps in fundamental and historical analysis by quickly extracting specific per-share details

When to use:

• Historical trend analysis for a firm’s par value
• Building comparisons or longitudinal studies of company fundamentals
• Verifying data for compliance or corporate reporting purposes

Syntax and Parameters

Below is the syntax for the Common Par (Historical) formula in Excel:

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

Return Value:
• Returns a numeric value representing the par value if data is available.
• Returns "NA" if the symbol is invalid, data is unavailable, or your license does not support the function.

?? Note: The function checks your MarketXLS license validity. If the license is invalid or if the symbol is not recognized, it will return "NA."

? Pro Tip: You can reference cells or use Excel date functions for parameters. For instance, you can place "MSFT" in A1 and 2022 in B1, then use:
=hf_Common_par(A1, B1)

Examples and Usage

Below are a few practical examples to illustrate various use cases:

1. Retrieve the value for a specific year:

   =hf_Common_par("MSFT", 2022)

   This returns Microsoft’s common par value for the year 2022.

2. Retrieve for a specific year and quarter:

   =hf_Common_par("MSFT", 2022, 2)

   This returns Microsoft’s par value for Q2 of 2022.

3. Use trailing twelve months (TTM) data:

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

   This returns the TTM par value for the end of Q3, 2022.

4. Leverage special strings for year:

   =hf_Common_par("MSFT", "lq")
   =hf_Common_par("MSFT", "ly")
   =hf_Common_par("MSFT", "lt")

   • "lq" for last quarter
   • "ly" for last year
   • "lt" for last 12 months

5. Handling year offsets:

   =hf_Common_par("MSFT", "lq-1")
   =hf_Common_par("MSFT", "ly-1")
   =hf_Common_par("MSFT", "lt-1")

   • "lq-1" retrieves data for one quarter before the last quarter
   • "ly-1" retrieves data for one year before the last year
   • "lt-1" retrieves data for the previous last 12 months

6. Using cell references or Excel date manipulation:

   =hf_Common_par(A1, B1)
   =hf_Common_par("MSFT", TEXT(C1,"yyyy"), TEXT(C2,"m"))

   • If A1 has "MSFT" and B1 has 2023, returns data for Microsoft in 2023.
   • Dynamically convert cells to correct year/quarter formats using TEXT().

Common Questions

1. What happens if the data is not available for the specified period?
   • The formula will return "NA" if no data is available or if the ticker is invalid.

2. Can I use the function for any asset type (indices, options, crypto)?
   • Yes. Use "^SPX" for indices, "@MSFT 110122C00020000" for options, or "BTCUSD:DEFAULT" for crypto.

3. Does the function affect Excel performance?
   • Generally, it is optimized for quick data retrieval. However, retrieving large sets of data or using the formula extensively in a workbook can lead to increased load times.

4. Should I include a quarter parameter for annual data?
   • No, annual data typically only requires a year value. The quarter parameter is optional but can refine results.

5. How does trailing twelve months (TTM) work here?
   • The TTM option aggregates data from the last 12 months up to the specified quarter or “lt” reference, providing a rolling view of the par value data.

For related metrics, try:

• Revenue (Historical)
• Cost Of Revenue (Historical)
• Gross Profit (Historical)
• R & D Expenses (Historical)
• Selling General and Administrative Expense (Historical)

These formulas give you deeper insights into a company’s overall fundamentals when used together.