Common Stock Equity (Historical) Formula in Excel
This guide explains how to use the Common Stock Equity (Historical) formula in Excel with MarketXLS. By applying this function, you can easily retrieve the total value of a company’s common stock equity for a specific historical period. This helps in conducting financial analysis, understanding ownership structures, and making informed decisions based on credible historical data.
Understanding Common Stock Equity (Historical)
Common Stock Equity (Historical) represents the value of equity ownership in a company as reflected by its outstanding common stock.
- Helps investors assess a company’s financial health over time.
- Useful in comparing equity trends across different business periods.
- Especially valuable for evaluating equity growth or reduction and how it influences broader financial metrics.
Syntax and Parameters
Use the hf_Common_Stock_Equity function to get historical common stock equity data.
=hf_Common_Stock_Equity(Symbol, year, [quarter], [TTM])| Parameter | Description | Required | Example |
|---|---|---|---|
| Symbol | The security identifier (stocks, indices, options, crypto). | Yes | "MSFT", "^SPX", "@MSFT 110122C00020000", "BTCUSD:DEFAULT" |
| year | The targeted year or special keyword. Can also be a date string (the function extracts year). Accepts “lq”, “lq-1”, “ly”, “ly-1”, “lt”, “lt-1”. | Yes | 2022, "ly", "2024-03-15" |
| quarter | A specific quarter (1 through 4). Defaults to 1 if omitted. | No | 1, 2, 3, or 4 |
| TTM | Set as “TTM” for trailing twelve months data. Leave blank for standard data. | No | "TTM" |
?? Note: The function returns a numeric value for valid inputs. If the symbol/license is invalid, or no data is found, “NA” is returned.
? Pro Tip: For best performance, reduce repeated formula calls by referencing cells instead of entering the same parameters multiple times.
Examples and Usage
Below are some practical ways to use hf_Common_Stock_Equity in your Excel worksheets.
- Symbol as cell reference and direct year entry:
=hf_Common_Stock_Equity(A1, 2022) - Direct symbol and date string (function uses the year portion):
=hf_Common_Stock_Equity("MSFT", "2024-03-15") - Using Excel date functions to pass only the year:
=hf_Common_Stock_Equity("MSFT", TEXT(A1,"yyyy")) - Including quarter detail:
=hf_Common_Stock_Equity("MSFT", 2022, 2) - Fetching trailing twelve months (TTM) data:
=hf_Common_Stock_Equity("MSFT", 2022, 3, "TTM") - Using special keywords “lq”, “ly”, “lt” for last quarter, last year, or last 12 months:
=hf_Common_Stock_Equity("MSFT", "lq")=hf_Common_Stock_Equity("MSFT", "ly")=hf_Common_Stock_Equity("MSFT", "lt")
- Example:This returns data from one quarter before the last quarter.
=hf_Common_Stock_Equity("MSFT","lq-1")
Common Questions
1. Why am I getting “NA” as a result?
“NA” can appear if the symbol is invalid, license is inactive, or no historical data is available for the requested period.
2. Does the function support both calendar and fiscal quarters?
Yes. The function captures data based on the specified year and quarter, even if a company’s fiscal cycle slightly differs from the calendar year.
3. Are large spreadsheets slow with multiple references to this function?
Repeated external calls can slow performance. To optimize, reference the same cell for repeated parameters or consolidate your data retrieval.
4. How do I handle options or crypto symbols?
Simply enter them as strings in the Symbol parameter. For example:
• Option: "@MSFT 110122C00020000"
• Crypto: "BTCUSD:DEFAULT"
5. Does “TTM” work if I omit the quarter parameter?
Yes. If you provide “TTM” without specifying a quarter, the function uses the default quarter value of 1 and calculates the trailing twelve months accordingly.
Use this hf_Common_Stock_Equity formula in Excel with MarketXLS to retrieve reliable historical data on common stock equity, enabling better research and analysis for your financial models.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today