YTD ( Year-To-Date) Google Sheets Formula

Last Updated on 13/06/2023 by DEUS Harvest

WHAT IS THE YEAR-TO-DATE (YTD) RETURN?

YTD (Year-to-Date) refers to the cumulative performance of a stock from the beginning of the calendar year to the current date.
It provides an overview of the stock’s performance over the year, taking into account all the price fluctuations and changes that have occurred during that period.
By considering the YTD return, investors can assess the stock’s performance relative to the start of the year and gain insights into its overall trend and performance trajectory.

YEAR-TO-DATE CALCULATION

The calculation of stock price YTD (Year-to-Date) requires two price points:
The previous year’s closing price and the current price.
By using the closing price of the previous year instead of the opening price of the new year, we can avoid potential price gaps that may occur between the closing and opening prices.

To illustrate the calculation, let’s consider an example where 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 = ( ( Current Price – Last Year Close Price) / Last Year Close Price ) * 100
= ( (20.49 – 24.44) / 24.44) *100
= – 16.16%

This calculation shows that the stock has experienced a negative YTD return of 16.16% since the start of the year.

---

---

RETRIEVING THE PREVIOUS YEAR’S END DATE

In this section, we’ll go over the process of getting the date of the previous year-end. We’ll use this date to get the closing price of the previous year and build our Year-to-Date (YTD) formula.

To achieve this, we will utilize the following functions: DATE(), YEAR(), and TODAY().

The DATE() function takes three arguments – year, month, and day – and converts them into a date format. The YEAR() function returns the year specified by a given date, while the TODAY() function retrieves the current date.

To construct the formula for obtaining the previous year’s date, we can use the following expression:

=DATE(YEAR(TODAY()) - 1, 12, 31)

This formula utilizes the DATE() function to create a date by combining the previous year (obtained using YEAR(TODAY()) – 1), the month of December (represented by 12), and the last day of the month (31st).

---

YEAR-TO-DATE GOOGLE SHEETS FORMULA

In our example, Cell B1 = “COUR ” (Coursera Inc)

Step 1. Request Current Price.

=GOOGLEFINANCE(B1)

Step 2. Request Last Year’s Close Price

Step 2.1
Request close prices from the previous 5 Days.

In scenarios where the last day of the year falls on a non-trading day, it is necessary to incorporate buffer days when requesting close prices. This can be achieved by subtracting “- 5” from the start_date and adding “+1” to the end_date argument.

The purpose of this adjustment is to account for the behavior of the GOOGLEFINANCE() function, which retrieves data up until the day before the specified end_date. By subtracting “- 5” from the year-end date formula, we extend the start_date to include additional days prior to the year-end. Additionally, by adding “+1” to the end_date, we ensure that the desired end_date is included in the data retrieval.

=QUERY(GOOGLEFINANCE(B1,"Close",DATE(YEAR(TODAY())-1,12,31)-5,DATE(YEAR(TODAY())-1,12,31)+1))

Step 2.2
Request the last traded price as a single value.

To retrieve the last traded price as a single value and remove the column labels, we can utilize the Query() function. By selecting Col2 (Close Prices) and ordering the data by Col1 (Dates) in descending order, we ensure that the most recent price is returned. To limit our request to only one value, we specify a limit of 1. Additionally, we set the header argument to 0, which removes the column labels from the output:

=QUERY(GOOGLEFINANCE(B1,"Close",DATE(YEAR(TODAY())-1,12,31)-5,DATE(YEAR(TODAY())-1,12,31)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1",0)

Step 3. Complete YTD Formula
To simply our calculation, we will express as YTD equation as follows :

YTD = ( Current Price / Last Year Close Price – 1) *100
YTD = ( Step 1 / Step 2.2 -1) *100

=TO_PERCENT(GOOGLEFINANCE(B1)/QUERY(GOOGLEFINANCE(B1,"Close",DATE(YEAR(TODAY())-1,12,31)-5,DATE(YEAR(TODAY())-1,12,31)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1",0)-1)

---

YEAR-TO-DATE PRICE ANALYSIS

YTD (Year-to-Date) is a valuable tool for comparing stocks within the same sector, analyzing the performance of underlying holdings in an index, or tracking the overall market. It allows us to filter and identify the best and worst-performing stocks based on their performance since the beginning of the calendar year.

The bar chart below provides a visual representation of how YTD can be utilized as a stock monitor to assess the general direction of the market. By plotting the YTD returns of various stocks or indices, we can quickly identify trends and gain insights into the relative performance of different investments. This helps investors make informed decisions and allocate their resources effectively.