Excel - what is the formula to show the year-to-date change of the stock price (by comparing the latest price everyday against the price on close price of 2 January 2025) of a particular company

Hi, Kin Wai1

Welcome to the Microsoft Community.

To calculate the year-to-date (YTD) change of a stock price in Excel by comparing the latest price each day against the closing price on January 2, 2025, you can use the STOCKHISTORY function along with some basic formulas.

Ref***: 《*** STOCKHISTORY function - Microsoft Support 》

**Important:**The STOCKHISTORY function requires a Microsoft 365 Personal, Microsoft 365 Family, Microsoft 365 Business Standard, or Microsoft 365 Business Premium subscription.

1. Get the closing price on January 2, 2025:
   • Assume the stock ticker symbol is in cell A1 (e.g., "AAPL" for Apple).
   • Use the STOCKHISTORY function to get the closing price on January 2, 2025: =STOCKHISTORY(A1, "2025-01-02", "2025-01-02", 0, 1, 0, 5)
   This formula will return the closing price on January 2, 2025, in the cell where you enter it (e.g., B1).
2. Get the latest stock price:
   • Use the STOCKHISTORY function to get the latest closing price: =STOCKHISTORY(A1, TODAY(), TODAY(), 0, 1, 0, 5)
   This formula will return the latest closing price in the cell where you enter it (e.g., B2).
3. Calculate the YTD change:
   • Use the following formula to calculate the YTD change: excel =(B2 - B1) / B1 This formula calculates the percentage change by subtracting the closing price on January 2, 2025, from the latest price, dividing the result by the closing price on January 2, 2025, and then multiplying by 100 to get the percentage.

Here's a step-by-step example:

1. Enter the stock ticker symbol in cell A1 (e.g., "AAPL").
2. Enter the formula to get the closing price on January 2, 2025 in cell B1:

   =STOCKHISTORY(A1, "2025-01-02", "2025-01-02", 0, 1, 0, 5)
   

3. Enter the formula to get the latest closing price in cell B2:

   =STOCKHISTORY(A1, TODAY(), TODAY(), 0, 1, 0, 5)
   

4. Enter the formula to calculate the YTD change in cell C1:

   =(B2 - B1) / B1
   

This will give you the year-to-date change of the stock price as a percentage.

If you have any more questions or need further assistance, feel free to ask!

Don't worry if the problem is not resolved, share your results and we will provide you with more support.

Best regards，

Cherry.Z - MSFT | Microsoft Community Support Specialist

Noted with thanks. That is excellent. But my excel version cannot directly recognise the stock ticker symbol. can you help me design a link so that I can get real time stock price? Many thanks