Excel stock price on specific date function (MarketXLS)

Dive into understanding how to extract a specific date’s share price in Excel in a jiffy, using simple functions such as STOCKHISTORY or MarketXLS Historical Functions. The data from Excel and the MarketXLS plug-in offers fascinating insights even if the chosen date was a stock market holiday!

How to get specific date stock price in Excel?

To get a specific date’s stock price in Excel, you’ll need the stock’s ticker symbol. Use the “STOCKHISTORY” function. Start by typing “=STOCKHISTORY(” in a cell. Then, input the ticker symbol in quotation marks, followed by the date you want in the format “YYYYMMDD”. It should look like this “=STOCKHISTORY(“AAPL”, 20200101)”. Press “Enter” and Excel will display the stock price on that specific date.

MarketXLS Historical Functions

When using MarketXLS, you can access historical stock market data easily through commands like Open Historical, Close Historical, High Historical, and Volume Historical. All that’s needed is the stock ticker name. For instance, if you need details about Microsoft’s closing price on May 2, 2024, the command would be =Close_Historical(“MSFT”,”2024-05-02″). Also, flexibility is provided as the date can be a cell holding the desired date. This makes data analysis on MarketXLS user-friendly and efficient.

What if the date was a stock market holiday?

MarketXLS Close_Historical functions offer flexible handling of stock market holidays. This function accepts additional arguments and specific instructions on how to manage such dates. You can use the “Include Holidays” argument and set it to True. If done, the formula computes results based on the final price from the previous trading session. By default, the function will showcase an “NA”. This indicates that the specified date was a market holiday. Hence, this function allows users to manage non-trading days effectively.

See the Video below.

The video below show the historical functions of MarketXLS.

Relative historical dates (with =Today()) function

MarketXLS Historical functions allow users to input the date in two different formats. One can either use a string in the “YYYY-MM-DD” format, such as “2024-04-15”, or a dynamic date i.e., “=TODAY()-5”. The latter represents a date that is five days earlier than the current day. This dynamic approach is beneficial as it automatically updates the referred date relative to the present date whenever you refresh your sheet. This auto-updating feature saves time and increases efficiency.

Summary

To extract a specific date’s stock price in Excel, MarketXLS Historical Functions can be used. For example, typing “=STOCKHISTORY(“AAPL”, 20200101)” into a cell will display Apple’s stock price on January 1, 2020. Similarly, “=Close_Historical(“MSFT”,”2024-05-02″)” will show Microsoft’s closing price on May 2, 2024. The functions can also handle stock market holidays, automatically using the final price from the previous trading day if instructed to include holidays. MarketXLS Historical functions also allow for dynamic dates which automatically update relative to the current day.