YTD ( Year to date) Calculation in Google Sheets

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 calculation chart ex
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)
The formula fetches the current price [Captured on cell b7]
The formula fetches the current price [Captured on cell b7]

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)))
The formula fetches last year close price  [Captured on cell B10]
The formula fetches last year close price [Captured on cell B10]

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.

Information not required, marked with black line
Information not required, marked with black line

As we only need the close price, and not the additional information, we make use of 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 ''")
The formula fetches last year close price, then removes the date column and header rows [Captured on cell B14]
The formula fetches last year close price, then removes the date column and header rows [Captured on cell B14]

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))),"SELECT Col2 label Col2 ''"))/QUERY(GOOGLEFINANCE(B1,"close",CONCAT("31/12/",(year(today())-1))),"SELECT Col2 label Col2 ''")
Complete YTD formula [Captured on cell B19]
Complete YTD formula [Captured on cell B19]

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

Complete

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.