What is the STOCKHISTORY Function?

—

by

Himanshu
in

The STOCKHISTORY function in Excel 365 allows you to pull historical stock prices (or other financial instruments) directly from Excel’s data service — without needing external plugins or manual downloads.

📌 Available only in:

  • Microsoft Excel 365 (and Excel for the web)
  • Not available in Excel 2019 or earlier versions

🔧 Syntax of STOCKHISTORY

STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property1], [property2], ...)

📘 Arguments Explained:

ArgumentDescription
stockStock ticker symbol or company name. For example: "TCS.NS", "AAPL"
start_dateThe date to start pulling data from (required).
end_dateOptional. If omitted, only data from start_date is returned.
intervalOptional. Frequency of data: 0 (daily), 1 (weekly), 2 (monthly). Default is daily.
headersOptional. 0 = no header, 1 = headers shown (default), 2 = headers and instrument info.
property1, property2,...Optional. Choose what data you want (default is Date and Close price). Options include: 0=Date, 1=Close, 2=Open, 3=High, 4=Low, 5=Volume

✅ Example Usage

1. Daily Stock Prices for TCS for 1 Month:

=STOCKHISTORY("TCS.NS", "2024-05-01", "2024-05-31")

Returns:

DateClose
01-May-243425.5
02-May-243450.0
……

2. Monthly Closing Price for Apple (AAPL):

=STOCKHISTORY("AAPL", "2024-01-01", "2024-06-01", 2)

Here, 2 means monthly interval.

3. Custom Properties (Open, High, Low, Close, Volume):

=STOCKHISTORY("RELIANCE.NS", "2024-06-01", "2024-06-10", 0, 1, 2, 3, 4, 1, 5)

This returns:

  • Date
  • Open
  • High
  • Low
  • Close
  • Volume

🛑 Notes and Limitations:

  • Requires Internet access.
  • May not work for all international tickers (like small-cap or regional stock exchanges).
  • Ticker symbols for Indian stocks typically end with .NS (for NSE) or .BO (for BSE), e.g., INFY.NS, RELIANCE.BO.
  • Excel might return an error if the ticker or date range is invalid.
  • Weekend and holiday data won’t be shown.

🧠 Use Cases:

  • Create a dynamic stock price tracker.
  • Build an automated portfolio dashboard.
  • Perform technical analysis on historical prices.
  • Use with charts for visualizing trends.

🎓 Want to Learn More About Smart Excel Features?

If you’re interested in mastering powerful Excel tools like STOCKHISTORY, dynamic charts, PivotTables, functions, and automation, check out this highly practical course:

🔗 Mastering MS Excel – A Comprehensive Training Course

✅ Learn:

  • Advanced Excel functions
  • Real-world automation & dashboards
  • Data analysis tools
  • Bonus: Introduction to VBA & macros

🎯 Click here to enroll now!

Top 10 STOCKHISTORY Function Questions and Answers for Excel 365

✅ 1. What is the primary use of the STOCKHISTORY function in Excel 365?

A. Import current stock prices
B. Retrieve historical stock data
C. Display live news feed
D. Generate financial reports automatically

✅ Correct Answer: B

✅ 2. Which version of Excel supports the STOCKHISTORY function?

A. Excel 2016
B. Excel 2019
C. Excel 365
D. Excel 2010

✅ Correct Answer: C

✅ 3. What is the default interval used by STOCKHISTORY if not specified?

A. Weekly
B. Monthly
C. Yearly
D. Daily

✅ Correct Answer: D

✅ 4. What does this formula return?

=STOCKHISTORY("INFY.NS", "2024-05-01", "2024-05-10")

A. Current price of INFY
B. Historical closing prices between May 1 and May 10
C. All financial data of INFY
D. Only stock volumes for INFY

✅ Correct Answer: B

✅ 5. Which property number is used to retrieve the “Volume” data in STOCKHISTORY?

A. 0
B. 1
C. 3
D. 5

✅ Correct Answer: D

✅ 6. In the formula below, what does the last “2” represent?

=STOCKHISTORY("AAPL", "2023-01-01", "2023-12-01", 2)

A. Volume
B. Monthly interval
C. Header type
D. Data column index

✅ Correct Answer: B

✅ 7. Which of the following is NOT a valid argument for the STOCKHISTORY function?

A. Stock ticker
B. Start date
C. Currency symbol
D. Property list

✅ Correct Answer: C

✅ 8. What happens if you try to use STOCKHISTORY in Excel 2019?

A. It works partially
B. It shows the latest stock price
C. It returns an error
D. It pulls data only for NSE stocks

✅ Correct Answer: C

✅ 9. What does the header argument “2” do in the STOCKHISTORY function?

A. Omits column headers
B. Displays only date and close price
C. Adds instrument info and column headers
D. Displays ticker symbol in formula bar

✅ Correct Answer: C

✅ 10. Which of the following tickers is valid for retrieving Indian stock data via STOCKHISTORY?

A. “RELIANCE”
B. “TCS.IN”
C. “TCS.NS”
D. “BOM.TCS”

✅ Correct Answer: C

On sale products