Week-to-Date Stock Price Return: Google Sheets Formula

Week-to-Date Stock Price Return: Google Sheets Formula

Last Updated on 08/04/2024 by Ndanileka


Stock price Week-to-Date (WTD) return is a measure of the change in a stock’s price from the beginning of the current trading week to the current date. The WTD return is calculated by subtracting the stock’s previous week’s closing price from its current price and then dividing the result by the previous week’s closing price. The result is then expressed as a percentage, representing the stock’s WTD return.

Week-to-Date Calculation
Week-to-Date Calculation

For example, Chevron had a previous week’s close of 169.45, and today’s current price is 170.00
WTD will be calculated as follows;
= ((Current Date – Previous Week’s Close) / Previous Week Close ) x 100
= ((169.45 – 170.00) /169.45 ) x 100
= 0.32%

To simply our equation, the above can be expressed as;
= (Current Date /Previous Week’s Close) – 1
= (( 170 / 169.45 ) -1 ) x 100
= 0.32%


In this section, we will learn how to retrieve the end date of the previous week. This date will be used to get the close price of the previous week for our WTD formula.

Convert Current Date into Number

When automating calculations in Google Sheets, it is important to ensure that the formula always retrieves the last available closing price from the previous week, even if a full trading week is unavailable due to holidays or other factors.
To achieve this, we need to convert the current day of the week into a number that represents the specific day and can be used for date calculations.

We will use the following functions to convert the day into a number:

  1. WEEKDAY(): This function returns a number that represents the day of the week for a given date.
  2. TODAY(): This function returns the current date.

For example, today’s date is “Wednesday – 08/02/2023”.
By applying the WEEKDAY() function to TODAY(), the formula will return 4, indicating that Wednesday is the fourth day of the week, considering Sunday as the first day.

WEEKDAY  function
Weekday function

If you want to learn more about the WEEKDAY() function, infoinspired.com has a detailed article with multiple use case example

Retrieve the Previous Week End Date

To calculate the end date, we start with today’s date (TODAY()) and subtract the number of days representing the current day of the week (WEEKDAY(TODAY())). This ensures that the resulting date is the end of the previous week.”

The formula returns the previous week's Saturday for a week starting on Sunday.
The formula returns the previous week’s end date, which is Saturday, for a week starting on Sunday.

The default end day in the WEEKDAY() function is Saturday, which is represented by the value 6.


With the formula in place to retrieve the previous week’s Saturday date, we can now use it to request the close prices from that specific week. By incorporating this formula into our data retrieval process, we ensure that we are consistently fetching the close prices from the previous week.

For the example below , Cell B1 = ” CVX” ( Chevron Corporation )

Step 1. Request the previous week’s close price.

Step 1.1 Request close prices from the previous 5 days

To accurately capture the previous week’s close price, we adjust our end date formula by subtracting an additional 5 days. This ensures that we retrieve the prices from Monday to Friday, accounting for any potential non-trading days.
For example, if there was no trading on Friday, we would use the price from Thursday. Similarly, if there was no trading on Thursday, we would use the price from Wednesday, and so on. By incorporating this adjustment, we account for any trading day gaps and ensure that the close price is returned.

Previous Week close prices,  google sheets formula.
Previous Week Close Prices Formula

Step 1.2. Return The Most Recent Close price

Use the QUERY() function to select and sort the “close prices” by date in descending order, limit the request to one value, and remove the column label.

Most Recent Close Price From The Previous Week, Google Sheets Formula
Previous Week Most Recent Close Price Formula

Step 2. Request Today’s Current Price

Current price google sheets formula
Current Price Formula

Step 3 Complete the WTD formula

WTD = (Step 2 / Step 1.2) -1 ) format as percentage

Week-to-Date Google Sheets Formula
Week-to-Date Google Sheets Formula

Leave a Reply

Back To Top

Discover more from DEUS HARVEST

Subscribe now to keep reading and get access to the full archive.

Continue reading