YTD ( Year to date) Google Sheets Formula

Formula updated – 09/01/2023 (Spreadsheet)
To account for the year-end date falling on a weekend.
Example: 2022 year-end 31/12/2022 was on Saturday, which resulted in google finance retrieving data from 03/01/2023. To work around this issue, I edited the close price formula.

=QUERY(GOOGLEFINANCE(B1,”Close”,CONCAT(“31/12/”,(year(today())-1))-5,CONCAT(“31/12/”,(year(today())-1))),”ORDER BY Col1 DESC LIMIT 1″)

What is YTD

YTD stands for “Year To Date,” which refers to how a stock has performed since the start of the calendar year to the current date.

YTD Calculation

Two price points are required to calculate stock price YTD: the previous year’s close price and the current price. We use the close price of the prior year instead of the opening price of the new year to avoid price gaps between the close and open.
Suppose a stock closed the year trading at 24.44 and now it’s trading at 20.49, we would calculate YTD as per the example below.

YTD = ( ( Today’s Price – Last Year Close Price) / Last year Close Price ) * 100
= ( (20.49 – 24.44) / 24.44) *100
= – 16.16%

YTD For Stock Analysis

YTD is a valuable tool for comparing stocks in the same sector, the underlying holdings in an index, or just a simple market tracker to filter the best & worst-performing stocks.
The bar chart below illustrates how YTD can be used as a stock monitor to monitor the market’s general direction.

---

---

Calculating YTD in Google Sheets

Step 1 – Data Request
Request 2 price points, current price and last year close price.

In our example, Cell B1 = “COUR”.

Step 1.1
Request current price

=GOOGLEFINANCE(B1)

Step 1.2
Request last year’s close price
To automate the retrieval of last year’s close price, we use the CONCAT() function by combing the text “31/12/” with the expression “today’s year – 1”.

=GOOGLEFINANCE(B1,"Close",CONCAT("31/12/",(year(today())-1)))

Step 1.2.1
Remove the date column and header from the close price.

When requesting historical prices, the google finance function will also return a date column and header rows (labels) for the prices requested.

As we only need the close price and not the additional information, we use the query function to remove that information.
(Learn more on how to structure historical price requests using the google query function on this on this post )

=QUERY(GOOGLEFINANCE(B1,"Close",CONCAT("31/12/",(year(today())-1))),"SELECT Col2 label Col2 ''")

Step 2
Combine step 1.1 and step 1.2.1 into a single formula to calculate YTD

Once we combine the formulas, we end up with a unified query to request the YTD in real time.

=(GOOGLEFINANCE(B1)-QUERY(GOOGLEFINANCE(B1,"Close",CONCAT("31/12/",(year(today())-1))-5,CONCAT("31/12/",(year(today())-1)))," SELECT Col2 ORDER BY Col1 DESC LIMIT 1 Label Col2''") )/QUERY(GOOGLEFINANCE(B1,"Close",CONCAT("31/12/",(year(today())-1))-5,CONCAT("31/12/",(year(today())-1)))," SELECT Col2 ORDER BY Col1 DESC LIMIT 1 Label Col2''")

Multiply by 100 or “format as percent” to complete the calculation.

Complete

View and copy the spreadsheet on the link below and tailor the data to your needs.