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.
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”.
Request current price
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”.
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 ''")
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.
View and copy the spreadsheet on the link below and tailor the data to your needs.
I get this error when plugging your formula into Google Sheets “ERROR: Function Quote parameter 3 expects number values. But 31/12/21 is a text and cannot be coerced to a number”. Any suggested fix?
I think it has to do with google sheets locale settings.
1) Try to change the date format “31/12/21 -(DD,MM,YYYY) ” to your locale format. For example, USA would be MM/DD/YYYY.
To view your locale on the spreadsheet go to File > Settings, General.
2) I have my locale set to United Kingdom, changing yours to the same should make the formula work.
Changing to UK did the trick, however the YTD returns are incorrect. Using VOE (an ETF) as an example, the YTD returns differ from those shown on the Vanguard website. Perhaps dividend payments have something to do with it. thanks again
It could be dividend payments, different data source or calculation method.
Also Yahoo Finance has the YTD at – 2.91%
Quick calculation, data from Google Finance:
Last year’s close 150.33
Last close 145.11
YTD = ((145.11- 150.33) / 150.33 ) x 100 = – 3.47%