MarketXLS

Inventory (Historical)

Returns the historical inventory balance - goods held for sale in the ordinary course of business. This is a key working capital component for manufacturing and retail companies.

Parameters

Parameter Required Description
Symbol Yes Stock ticker symbol (e.g., WMT, AAPL)
Year Yes Fiscal year (2023) or period code (lq, ly)
Quarter No Quarter number 1-4 (default: 1)
TTM No Set to "TTM" for trailing twelve months

Inventory Components

Type Description
Raw Materials Inputs for production
Work in Process Partially completed goods
Finished Goods Ready for sale
Merchandise Retail inventory for resale

Notes

  • Zero or minimal for service companies
  • High for retailers and manufacturers
  • Seasonality affects levels (holiday buildup)
  • LIFO vs FIFO accounting affects values

Syntax

=hf_Inventory(Symbol, Year, [Quarter], [TTM])
Excel Desktop (Windows)

Parameters

symbolstringRequired

Stock ticker symbol

yearstringRequired

Fiscal year or period code (e.g., 2023, lq, ly)

quarterstring

Calendar quarter (1-4)

TTMstring

Set to 'TTM' for trailing twelve months

Returns

number

Inventory balance

Examples

=hf_Inventory("AAPL", 2023, 4)
Q4 2023 inventory
=hf_Inventory("WMT", "ly")
Last fiscal year
=hf_Inventory("AMZN", 2023, , "TTM")
Trailing period
=hf_Inventory(A1, B1, C1)
From cell references

When to Use

  • Inventory turnover calculation
  • Working capital analysis
  • Days inventory outstanding (DIO)
  • Supply chain efficiency analysis
  • Retail and manufacturing analysis

When NOT to Use

Scenario Use Instead
Need total current assets hf_Current_Assets()
Need COGS hf_Cost_of_Revenue()
Need receivables hf_Trade_and_Non_Trade_Receivables()
Need cash hf_Cash_and_Equivalents()

Common Issues & FAQ

How do I calculate inventory turnover?

Inventory Turnover = COGS / Average Inventory: =hf_Cost_of_Revenue("WMT","ly") / ((hf_Inventory("WMT","ly") + hf_Inventory("WMT","ly-1"))/2)

How do I calculate Days Inventory Outstanding (DIO)?

DIO = (Inventory / COGS) x 365: =(hf_Inventory("WMT","lq") / hf_Cost_of_Revenue("WMT","lq",,"TTM")) * 365

Why is Apple's inventory so low?

Apple uses a just-in-time supply chain with low inventory levels. They turn inventory over quickly. Compare to traditional retailers with higher inventory.

Related Formulas

More MarketXLS Historical Fundamentals formulas you can use in the same worksheet:

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Inventory (Historical) and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions